Beginning with A-Shell 7.7.1771, support is provided for a variation of SQLOP_FETCH_ROW that fetches a result set into a DYNSTRUCT, matching up fields by name or number.
xcall SQL, SQLOP_FETCH_ROW, cmdhdr, ds
where ds is a DYNSTRUCT than has been initialized/bound to a structure definition.
To distinguish this variation from others, you must set one of the following flags in cmdhdr.opflags:
FETCHR_DYNFLDNUM (&h0080) ! fetch row into dynstruct by field number
FETCHR_DYNFLDNAME (&h0100) ! fetch row into dynstruct by field name
These are defined in SQL.DEF, available along with any other relevant utility module updates in 909060 of the EXLIB repository.
Comments
| • | Although SQL field names are case INsensitive, DYNSTRUCT field names are case sensitive. So if using FETCHR_DYNFLDNAME, the names in the structure definition must match exactly the SQL field names to be fetched. Any mismatches will result in that field/column being ignored. It doesn't interfere with any other field fetch operations, but will cause the new cmdhdr.rc status flag SQLERR_DSFLDUNDEF (32) to be returned. (Note that you may want to use 'AS name' clauses in your query to clean up any such mismatches and to assign matchable names to any calculated result columns.) Arrays are not supported in this mode. |
| • | In the FETCHR_DYNFLDNUM (by field number) variation, the result columns are matched up by the sequence number of the fields in the structure,starting at 1. In addition to scalar fiels, single-dimension arrays are also supported, as in the example below: |
defstruct ST_DEMO
map2 custid,s,10 ! field #1
map2 cusname,s,30 ! field #2
map2 lastsales(5),f ! field #3-#7
map2 custype,b,2 ! field #8
If there are more fields in the result set than in the structure, the excess result fields will be ignored, and the SQLERR_DSFLDUNDEF (32) status code will be returned in cmdhdr.rc
| • | The logic for this is in A-Shell rather than the ASQL connector, but does require at least 1.6.148 (MySQL) and 1.6.120 (ODBC) |
