Re: ECPG mass retrieval?

How about:

EXEC SQL BEGIN DECLARE SECTION;
   int *num = NULL
   char **string = NULL;
   int low, high;
   char *context = "some context";
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT  number, string  INTO :num, string
    WHERE number BETWEEN :low AND :hi AND string = :context;

num_rec_returned = sqlca.sqlerrd[2];

for(i = 0; i < num_rec_returned; ++i)
    printf("%d %s", num, string);

free(num);
free(string); 

ECPG will alloc the memory it needs, you just need to remember to free()
it.  And yes, free(string) frees all of string and string[0], string[1], ... string[n].

Just remember to pass in NULL pointers to your SELECT statement.


On Mon, 2004-03-29 at 06:13, Stergios Zissakis wrote:
> Hi there,
>
> It is possible to do bulk retrievals using ECPG?
>
> What I mean is that I want to be able to delcare a host array variable, of
> size 100 for example, and get
> to retrive my data in batches of 100 rows.
>
> To be more specific the following piece of code, using a cursor, results in
> the databse being accessed at every fetch statement degrading performance:
>
> 		sprintf(stmt, "select * from test where number between %d and %d and
> string = \'%s\'", lower, upper, context);
>
> 		EXEC SQL PREPARE S FROM :stmt;
> 		EXEC SQL AT :ctx DECLARE curs CURSOR FOR S;
> 		EXEC SQL AT :ctx OPEN curs;
>
> 		EXEC SQL WHENEVER NOT FOUND DO break;
>
> 		while( true )
> 		{
> 			EXEC SQL AT :ctx FETCH NEXT FROM curs INTO :num, :str;
> 		}
>
>
> What alternatives do I have to fetch things in bulk?
>
> Thanks for your help.
>
> Regards,
>
> Stergios Zissakis
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

-- 

Edmund Bacon <ebacon@xxxxxxxxxxxxx>