Using a prepared statement
Note that PreparedQuery events support only ODBC/JDBC data types. Vendor-specific data types are not allowed.
To use a prepared statement
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. As with any query, the relevant init action should be called each time before calling the query's start action. 6. Call the Query event's setInputParams() action, passing in the values to be used for the replaceable parameters. This should always be called before starting a query that is using a prepared query.
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.