Re: dynamic SQL question

Marty Frongillo wrote:
>
>
> EXEC SQL WHENEVER SQLERROR DO
> dyn_error ("sqlerror\n");
>
> EXEC SQL WHENEVER NOT FOUND do break;
>
> EXEC SQL PREPARE S FROM :select_stmt;
>
> EXEC SQL DECLARE C CURSOR FOR S;
>
> EXEC SQL OPEN C;
>
> for(;;)
> {
> EXEC SQL FETCH C INTO :fields;
>
> num_rows = sqlca.sqlerrd[2];
> }
>
> /* always return the number of rows found */
>
> return (num_rows);
>
> notfound:
> return (-UPA_DB_NO_REGS_FOUND);
> }

Marty,

	You probably are fetching data from the table, but you don't
	terminate the for-ever loop (for(;;)) until you get an error
	or you get NOT FOUND.  So the function will always return
	NOT FOUND unless there is an error, whether you get rows or
	not.  Each fetch will return up to 10 rows, with sqlcode=0
	and sqlca.sqlerrd[2] equal to the number of rows returned
	CUMULATIVELY since the cursor was opened until there are no
	more rows.  At that point it will return 1403 to indicate there
	is no more data.  

	Since this is PRO*C and you have complete control, I would 
	recommend explicitly checking the sqlca.sqlcode after each
	fetch and not use the EXEC SQL WHENEVER construct.
	This way you can end the loop whenever you want.
	Note if there are more rows than your host array holds, you
	will have to do multiple fetches to get all the data.  One
	way we handled this was to have the different functions prepare
	and open the cursor, and then loop around another function that
	fetched rows until there were no more, then return.

	If you still have problems, email me and I can send you an example.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.