pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedECPG mass retrieval?
4+ messages / 4 participants
[nested] [flat]
* ECPG mass retrieval?
@ 2004-03-29 13:13 Stergios Zissakis <szis@intranet.gr>
0 siblings, 2 replies; 4+ messages in thread
From: Stergios Zissakis @ 2004-03-29 13:13 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ECPG mass retrieval?
@ 2004-03-29 14:50 Edmund Bacon <ebacon@onesystem.com>
parent: Stergios Zissakis <szis@intranet.gr>
1 sibling, 0 replies; 4+ messages in thread
From: Edmund Bacon @ 2004-03-29 14:50 UTC (permalink / raw)
To: Stergios Zissakis <szis@intranet.gr>; +Cc: pgsql-interfaces
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>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ECPG mass retrieval?
@ 2004-03-29 15:02 Goulet, Dick <DGoulet@vicr.com>
0 siblings, 0 replies; 4+ messages in thread
From: Goulet, Dick @ 2004-03-29 15:02 UTC (permalink / raw)
To: pgsql-interfaces
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@onesystem.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@onesystem.com> ebacon@onesystem.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ECPG mass retrieval?
@ 2004-03-29 15:15 Michael Meskes <meskes@postgresql.org>
parent: Stergios Zissakis <szis@intranet.gr>
1 sibling, 0 replies; 4+ messages in thread
From: Michael Meskes @ 2004-03-29 15:15 UTC (permalink / raw)
To: Stergios Zissakis <szis@intranet.gr>; +Cc: pgsql-interfaces
On Mon, Mar 29, 2004 at 04:13:22PM +0300, Stergios Zissakis wrote:
> It is possible to do bulk retrievals using ECPG?
Yes.
> What alternatives do I have to fetch things in bulk?
Just look into test1.pgc in the source tree to see how it works. Of
course, as already pointed out, you don't have to use fixed-size arrays.
Ecpg can allocate the memory for you.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-03-29 15:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-29 13:13 ECPG mass retrieval? Stergios Zissakis <szis@intranet.gr>
2004-03-29 14:50 ` Edmund Bacon <ebacon@onesystem.com>
2004-03-29 15:15 ` Michael Meskes <meskes@postgresql.org>
2004-03-29 15:02 Re: ECPG mass retrieval? Goulet, Dick <DGoulet@vicr.com>
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