Forum Discussion

chicks's avatar
chicks
Regular Contributor
10 years ago

executing SQL procedure from javascript

I have been able to read database fields and update database fields.    However, when I try to execute a sql procedure, at best I'm getting an Oracle error 00900  "invalid sql statement"  or an undef...
  • mtsmith's avatar
    mtsmith
    10 years ago

    Here's a more complete example, with an input parameter:

     

    //**********************************************************************************************************************
    /** @desc   RunStoredProcedure
    *        Open a connection to a database and run the specified stored procedure
    *   @function
    *   @param    
    *      {string} psStoredProcedure - the stored procedure name, for example "MyStoredProc"
    *                 {dictionary} pdData - dictionary object containing input parameters
    *      {string} sDSN  - Dataset name.
    *      {string} sUser - Connect as this user.
    *      {string} sPassword - Password.
    *           
    *   @returns    {int}  1 on success, 0 on failure */
    //**********************************************************************************************************************
    function RunStoredProcedure(psStoredProcedure, pdData, sDSN, sUser, sPassword) {
      
       try {

          lResult = 0;
          var adDate = 7;
          var oConn = new ActiveXObject('ADODB.Connection');
      
      if (aqEnvironment.GetWinMajorVersion() == 5) // 5 == XP
             var sConnStr = "Driver={Microsoft ODBC for Oracle};CONNECTSTRING="+sDSN+";uid="+sUser+";pwd="+sPassword+";"; 
      else // 6 = W7
       sConnStr =  "Provider=OraOLEDB.Oracle;Data Source="+sDSN+";User ID="+sUser+";Password="+sPassword+";";
         
      oConn.ConnectionString = sConnStr;
          oConn.ConnectionTimeout = 0;     
          oConn.Mode = adModeShareExclusive;
          oConn.Open;
      
          //create and execute the stored procedure command object
          oCmdSP = new ActiveXObject("ADODB.Command");
          oCmdSP.ActiveConnection = oConn;         
          oCmdSP.CommandType = adCmdStoredProc;
          oCmdSP.CommandText = psStoredProcedure;
          if (pdData !== undefined ) { 
             if (pdData.Exists("RunDate") && pdData.Item("RunDate") !== undefined && pdData.Item("RunDate") !== null) {
                oParmSP = oCmdSP.CreateParameter ("dDateParm", adDate, adParamInput);
                oParmSP.value = pdData.Item("RunDate")
                oCmdSP.Parameters.Append(oParmSP);
             }   
          }
          oCmdSP.Execute();
          lResult = 1; 
       }
       catch (e) {
          DLI_WriteLog (gsLogFile, "Error", sMyName + ', ' + e.description);
          lResult = 0;
       }
      
       if (oConn){
          oConn.Close;
          oConn = null;
          oCmdSP = null;
       }  
       return lResult;
    } //RunStoredProcedure