Thread: ECPG mass retrieval?

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

<br /> How about:<br /><br /> EXEC SQL BEGIN DECLARE SECTION;<br />    int *num = NULL<br />    char **string =
NULL;<br/>    int low, high;<br />    char *context = "some context";<br /> EXEC SQL END DECLARE SECTION;<br /><br />
EXECSQL SELECT  number, string  INTO :num, string<br />     WHERE number BETWEEN :low AND :hi AND string = :context;<br
/><br/> num_rec_returned = sqlca.sqlerrd[2];<br /><br /> for(i = 0; i < num_rec_returned; ++i)<br />     printf("%d
%s",num, string);<br /><br /> free(num);<br /> free(string); <br /><br /> ECPG will alloc the memory it needs, you just
needto remember to free()<br /> it.  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-29at 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, "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</i></font></pre></blockquote><pre><table cellpadding="0" cellspacing="0"
width="100%">
<tr>
<td>
-- <br />
Edmund Bacon <<a href="mailto:ebacon@onesystem.com"><u>ebacon@onesystem.com</u></a>>
</td>
</tr>
</table>
</pre>

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 <ebacon@onesystem.com> 

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!