X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 565D1D1EE42
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Mar 2004 14:50:51 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 18628-07
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Mar 2004 10:50:58 -0400 (AST)
Received: from happy.onesystem.ca (happy.onesystem.ca [205.206.61.17])
	by svr1.postgresql.org (Postfix) with ESMTP id 7BA84D1EE36
	for <pgsql-interfaces@postgresql.org>;
	Mon, 29 Mar 2004 10:50:48 -0400 (AST)
Received: from elb_lx.onesystem.ca ([10.1.1.50])
	by happy.onesystem.ca with asmtp (Exim 4.14)
	id 1B7y6J-00007c-Ej; Mon, 29 Mar 2004 07:50:55 -0700
Subject: Re: ECPG mass retrieval?
From: Edmund Bacon <ebacon@onesystem.com>
To: Stergios Zissakis <szis@intranet.gr>
Cc: pgsql-interfaces <pgsql-interfaces@postgresql.org>
In-Reply-To: <NGBBJHBHDKPNOODGDMMAKELNCGAA.szis@intranet.gr>
References: <NGBBJHBHDKPNOODGDMMAKELNCGAA.szis@intranet.gr>
Content-Type: multipart/alternative; boundary="=-TZBbVlWsbzC9BtS2R328"
Message-Id: <1080571854.10084.10.camel@elb_lx.onesystem.ca>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 
Date: Mon, 29 Mar 2004 07:50:55 -0700
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200403/38
X-Sequence-Number: 4817

--=-TZBbVlWsbzC9BtS2R328
Content-Type: text/plain
Content-Transfer-Encoding: 7bit


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>

--=-TZBbVlWsbzC9BtS2R328
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.0.10">
</HEAD>
<BODY>
<BR>
How about:<BR>
<BR>
EXEC SQL BEGIN DECLARE SECTION;<BR>
&nbsp;&nbsp; int *num = NULL<BR>
&nbsp;&nbsp; char **string = NULL;<BR>
&nbsp;&nbsp; int low, high;<BR>
&nbsp;&nbsp; char *context = &quot;some context&quot;;<BR>
EXEC SQL END DECLARE SECTION;<BR>
<BR>
EXEC SQL SELECT&nbsp; number, string&nbsp; INTO :num, string<BR>
&nbsp;&nbsp;&nbsp; WHERE number BETWEEN :low AND :hi AND string = :context;<BR>
<BR>
num_rec_returned = sqlca.sqlerrd[2];<BR>
<BR>
for(i = 0; i &lt; num_rec_returned; ++i)<BR>
&nbsp;&nbsp;&nbsp; printf(&quot;%d %s&quot;, num, string);<BR>
<BR>
free(num);<BR>
free(string); <BR>
<BR>
ECPG will alloc the memory it needs, you just need to remember to free()<BR>
it.&nbsp; And yes, free(string) frees all of string and string[0], string[1], ... string[n].<BR>
<BR>
Just remember to pass in NULL pointers to your SELECT statement.<BR>
<BR>
<BR>
On Mon, 2004-03-29 at 06:13, Stergios Zissakis wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE><FONT COLOR="#737373"><I>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, &quot;select * from test where number between %d and %d and
string = \'%s\'&quot;, 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</I></FONT></PRE>
</BLOCKQUOTE>
<PRE><TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
-- <BR>
Edmund Bacon &lt;<A HREF="mailto:ebacon@onesystem.com"><U>ebacon@onesystem.com</U></A>&gt;
</TD>
</TR>
</TABLE>
</PRE>
</BODY>
</HTML>

--=-TZBbVlWsbzC9BtS2R328--