Executing SQL statements by using a rowset cursor

You must use the WITH ROWSET POSITIONING clause of the DECLARE CURSOR statement if you plan to use a rowset-positioned FETCH statement.

The following example shows a FETCH statement that retrieves 20 rows into host variable arrays that are declared in your program:

EXEC SQL
  FETCH NEXT ROWSET FROM C1  
  FOR 20 ROWS 
  INTO :HVA-EMPNO, :HVA-LASTNAME, :HVA-SALARY :INDA-SALARY
END-EXEC.

When your program executes a FETCH statement with the ROWSET keyword, the cursor is positioned on a rowset in the result table. That rowset is called the current rowset. The dimension of each of the host variable arrays must be greater than or equal to the number of rows to be retrieved.

Suppose that you want to dynamically allocate the storage needed for the arrays of column values that are to be retrieved from the employee table. You must:

1. Declare an SQLDA structure and the variables that reference the SQLDA.
2. Dynamically allocate the SQLDA and the arrays needed for the column values.
3. Set the fields in the SQLDA for the column values to be retrieved.
4. Open the cursor.
5. Fetch the rows.

You must first declare the SQLDA structure. The following SQL INCLUDE statement requests a standard SQLDA declaration:

EXEC SQL INCLUDE SQLDA;

Your program must also declare variables that reference the SQLDA structure, the SQLVAR structure within the SQLDA, and the DECLEN structure for the precision and scale if you are retrieving a DECIMAL column. For C programs, the code looks like this:

struct sqlda *sqldaptr;
struct sqlvar *varptr;
struct DECLEN {
   unsigned char precision;
   unsigned char scale; 
   };

Before you can set the fields in the SQLDA for the column values to be retrieved, you must dynamically allocate storage for the SQLDA structure. For C programs, the code looks like this:

sqldaptr = (struct sqlda *) malloc (3 * 44 + 16);

The size of the SQLDA is SQLN * 44 + 16, where the value of the SQLN field is the number of output columns.

You must set the fields in the SQLDA structure for your FETCH statement. Suppose you want to retrieve the columns EMPNO, LASTNAME, and SALARY. The C code to set the SQLDA fields for these columns looks like this:

strcpy(sqldaptr->sqldaid,"SQLDA");
sqldaptr->sqldabc = 148;      /* number bytes of storage allocated for the SQLDA */
sqldaptr->sqln = 3;                              /* number of SQLVAR occurrences */
sqldaptr->sqld = 3;
varptr = (struct sqlvar *) (&(sqldaptr->sqlvar[0]));    /* Point to first SQLVAR */
varptr->sqltype = 452;                                      /* data type CHAR(6) */
varptr->sqllen = 6;
varptr->sqldata = (char *) hva1;
varptr->sqlind = (short *) inda1;
varptr->sqlname.length = 8;
memcpy(varptr->sqlname.data, "\x00\x00\x00\x00\x00\x01\x00\x14",varptr->sqlname.length);
varptr = (struct sqlvar *) (&(sqldaptr->sqlvar[0]) + 1); /* Point to next SQLVAR */
varptr->sqltype = 448;                                  /* data type VARCHAR(15) */
varptr->sqllen = 15;
varptr->sqldata = (char *) hva2;
varptr->sqlind = (short *) inda2;
varptr->sqlname.length = 8;
memcpy(varptr->sqlname.data, "\x00\x00\x00\x00\x00\x01\x00\x14",varptr->sqlname.length);
varptr = (struct sqlvar *) (&(sqldaptr->sqlvar[0]) + 2); /* Point to next SQLVAR */
varptr->sqltype = 485;                                 /* data type DECIMAL(9,2) */
((struct DECLEN *) &(varptr->sqllen))->precision = 9;
((struct DECLEN *) &(varptr->sqllen))->scale = 2;
varptr->sqldata = (char *) hva3;
varptr->sqlind = (short *) inda3;
varptr->sqlname.length = 8;
memcpy(varptr->sqlname.data, "\x00\x00\x00\x00\x00\x01\x00\x14",varptr->sqlname.length);

The SQLDA structure has these fields:

• SQLDABC indicates the number of bytes of storage that are allocated for the SQLDA. The storage includes a 16-byte header and 44 bytes for each SQLVAR field. The value is SQLN x 44 + 16, or 148 for this example.
• SQLN is the number of SQLVAR occurrences (or the number of output columns).
• SQLD is the number of variables in the SQLDA that are used by DB2® when processing the FETCH statement.
• Each SQLVAR occurrence describes a host variable array or buffer into which the values for a column in the result table are to be returned. Within each SQLVAR:
  • SQLTYPE indicates the data type of the column.
  • SQLLEN indicates the length of the column. If the data type is DECIMAL, this field has two parts: the PRECISION and the SCALE.
  • SQLDATA points to the first element of the array for the column values. For this example, assume that your program allocates the dynamic variable arrays hva1, hva2, and hva3, and their indicator arrays inda1, inda2, and inda3.
  • SQLIND points to the first element of the array of indicator values for the column. If SQLTYPE is an odd number, this attribute is required. (If SQLTYPE is an odd number, null values are allowed for the column.)
  • SQLNAME has two parts: the LENGTH and the DATA. The LENGTH is 8. The first two bytes of the DATA field is X'0000'. Bytes 5 and 6 of the DATA field are a flag indicating whether the variable is an array or a FOR n ROWS value. Bytes 7 and 8 are a two-byte binary integer representation of the dimension of the array.

You can open the cursor only after all of the fields have been set in the output SQLDA:

EXEC SQL OPEN C1;

After the OPEN statement, the program fetches the next rowset:

EXEC SQL 
  FETCH NEXT ROWSET FROM C1 
  FOR 20 ROWS
  USING DESCRIPTOR :*sqldaptr; 

The USING clause of the FETCH statement names the SQLDA that describes the columns that are to be retrieved.

After your program executes a FETCH statement to establish the current rowset, you can use a positioned UPDATE statement with either of the following clauses:

• Use WHERE CURRENT OF to modify all of the rows in the current rowset
• Use FOR ROW n OF ROWSET to modify row n in the current rowset

An example of a positioned UPDATE statement that uses the WHERE CURRENT OF clause is:

EXEC SQL
  UPDATE DSN8A10.EMP 
    SET SALARY = 50000
    WHERE CURRENT OF C1
END-EXEC.

When the UPDATE statement is executed, the cursor must be positioned on a row or rowset of the result table. If the cursor is positioned on a row, that row is updated. If the cursor is positioned on a rowset, all of the rows in the rowset are updated.

An example of a positioned UPDATE statement that uses the FOR ROW n OF ROWSET clause is:

EXEC SQL
  UPDATE DSN8A10.EMP 
    SET SALARY = 50000
    FOR CURSOR C1 FOR ROW 5 OF ROWSET
END-EXEC.

When the UPDATE statement is executed, the cursor must be positioned on a rowset of the result table. The specified row (in the example, row 5) of the current rowset is updated.

After your program executes a FETCH statement to establish the current rowset, you can use a positioned DELETE statement with either of the following clauses:

• Use WHERE CURRENT OF to delete all of the rows in the current rowset
• Use FOR ROW n OF ROWSET to delete row n in the current rowset

An example of a positioned DELETE statement that uses the WHERE CURRENT OF clause is:

EXEC SQL
  DELETE FROM DSN8A10.EMP 
    WHERE CURRENT OF C1
END-EXEC.

When the DELETE statement is executed, the cursor must be positioned on a row or rowset of the result table. If the cursor is positioned on a row, that row is deleted, and the cursor is positioned before the next row of its result table. If the cursor is positioned on a rowset, all of the rows in the rowset are deleted, and the cursor is positioned before the next rowset of its result table.

An example of a positioned DELETE statement that uses the FOR ROW n OF ROWSET clause is:

EXEC SQL
  DELETE FROM DSN8A10.EMP 
    FOR CURSOR C1 FOR ROW 5 OF ROWSET
END-EXEC.

When the DELETE statement is executed, the cursor must be positioned on a rowset of the result table. The specified row of the current rowset is deleted, and the cursor remains positioned on that rowset. The deleted row (in the example, row 5 of the rowset) cannot be retrieved or updated.