xcall SQL, SQLOP_PST_PREPARE, cmdhdr, stmstr {,qrystr}
The SQLOP_PST_PREPARE function may be used to define a “prepared statement” for later execution. This primarily makes sense for data manipulation statements that are executed repetitively, except for the actual data bindings (e.g. INSERT, REPLACE, UPDATE and SELECT). They offer two kinds of two potential advantages over those that executed immediately (via SQLOP_QUERY):
| • | Performance: Prepared statements can improve performance by eliminating the need to parse repetitive statements each time they are executed. In addition, they typically save a packet roundtrip between the server and client, and they permit more efficient binding between application data and database fields. |
| • | Easier binding of binary data: Unlike statements which are immediately executed (via SQLOP_QUERY), which require all data to be expressed in ASCII format (thus requiring ‘escaping’ for floating point and binary data), prepared statements permit direct binding between application variables and database fields. |
Parameters
cmdhdr (ST_SQL_CMDHDR), [in/out]
The fields of interest to this opcode in the cmdhdr structure (defined in SQL.DEF) are listed below.
Field |
Dir |
Notes |
|---|---|---|
handle |
in |
handle to connection (returned from SQLOP_INIT_CONN) |
dbmsconid |
in |
|
opflags |
n/a |
|
cmdarg1 |
in |
length of stmstring ? |
cmdarg2 |
n/a |
|
rc |
out |
return code (0 = ok, see SQLERR_xxx) |
rcext |
out |
extended error code |
info1 |
out |
# of parameters processed. (This should match the number of “?” placeholders in your stmstring, and the number of parameters that you bind in a subsequent SQLOP_PST_BIND operation before executing the statement.) |
info2 |
|
|
sqlstate |
out |
ANSI standard SQL state code |
reshdl |
n/a |
|
psthdl |
in/ out |
Desired handle to identify prepared statement with (1-32). If set to 0 on input, will be returned with the actual handle assigned (1-32). |
stmstr [string, in]
A string containing a single, eligible SQL statement, with the parameters marked by ‘?’ characters. For example:
“insert into student(id, name, sex) values(?,?,?)”
qrystr [string, in, optional]
This string can be used when stmstr specifies a non-SELECT-type statement (e.g. INSERT), to specify a corresponding SELECT statement which can be used to determine database file type information for the parameters in the stmstr. Taking the example given above, in order to be able to query the database about the field types for (id, name, sex), you might specify this query:
“select (id, name, sex) from student limit 1 where id = '!'”
As part of the initialization of the prepared insert statement, ASQL would issue the query given in the qrystr parameter so as to be able to retrieve the field types for the columns of the query. (The “limit 1” clause is highly recommended here, since we don’t need to actually select more than 1 row in order to query the field types, and a highly restrictive “where” clause is also highly recommend to limit the database processing time, since we don't really want to select anything, and since “limit” may only limit the size of the result set, without limiting the actual selection operation internally.)
This concept may seem roundabout, and indeed is, but at least in the case of MySQL, there is no other built-in mechanism to identify the fields (and retrieve their definitions) associated with the placeholders in non-SELECT prepared statements. (In the simple insert example given above, we could just parse the insert statement, but in an arbitrary case, it would require duplicating the SQL statement parsing logic.) For database connectors where the field information can be extracted directly from the prepared statement, the qrystr would be ignored.
Note that the SQLOP_PST_BIND function offers other possibilities for specifying the database field types, but in most cases, it probably makes the most sense to get the field type information directly from the database rather than from the application.
Notes
Not all statements, nor all parameter types are eligible to be prepared in this way. Consult the documentation for the database or driver for further details.
Once a statement has been prepared, you must define a set of bindings (one for each of the ‘?’ placeholders) using SQLOP_PST_BIND, before the statement can be executed.
