Table of Contents Previous Next


3 Using Embedded SQL : 3.2 Example - Using a Cursor to Process a Result Set

1.
Use the DECLARE CURSOR statement to define a cursor.
2.
Use the OPEN CURSOR statement to open the cursor.
3.
Use the FETCH statement to retrieve data from a cursor.
4.
Use the CLOSE CURSOR statement to close the cursor.
After declaring host variables, our example connects to the edb database using a user-supplied role name and password, and queries the emp table. The query returns the values into a cursor named employees. The code sample then opens the cursor, and loops through the result set a row at a time, printing the result set. When the sample detects the end of the result set, it closes the connection.
:v_empno, :v_ename, :v_sal, :v_comm INDICATOR :v_comm_ind;
argv[] is an array that contains the command line arguments entered when the user runs the client application. argv[1] contains the first command line argument (in this case, a username), and argv[2] contains the second command line argument (a password); please note that we have omitted the error-checking code you would normally include a real-world application. The declaration initializes the values of username and password, setting them to the values entered when the user invoked the client application.
You may be thinking that you could refer to argv[1] and argv[2] in a SQL statement (instead of creating a separate copy of each variable); that will not work. All host variables must be declared within a BEGIN/END DECLARE SECTION (unless you are compiling in PROC mode). Since argv is a function parameter (not an automatic variable), it cannot be declared within a BEGIN/END DECLARE SECTION. If you are compiling in PROC mode, you can refer to any C variable within a SQL statement.
The CONNECT statement creates a connection to the edb database, using the values found in the :username and :password host variables to authenticate the application to the server when connecting.
employees will contain the result set of a SELECT statement on the emp table. The query returns employee information from the following columns: empno, ename, sal and comm. Notice that when you declare a cursor, you do not include an INTO clause - instead, you specify the target variables (or descriptors) when you FETCH from the cursor.
In the subsequent FETCH section, the client application will loop through the contents of the cursor; the client application includes a WHENEVER statement that instructs the server to break (that is, terminate the loop) when it reaches the end of the cursor:
The client application then uses a FETCH statement to retrieve each row from the cursor INTO the previously declared host variables:
:v_empno, :v_ename, :v_sal, :v_comm INDICATOR :v_comm_ind;
The FETCH statement uses an INTO clause to assign the retrieved values into the :v_empno, :v_ename, :v_sal and :v_comm host variables (and the :v_comm_ind null indicator). The first value in the cursor is assigned to the first variable listed in the INTO clause, the second value is assigned to the second variable, and so on.
The FETCH statement also includes the INDICATOR keyword and a host variable to hold a null indicator. If the comm column for the retrieved record contains a NULL value, v_comm_ind is set to a non-zero value, indicating that the column is NULL.
If the null indicator is 0 (that is, false), v_comm contains a meaningful value, and the printf function displays the commission. If the null indicator contains a non-zero value, comm is NULL, and printf displays the string 'NULL'. Please note that a host variable (other than a null indicator) contains no meaningful value if you fetch a NULL into that host variable; you must use null indicators for any value which may be NULL.
The final statements in the code sample close the cursor (employees), and the connection to the server:

3 Using Embedded SQL : 3.2 Example - Using a Cursor to Process a Result Set

Table of Contents Previous Next