X-Original-To: pgsql-interfaces-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 896649FA4A8 for ; Wed, 2 Aug 2006 15:07:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 75654-03 for ; Wed, 2 Aug 2006 15:07:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from home.nohrsc.nws.gov (home.nohrsc.nws.gov [192.46.108.2]) by postgresql.org (Postfix) with ESMTP id 1BA869F9316 for ; Wed, 2 Aug 2006 15:07:38 -0300 (ADT) Received: from noaa.gov (opps-gw.nohrsc.nws.gov [192.46.108.142]) by home.nohrsc.nws.gov (Postfix) with ESMTP id AE64CA5AF; Wed, 2 Aug 2006 13:07:34 -0500 (CDT) Message-ID: <44D0E9E6.1010009@noaa.gov> Date: Wed, 02 Aug 2006 13:07:34 -0500 From: andy rost User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joachim Wieland Cc: pgsql-interfaces@postgresql.org Subject: Re: Using FETCH ALL with descriptor areas References: <44CA74FA.5010305@noaa.gov> <20060801073948.GA2236@mcknight.de> In-Reply-To: <20060801073948.GA2236@mcknight.de> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/10 X-Sequence-Number: 6224 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 > exec sql include sqlca; > #include > 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 --------------------------------------------------------------------------------