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 contains tags for the location of the new elements of SQL code. Use the following command to copy the template to your local directory:
$ cp /usr/lib/allbase/hpsql/programs/TMPLC2 showtitle.sql 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, as follows:
/******** Insert Additional Host Variable Declarations ***/
char Selection[41];
char Timing[21];
sqlind Timingind;
/*********************************************************/
|
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;
/*********************************************************/
|
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 host language statements (for example, printf in C, or DISPLAY in COBOL)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 procedure that is called after the embedded SELECT statement. First, include a call to a procedure that fetches titles:
/******** Insert Procedure Call here *********************/
SelectTitles();
/*********************************************************/
|
This line should be inserted immediately after the statements that display data for the Albums table.
Next, code the procedure itself, as follows:
/******** Insert Fetch-and-Display Procedure here ********/
int SelectTitles()
{
printf("\nSelection Timing\n\n");
EXEC SQL OPEN C1;
do {
FetchTitles();
} while (sqlca.sqlcode != 100);
EXEC SQL CLOSE C1;
EXEC SQL COMMIT WORK;
}
int FetchTitles()
{
EXEC SQL FETCH C1 INTO :Selection, :Timing :Timingind;
if(Timingind == -1) sprintf(Timing,"");
if (sqlca.sqlcode != 100)
printf("%-35s%s\n",Selection, Timing);
}
/*********************************************************/
|
Notice that 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. The loop terminates as soon as sqlcode is 100,
indicating there are no more rows in the cursor. 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 code 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 the revised application program:
$ psqlc MUSICDBE -i showtitle.sql -d
|
Compile and link as follows on the Series 800:
$ cc showtitle.c -o showtitle.r -lsql -lcl -lportnls
|
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"):
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
|
