The following example shows how to nest two cursors. Cursor c2 depends upon the value fetched into title-id from cursor c1.
The program gets the value of title-id at open time, not at declare time.
exec sql include sqlca;
main()
{
exec sql begin declare section;
CS_CHAR title_id[7];
CS_CHAR title[81];
CS_INT totalsales;
CS_SMALLINT salesind;
CS_CHAR au_lname[41];
CS_CHAR au_fname[21];
exec sql end declare section;
exec sql whenever sqlerror call error_handler();
exec sql whenever sqlwarning call error_handler();
exec sql whenever not found continue;
exec sql connect “sa” identified by ““;
exec sql declare c1 cursor for
select title_id, title, total_sales from pubs2..titles;
exec sql declare c2 cursor for
select au_lname, au_fname from pubs2..authors
where au_id in (select au_id from pubs2..titleauthor
where title_id = :title_id);
exec sql open c1;
for (;;)
{
exec sql fetch c1 into :title_id, :title,
:totalsales :salesind;
if (sqlca.sqlcode ==100)
break;
printf(“\nTitle ID: %s, Total Sales: %d”, title_id, totalsales);
printf(“\n%s”, title);
if (totalsales > 10)
{
exec sql open c2;
for (;;)
{
exec sql fetch c2 into :au_lname, :au_fname;
if (sqlca.sqlcode == 100)
break;
printf(“\n\tauthor: %s, %s”, au_lname, au_fname);
}
exec sql close c2;
}
}
exec sql close c1;
exec sql disconnect all;
}
error_handler()
{
printf(“%d\n%s\n”,sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
exec sql disconnect all;
exit(0);
}
The following example is for insensitive scrollable cursors:
/*
** example4.cp
**
** This example is a non-interactive query program that
** shows the user some actions executed by a scrollable,
** insensitive cursor. This serves as a demo for usage
** of scrollable cursors in ESQL/C.
*/
#include <stdio.h>
#include "sybsqlex.h"
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
#define TITLE_STRING 65
EXEC SQL END DECLARE SECTION;
void error_handler();
void warning_handler();
void notfound_handler();
int
main(int argc, char *argv[])
{
EXEC SQL BEGIN DECLARE SECTION;
char username[30];
char password[30];
char a_type[TITLE_STRING+1];
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR CALL error_handler();
EXEC SQL WHENEVER SQLWARNING CALL warning_handler();
EXEC SQL WHENEVER NOT FOUND CALL notfound_handler();
strcpy(username, USER);
strcpy(password, PASSWORD);
EXEC SQL CONNECT :username IDENTIFIED BY :password;
EXEC SQL USE pubs2;
/*
** Declare an insensitive scrollable cursor against the
** titles table.
*/
EXEC SQL DECLARE typelist INSENSITIVE SCROLL CURSOR FOR
SELECT DISTINCT title FROM titles;
EXEC SQL OPEN typelist;
printf("\n==> Selecting the FIRST book Title:\n");
/*
** Fetch the first row in cursor resultset
*/
EXEC SQL FETCH FIRST FROM typelist INTO :a_type;
printf("\n%s\n", a_type);
/*
** Fetch the last row in cursor resultset
*/
printf("\n==> Selecting the LAST book Title:\n");
EXEC SQL FETCH LAST FROM typelist INTO :a_type;
printf("\n%s\n", a_type);
/*
** Fetch the previous (PRIOR) row based on current
** cursor position
*/
printf("\n==> Selecting the PREVIOUS book Title:\n");
EXEC SQL FETCH PRIOR FROM typelist INTO :a_type;
printf("\n%s\n", a_type);
/*
** Jump 5 rows back from current cursor position
*/
printf("\n==> Rewinding 5 STEPS through the Book
selection...:\n");
EXEC SQL FETCH RELATIVE -5 FROM typelist INTO :a_type;
printf("\n%s\n", a_type);
/*
** Fetch the next row based on current cursor position
*/
printf("\n==> Selecting the NEXT book Title:\n");
EXEC SQL FETCH NEXT FROM typelist INTO :a_type;
printf("\n%s\n", a_type);
/*
** Jump out of the cursor result set. Note that this will
** lead to a "no rows found" condition. There are only 18
** rows in 'titles'.
*/
a_type[0] = '\0';
printf("\n==> Jumping out of the resultset.\n");
EXEC SQL FETCH ABSOLUTE 100 FROM typelist INTO :a_type;
printf("\n%s\n", a_type);
/* Close shop */
EXEC SQL CLOSE typelist;
printf("\n==> That's it for now.\n");
EXEC SQL DISCONNECT DEFAULT;
return(STDEXIT);
}
/* Error handlers deleted */
See the online sample programs for more examples using cursors. For details on accessing the online examples, see the Open Client and Open Server Programmer’s Supplement for your platform.
