Executing Stored Procedures

To execute a stored procedure, use one of the following methods:

ExecuteProcedure()

Method ExecuteProcedure(ByRef pResultSnapshots As %ListOfObjects,
                Output pOutputParms As %ListOfDataTypes,
                pQueryStatement As %String,
                pIO As %String = "",
                pInputParms...) As %Status

Executes an SQL CALL statement that runs a stored procedure. You can pass any number of parameters. Notes:

The result is returned by reference in the first argument as a list of EnsLib.SQL.SnapshotOpens in a new tab objects.
You can create a list of new instances of EnsLib.SQL.SnapshotOpens in a new tab and pass the list into the method as the first argument. If you do, the method populates these instances and uses the values of its FirstRow and MaxRowsToGet properties to choose the set of rows that each will represent. The method then returns the list of instances.
The second argument is the list of the output values of all scalar output and input/output parameters. If the procedure returns a scalar return value and your statement retrieves it, this value will be the first output value.
The third argument is the SQL CALL statement that runs a stored procedure. This statement can include the standard SQL ? to represent replaceable parameters.

Important:

The name of the stored procedure is case-sensitive. Also, make sure that the pQueryStatement statement supplies an argument for every input or input/output parameter that the SQL query requires.
The fourth (optional) argument indicates the type (input, output, or input/output) for each parameter. If you specify this argument, use a string that consists of the characters i, o, and b; the character at a given position indicates the type of the corresponding parameter. For example, iob means that the first parameter is input, the second parameter is output, and the third parameter is both input and output.

Tip:

By default, the adapter calls the ODBC function DescribeProcedureColumns to get information about the parameters and logs warnings if the parameter types specified here are different from the types returned by that function. To prevent the adapter from making this check, append an asterisk (*) to the end of this string.

Not all database support all these types of parameters. Be sure to use only the types that are supported by the database to which you are connecting.

ExecuteProcedureParmArray()

Method ExecuteProcedureParmArray(ByRef pResultSnapshots As %ListOfObjects,
                Output pOutputParms As %ListOfDataTypes,
                pQueryStatement As %String,
                pIO As %String = "",
                ByRef pIOParms) As %Status

Executes an SQL CALL statement that runs a stored procedure. This method is similar to the preceding method with the exception of how parameters are specified. For this method, you specify the parameters in an InterSystems IRIS multidimensional array (pParms), as described in Specifying Parameters in an InterSystems IRIS Multidimensional Array.

You use the ExecuteProcedureParmArray() method if you need to specify parameters and if the ODBC driver that you are using does not support the ODBC SQLDescribeParam function.

Also note:

For a given parameter, if you specify the input/output type within the pIOParms array and within the pIO argument, the type given in pIOParms array takes precedence.
If you specify any input/output types within the pIOParms array, then for all output parameters, be sure to leave the corresponding array nodes undefined.

If you have configured your SQL adapter to use JDBC through the Java Gateway, output parameters that have a large object value are returned as streams. For compatibility with older versions of InterSystems IRIS, you can set a global to return these large object output parameters as strings. But even with this global set, if the object exceeds the size allowed for a string, it is returned as a stream. To set this compatibility behavior for a configuration item SQLservice, set the global ^Ens.Config("JDBC","LOBasString","SQLservice") to 1.

Example

The following code executes a stored procedure that has three parameters: an output parameter, an input parameter, and another output parameter. The input parameter is extracted from the Parameters property of the request message: pReq.Parameters.GetAt(1). The output parameters are ignored.

  Set tQuery="{ ?=call Sample.Employee_StoredProcTest(?,?) }"
  Set tSC = ..Adapter.ExecuteProcedure(.tRTs,.tOutParms,tQuery,"oio",pReq.Parameters.GetAt(1))
  Set tRes.ParametersOut = tOutParms

In this example, tRTs represents a result set that was previously created.

Specifying Statement Attributes

Performing Updates, Inserts, and Deletes