From: Edmund Bacon <ebacon@onesystem.com>
To: Stergios Zissakis <szis@intranet.gr>
Cc: pgsql-interfaces <pgsql-interfaces@postgresql.org>
Subject: Re: ECPG mass retrieval?
Date: Mon, 29 Mar 2004 07:50:55 -0700
Message-ID: <1080571854.10084.10.camel@elb_lx.onesystem.ca> (raw)
In-Reply-To: <NGBBJHBHDKPNOODGDMMAKELNCGAA.szis@intranet.gr>
References: <NGBBJHBHDKPNOODGDMMAKELNCGAA.szis@intranet.gr>
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@onesystem.com>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: ebacon@onesystem.com, szis@intranet.gr
Subject: Re: ECPG mass retrieval?
In-Reply-To: <1080571854.10084.10.camel@elb_lx.onesystem.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox