xcall SQL, SQLOP_FETCH_ROW, cmdhdr, field1, … fieldN
xcall SQL, SQLOP_FETCH_ROW, cmdhdr, field(1)
xcall SQL, SQLOP_FETCH_ROW, cmdhdr, rowstring
xcall SQL, SQLOP_FETCH_ROW, cmdhdr, $map()
This operation retrieves the actual field data for the current row of the current result set.
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 |
see FETCHR_xxx |
cmdarg1 |
in |
max # elements in field() array (second syntax form above) or delimiter to use if FETCHR_DELIMETER (third syntax form) |
cmdarg2 |
in |
row number within result set to fetch (1=first row); 0 for next row (or first if last operation was the query). |
rc |
out |
return code (0 = ok, see SQLERR_xxx) |
rcext |
out |
extended error code |
info1 |
out |
# of fields returned. |
info2 |
in/ out |
row # just read (0 indicates no more rows available). Note that when cmdarg2 is set to 0 to read the next row, the routine assumes that info2 is left untouched by the application (and thus can simply be incremented to return the next row). See Multiple Result Sets for other caveats. |
sqlstate |
out |
ANSI standard SQL state code |
reshdl |
in |
result set handle. Can be ignored if only working with one result set at a time. Otherwise you must set it to match the value used for the SQLOP_QUERY call which created the result set. |
psthdl |
in |
set to the prepared statement handle for the statement which generated the result set (i.e. from SQLOP_PST_PREPARE), or 0 the result set was generated from a directly executed statement. |
opflags |
Value |
Notes |
|---|---|---|
FETCHR_FIELDS |
&h0000 |
(first syntax form) Data retrieved into individual fields, which can be of differing sizes and types. |
FETCHR_ARRAY |
&h0001 |
field(1) argument (second syntax line above) is an array of max cmdarg1 elements. |
FETCHR_ROWSTR |
&h0002 |
(for third syntax form) rowstring is a single string argument to receive entire row. (Perfect for XTREE) |
FETCHR_ROWMAP |
&h0010 |
(for fourth syntax form) The specified ordmap $map() will be populated such that $map(column-name) -> column-value. |
FETCHR_ORGNAME |
&h0020 |
(for fourth syntax form) Combine with FETCHR_ROWMAP and optionally FETCHR_TBLNAME to use the column org'name (original name) instead of the name and/or org'table instead of table. |
FETCHR_TBLNAME |
&h0040 |
(for fourth syntax form) Combine with FETCHR_ROWMAP and optionally FETCHR_ORGNAME to prepend the table (or org'table) name to the column name (or org'name) as the key. For example, given a table named "planes" and a column "model", a returned pair might be $map("planes:model") -> "B52". |
FETCH_NULLS |
&h0100 |
With FETCHR_ROWMAP (fourth syntax form), causes NULL fields to be returned as "NULL" rather than "". For the first three syntax forms, causes NULL fields to be returned as .NULL (which displays as "<null>"). Otherwise, NULL fields are returned as "". |
field1, … fieldN [any type, out]
(first syntax form) Each of the field parameters returns the data for the associated column of the applicable row of the result set (according to the sequence of columns determined by the query). An effort will be made to perform “appropriate” data type conversions, but the simplest method is just to use strings for all data except for blobs containing binary data that cannot be represented appropriately using printable characters. For all other field types (integers, dates, floats, etc.), the field will be formatted as a string according to the field type and flags. (For example, dates will always appear as “ccyy-mm-dd”.) Another advantage of using string variables to receive the data is that it allows you to use a generic array of string variables of an appropriate maximum size, rather than having to match up specific variable types to specific queries.
field(1) [first element in an array of strings, out]
(second syntax form) This form of the field list may be used to specify an array of strings, with each element to receive one field from the current row of the result set. This may be more convenient than specifying individual xcall arguments for each field but does limit you to string format. Dynamic strings—i.e. s,0—are supported though, eliminating concerns about having to allow for the maximum field size. The element specified will be considered as the first element in an array, and the cmdarg2 member of the cmdhdr structure must specify the maximum number of items—starting with the specified item—in the array.
rowstring [string, out]
(third syntax form) The entire row is returned as a single string, with field sizes either determined by the column information (SQLOP_FETCH_COLUMNS) or delimited by the character whose ASCII value is in the cmdarg2 field (FETCHR_DELIMITER) or using standard CSV formatting (FETCHR_CSV).
$map() [ordmap(varstr;varx) by reference]
(fourth syntax form; FETCHR_ROWMAP) Each field in the query result is returned as an entry in the ordered map, with the key being the field name (or org'name, possibly prefixed with the table name; see FETCHR_ORGNAME, FETCHR_TBLNAME), and the value being the field value. The value representation, except for BLOB types, will be the same as for the FETCHR_ROWSTR or FETCHR_ARRAY modes, i.e. a string suitable for display purposes. For BLOB types, the representation will be the same as the storage format, in which case you'll need to copy the value from the $map() to a variable of suitable type/size for practical use. For string values, ordmap(varstr;varx) is the same as ordmap(varstr;varstr) so no conversion is needed. Note that if the query result contains unnamed fields, such as expressions, a pseudo column name of the form "expr-#" will be assigned as the key to avoid having multiple result fields with the same key. Also note that the map is not automatically cleared before each SQLOP_FETCH_ROW operation. Typically this is not an issue since each row in the result set will have the same number of fields, but in most cases, the application may need to explicitly issue a .CLEAR $map() statement prior to fetching the first row so as to eliminate any map entries left over from a prior query.
Notes
If the QRYF_NOSTORE flags was used for the query, you must keep issuing SQLOP_FETCH_ROW requests (preferably without delay) until info2 comes back as 0.
See Also
History
2023 April, ashodbc connector 1.6.120 & ashmysql connector 1.6.146: FETCH_NULLS added.
2018 February, A-Shell 6.5.1630, ashmysql connector version 1.4.142: Implement the $map() option (fourth syntax form). Requires A-Shell 6.5.1630.0+.
