Using a prepared statement
To use a prepared statement, follow the steps below. Note that PreparedQuery events support only ODBC/JDBC data types. Vendor-specific data types are not allowed.
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 if using replaceable parameters and the output types if it will be used as a stored procedure.
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; you can use question mark characters
? to indicate replaceable parameters.
inputTypes — This is optional, but if you use replaceable parameters in the
queryString, you need to specify the types that will be used in the query.
outputTypes — This is optional, but if the
PreparedQuery event is to be used for a stored procedure and it uses output parameters, you need to specify the output types.
For example:
sequence<string> inputTypes := ["INTEGER","INTEGER"];
myPreparedQuery.init (
myConnection,
"SELECT * FROM mytable WHERE inventory > ? and inventory <?",
inputTypes, new sequence<string>);
4. Call the new PreparedQuery event’s create action, passing in the name of the callback action.
5. In the callback action's code, call the Query event’s initPreparedQuery action (instead of the initQuery action), passing in the name of the PreparedQuery event. 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.