Preprocessing of Dynamic Queries

• If you are using a dynamic cursor to update, be sure your SELECT statement contains a FOR UPDATE OF clause.

• An UPDATE WHERE CURRENT command must map to an appropriate SELECT statement. Be sure all of the columns you might possibly want to update are specified in the FOR UPDATE OF clause.

  For example, if the host variable or string from which you prepare contains the following statement, you can use the UPDATE WHERE CURRENT command to change the content of all the columns in qualifying rows of PurchDB.Parts.

  SELECT PartNumber FROM PurchDB.Parts WHERE PartNumber BETWEEN 9000 AND 9999 FOR UPDATE OF PartNumber, PartName, SalesPrice

  However, if your prepared command is based on a host variable or string containing the following statement, you will only be able to use UPDATE WHERE CURRENT to change column SalesPrice in any qualifying rows of PurchDB.Parts.

  SELECT PartNumber FROM PurchDB.Parts WHERE PartNumber BETWEEN 9000 AND 9999 FOR UPDATE OF SalesPrice

• Your error checking strategy might include routines to parse user input for an acceptable SELECT statement and/or routines to test specific sqlca field values and invoke SQLEXPLAIN. This error checking strategy may need to be modified, if the syntax of the SELECT statement has changed for a particular ALLBASE/SQL release.

Table 8-1 SQLDA Fields

Table 8-2 Fields in a Format Array Record

    0 = SMALLINT or INTEGER

    1 = BINARY*

    2 = CHAR*

    3 = VARCHAR*

    4 = FLOAT

    5 = DECIMAL

    8 = NATIVE CHAR *

    9 = NATIVE VARCHAR *

   10 = DATE*

   11 = TIME*

   12 = DATETIME*

   13 = INTERVAL*

   14 = VARBINARY*

    0 bytes:  column defined NOT NULL

    2 bytes:  column allows null values

For query results of unknown format, you declare the data buffer as a character array:

   #define  MaxDataBuff  2500    /* bytes in data buffer  */

   .

   .

   .

   char  DataBuffer[MaxDataBuff]; /*  the data buffer  */

The data buffer must be large enough to hold all the rows ALLBASE/SQL retrieves each time you execute the FETCH command, i.e., the number of rows you specify in SQLDA.SqlNRow. The data buffer defined above can hold as many as 2500 bytes of data.

Although the data buffer above can hold 2500 bytes, it would not be able to hold 2500 bytes of column values if any of the values were null and/or VARCHAR:

You can use the SQLDA.SqlRowLen value to compute how many rows will fit into the data buffer. Dividing SQLDA.SqlRowLen into SQLDA.SqlBufLen gives you the number of rows, including any VARCHAR prefixes and null indicator suffixes accompanying data values in the row:

   sqlda.sqlnrow = ((sqlbuflen) / (sqlrowlen));

The data buffer declaration shown above is an array of char, because the format of the query result is unknown at programming time.

When you know the query result format in advance, you can declare a data buffer as an array of records having the expected format. When a column can contain null values, you must declare a 2-byte indicator variable, immediately following the variable for that column. The indicator variable will hold the 2-byte suffix ALLBASE/SQL returns with the data value.

In the following example, Column3Ind is an indicator variable for Column3:

   struct  {

     int     strlen_col1;  /* string length of column1  */

     char    column1[20];

     short   column2;

     int     column3;

     short   column3Ind;    /* indicator variable  */

   } databuffer[MaxDataBuff];

When a column contains a VARCHAR data type, a 4 byte integer must be declared immediately before the variable for that column to hold the string length. The application itself needs to properly place the string terminator, ASCII 0, into the string.

   struct  SQLVarChar {

     int length;                   /* actual length of VARCHAR value */

     char VarCharCol[MaxColSize];  /* VARCHAR value */

     };

The data types you declare for a query result of known format need not be equivalent to the data types of their corresponding columns, but they should be compatible. (DATE, TIME, DATETIME, and INTERVAL values are treated like CHAR values.) Refer to the ALLBASE/SQL Reference Manual for the rules governing data type compatibility and conversion for complete information on this topic. The ALLBASE/SQL Reference Manual also addresses type conversion that may occur when a select list item is an expression containing data of different types. When you expect truncation, the column must allow nulls in order to detect the truncation.

• Declare a data buffer to hold the query result. This may be structured or not, depending on whether you know the format of the query result in advance. The following is unstructured:

  #define MaxDataBuff 1000 /* bytes in data buffer */ char DataBuffer[MaxDataBuff]; /* the data buffer */

  When the select list is known, you can define the data buffer as an array of records having the expected format:

  #define MaxNbrRows 200 char DataBuffer[MaxNbrRows]; struct { Column1DataType Column1; Column2DataType Column2; } DataBuffer[MaxNbrRows];

• You declare a format array as sqlformat_type. This type is defined for you in the preprocessor generated type include file. The number of records in the format array in this example is 1024, which allows for the maximum size query result of 1024 columns.

  #define NbrFmtRecords 1024 / *Columns expected */ . . sqlformat_type sqlfmts[NbrFmtRecords]; / *sqlfmts is the format array */

• Use a host variable for the SELECT command, and pass it to ALLBASE/SQL in the PREPARE command:

  EXEC SQL BEGIN DECLARE SECTION; char DynamicCommand[2048]; EXEC SQL END DECLARE SECTION; . . . EXEC SQL PREPARE Cmd1 FROM :DynamicCommand;

• Initialize two sqlda fields, sqln and sqlfmtarr. sqln is set to the size of the format array, and sqlfmtarr is set to its address.

  . sqlda.sqln = NbrFmtRecords; /* Maximum select list elements*/ sqlda.sqlfmtarr = sqlfmts; /* Format array address */

• Execute the DESCRIBE command:

  EXEC SQL DESCRIBE Cmd1 INTO sqlda;

  During the execution of the DESCRIBE command, ALLBASE/SQL returns to the format array and to the SQLDA the information you need later to parse and handle the query result. You use format array information to parse the data buffer when you do not know in advance the format of a query result.

  	NOTE: When you know the format of the query result in advance, you can define a data buffer having the format you expect, and you do not need to use format array information to parse it. However, you still need to declare the format array.

• Declare and open a cursor for the prepared query:

  EXEC SQL DECLARE Cursor1 CURSOR FOR Cmd1; EXEC SQL OPEN Cursor1;

• Before retrieving rows into the data buffer, initialize three SQLDA fields. These fields identify your data buffer and specify how many rows you want retrieved into the data buffer each time the FETCH command is executed:

  with SQLDA do begin sqlda.sqlbuflen = sizeof(DataBuffer); /* bytes in data buffer */ sqlda.sqlrowbuf = int(DataBuffer); /* data buffer address */ /* cast as INT */ sqlda.sqlnrow = ((sqlda.sqlbuflen) / (sqlda.sqlrowlen)); /* number of rows to FETCH */ end;

• Execute the FETCH command. ALLBASE/SQL packs the data buffer with as many rows from the active set as you specified in SQLDA.SqlNRow. ALLBASE/SQL puts the first select list value into the data buffer, starting at the first byte of the format array and including any VARCHAR prefixes, ALLBASE/SQL null indicators for columns that can contain null values, and any alignment bytes provided by the C compiler. Then ALLBASE/SQL writes the second through last select list values for the first row. If the query result contains another row, the first through last select list values in that row are written to the data buffer. Data values are thus concatenated in the data buffer until the last row has been fetched. When the last row in the active set has been fetched, ALLBASE/SQL sets SQLCA.SQLCODE to 100.

  In Figure 8-5, two columns are selected from the vendors table in the sample database. Column VendorNumber is defined in the table as an INTEGER that cannot contain a null value. Column VendorRemarks is defined in the table as a VARCHAR that can contain a null value. Since the VendorRemarks column can contain a null value, a two byte null indicator needs to be provided immediately following this VARCHAR data column. Note the two byte filler that completes the VendorRemarks column definition. The figure illustrates the relationships between column definitions and the layout of data in the data buffer.

  Figure 8-5 Format of the Data Buffer

  

  Note that the number of rows to retrieve with each execution of the FETCH command is specified in SQLDA.SqlNRow. As shown in the above example, you can calculate the number of rows that will fit into the data buffer by dividing the row length (in bytes) into the number of bytes in the data buffer. Sqlrowlen, one of the SQLDA fields set by ALLBASE/SQL when you execute the DESCRIBE command, contains the number of bytes in each row.

  do { EXEC SQL FETCH Cursor1 USING DESCRIPTOR sqlda; DisplayRow(); } while (sqlca.sqlcode !=100)

• If the query result is of unknown format, parse rows out of the data buffer after each execution of the FETCH command. The technique for parsing is shown in detail in the next section.

• Check for null values, taking appropriate action when one is found.

• Examine the data type and length of the data element itself, assigning it to an appropriate variable of the corresponding size.

• Increment the offset variable by the value of SQLDA.SqlRowLen (the length of a complete row).

Figure 8-6 Parsing the Data Buffer in cex10a