Re: ECPG mass retrieval?

BTW: As I've asked before, but I guess got ignored, does anyone have a make file for ECPG on the WIN32 platform?

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
From: Edmund Bacon [mailto:ebacon(at)onesystem(dot)com]
Sent: Monday, March 29, 2004 9:51 AM
To: Stergios Zissakis
Cc: pgsql-interfaces
Subject: Re: [INTERFACES] 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 < <mailto:ebacon(at)onesystem(dot)com> ebacon(at)onesystem(dot)com>