Obtaining Results from a Result Data Set Using a Cursor


So far, we have looked at a variety of ways in which a query can be constructed using the SELECT SQL statement. We have also seen how the results of a query can be returned to the user when an SQL statement is executed from the Command Editor or the Command Line Processor. However, we have not seen how the results of a query can be obtained when a SELECT statement is executed from an application program.

When a query is executed from within an application, DB2 uses a mechanism known as a cursor to retrieve data values from the result data set produced. The name "cursor" probably originated from the blinking cursor found on early computer screens, and just as that cursor indicated the current position on the screen and identified where typed words would appear next, a DB2 cursor indicates the current position in the result data set (i.e., the current row) and identifies which row of data will be returned to the application next. Depending upon how it has been defined, a cursor can be categorized as follows:

  • Read-only: Read-only cursors are cursors that have been constructed in such a way that rows in their corresponding result data set can be read but not modified or deleted. A cursor is considered read-only if it is based on a read-only SELECT statement. (For example, the statement "SELECT deptname FROM department" is a read-only SELECT statement.)

  • Updatable: Updatable cursors are cursors that have been constructed in such a way that rows in their corresponding result data set can be modified or deleted. A cursor is considered updatable if the FOR UPDATE clause was specified when the cursor was created. (Only one table can be referenced in the SELECT statement that is used to create an updatable cursor.)

  • Ambiguous: Ambiguous cursors are cursors that have been constructed in such a way that it is impossible to tell if they are meant to be read-only or updatable. (Ambiguous cursors are treated as read-only cursors if the BLOCKING ALL option was specified during precompiling or binding. Otherwise, they are considered updatable.)

Regardless of which type of cursor used, the following steps must be performed in order for a cursor is to be incorporated into an application program:

  1. Declare (define) the cursor along with its type and associate it with the desired query (SELECT or VALUES SQL statement).

  2. Open the cursor. This action will cause the corresponding query to be executed and a result data set to be produced.

  3. Retrieve (fetch) each row in the result data set, one by one, until an "End of data" condition occurs-each time a row is retrieved from the result data set, the cursor is automatically moved to the next row.

  4. If appropriate, alter or delete the current row by executing an UPDATE ... WHERE CURRENT OF or a DELETE ... WHERE CURRENT OF SQL statement (only if the cursor is updatable).

  5. Close the cursor. This action will cause the result data set that was produced when the corresponding query was executed to be deleted.

With DB2 (as with most other relational database management systems), the following SQL statements are used to carry out the preceding steps:

 DECLARE CURSOR OPEN FETCH CLOSE 

The DECLARE CURSOR Statement

Before a cursor can be used in an application program, it must be created and associated with the SELECT statement that will be used to generate its corresponding result data set. This is done by executing the DECLARE CURSOR SQL statement. The basic syntax for this statement is:

 DECLARE CURSOR [CursorName] <WITH HOLD> <WITH RETURN <TO CLIENT | TO CALLER>> FOR [[SELECTStatement]|[StatementName]] <FOR READ ONLY | FOR FETCH ONLY |   FOR UPDATE <OF [ColumnName, ...]>> 

where:

CursorName

Identifies the name that is to be assigned to the cursor to be created.

SELECTStatement

Identifies a SELECT SQL statement that, when executed, will produce a result data set that is to be associated with the cursor to be created.

StatementName

Identifies a prepared SELECT SQL statement that, when executed, will produce a result data set that is to be associated with the cursor to be created. (This SELECT statement must be prepared with the PREPARE SQL statement before it is used to create a cursor; this statement can contain parameter markers.)

ColumnName

Identifies the name of one or more columns in the result data set to be produced whose values can be modified by performing a positioned update or a positioned delete operation. (Each name provided must identify an existing column in the result data set produced.)

If the WITH HOLD option is specified when the DECLARE CURSOR statement is executed, the cursor created will remain open (once it has been opened) across transaction boundaries and must be explicitly closed. (If this option is not used, the scope of the cursor is limited to the transaction in which it is defined, and the cursor will be closed automatically when the transaction that declares and opens it is terminated.) If the WITH RETURN option is specified when the DECLARE CURSOR statement is executed, it is assumed that the cursor has been created from within a stored procedure and that once opened, the cursor is to remain open when control is passed back to either the calling application or the client application, depending on how the WITH RETURN option was specified.

Tip 

The clauses FOR READ ONLY, FOR FETCH ONLY, and FOR UPDATE <OF [ColumnName, ]> are actually part of the SELECT statement used to build the result data set associated with the cursor and are not part of the DECLARE CURSOR statement's syntax. As you might imagine, the use (or lack) of these clauses determine whether the cursor to be created will be a read-only, updatable, or ambiguous cursor.

Thus, if you wanted to define a read-only cursor named MY_CURSOR that is associated with a result data set that contains values obtained from the WORKDEPT and JOB columns found in a table named EMPLOYEE, you could do so by executing a DECLARE CURSOR statement that looks something like this:

 DECLARE my_cursor CURSOR   FOR SELECT workdept, job FROM employee   FOR READ ONLY 

Multiple cursors can be created within a single application; however, each cursor created (within the same source code file) must be assigned a unique name.

The OPEN Statement

Although a cursor is defined when the DECLARE CURSOR SQL statement is executed, the result data set associated with the cursor is not actually produced until the cursor is opened; when a cursor is opened, all rows that satisfy the query associated with the cursor's definition are retrieved and copied to a result data set. Cursors are opened by executing the OPEN SQL statement. The basic syntax for this statement is:

 OPEN [CursorName] <USING [HostVariable], ... |   USING DESCRIPTOR [DescriptorName]> 

where:

CursorName

Identifies the name to be assigned to the cursor to be opened.

HostVariable

Identifies one or more host variables that are to be used to provide values for any parameter markers that were coded in the SELECT statement used to create the cursor to be opened. (Host variables and parameter markers are used to provide dynamic information to the DB2 Optimizer when an SQL statement is prepared for execution.)

DescriptorName

Identifies an SQL Descriptor Area (SQLDA) data structure variable that contains descriptions of each host variable that is to be used to provide values for parameter markers coded in the SELECT statement used to create the cursor to be opened. (The SQLDA data structure variable is another way to provide dynamic information to the DB2 Optimizer when an SQL statement is prepared for execution.)

Thus, if you wanted to open a cursor named MY_CURSOR (which, in turn, would cause the corresponding result data set to be produced), you could do so by executing an OPEN statement that looks like this:

 OPEN my_cursor 

On the other hand, if you wanted to open a cursor named MY_CURSOR and associate two host variables (named LASTNAME and FIRSTNAME) with parameter markers that were coded in the SELECT statement that was used to create the cursor to be opened, you could do so by executing an OPEN statement that looks like this:

 OPEN MY_CURSOR USING :lastname, :firstname 

It is important to note that the rows of the result data set associated with a query may be derived during the execution of the OPEN statement (in which case a temporary table may be created to hold them); or they may be derived during the execution of each subsequent FETCH statement. In either case, when a cursor is opened, it is placed in the "Open" state, and the cursor pointer is positioned before the first row of data in the result data set produced; if the result data set is empty, the position of the cursor is effectively "after the last row," and any subsequent FETCH operations performed will generate a NOT FOUND (+100) condition.

Tip 

It is important to note that once a cursor has been opened, it can be in one of three possible positions: "Before a Row of Data," "On a Row of Data," or "After the Last Row of Data." If a cursor is positioned "Before a Row of Data," it will be moved just before the first row of the result data set, and the data values stored in that row will be assigned to the appropriate host variables when the FETCH statement is executed. If a cursor is positioned "On a Row of Data" when the FETCH statement is executed, it will be moved to the next row in the result data set (if one exists), and the data values stored in that row will be assigned to the appropriate host variables. If a cursor is positioned on the last row of the result data set when the FETCH statement is executed, it will be moved to the "After the Last Row of Data" position, the value +100 will be assigned to the sqlcode field of the current SQLCA data structure variable, and the value "02000" will be assigned to the sqlstate field of the current SQLCA data structure variable. (In this case, no data is copied to the host variables specified.)

The FETCH Statement

Once a cursor has been opened, data is retrieved from its associated result data set by calling the FETCH statement repeatedly until all records have been processed. The basic syntax for the FETCH statement is:

 FETCH <FROM> [CursorName] INTO [HostVariable, ...] 

or

 FETCH <FROM> [CursorName] USING DESCRIPTOR [DescriptorName] 

where

CursorName

Identifies the name assigned to the cursor that data is to be retrieved from.

HostVariable

Identifies one or more host variables to which values obtained from the result data set associated with the specified cursor are to be copied.

DescriptorName

Identifies an SQL Descriptor Area (SQLDA) data structure variable that contains descriptions of each host variable to which values obtained from the result data set associated with the specified cursor are to be copied.

Thus, if you wanted to retrieve a record from the result data set associated with a cursor named MY_CURSOR and copy the values obtained to two host variables named DEPTNUMBER and DEPTNAME, you could do so by executing a FETCH statement that looks something like this:

 FETCH FROM my_cursor CURSOR INTO :deptnumber, :deptname 

The CLOSE Statement

When all records stored in the result data set associated with a cursor have been retrieved (and copied to host variables) or when the result data set associated with a cursor is no longer needed, it can be destroyed by executing the CLOSE SQL statement. The syntax for this statement is:

 CLOSE [CursorName] <WITH RELEASE> 

where:

CursorName

Identifies the name assigned to the cursor to be closed.

If the WITH RELEASE option is specified when the CLOSE statement is executed, an attempt will be made to release all locks that were acquired on behalf of the cursor. (It is important to note that not all of the locks acquired are necessarily released; some locks may be held for other operations or activities.)

Therefore, if you wanted to close a cursor named MY_CURSOR and destroy its associated result data set, you could do so by executing a CLOSE statement that looks like this:

 CLOSE my_cursor 

Putting It All Together

Now that we have seen how each of the cursor-processing statements available are used, let's examine how they are typically coded in an application. An application written in the C programming language that uses a cursor to obtain and print employee identification numbers and last names for all employees who have the job title DESIGNER might look something like this:

 #include <stdio.h> #include <stdlib.h> #include <sql.h> void main() {   /* Include The SQLCA Data Structure Variable */   EXEC SQL INCLUDE SQLCA;   /* Declare The SQL Host Memory Variables */   EXEC SQL BEGIN DECLARE SECTION;     char    EmployeeNo[7];     char    LastName[16];   EXEC SQL END DECLARE SECTION;   /* Connect To The SAMPLE Database */   EXEC SQL CONNECT TO sample USER db2admin USING ibmdb2;     /* Declare A Cursor */   EXEC SQL DECLARE c1 CURSOR FOR     SELECT empno, lastname     FROM employee     WHERE job = 'DESIGNER';   /* Open The Cursor */   EXEC SQL OPEN c1;   /* Fetch The Records */   while (sqlca.sqlcode == SQL_RC_OK)   {     /* Retrieve A Record */     EXEC SQL FETCH c1       INTO :EmployeeNo, :LastName     /* Print The Information Retrieved */     if (sqlca.sqlcode == SQL_RC_OK)       printf("%s, %s\n", EmployeeNo, LastName);   }   /* Close The Cursor */   EXEC SQL CLOSE c1;   /* Issue A COMMIT To Free All Locks */   EXEC SQL COMMIT;   /* Disconnect From The SAMPLE Database */   EXEC SQL DISCONNECT CURRENT; } 

Remember, an application can use several cursors concurrently; however, each cursor must have its own unique name and its own set of DECLARE CURSOR, OPEN, FETCH, and CLOSE SQL statements.




DB2 9 Fundamentals Certification Study Guide
DB2 9 Fundamentals: Certification Study Guide
ISBN: 1583470727
EAN: 2147483647
Year: 2007
Pages: 93

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net