<%@LANGUAGE="VBSCRIPT"%> <% '===================================================== ' sdk_opr_json.asp ' Ajax support for SDK operations ' addSdkName ' getSdkNameData ' updateSdkName ' getSdkNames ' getBaseViews ' getSdkDetails ' getSdkVersions ' getSdkVersionDetails ' updateSdkVersionDetails ' getProjectList ' getReleaseList ' addNewSdkVersion ' deleteSdkVersion ' setSdkState ' setSdkContentState ' '===================================================== %> <% 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 parOpr Dim result Dim SqlQry Dim rsQry parOpr = QStrPar("action") result = -1 ' 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 Function canModify canModify = canActionControl("AdminSdk") If NOT canModify Then result = -4 oJSON.data("error") = 1 oJSON.data("emsgSummary") = "Permission denied" oJSON.data("emsgDetails") = oJSON.data("emsgSummary") End If End Function ' ' Perform the body of the operations within a Sub and use ' On Error Resule Next to catch errors that accur in the code ' On Error Resume Next If (parOpr = "addSdkName") Then If canModify() Then addSdkName ElseIf (parOpr = "getSdkNameData") Then getSdkNameData ElseIf (parOpr = "updateSdkName") Then If canModify() Then updateSdkName ElseIf (parOpr = "getSdkNames") Then getSdkNames ElseIf (parOpr = "getBaseViews") Then getBaseViews ElseIf (parOpr = "getSdkDetails") Then getSdkDetails ElseIf (parOpr = "getSdkVersions") Then getSdkVersions ElseIf (parOpr = "getSdkVersionDetails") Then getSdkVersionDetails ElseIf (parOpr = "getSdkUsage") Then getSdkUsage ElseIf (parOpr = "updateSdkVersionDetails") Then updateSdkVersionDetails ElseIf (parOpr = "getProjectList") Then getProjectList ElseIf (parOpr = "getReleaseList") Then getReleaseList ElseIf (parOpr = "addNewSdkVersion") Then addNewSdkVersion ElseIf (parOpr = "deleteSdkVersion") Then deleteSdkVersion ElseIf (parOpr = "setSdkState") Then setSdkState ElseIf (parOpr = "setSdkContentState") Then setSdkContentState ElseIf (parOpr = "cloneSdkPackages") Then cloneSdkPackages ElseIf (parOpr = "setSdkPackages") Then setSdkPackages Else oJSON.data("error") = 1 oJSON.data("emsgSummary") = "Unknown JSON Operation" oJSON.data("emsgDetails") = "The Requested JSON operation is not supported: " & parOpr End If ' SQL error detection and reporting If objEH.LastOraFailed Then oJSON.data("error") = 1 result = -1 oJSON.data("emsgSummary") = objEH.MessageSummary oJSON.data("emsgDetails") = objEH.MessageDetails oJSON.data("SqlQry") = SqlQry ' ' Detect program errors ElseIf Err.number <> 0 Then result = -3 oJSON.data("error") = 2 oJSON.data("errnum") = Err.number oJSON.data("errtxt") = Err.description oJSON.data("errsrc") = Err.source oJSON.data("emsgSummary") = "Internal VBScript Error:" & Err.number & ":" & Err.description End If On error goto 0 'Write single value oJSON.data("result") = result 'function Sleep(seconds) ' dim oshell, cmd ' set oShell = CreateObject("Wscript.Shell") ' cmd = "cmd.exe /c timeout " & seconds & " /nobreak" ' oShell.Run cmd,0,1 'End function ' 'Sleep(2) ' DEBUG: A Hash of the user provided requests 'Return the object Response.Write oJSON.JSONoutput() Set oJSON = Nothing Call Destroy_All_Objects %> <% '------------------------------------------------- ' Function: addSdkName ' Description: Create a new SDK Name entry Sub addSdkName Dim baseView : baseView = "SDK_" & QStrPar("sdkName") Dim SEQ_view_id ' Add a new entry ' ' Setup for the database access OraDatabase.Parameters.Add "SDKNAME", QStrPar("sdkName"), ORAPARM_INPUT, ORATYPE_VARCHAR2 OraDatabase.Parameters.Add "SDKCOMMENT", QStrPar("sdkComment"), ORAPARM_INPUT, ORATYPE_VARCHAR2 OraDatabase.Parameters.Add "SDKBASEVIEW", baseView, ORAPARM_INPUT, ORATYPE_VARCHAR2 OraDatabase.Parameters.Add "SDKVIEWID", 0, ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "SDKPROJECT", QStrPar("sdkProject"), ORAPARM_INPUT, ORATYPE_NUMBER ' Test existance - simply to provide a Nice Error message SqlQry = "SELECT * FROM SDK_NAMES WHERE SDK_NAME = :SDKNAME" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) On Error GoTo 0 If rsQry.RecordCount <> 0 Then result = -3 oJSON.data("error") = -1 oJSON.data("emsgSummary") = "SDK Name already exists" oJSON.data("emsgDetails") = oJSON.data("emsgSummary") Else ' Test for existance of Base View Name ' SqlQry = "SELECT * FROM VIEWS WHERE UPPER(VIEW_NAME) = UPPER(:SDKBASEVIEW)" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) On Error GoTo 0 If rsQry.RecordCount <> 0 Then ' Use existing base view SEQ_view_id = rsQry("VIEW_ID") OraDatabase.Parameters("SDKVIEWID").Value = SEQ_view_id Else ' Create a new Base View for this SDK ' Return new Base View SEQ_view_id = Get_From_DUAL("SEQ_VIEW_ID.nextval") OraDatabase.Parameters("SDKVIEWID").Value = SEQ_view_id On Error Resume Next OraDatabase.ExecuteSQL _ "INSERT INTO views (view_id, view_name, owner_id, base_view, public_read)"&_ " VALUES ("& SEQ_view_id &", '"& baseView &"', 0,'S','Y')" End If If Err.Number = 0 Then SqlQry = "INSERT INTO SDK_NAMES (SDK_NAME, SDK_COMMENT, VIEW_ID, PROJ_ID) VALUES (:SDKNAME, :SDKCOMMENT, :SDKVIEWID, :SDKPROJECT )" On Error Resume Next OraDatabase.ExecuteSQL SqlQry If Err.Number = 0 Then result = 0 End If End If objEH.CatchORA ( OraSession ) On Error GoTo 0 End If OraDatabase.Parameters.Remove "SDKPROJECT" OraDatabase.Parameters.Remove "SDKVIEWID" OraDatabase.Parameters.Remove "SDKBASEVIEW" OraDatabase.Parameters.Remove "SDKCOMMENT" OraDatabase.Parameters.Remove "SDKNAME" End Sub '------------------------------------------------- ' Function: getSdkNameData ' Description: get data for a specified SDK Name Sub getSdkNameData ' Get Data for an existing entry ' Setup for the database access OraDatabase.Parameters.Add "SDKID", QStrPar("sdkId"), ORAPARM_INPUT, ORATYPE_NUMBER SqlQry = "SELECT * from SDK_NAMES where SDK_ID=:SDKID" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) OraDatabase.Parameters.Remove "SDKID" Dim resultSet : Set resultSet = oJSON.Collection() If ((NOT rsQry.BOF) AND (NOT rsQry.EOF)) Then Dim ii for ii = 0 to rsQry.Fields.Count - 1 resultSet (rsQry.FieldName(ii)) = rsQry.Fields(ii) Next Set oJSON.data("aaData") = resultSet result = 0 End If rsQry.Close Set rsQry = Nothing End Sub '------------------------------------------------- ' Function: updateSdkName ' Description: Update an existing SDK Names entry Sub updateSdkName ' ' OraDatabase.Parameters.Add "SDKID", QStrPar("sdkId"), ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "SDKNAME", QStrPar("sdkName"), ORAPARM_INPUT, ORATYPE_VARCHAR2 OraDatabase.Parameters.Add "SDKCOMMENT", QStrPar("sdkComment"), ORAPARM_INPUT, ORATYPE_VARCHAR2 OraDatabase.Parameters.Add "SDKBASEVIEW", QStrPar("sdkBaseView"), ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "SDKPROJECT", QStrPar("sdkProject"), ORAPARM_INPUT, ORATYPE_NUMBER ' Test existance - simply to provide a Nice Error message SqlQry = "SELECT * FROM SDK_NAMES WHERE SDK_NAME = :SDKNAME AND SDK_ID != :SDKID" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) On Error GoTo 0 If rsQry.RecordCount <> 0 Then result = -3 oJSON.data("error") = -1 oJSON.data("emsgSummary") = "SDK Name already exists" oJSON.data("emsgDetails") = oJSON.data("emsgSummary") Else ' Update an existing entry SqlQry = "UPDATE SDK_NAMES SET SDK_NAME = :SDKNAME, SDK_COMMENT = :SDKCOMMENT, VIEW_ID = :SDKBASEVIEW, PROJ_ID = :SDKPROJECT WHERE SDK_ID = :SDKID" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next OraDatabase.ExecuteSQL SqlQry objEH.CatchORA ( OraSession ) On Error GoTo 0 result = 0 End If OraDatabase.Parameters.Remove "SDKPROJECT" OraDatabase.Parameters.Remove "SDKBASEVIEW" OraDatabase.Parameters.Remove "SDKCOMMENT" OraDatabase.Parameters.Remove "SDKNAME" OraDatabase.Parameters.Remove "SDKID" End Sub '------------------------------------------------- ' Function: getSdkNames ' Description: Get Project information ' Array of: ' SDK_ID ' SDK_NAME ' Parameters: proj_id (optional) If present, limit names to those available in this project Sub getSdkNames Dim proj_id : proj_id = QStrPar("proj_id") ' Get Data for an existing entry ' Setup for the database access If proj_id <> "" Then SqlQry = "SELECT SDK_ID, SDK_NAME from SDK_NAMES WHERE proj_id = " & proj_id & " order by UPPER(SDK_NAME) ASC" Else SqlQry = "SELECT SDK_ID,SDK_NAME from SDK_NAMES order by UPPER(SDK_NAME) ASC" End If objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) Set oJSON.data("aaData") = oJSON.Collection() While (NOT rsQry.BOF) AND (NOT rsQry.EOF) Set newitem = oJSON.AddToCollection(oJSON.data("aaData")) Dim ii for ii = 0 to rsQry.Fields.Count - 1 newitem (rsQry.FieldName(ii)) = rsQry.Fields(ii) Next rsQry.MoveNext Wend result = 0 rsQry.Close Set rsQry = Nothing End Sub '------------------------------------------------- ' Function: getBaseViews ' Description: Get Base Views ' Array of: ' VIEW_ID ' VIEW_NAME Sub getBaseViews ' Get Data for an existing entry ' Setup for the database access SqlQry = "SELECT VIEW_ID, VIEW_NAME from VIEWS WHERE base_view = 'S' order by UPPER(VIEW_NAME) ASC" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) Set oJSON.data("aaData") = oJSON.Collection() While (NOT rsQry.BOF) AND (NOT rsQry.EOF) Set newitem = oJSON.AddToCollection(oJSON.data("aaData")) Dim ii for ii = 0 to rsQry.Fields.Count - 1 newitem (rsQry.FieldName(ii)) = rsQry.Fields(ii) Next rsQry.MoveNext Wend result = 0 rsQry.Close Set rsQry = Nothing End Sub '------------------------------------------------- ' Function: getSdkDetails ' Description: Get Details for a specific SDK Family ' Hash of: ' Lots of stuff ' ' Options: sdk_id - SDK to get Sub getSdkDetails ' Get Data for an existing entry ' Setup for the database access Dim sdk_id : sdk_id = QStrPar("sdk_id") OraDatabase.Parameters.Add "SDK_ID", sdk_id, ORAPARM_INPUT, ORATYPE_NUMBER SqlQry = "SELECT * " &_ "FROM SDK_NAMES " &_ "WHERE SDK_ID = :SDK_ID" ' Perform Query objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) on error goto 0 ' Process Results Dim resultSet : Set resultSet = oJSON.Collection() If ((NOT rsQry.BOF) AND (NOT rsQry.EOF)) Then Dim ii for ii = 0 to rsQry.Fields.Count - 1 resultSet (rsQry.FieldName(ii)) = rsQry.Fields(ii) Next Set oJSON.data("aaData") = resultSet result = 0 End If result = 0 rsQry.Close Set rsQry = Nothing OraDatabase.Parameters.Remove "SDK_ID" End Sub '------------------------------------------------- ' Function: getSdkVersions ' Description: Get SDK Versions information for a given family ' Array of: ' sdktag_id ' sdktag_name ' sdktag_state ' ' Options: sdk_id - Get versions for given sdk family ' active - true: Only active Releases, else use mode ' mode - true: All Releases, otherwise only non-closed releases Sub getSdkVersions ' Get Data for an existing entry ' Setup for the database access Dim sdk_id : sdk_id = QStrPar("sdk_id") OraDatabase.Parameters.Add "SDK_ID", sdk_id, ORAPARM_INPUT, ORATYPE_NUMBER ' ' Limit Selection to non-closed releases ' Dim limit If NOT QStrPar("mode") Then limit = " AND SDK_STATE NOT IN ('D')" End If If QStrPar("active") Then limit = " AND SDK_STATE NOT IN ('D','U')" End If SqlQry = "SELECT SDKTAG_ID,SDKTAG_NAME,SDK_STATE as SDKTAG_STATE from SDK_TAGS" &_ " WHERE SDK_ID = :SDK_ID" & limit &_ " ORDER BY UPPER(SDKTAG_NAME) ASC" ' Perform Query objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) ' Process Results Set oJSON.data("aaData") = oJSON.Collection() While (NOT rsQry.BOF) AND (NOT rsQry.EOF) Set newitem = oJSON.AddToCollection(oJSON.data("aaData")) Dim ii for ii = 0 to rsQry.Fields.Count - 1 newitem (rsQry.FieldName(ii)) = rsQry.Fields(ii) Next rsQry.MoveNext Wend result = 0 rsQry.Close Set rsQry = Nothing OraDatabase.Parameters.Remove "SDK_ID" End Sub '------------------------------------------------- ' Function: getSdkVersionDetails ' Description: Get Details for a specific SDK Version ' Hash of: ' Lots of stuff ' ' Options: sdktag_id - SDK Version to get Sub getSdkVersionDetails ' Get Data for an existing entry ' Setup for the database access Dim sdktag_id : sdktag_id = QStrPar("sdktag_id") OraDatabase.Parameters.Add "SDKTAG_ID", sdktag_id, ORAPARM_INPUT, ORATYPE_NUMBER SqlQry = _ "SELECT st.SDKTAG_ID, " &_ " st.SDKTAG_NAME, " &_ " st.DESCRIPTION, " &_ " st.SDK_ID, " &_ " TO_CHAR(st.CREATED_STAMP, 'Dy DD-Mon-YYYY HH24:MI:SS') as CREATED_STAMP , " &_ " st.CREATOR_ID, " &_ " TO_CHAR(st.STATE_STAMP, 'Dy DD-Mon-YYYY HH24:MI:SS') as STATE_STAMP , " &_ " st.STATE_ID, " &_ " st.SDK_STATE, " &_ " sn.SDK_NAME, " &_ " sn.SDK_COMMENT," &_ " rt.RTAG_NAME," &_ " p.PROJ_NAME" &_ " FROM SDK_TAGS st, " &_ " SDK_NAMES sn," &_ " RELEASE_TAGS rt," &_ " PROJECTS p" &_ " WHERE st.SDKTAG_ID = :SDKTAG_ID " &_ " AND st.SDK_ID = sn.SDK_ID " &_ " AND st.RTAG_ID = rt.RTAG_ID" &_ " AND rt.PROJ_ID = p.PROJ_ID" ' Perform Query objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) on error goto 0 ' Process Results Dim resultSet : Set resultSet = oJSON.Collection() If ((NOT rsQry.BOF) AND (NOT rsQry.EOF)) Then Dim ii for ii = 0 to rsQry.Fields.Count - 1 resultSet (rsQry.FieldName(ii)) = rsQry.Fields(ii) Next Set oJSON.data("aaData") = resultSet result = 0 End If result = 0 rsQry.Close Set rsQry = Nothing OraDatabase.Parameters.Remove "SDKTAG_ID" End Sub '------------------------------------------------- ' Function: updateSdkVersionDetails ' Description: Update specific parts of the SDK Version Meta Data ' ' Options: sdktag_id - SDK Version to update ' sdkTagName - ' sdkTagComment - Sub updateSdkVersionDetails ' Get Data for an existing entry ' Setup for the database access Dim sdktag_id : sdktag_id = QStrPar("sdktag_id") Dim sdkTagName : sdkTagName = QStrPar("sdkTagName") Dim sdkTagComment : sdkTagComment = QStrPar("sdkTagComment") OraDatabase.Parameters.Add "SDKTAG_ID", sdktag_id, ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "SDKTAG_NAME", sdkTagName, ORAPARM_INPUT, ORATYPE_VARCHAR2 OraDatabase.Parameters.Add "DESCRIPTION", sdkTagComment, ORAPARM_INPUT, ORATYPE_VARCHAR2 ' Update an existing entry SqlQry = "UPDATE SDK_TAGS SET SDKTAG_NAME = :SDKTAG_NAME, DESCRIPTION = :DESCRIPTION WHERE SDKTAG_ID = :SDKTAG_ID" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next OraDatabase.ExecuteSQL SqlQry objEH.CatchORA ( OraSession ) On Error GoTo 0 result = 0 OraDatabase.Parameters.Remove "DESCRIPTION" OraDatabase.Parameters.Remove "SDKTAG_NAME" OraDatabase.Parameters.Remove "SDKTAG_ID" End Sub '------------------------------------------------- ' Function: getSdkUsage ' Description: Get SDK Usage information for a given sdktag_id ' Array of (unless countOnly is not empty): ' sdktag_id ' sdktag_name ' sdktag_state ' useCount ' ' Options: sdktag_id - Get information for given sdktag ' countOnly - Just get count (Simple Test) Sub getSdkUsage ' Get Data for an existing entry ' Setup for the database access Dim sdktag_id : sdktag_id = QStrPar("sdktag_id") OraDatabase.Parameters.Add "SDKTAG_ID", sdktag_id, ORAPARM_INPUT, ORATYPE_NUMBER SqlQry = "SELECT DISTINCT p.proj_id, rc.rtag_id, p.PROJ_NAME, rt.RTAG_NAME" &_ " FROM release_content rc," &_ " release_tags rt, PROJECTS p" &_ " WHERE rc.SDKTAG_ID = :SDKTAG_ID" &_ " and rc.RTAG_ID = rt.RTAG_ID" &_ " and p.PROJ_ID = rt.PROJ_ID" &_ " ORDER BY UPPER(p.PROJ_NAME), UPPER(rt.RTAG_NAME)" ' Perform Query objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) ' Process Results If QStrPar("countOnly") = "" Then Set oJSON.data("aaData") = oJSON.Collection() While (NOT rsQry.BOF) AND (NOT rsQry.EOF) Set newitem = oJSON.AddToCollection(oJSON.data("aaData")) Dim ii for ii = 0 to rsQry.Fields.Count - 1 newitem (rsQry.FieldName(ii)) = rsQry.Fields(ii) Next rsQry.MoveNext Wend End If ' Return count too oJSON.data("useCount") = rsQry.RecordCount result = 0 rsQry.Close Set rsQry = Nothing OraDatabase.Parameters.Remove "SDKTAG_ID" End Sub '------------------------------------------------- ' Function: getProjectList ' Description: Get Project information ' Array of: ' proj_id ' proj_name Sub getProjectList ' Get Data for an existing entry ' Setup for the database access SqlQry = "SELECT PROJ_ID,PROJ_NAME from PROJECTS order by PROJ_NAME ASC" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) Set oJSON.data("aaData") = oJSON.Collection() While (NOT rsQry.BOF) AND (NOT rsQry.EOF) Set newitem = oJSON.AddToCollection(oJSON.data("aaData")) Dim ii for ii = 0 to rsQry.Fields.Count - 1 newitem (rsQry.FieldName(ii)) = rsQry.Fields(ii) Next rsQry.MoveNext Wend result = 0 rsQry.Close Set rsQry = Nothing End Sub '------------------------------------------------- ' Function: getReleaseList ' Description: Get Release information for a project ' Array of: ' rtag_id ' rtag_name ' official ' Also ' proj_id ' ' Options: proj_id - Get Releases for given project ' rtag_id - Get Releases for project of this release ' mode - true: All Releases, otherwise only non-closed releases Sub getReleaseList ' Get Data for an existing entry ' Setup for the database access Dim rtag_id : rtag_id = QStrPar("rtag_id") Dim proj_id : proj_id = QStrPar("proj_id") OraDatabase.Parameters.Add "PROJ_ID", proj_id, ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "RTAG_ID", rtag_id, ORAPARM_INPUT, ORATYPE_NUMBER ' ' Limit Selection to non-closed releases ' Dim limit If NOT QStrPar("mode") Then limit = " AND OFFICIAL IN ('N','R','C')" End If Dim where If rtag_id <> "" Then where = " WHERE proj_id = (SELECT PROJ_ID from RELEASE_TAGS where RTAG_ID=:RTAG_ID)" Else where = " WHERE PROJ_ID=:PROJ_ID" End If SqlQry = "SELECT PROJ_ID,RTAG_ID,RTAG_NAME,OFFICIAL from RELEASE_TAGS" &_ where & limit &_ " ORDER BY RTAG_NAME ASC" ' Perform Query objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) ' Process Results Set oJSON.data("aaData") = oJSON.Collection() While (NOT rsQry.BOF) AND (NOT rsQry.EOF) Set newitem = oJSON.AddToCollection(oJSON.data("aaData")) Dim ii for ii = 1 to rsQry.Fields.Count - 1 newitem (rsQry.FieldName(ii)) = rsQry.Fields(ii) Next proj_id = rsQry.Fields(0) rsQry.MoveNext Wend oJSON.data("proj_id") = proj_id result = 0 rsQry.Close Set rsQry = Nothing OraDatabase.Parameters.Remove "RTAG_ID" OraDatabase.Parameters.Remove "PROJ_ID" End Sub '------------------------------------------------- ' Function: addNewSdkVersion ' Description: Add a New Sdk Version ' Returns the SDKTAG_ID if the created entry Sub addNewSdkVersion Dim userId : userId = objAccessControl.UserId() Dim sdkTagId ' Add a new entry ' ' Setup for the database access ' OraDatabase.Parameters.Add "SDKTAG_NAME", QStrPar("sdkName"), ORAPARM_INPUT, ORATYPE_VARCHAR2 OraDatabase.Parameters.Add "DESCRIPTION", QStrPar("sdkComment"), ORAPARM_INPUT, ORATYPE_VARCHAR2 OraDatabase.Parameters.Add "SDK_ID", QStrPar("sdk_id"), ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "RTAG_ID", QStrPar("rtag_id"), ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "CREATOR_ID", userId, ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "STATE_ID", userId, ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "SDKTAG_ID", NULL, ORAPARM_BOTH, ORATYPE_NUMBER ' Test existance - simply to provide a Nice Error message SqlQry = "SELECT * FROM SDK_TAGS WHERE SDKTAG_NAME = :SDKTAG_NAME AND SDK_ID = :SDK_ID" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) On Error GoTo 0 If rsQry.RecordCount <> 0 Then result = -3 oJSON.data("error") = -1 oJSON.data("emsgSummary") = "SDK Verion Name already exists in this SDK family" oJSON.data("emsgDetails") = oJSON.data("emsgSummary") Else SqlQry = "BEGIN "&_ " INSERT INTO SDK_TAGS (SDKTAG_NAME, DESCRIPTION, SDK_ID, RTAG_ID, CREATOR_ID, STATE_ID)" &_ " VALUES (:SDKTAG_NAME, :DESCRIPTION, :SDK_ID, :RTAG_ID, :CREATOR_ID, :STATE_ID)"&_ " RETURNING SDKTAG_ID INTO :SDKTAG_ID;" &_ " END;" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next OraDatabase.ExecuteSQL SqlQry sdkTagId = OraDatabase.Parameters("SDKTAG_ID").Value ' ' Create the initial SDK_CONTENT ' If Err.Number = 0 Then SqlQry = "INSERT INTO SDK_CONTENT ( SDKTAG_ID, PV_ID) SELECT :SDKTAG_ID, pv_id FROM RELEASE_CONTENT WHERE RTAG_ID = :RTAG_ID" OraDatabase.ExecuteSQL SqlQry End If objEH.CatchORA ( OraSession ) On Error GoTo 0 oJSON.data("SDKTAG_ID") = sdkTagId result = 0 End If OraDatabase.Parameters.Remove "SDKTAG_NAME" OraDatabase.Parameters.Remove "DESCRIPTION" OraDatabase.Parameters.Remove "SDK_ID" OraDatabase.Parameters.Remove "RTAG_ID" OraDatabase.Parameters.Remove "CREATOR_ID" OraDatabase.Parameters.Remove "STATE_ID" OraDatabase.Parameters.Remove "SDKTAG_ID" End Sub '------------------------------------------------- ' Function: deleteSdkVersion ' Description: Delete the specified Sdk Version ' Will delete SDK_NAME entry if this is the last one ' sdktag_id - Version to delete ' ' SHOULD have checks to ensure that its not in use Sub deleteSdkVersion Dim sdkTagId : sdkTagId = QStrPar("sdktagId") ' ' Setup for the database access ' OraDatabase.Parameters.Add "SDKTAG_ID", sdkTagId, ORAPARM_INPUT, ORATYPE_NUMBER ' Test to see if the version is in use - simply to provide a Nice Error message SqlQry = "SELECT * FROM SDK_TAGS WHERE SDKTAG_ID = :SDKTAG_ID" 'objEH.ErrorRedirect = FALSE 'objEH.TryORA ( OraSession ) 'On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) 'objEH.CatchORA ( OraSession ) On Error GoTo 0 If rsQry.RecordCount = 0 Then result = -3 oJSON.data("error") = -1 oJSON.data("emsgSummary") = "SDK Verion does not exist" oJSON.data("emsgDetails") = oJSON.data("emsgSummary") Else ' Determine if the SDK is in use ' SqlQry = "SELECT distinct rtag_id FROM RELEASE_CONTENT WHERE SDKTAG_ID = :SDKTAG_ID" Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) If rsQry.RecordCount <> 0 Then oJSON.data("useCount") = rsQry.RecordCount result = 0 Else ' Determine the number of times this SDK_TAG is in use ' Determine the sdk_tag SqlQry = "SELECT st.SDK_ID, st.SDKTAG_ID FROM SDK_TAGS st WHERE SDK_ID IN ( SELECT SDK_ID FROM SDK_TAGS WHERE SDKTAG_ID=:SDKTAG_ID)" Dim sdkId, sdkTagCount objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) If Err.Number = 0 Then sdkTagCount = rsQry.RecordCount oJSON.data("sdkTagCount") = sdkTagCount If ((NOT rsQry.BOF) AND (NOT rsQry.EOF)) Then sdkId = rsQry.Fields(0) oJSON.data("sdkId") = sdkId End If ' Delete content SqlQry = "DELETE FROM SDK_CONTENT WHERE SDKTAG_ID = :SDKTAG_ID" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next OraDatabase.ExecuteSQL SqlQry ' Delete tag If Err.Number = 0 Then SqlQry = "DELETE FROM SDK_TAGS WHERE SDKTAG_ID = :SDKTAG_ID" OraDatabase.ExecuteSQL SqlQry End If If Err.Number = 0 Then ' Possibly delete the Sdk Name entry If sdkTagCount = 1 Then SqlQry = "DELETE FROM SDK_NAMES WHERE SDK_ID = :SDK_ID" OraDatabase.Parameters.Add "SDK_ID", sdkId, ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.ExecuteSQL SqlQry OraDatabase.Parameters.Remove "SDK_ID" oJSON.data("SdkNameRemoved") = sdkId End If End If End If objEH.CatchORA ( OraSession ) On Error GoTo 0 result = 0 End If End If OraDatabase.Parameters.Remove "SDKTAG_ID" End Sub '------------------------------------------------- ' Function: setSdkState ' Description: Update the state of the SDK Release ' Request Parameters ' sdktag_id ' sdk_state Sub setSdkState Dim userId : userId = objAccessControl.UserId() ' ' OraDatabase.Parameters.Add "SDKTAG_ID", QStrPar("sdktagId"), ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "SDK_STATE", QStrPar("sdk_state"), ORAPARM_INPUT, ORATYPE_CHAR OraDatabase.Parameters.Add "STATE_ID", userId, ORAPARM_INPUT, ORATYPE_NUMBER ' Update an existing entry SqlQry = "UPDATE SDK_TAGS SET SDK_STATE = :SDK_STATE, STATE_ID = :STATE_ID, STATE_STAMP = SYSTIMESTAMP WHERE SDKTAG_ID = :SDKTAG_ID" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next OraDatabase.ExecuteSQL SqlQry objEH.CatchORA ( OraSession ) On Error GoTo 0 result = 0 OraDatabase.Parameters.Remove "STATE_ID" OraDatabase.Parameters.Remove "SDK_STATE" OraDatabase.Parameters.Remove "SDKTAG_ID" End Sub '------------------------------------------------- ' Function: setSdkContentState ' Description: Update the state of a SDK conent item ' Request Parameters ' sdktag_id ' pv_id ' sdkpkg_state Sub setSdkContentState ' ' OraDatabase.Parameters.Add "SDKTAG_ID", QStrPar("sdktagId"), ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "PV_ID", QStrPar("pv_id"), ORAPARM_INPUT, ORATYPE_VARCHAR2 OraDatabase.Parameters.Add "SDKPKG_STATE", QStrPar("sdkpkg_state"), ORAPARM_INPUT, ORATYPE_CHAR ' Update an existing entry SqlQry = "UPDATE SDK_CONTENT SET SDKPKG_STATE = :SDKPKG_STATE WHERE SDKTAG_ID = :SDKTAG_ID AND PV_ID = :PV_ID" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next OraDatabase.ExecuteSQL SqlQry objEH.CatchORA ( OraSession ) On Error GoTo 0 result = 0 OraDatabase.Parameters.Remove "SDKPKG_STATE" OraDatabase.Parameters.Remove "PV_ID" OraDatabase.Parameters.Remove "SDKTAG_ID" End Sub '------------------------------------------------- ' Function: cloneSdkPackages ' Description: Clone state from reference SDK ' Merge selected items (Leave unselected alone) ' Request Parameters ' sdktag_id ' sdk_ref_tag_id Sub cloneSdkPackages OraDatabase.Parameters.Add "SDKTAG_ID", QStrPar("sdktagId"), ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "SDK_REFTAG_ID", QStrPar("sdk_reftag_id"), ORAPARM_INPUT, ORATYPE_CHAR ' Merge ref state into sdk SqlQry = _ "MERGE INTO SDK_CONTENT t USING " &_ "(SELECT A.PV_ID, " &_ " A.sdkpkg_state, " &_ " B.sdkpkg_state AS REF_SDKPKG_STATE " &_ "FROM " &_ " (SELECT p.pkg_name " &_ " || pv.v_ext AS mname, " &_ " sc.pv_id, " &_ " sc.sdkpkg_state " &_ " FROM SDK_CONTENT SC , " &_ " PACKAGE_VERSIONS pv, " &_ " PACKAGES p " &_ " WHERE sc.sdktag_id = :sdktag_id " &_ " AND pv.pv_id = sc.pv_id " &_ " AND p.pkg_id = pv.pkg_id " &_ " ) A " &_ "INNER JOIN " &_ " (SELECT p.pkg_name " &_ " || pv.v_ext AS mname, " &_ " sc.pv_id, " &_ " sc.sdkpkg_state " &_ " FROM SDK_CONTENT SC , " &_ " PACKAGE_VERSIONS pv, " &_ " PACKAGES p " &_ " WHERE sc.sdktag_id = :sdk_reftag_id " &_ " AND pv.pv_id = sc.pv_id " &_ " AND p.pkg_id = pv.pkg_id " &_ " ) B " &_ "ON A.mname = B.mname " &_ "AND A.sdkpkg_state != B.sdkpkg_state AND A.sdkpkg_state != 'E' " &_ ") aa ON (t.pv_id = aa.pv_id ) " &_ "WHEN MATCHED THEN " &_ " UPDATE SET t.sdkpkg_state = aa.ref_sdkpkg_state " &_ "WHERE t.sdktag_id = :sdktag_id" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next OraDatabase.ExecuteSQL SqlQry objEH.CatchORA ( OraSession ) On Error GoTo 0 result = 0 OraDatabase.Parameters.Remove "SDKTAG_ID" OraDatabase.Parameters.Remove "SDK_REFTAG_ID" End Sub '------------------------------------------------- ' Function: setSdkPackages ' Description: Clear state from reference SDK ' Request Parameters ' sdktag_id Sub setSdkPackages OraDatabase.Parameters.Add "SDKTAG_ID", QStrPar("sdktagId"), ORAPARM_INPUT, ORATYPE_NUMBER OraDatabase.Parameters.Add "SDKPKG_STATE", QStrPar("sdkpkg_state"), ORAPARM_INPUT, ORATYPE_CHAR ' Reset ref state into sdk SqlQry = _ "UPDATE SDK_CONTENT " &_ " SET SDKPKG_STATE = :SDKPKG_STATE " &_ " WHERE SDKTAG_ID = :sdktag_id AND SDKPKG_STATE != :SDKPKG_STATE" objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) On Error Resume Next OraDatabase.ExecuteSQL SqlQry objEH.CatchORA ( OraSession ) On Error GoTo 0 result = 0 OraDatabase.Parameters.Remove "SDKTAG_ID" OraDatabase.Parameters.Remove "SDKPKG_STATE" End Sub %>