Date: Tue, 1 Aug 2006 09:39:48 +0200
From: Joachim Wieland <joe@mcknight.de>
To: andy rost <andy.rost@noaa.gov>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: Using FETCH ALL with descriptor areas
Message-ID: <20060801073948.GA2236@mcknight.de>
References: <44CA74FA.5010305@noaa.gov>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="azLHFNyN32YCQGCU"
Content-Disposition: inline
In-Reply-To: <44CA74FA.5010305@noaa.gov>
User-Agent: Mutt/1.5.9i


--azLHFNyN32YCQGCU
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--azLHFNyN32YCQGCU
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="dynalloc2.pgc"

#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;
}

--azLHFNyN32YCQGCU--