<%@LANGUAGE="VBSCRIPT"%> <% '===================================================== ' sdk_versions_json.asp ' Ajax support for table of SDK Versions ' Designed to be driven by the jquery tablescroller '===================================================== %> <% Option explicit ' Essential to get UTF through all the hoops. ie: VÄSTTRAFIK (VTK) Response.ContentType = "text/html" Response.AddHeader "Content-Type", "text/html;charset=UTF-8" Response.CodePage = 65001 Response.CharSet = "UTF-8" %> <% '------------ Variable Definition ------------- Dim result : result = -1 Dim SqlQry Dim rsQry Dim sdk_id : sdk_id = Request("sdk_id") ' Init the output JSON class ' Operations can add data ' Default data will be added at the end Dim oJSON :Set oJSON = New aspJSON Dim newitem ' ' Determine the MAX size of the record set, without any filtering ' Gives bad results when searching Dim MaxCount : MaxCount = 0 SqlQry = "select count(*) as count from SDK_TAGS st where sdk_id = '" & sdk_id &"'" oJSON.data("CountQry") = SqlQry On Error Resume Next objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) If ((NOT rsQry.BOF) AND (NOT rsQry.EOF)) Then MaxCount = rsQry("COUNT") End If rsQry.Close Set rsQry = Nothing ' Basic Header ' iTotalRecords = total records without any filtering/limits ' iTotalDisplayRecords = filtered result count oJSON.data("draw") = CInt(Request.QueryString("draw")) oJSON.data("recordsTotal") = MaxCount 'oJSON.data("recordsFiltered") = MaxCount Dim vName for each vName in Request.QueryString oJSON.data("sReq_" & vName) = Request.QueryString(vName) next ' Extract selected range result = 0 dim firstRow,lastRow firstRow = CInt(Request.QueryString("start")) lastRow = firstRow + CInt(Request.QueryString("length")) ' Define the data items to extract from the database ' An array of items to extract ' Dim dataCols: dataCols = Array ( _ "SDKTAG_ID" ,_ "SDKTAG_NAME" ,_ "DESCRIPTION" ,_ "TO_CHAR(CREATED_STAMP, 'Dy DD-Mon-YYYY HH24:MI:SS') as CREATED_STAMP_TXT" ,_ "USER_NAME as CREATOR_NAME" ,_ "SDK_STATE" ) ' Define array of colums to sort by ' Must match user sort column request Dim sortCols: sortCols = Array ( _ "SDKTAG_ID" ,_ "UPPER(SDKTAG_NAME)" ,_ "UPPER(DESCRIPTION)" ,_ "CREATED_STAMP" ,_ "UPPER(CREATOR_NAME)" ,_ "UPPER(SDK_STATE)" ) ' Dim determine sorting options Dim sortString If Request.QueryString("order[0][column]") <> "" Then sortString = " ORDER BY " & sortCols(CInt(Request.QueryString("order[0][column]"))) sortString = sortString & " " & Request.QueryString("order[0][dir]") Else sortString = " ORDER BY " & sortCols(CInt(1)) & " asc" End If ' Filter (search ) Dim searchString : searchString = "" If Request.QueryString("search[value]") <> "" Then searchString = searchString + " AND upper(SDKTAG_NAME) LIKE upper('%" & Request.QueryString("search[value]") & "%')" End If ' Filter (state) If Request.QueryString("sdkstateFilter") <> "" Then searchString = searchString + " AND UPPER(SDK_STATE) in (" & Request.QueryString("sdkstateFilter") & ")" End If ' Create a list of cols that we need. Avoids ambiguity in selections Dim x,colList,colListJoin For x = Lbound(dataCols) to Ubound(dataCols) colList = colList & colListJoin & dataCols(x) colListJoin = "," Next Dim whereString Dim BasicSql BasicSql = "select " & colList &_ " FROM SDK_TAGS st, USERS u " &_ " where sdk_id = "&sdk_id &_ " AND st.CREATOR_ID = u.USER_ID(+)" &_ whereString &_ searchString &_ sortString SqlQry = "select * from ( "&_ "select a.*, ROWNUM rnum from (" & BasicSql &_ ") a where ROWNUM <= " & lastRow &_ ") where rnum >= " & firstRow ' ' Perform a query without pagination ' Assume the record set is small enough that it won't impact speed ' ' ' Determine the size of the record set ' Gives bad results when searching Dim SqlCount: SqlCount = "select count(*) as count from (" & BasicSql & ")" On Error Resume Next objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) Set rsQry = OraDatabase.DbCreateDynaset( SqlCount, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) If ((NOT rsQry.BOF) AND (NOT rsQry.EOF)) Then MaxCount = rsQry("COUNT") End If rsQry.Close Set rsQry = Nothing ' Basic Header ' iTotalRecords = total records without any filtering/limits ' iTotalDisplayRecords = filtered result count 'oJSON.data("recordsTotal") = MaxCount oJSON.data("recordsFiltered") = MaxCount ' Assist in debug oJSON.data("BasicSql") = BasicSql 'oJSON.data("SqlQry") = SqlQry ' Perform the database query Set oJSON.data("aaData") = oJSON.Collection() On Error Resume Next objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) ' Process each row and return required fields to the user If objEH.Finally Then On Error goto 0 While (NOT rsQry.BOF) AND (NOT rsQry.EOF) Set newitem = oJSON.AddToCollection(oJSON.data("aaData")) Dim ii for ii = 0 to rsQry.Fields.Count - 2 newitem (rsQry.FieldName(ii)) = rsQry.Fields(ii) 'newitem (ii) = rsQry.Fields(ii) Next rsQry.MoveNext Wend End IF rsQry.Close Set rsQry = Nothing ' ' SQL error detection and reporting ' The content of 'error' will be dsplayed to the user. If objEH.LastOraFailed Then oJSON.data("error") = objEH.MessageSummary oJSON.data("emsgDetails") = objEH.MessageDetails oJSON.data("SqlQry") = SqlQry End If 'Return the object Response.Write oJSON.JSONoutput() Set oJSON = Nothing Call Destroy_All_Objects %>