pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing FETCH ALL with descriptor areas
3+ messages / 2 participants
[nested] [flat]
* Using FETCH ALL with descriptor areas
@ 2006-07-28 20:35 andy rost <andy.rost@noaa.gov>
0 siblings, 1 reply; 3+ messages in thread
From: andy rost @ 2006-07-28 20:35 UTC (permalink / raw)
To: pgsql-interfaces
Is it possible, in ECPG, to use FETCH ALL with descriptor areas? If so,
can anyone provide a simple example. If not, does anyone have a get
around. We've found FETCH NEXT to be pretty expensive when selecting
even a modest number of tuples.
--
--------------------------------------------------------------------------------
Andrew Rost
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Dr. West, Chanhassen, MN 55317-8582
Voice: (952)361-6610 x 234
Fax: (952)361-6634
andy.rost@noaa.gov
http://www.nohrsc.noaa.gov
--------------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using FETCH ALL with descriptor areas
@ 2006-08-01 07:39 Joachim Wieland <joe@mcknight.de>
parent: andy rost <andy.rost@noaa.gov>
0 siblings, 1 reply; 3+ messages in thread
From: Joachim Wieland @ 2006-08-01 07:39 UTC (permalink / raw)
To: andy rost <andy.rost@noaa.gov>; +Cc: pgsql-interfaces
On Fri, Jul 28, 2006 at 03:35:06PM -0500, andy rost wrote:
> Is it possible, in ECPG, to use FETCH ALL with descriptor areas? If so,
> can anyone provide a simple example. If not, does anyone have a get
> around. We've found FETCH NEXT to be pretty expensive when selecting
> even a modest number of tuples.
I don't know if it is what you're looking for nor if it is faster at all but
I attach an example with descriptors and auto-allocation.
Joachim
--
Joachim Wieland joe@mcknight.de
GPG key available
#include <stdio.h>
exec sql include sqlca;
#include <stdlib.h>
exec sql include ../regression;
int main(void)
{
exec sql begin declare section;
int *ip1=0;
char **cp2=0;
int *ipointer1=0;
int *ipointer2=0;
int colnum;
exec sql end declare section;
int i;
ECPGdebug(1, stderr);
exec sql whenever sqlerror do sqlprint();
exec sql connect to REGRESSDB1;
exec sql create table test (a int, b text);
exec sql insert into test values (1, 'one');
exec sql insert into test values (2, 'two');
exec sql insert into test values (NULL, 'three');
exec sql insert into test values (4, 'four');
exec sql insert into test values (5, NULL);
exec sql insert into test values (NULL, NULL);
exec sql allocate descriptor mydesc;
exec sql select * into descriptor mydesc from test;
exec sql get descriptor mydesc :colnum=COUNT;
exec sql get descriptor mydesc value 1 :ip1=DATA, :ipointer1=INDICATOR;
exec sql get descriptor mydesc value 2 :cp2=DATA, :ipointer2=INDICATOR;
printf("Result (%d columns):\n", colnum);
for (i=0;i < sqlca.sqlerrd[2];++i)
{
if (ipointer1[i]) printf("NULL, ");
else printf("%d, ",ip1[i]);
if (ipointer2[i]) printf("NULL, ");
else printf("'%s', ",cp2[i]);
printf("\n");
}
ECPGfree_auto_mem();
printf("\n");
exec sql deallocate descriptor mydesc;
exec sql rollback;
exec sql disconnect;
return 0;
}
Attachments:
[text/plain] dynalloc2.pgc (1.4K, ../../20060801073948.GA2236@mcknight.de/2-dynalloc2.pgc)
download | inline:
#include <stdio.h>
exec sql include sqlca;
#include <stdlib.h>
exec sql include ../regression;
int main(void)
{
exec sql begin declare section;
int *ip1=0;
char **cp2=0;
int *ipointer1=0;
int *ipointer2=0;
int colnum;
exec sql end declare section;
int i;
ECPGdebug(1, stderr);
exec sql whenever sqlerror do sqlprint();
exec sql connect to REGRESSDB1;
exec sql create table test (a int, b text);
exec sql insert into test values (1, 'one');
exec sql insert into test values (2, 'two');
exec sql insert into test values (NULL, 'three');
exec sql insert into test values (4, 'four');
exec sql insert into test values (5, NULL);
exec sql insert into test values (NULL, NULL);
exec sql allocate descriptor mydesc;
exec sql select * into descriptor mydesc from test;
exec sql get descriptor mydesc :colnum=COUNT;
exec sql get descriptor mydesc value 1 :ip1=DATA, :ipointer1=INDICATOR;
exec sql get descriptor mydesc value 2 :cp2=DATA, :ipointer2=INDICATOR;
printf("Result (%d columns):\n", colnum);
for (i=0;i < sqlca.sqlerrd[2];++i)
{
if (ipointer1[i]) printf("NULL, ");
else printf("%d, ",ip1[i]);
if (ipointer2[i]) printf("NULL, ");
else printf("'%s', ",cp2[i]);
printf("\n");
}
ECPGfree_auto_mem();
printf("\n");
exec sql deallocate descriptor mydesc;
exec sql rollback;
exec sql disconnect;
return 0;
}
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using FETCH ALL with descriptor areas
@ 2006-08-02 18:07 andy rost <andy.rost@noaa.gov>
parent: Joachim Wieland <joe@mcknight.de>
0 siblings, 0 replies; 3+ messages in thread
From: andy rost @ 2006-08-02 18:07 UTC (permalink / raw)
To: Joachim Wieland <joe@mcknight.de>; +Cc: pgsql-interfaces
Worked like a charm. And its fast. Thanks Joachim
Joachim Wieland wrote:
> On Fri, Jul 28, 2006 at 03:35:06PM -0500, andy rost wrote:
>
>>Is it possible, in ECPG, to use FETCH ALL with descriptor areas? If so,
>>can anyone provide a simple example. If not, does anyone have a get
>>around. We've found FETCH NEXT to be pretty expensive when selecting
>>even a modest number of tuples.
>
>
> I don't know if it is what you're looking for nor if it is faster at all but
> I attach an example with descriptors and auto-allocation.
>
>
> Joachim
>
>
>
> ------------------------------------------------------------------------
>
> #include <stdio.h>
> exec sql include sqlca;
> #include <stdlib.h>
> exec sql include ../regression;
>
> int main(void)
> {
> exec sql begin declare section;
> int *ip1=0;
> char **cp2=0;
> int *ipointer1=0;
> int *ipointer2=0;
> int colnum;
> exec sql end declare section;
> int i;
>
> ECPGdebug(1, stderr);
>
> exec sql whenever sqlerror do sqlprint();
> exec sql connect to REGRESSDB1;
>
> exec sql create table test (a int, b text);
> exec sql insert into test values (1, 'one');
> exec sql insert into test values (2, 'two');
> exec sql insert into test values (NULL, 'three');
> exec sql insert into test values (4, 'four');
> exec sql insert into test values (5, NULL);
> exec sql insert into test values (NULL, NULL);
>
> exec sql allocate descriptor mydesc;
> exec sql select * into descriptor mydesc from test;
> exec sql get descriptor mydesc :colnum=COUNT;
> exec sql get descriptor mydesc value 1 :ip1=DATA, :ipointer1=INDICATOR;
> exec sql get descriptor mydesc value 2 :cp2=DATA, :ipointer2=INDICATOR;
>
> printf("Result (%d columns):\n", colnum);
> for (i=0;i < sqlca.sqlerrd[2];++i)
> {
> if (ipointer1[i]) printf("NULL, ");
> else printf("%d, ",ip1[i]);
>
> if (ipointer2[i]) printf("NULL, ");
> else printf("'%s', ",cp2[i]);
> printf("\n");
> }
> ECPGfree_auto_mem();
> printf("\n");
>
> exec sql deallocate descriptor mydesc;
> exec sql rollback;
> exec sql disconnect;
> return 0;
> }
--
--------------------------------------------------------------------------------
Andrew Rost
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Dr. West, Chanhassen, MN 55317-8582
Voice: (952)361-6610 x 234
Fax: (952)361-6634
andy.rost@noaa.gov
http://www.nohrsc.noaa.gov
--------------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-08-02 18:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-28 20:35 Using FETCH ALL with descriptor areas andy rost <andy.rost@noaa.gov>
2006-08-01 07:39 ` Joachim Wieland <joe@mcknight.de>
2006-08-02 18:07 ` andy rost <andy.rost@noaa.gov>
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