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

chicks's avatar
chicks
Regular Contributor

Thanks.  I tried this and got OraOLEDB unspecified error unfortunately.

mtsmith's avatar
mtsmith
Contributor
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

  • chicks's avatar
    chicks
    Regular Contributor
    10 years ago

     

    I got that to work.  Thanks very much!   One major difference was that I was including the "execute" in my stored procedure statement.  I had tried both with and without and it did not seem to make any difference.

     

    Other minor (?) differences are setting the connection timeout to 0 and the connection mode to adModeShareExclusive.  Also I was setting the command type explicitly to 4 rather than adCmdStoredProc.... 

     

    Once I've finished refactoring the routine I'll experiment and see which if any of thes actually made the difference