Skip site navigation (1) Skip section navigation (2)

Re: Using FETCH ALL with descriptor areas

From: andy rost <andy(dot)rost(at)noaa(dot)gov>
To: Joachim Wieland <joe(at)mcknight(dot)de>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: Using FETCH ALL with descriptor areas
Date: 2006-08-02 18:07:34
Message-ID: 44D0E9E6.1010009@noaa.gov (view raw or flat)
Thread:
Lists: 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(dot)rost(at)noaa(dot)gov
http://www.nohrsc.noaa.gov
--------------------------------------------------------------------------------



In response to

pgsql-interfaces by date

Next:From: AndroDate: 2006-08-04 14:38:20
Subject: PQftype() and Oid
Previous:From: Michael FuhrDate: 2006-08-02 15:11:16
Subject: Re: PGconn structure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group