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();
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
