Forum Discussion

chicks's avatar
chicks
Regular Contributor
11 years ago
Solved

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 undefined Oracle error.

 

The procedure "execute  dbowner.update_dog_name " works fine from SQL developer.

 

I tried searching for executing SQL procedure using javascript, and found some indications that it was NOT possible, but nothing conclusive.

 

Has anybody done this ?

 

Psuedocode for the update follows.

 

// update

 

// set connection

conObj = new ActiveXObject("ADODB.Connection");

 

var connectionString = (details removed )

 

conObj.Open(connectionString);

 

// create command and execute

cmd = new ActiveXObject("ADODB.Command");

cmd.CommandText = sqlUpdateStatement;  // passed in as command parameter

cmd.ActiveConnection = conObj;

cmd.Execute();

 

 

5 Replies

    • chicks's avatar
      chicks
      Regular Contributor
      11 years ago
      Copy Link

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

      • mtsmith's avatar
        mtsmith
        Contributor
        11 years ago
        Copy Link

        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