xcall SQL, SQLOP_QUERY, cmdhdr, querystring
The SQLOP_QUERY function may be used to pass an arbitrary “query string” to the database to be interpreted and executed. Note that in SQL parlance, essentially every statement is a “query”, so you can use this call to execute just about any kind of SQL statement at all, including statements to create databases, select records, post data, etc.
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 |
in |
QRYF_xxx (see below) |
cmdarg1 |
in |
length of querystring if QRYF_REAL |
cmdarg2 |
n/a |
|
rc |
out |
return code (0 = ok, see SQLERR_xxx) |
rcext |
out |
extended error code |
info1 |
out |
# columns in result set (for SELECT-type statements) |
info2 |
out |
# rows returned from SELECT-type statements (assuming QRYF_NOSTORE flag not set, and rc = 0); or # rows “affected” for other kinds of statements. See notes below. |
sqlstate |
out |
ANSI standard SQL state code |
reshdl |
in |
Handle to use for the result set. Default result set is 0. Field may be ignored unless you need to start a new query before being finished with the previous query results. Use 1 for the second result set, 2 for the third, etc. (Up to 15.) See notes below for further information. |
psthdl |
n/a |
|
opflags Symbol |
Value |
Notes |
|---|---|---|
QRYF_REAL |
&h0001 |
querystring contains “real” (i.e. binary) data; up to length specified in cmdarg1. (not yet implemented) |
QRYF_NOSTORE |
&h0002 |
Don’t store result set locally – see notes below. With ODBC, the default attributes effectively set this flag. To alter that, you may need to use SQLOP_SET_STMATR to set one of the cursor attributes (such as cursor type insensitive and/or static). |
querystring [string, in]
A string containing a valid SQL statement. Normally this must be a null-terminated ASCII string, but if the QRYF_REAL flag is set, then it may contain binary data up to the length specified in the cmdarg1 field of cmdhdr.
Examples
“show tables”
“describe customer_table”
“select * from customer_table where zipcode = ‘91367’”
“select name,phone from customer_table where last_sale >= ‘1999-12-31’”
“insert into pet (name, species) values (‘Ginger’, ‘canine’)
Notes
For (non-ODBC) SELECT-type statements (SELECT, SHOW, DESCRIBE, etc.), the entire result set is normally stored (cached) locally, and the number of rows is returned in info2 (unless rc # 0 in which case check rcext for error codes). The application must then perform additional XCALL SQL operations to retrieve information about the columns (SQLOP_FETCH_FIELDS) and to retrieve the actual rows (SQLOP_FETCH_ROW). When done processing the result set, you must use SQLOP_FREE_RESULT to release the resources associated with the result set, which may be considerable.
If the QRYF_NOSTORE flag set, or for any ODBC query, instead of storing the result set locally, it is kept on the database server and returned one row at a time—one per SQLOP_FETCH_ROW operation. This mode is useful for handling extremely large result sets, but is otherwise difficult to work with due to the following:
| • | the query will not return the number of rows (info2 will be set to 0 by the MySQL connector, or -1 by the ODBC connector). |
| • | you must use SQLOP_FETCH_ROW as many times as needed to retrieve the entire result set (i.e. until it returns 0 in info2) before freeing the result set; failure to do that will leave the remaining results on the server and lead to a “commands out of sync” error on the subsequent operation. |
| • | the server may be tied up while you are retrieving the data, so you should not allow the process to be suspended (e.g. by having to wait on user input). |
Non-select statements do not return a result set. If no result set returned, but the application context is unable to tell whether a result set should have been returned, use the SQLOP_GETINFO option SQLINFO_FIELDCOUNT, which see above.
For certain statements that alter data (INSERT, LOAD, ALTER TABLE, UPDATE), although no result set is returned, you can get an information string with further status information on the number of records processed, deleted, skipped, duplicates, warnings, etc. using the SQLOP_GET_INFO option SQLINFO_QUERY (which see above).
Although not always necessary, it's a good idea to call SQLOP_FREE_RESULT to free the result set (if any) and reset the cursor state before issuing another SQLOP_QUERY, even after query statements that you don't expect results from. Otherwise a subsequent query may fail—e.g. with cmdhdr.sqlstate = 24000 for 'invalid cursor state.' Along the same lines, it's a good idea to record the cmdhdr.rcext and cmdhdr.sqlstate values whenever cmdhdr.rc returns a non-zero error code, as they are often critical to debugging the failure.
See Also
