Using a stored procedure
Queries in Apama applications use stored procedures by specifying the name of the stored procedure in a prepared statement's query string.
To use a stored procedure
1. Create a new Query event.
2. Create a new PreparedQuery event.
3. Call the new PreparedQuery event's init() action, passing in the database connection, the query string, the input types, and the output types.
The definition for the init() action is:
action init (
Connection conn,
string queryString,
sequence<string> inputTypes,
sequence<string> outputTypes)
The arguments for the init() action are:
conn — The name of the database's
Connection event.
queryString — The SQL query string; enclose the name of the database's stored procedure in curly brace characters (
{ }) and use question mark characters (
?) to indicate replaceable parameters.
inputTypes — Specify the types that will be used for the replaceable parameters in the
queryString.
outputTypes — Specify the types that will be used for the replaceable parameters in the result.
For example:
sequence<string> inputTypes := ["INTEGER", "NULL", "INTEGER"];
sequence<string> outputTypes := ["NULL", "INTEGER", "INTEGER"];
myPreparedQuery.init (
myConnection,
"{call myprocedure(?,?,?)}",
inputTypes,
outputTypes);
If a parameter is used as both an input and output type, it must be specified in both places.
If it is only an input type it must be specified as NULL in
outputType.
If it is only an output type it must be specified as NULL in
inputType.
Therefore, in the example above, the first parameter is just an input type; the second parameter is just an output type; and the third parameter is both an input and output type.
4. Call the new PreparedQuery event's create() action, passing in the name of the callback action.
5. In the callback action's code or once the callback action has been called, call the Query event's initPreparedQuery() action instead of the initQuery() action, passing in the name of the PreparedQuery event. An error will be reported if the Query event's initPreparedQuery is called before the PreparedQuery create callback has been called. See
Executing standard queries. 6. Call the Query event's setInputParams() action, passing in the values to be used for the replaceable parameters.
The definition of the setInputParams() action is:
setInputParams(sequence<string> inputParams)
If you want to use NULL for the value of a replaceable parameter, use ADBC_NULL.
7. If necessary, call any of the other Query actions, such as setBatchSize(), as required.