xcall SQL, SQLOP_FETCH_GRID, cmdhdr, $grid()
This operation retrieves the entire (or remaining) current result set into a gridmap.
Requires minimum A-Shell version 6.5.1730.
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 opflags table below |
cmdarg1 |
n/a |
|
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 |
number of fields returned. |
info2 |
out |
number of rows returned. |
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 |
|---|---|---|
FETCH_NULLS |
&h0100 |
Causes NULL fields to be returned as "NULL" rather than "". (Collections cannot support the .NULL, i.e. dot-null representation because it is used to delete key-value pairs from the collection.) |
FETCH_MAXLENS |
&h0200 |
Requires minimum A-Shell version 6.5.1734. Causes an additional row #0 to be added to the grid, whose values (string representation of integers) are the actual maximum lengths of the query results for each column. This is intended to be useful when dealing with database fields declared with larger than necessary maximum lengths; knowing the actual maximum lengths may help you display or manipulate it more efficiently. |
$grid() [gridmap(int;varstr;varstr) or gridmap(int;int;varstr) by reference]
The entire current result set (or what remains of it after prior SQLOP_FETCH_ROW operations) will be returned here. In the (int; int; varstr) variation, each value is indexed by its row and column #, with first row containing the column (field) names. (The resulting gridmap will have one more row than the result set.) In the (int; varstr; varstr) variation, each value is indexed by its row # and column name. Note that in this latter case, the result set columns are reordered alphabetically. All values will be converted to string / display format.
Notes
This operation is similar to the FETCHR_ROWMAP variation of the SQLOP_FETCH_ROW operation, except:
| • | SQLOP_FETCH_GRID returns all of the (remaining) result set data in a single operation, whereas SQLOP_FETCH_ROW returns only one row at a time (and allows the app to seek to a specific row.) |
| • | Since the gridmap collection type supports only string (varstr) values, it does not support BLOB data types. (Any such columns will be ignored by SQLOP_FETCH_GRID, whereas SQLOP_FETCH_ROW / FETCHR_ROWMAP can handle them using an ordmap(varstr;varx) collection. |
| • | SQLOP_FETCH_GRID always starts by clearing the grid, whereas SQLOP_FETCH_ROW / FETCH_ROWMAP does not, possibly opening the door to build a single map containing more than one query result.) |
Example 1
map1 cmdhdr, ST_SQL_CMDHDR
map1 query$,s,0
dimx $grid, gridmap(int; varstr; varstr) ! row#, colname -> value
... (init lib, connect, etc.)
query$ = "SELECT Genus, Species, Name FROM Plants WHERE Type = 'Native'
xcall SQL, SQLOP_QUERY, cmdhdr, query$
if cmdhdr.rc = 0 then ! success
xcall SQL, SQLOP_FETCH_GRID, cmdhdr, $grid()
if cmdhdr.rc = 0 then ! success
foreach $$i in $grid()
? "#"; .key($grid(), 1); ":"; .key($grid(),2); " --> "; $$i
next $$i
endif
xcall SQL, SQLOP_FREE_RESULT, cmdhdr
endif
The result would look something like this:
#1: Genus --> Artemisia
#1: Name -> California sagebrush
#1: Species --> A. california
#2: Genus --> Encelia
#2: Name -> Bush sunflower
#2: Species -> E. california
etc.
Note that in the gridmap(int; varstr; varstr) variation, the columns are ordered alphabetically rather than the way they are in the query results.
Example 2
This example uses the same query as above, but retrieves the data into a gridmap(int; int; varstr) and then outputs the results using a pair of nested for/next loops instead of iterating. Note that in this variation, the first row of the gridmap contains the column names (in the same order as in the query); the data starts on row 2.
map1 cmdhdr, ST_SQL_CMDHDR
map1 query$,s,0
map1 row,i,4
map1 col,i,2
dimx $gridi, gridmap(int; int; varstr) ! row#, col# -> value
... (init lib, connect, etc.)
query$ = "SELECT Genus, Species, Name FROM Plants WHERE Type = 'Native'
xcall SQL, SQLOP_QUERY, cmdhdr, query$
if cmdhdr.rc = 0 then ! success
xcall SQL, SQLOP_FETCH_GRID, cmdhdr, $gridi()
if cmdhdr.rc = 0 then ! success
for row = 2 to cmdhdr.info2 ! (all data rows; row 1 has col names)
for col = 1 to cmdhdr.info1 ! (all columns)
? row; ","; col; " (";$gridi(1,col); ") --> "; $gridi(row,col)
next col
next row
endif
xcall SQL, SQLOP_FREE_RESULT, cmdhdr
endif
The result would look something like this:
2, 1 (Genus) --> Artemisia
2, 2 (Species) --> A. californica
2, 3 (Name) --> California sagebrush
3, 1 (Genus) --> Encelia
3, 2 (Species) --> E. californica
3, 3 (Name) --> Bush sunflower
etc.
See Also
