Re: ProC Question!

dorian_at_wam.umd.edu (Dorian Winterfeld) writes:

> Is it possible to open multiple [Pro*C] cursors in the same program?
> Either at the same time, by nesting one cursor inside another, or
> consecutively, one after the other.

Yes, no problem.

> From the manual, the only way
> to 'break out' of a loop is 'WHEN NOT FOUND GOTO..'. This seems very
> limiting as it applies to all SELECT statements.

I think you might have misunderstood how WHENEVER NOT FOUND works. The scope of the statement is physical rather than logical; it applies to all SELECT/FETCH statements which follow it in the source code, until either the physical end of the compilation unit or the next WHENEVER NOT FOUND. So if you don't mind having several labels (yuk) you can use WHENEVER NOT FOUND to control several SELECT/FETCHes. Here's an example:

EXEC SQL WHENEVER NOT FOUND GOTO LABEL_A ; EXEC SQL OPEN CURSOR_A; while (true) do
begin

	EXEC SQL FETCH CURSOR_A INTO ... ;
	EXEC SQL WHENEVER NOT FOUND GOTO LABEL_B ;
	EXEC SQL OPEN CURSOR_B ;

	while (true) do
	begin
		EXEC SQL FETCH CURSOR_B INTO ... ;
	end;

	label_b:
	{ Jump to here when all cursor B rows have been fetched }
	EXEC SQL CLOSE CURSOR_B ;

label_a:
{ Jump to here when all cursor A rows have been fetched } EXEC SQL CLOSE CURSOR_A ; Hope this helps.

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                       6900 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE