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);
}
See the online sample programs for more examples using cursors. For details on accessing the online examples, see the Open Client/Server Programmer’s Supplement for your platform.
