Suppose you wanted to display all the selections that are on a particular album in the Albums table in MUSICDBE. So far the application accesses only the Albums table, but you can quickly add the code that will display the multiple selections for each album that are stored in the Titles table.
The device used by embedded SQL to handle multiple-row query results in applications is known as a cursor. You use a cursor in a
sequence of cursor-related SQL statements:
These have to be added to the source program, together with additional
host variable declarations and data display statements for the Titles table.
A second template file names contains tags for the location of the new elements of SQL code. COBOL programmers in MPE/iX should use the following command:
: FCOPY FROM=TMPLCBL2.SAMPLEDB.SYS;TO=STITLES;NEW Return
|
Edit the new file, and include the additional statements as shown in the following sections.
Adding Additional Host Variable Declarations |
 |
Add host variable declarations for two additional columns in the Titles table.
/******** Insert Additional Host Variable Declarations ***/
01 SELECTION PIC X(40).
01 TIMING PIC X(20).
01 TIMINGIND SQLIND.
/*********************************************************/
|
These will hold data for the individual selections to be displayed by the application for each album.
Adding the Cursor Declaration |
|
Next, add a cursor declaration, as follows:
/******** Insert Cursor Declaration here *****************/
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT SELECTION, TIMING
FROM TITLES
WHERE ALBUMCODE = :ALBUMCODE
END-EXEC.
/*********************************************************/
|
In COBOL, this should be inserted inside a routine within the PROCEDURE DIVISION.
Adding a FETCH and Display Loop |
|
After it is declared, a cursor is used in an application when you wish to access a query result that has more than a single row. Use the following
statements to read and display multiple rows of data returned:
Use the OPEN statement to open a cursor that has previously been declared.
Use the FETCH statement to bring data into a set of host variables a row at a time. You use FETCH in a loop until the value of SQLCA.SQLCODE is 100, which indicates there are no more rows.
For each FETCH, use DISPLAY to display each row in the query result.
Use the SQL CLOSE statement to close the cursor.
Use the SQL COMMIT WORK statement to end the transaction.
These statements can be coded inside a COBOL paragraph that is performed after the embedded SELECT statement. Use the following PERFORM statement:
/******** Insert Procedure Call here *********************/
PERFORM B200-SELECT-TITLE THRU B200-EXIT.
/*********************************************************/
|
Here is an example of a fetch-and-display procedure in COBOL:
/******** Insert Fetch-and-Display Procedure here ********/
B200-SELECT-TITLE.
DISPLAY "Selection Timing".
DISPLAY " ".
EXEC SQL OPEN C1 END-EXEC.
PERFORM B300-FETCH-TITLES THRU B300-EXIT
UNTIL SQLCODE = 100.
EXEC SQL CLOSE C1 END-EXEC.
EXEC SQL COMMIT WORK END-EXEC.
B200-EXIT.
EXIT.
B300-FETCH-TITLES.
EXEC SQL
FETCH C1 INTO :SELECTION, :TIMING :TIMINGIND
END-EXEC.
IF TIMINGIND = -1
MOVE SPACES TO TIMING.
IF SQLCODE NOT = 100
DISPLAY SELECTION, TIMING.
B300-EXIT.
EXIT.
/*********************************************************/
|
The revised application makes use of the SQLCODE element in the SQLCA to test for the end of the FETCH loop and to determine whether a row was retrieved in the SELECT statement. You should include similar checks following all embedded SQL statements to ensure the desired result.
The revised application also includes a COMMIT WORK statement following the CLOSE C1. You must include appropriate COMMIT WORK or ROLLBACK WORK statements to ensure concurrency control and data integrity in your applications, just as you would in an interactive session. You can also include explicit BEGIN WORK statements. For complete information, refer to the chapter "Concurrency Control through Locks and Isolation Levels" in the ALLBASE/SQL Reference Manual.
Preprocessing and Compiling the Revised Application |
|
Use the following command to preprocess, compile, and link the revised application program:
: PPCOB STITLES,MUSICDBE, STITLEP Return
|
Run the revised application, and enter a value of 2008 for an album code. The result should look as follows (assuming that you have set up the database as described in the chapter "Setting up a Database with ISQL"):
: STITLEP
Enter an Album Code: 2008 Return
Album Title: Franz Schubert: Lieder
Recording Co: atlantic
Comment:
Selection Timing
Der Saenger 0 00:00:00.000
Fruehlingslied 0 00:04:55.000
Fruehlingslaube 0 00:03:27.000
Vor Meiner Wiege 0 00:05:45.000
Drang in die Ferne 0 00:03:36.000
Der Musensohn 0 00:02:06.000
Viola 0 00:14:34.000
Vergissmeinnicht 0 00:14:16.000
|
