Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id D28972E002F for ; Sun, 18 May 2008 22:30:13 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 04066-03 for ; Sun, 18 May 2008 22:29:58 -0300 (ADT) X-Greylist: delayed 00:16:09.073364 by SQLgrey-1.7.6 Received: from omr13.networksolutionsemail.com (omr13.networksolutionsemail.com [205.178.146.63]) by developer.postgresql.org (Postfix) with ESMTP id E2D842E0043 for ; Sun, 18 May 2008 22:30:03 -0300 (ADT) Received: from mail.networksolutionsemail.com (ns-omr13.mgt.hosting.dc2.netsol.com [10.49.6.76]) by omr13.networksolutionsemail.com (8.13.6/8.13.6) with SMTP id m4J1Dqgw027326 for ; Sun, 18 May 2008 21:13:52 -0400 Received: (qmail 21807 invoked by uid 78); 19 May 2008 01:13:52 -0000 Received: from unknown (HELO webmail12) (205.178.146.50) by ns-omr13.lb.hosting.dc2.netsol.com with SMTP; 19 May 2008 01:13:52 -0000 Received: from 64.221.219.104 (clarkhorse@clarktx.com [64.221.219.104]) by webmail12 (Netsol 11.2.30) with WEBMAIL id 10492; Mon, 19 May 2008 01:13:52 +0000 From: clarkhorse@clarktx.com To: pgsql-interfaces@postgresql.org Importance: Normal Sensitivity: Normal Message-ID: X-Mailer: Network Solutions Webmail, Build 11.2.30 X-Originating-IP: [64.221.219.104] X-Forwarded-For: [(null)] Date: Mon, 19 May 2008 01:13:52 +0000 Subject: Newbie problem with from database in C MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="--=_vm_0011_W5749323096_10492_1211159632" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.6 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, RCVD_NUMERIC_HELO=2.599 X-Spam-Level: ** X-Archive-Number: 200805/14 X-Sequence-Number: 6738 ----=_vm_0011_W5749323096_10492_1211159632 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable I have been trying to use use libpq interface to get data from PostgreSQL= to a C variable. I started reading through the book PostgreSQL 2nd Editi= on by Korry and Susan Douglas and ran into a snag as soon as I started tr= ying to read numbers into variables. I started with int32 datatypes which they used in their example, then tri= ed int and int4 then int 16 and int2 which is what they used in there exa= mple by table testing has the data types idno integer runname character varying (5) That is all I put into it for testing. characters are fine so far. I have placed the code I am using below. Would appreciate it if someone could tell me what is wrong or provide me = with a reference on how to do this properly. The code below is marked as '/////Debug' so I could find it and make chan= ges trying to figure out what is going on Thanks, -------------------------------------------------------------------------= ------------------- /* ** File: client3d.c */ #include #include #include #include #include #include #define MAX_PRINT_LEN 40 static bool status_ok( PGresult * result ) { ExecStatusType status =3D PQresultStatus( result ); if( status =3D=3D PGRES_TUPLES_OK || status =3D=3D PGRES_COMMAND_OK ) return( TRUE ); else return( FALSE ); } static PGresult * exec_sql( PGconn * conn, bool destroy_result, const cha= r * text, ... ) { char buf[2048]; va_list args; PGresult * result; va_start( args, text ); vsnprintf( buf, sizeof( buf ), text, args ); result =3D PQexec( conn, buf ); if( destroy_result =3D=3D TRUE ) { if( status_ok( result ) =3D=3D FALSE ) { fprintf( stderr, "%s\n%s\n", buf, PQresultErrorMessage( result )); exit( -1 ); } PQclear( result ); return( NULL ); } else return( result ); } static char separator[MAX_PRINT_LEN+1]; void print_result_set( PGresult * result ) { int col; int row; int * sizes; /* ** Compute the size for each column */ sizes =3D (int *)calloc( PQnfields( result ), sizeof( int )); for( col =3D 0; col < PQnfields( result ); col++ ) { int len =3D 0; for( row =3D 0; row < PQntuples( result ); row++ ) { if( PQgetisnull( result, row, col )) len =3D 0; else len =3D PQgetlength( result, row, col ); if( len > sizes[col] ) sizes[col] =3D len; } if(( len =3D strlen( PQfname( result, col ))) > sizes[col] ) sizes[col] =3D len; if( sizes[col] > MAX_PRINT_LEN ) sizes[col] =3D MAX_PRINT_LEN; } /* ** Print the field names. */ for( col =3D 0; col < PQnfields( result ); col++ ) { printf( "%-*s ", sizes[col], PQfname( result, col )); } printf( "\n" ); /* ** Print the separator line */ memset( separator, '-', MAX_PRINT_LEN ); for( col =3D 0; col < PQnfields( result ); col++ ) { printf( "%*.*s ", sizes[col], sizes[col], separator ); } printf( "\n" ); /* ** Now loop through each of the tuples returned by ** our query and print the results. */ for( row =3D 0; row < PQntuples( result ); row++ ) { for( col =3D 0; col < PQnfields( result ); col++ ) { if( PQgetisnull( result, row, col )) printf( "%*s", sizes[col], "" ); else printf( "%*s ", sizes[col], PQgetvalue( result, row, col )); } printf( "\n" ); } printf( "(%d rows)\n", PQntuples( result )); free( sizes ); } /////////////Debug //////////This is the function that prints out the data void print_binary_result_set( PGresult * result ) { int row; int32 * idno; char * runname; for( row =3D 0; row < PQntuples( result ); row++ ) { idno =3D (int32 *)PQgetvalue( result, row,0); runname =3D (char *)PQgetvalue( result, row,1); printf( "%d %8.8s \n",* idno , runname); } } void process_query( PGconn * connection, const char * query_text ) { PGresult * result; #if 0 if(( result =3D PQexec( connection, query_text )) =3D=3D NULL ) { printf( "%s\n", PQ:errorMessage( connection )); return; } #else exec_sql( connection, TRUE, "BEGIN TRANSACTION" ); //////////////Debug exec_sql( connection, TRUE, "DECLARE mycursor BINARY CURSOR FOR SELECT *= FROM testing" ); result =3D exec_sql( connection, FALSE, "FETCH ALL FROM mycursor" ); #endif if( PQresultStatus( result ) =3D=3D PGRES_TUPLES_OK ) { if( PQbinaryTuples( result )) print_binary_result_set( result ); else print_result_set( result ); } else if( PQresultStatus( result ) =3D=3D PGRES_COMMAND_OK ) { printf( "%s", PQcmdStatus( result )); if( strlen( PQcmdTuples( result ))) printf( " - %s rows\n", PQcmdTuples( result )); else printf( "\n" ); } else { printf( "%s\n", PQresultErrorMessage( result )); } PQclear( result ); exec_sql( connection, TRUE, "END TRANSACTION" ); } int main( int argc, char * argv[] ) { PGconn * connection; if( argc !=3D 2 ) { printf( "usage : %s \"connection-string\"\n", argv[0] ); printf( "example: %s \"user=3Dmyname password=3Dcows\"\n", argv[0]); exit( 1 ); } if(( connection =3D PQconnectdb( argv[1] )) =3D=3D NULL ) { printf( "Fatal error - unable to allocate connection\n" ); exit( 1 ); } if( PQstatus( connection ) !=3D CONNECTION_OK ) printf( "%s\n", PQerrorMessage( connection )); else /////Debug process_query( connection, "SELECT * FROM testing" ); PQfinish( connection ); exit( 0 ); } ----=_vm_0011_W5749323096_10492_1211159632 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable I have been trying to use use libpq inte= rface to get data from PostgreSQL to a C variable.  I started readin= g through the book PostgreSQL 2nd Edition by Korry and Susan Douglas and = ran into a snag as soon as I started trying to read numbers into variable= s.
I started with int32 datatypes which they used in their example, th= en tried int and int4 then int 16 and int2 which is what they used in the= re example

by table testing has the data types
idno integer
= runname character varying (5)

That is all I put into it for testin= g.

characters are fine so far.  I have placed the code I am u= sing below. 
Would appreciate it if someone could tell me what i= s wrong or provide me with a reference on how to do this properly.
The code below is marked as '/////Debug' so I could find it and make cha= nges trying to figure out what is going on


Thanks,

----= -------------------------------------------------------------------------= ---------------

/*
** File: client3d.c
*/

#include &l= t;stdlib.h>
#include <string.h>
#include <stdarg.h><= br>

#include <libpq-fe.h>
#include <postgres.h>
= #include <utils/date.h>

#define MAX_PRINT_LEN   40=

static bool status_ok( PGresult * result )
{
  &n= bsp; ExecStatusType  status =3D PQresultStatus( result );

&nb= sp;   if( status =3D=3D PGRES_TUPLES_OK || status =3D=3D PGRES_= COMMAND_OK )
        return( TRUE )= ;
  else
      return( FALSE );
}<= br>
static PGresult * exec_sql( PGconn * conn, bool destroy_result, co= nst char * text, ... )
{
    char   &= nbsp;    buf[2048];
    va_list &nb= sp;   args;
    PGresult *  result;
<= br>    va_start( args, text );
    vsnpr= intf( buf, sizeof( buf ), text, args );

    result = =3D PQexec( conn, buf );

    if( destroy_result =3D= =3D TRUE )
    {
      = ;  if( status_ok( result ) =3D=3D FALSE )
    = ;    {
        =     fprintf( stderr, "%s\n%s\n", buf, PQresultErrorMessage= ( result ));
         &nb= sp;  exit( -1 );
        }
=
        PQclear( result );
&nbs= p;       return( NULL );
  &nb= sp; }
    else
      &= nbsp; return( result );
}


static char separator[MAX_PRINT_L= EN+1];

void print_result_set( PGresult * result )
{
  i= nt          col;
  i= nt          row;
  i= nt        * sizes;

/*
**&nbs= p; Compute the size for each column
*/
  sizes =3D (int *)call= oc( PQnfields( result ), sizeof( int ));

  for( col =3D 0; co= l < PQnfields( result ); col++ )
  {
    int= len =3D 0;

    for( row =3D 0; row < PQntuples(= result ); row++ )
    {
    &nb= sp; if( PQgetisnull( result, row, col ))
     = ;   len =3D 0;
      else
 =        len =3D PQgetlength( result, row, co= l );

      if( len > sizes[col] )
&= nbsp;       sizes[col] =3D len;
 &n= bsp;  }
    if(( len =3D strlen( PQfname( result, = col ))) > sizes[col] )
      sizes[col] =3D= len;

    if( sizes[col] > MAX_PRINT_LEN )
&n= bsp;     sizes[col] =3D MAX_PRINT_LEN;
  }
/*
**  Print the field names.
*/
  for( col =3D 0= ; col < PQnfields( result ); col++ )
  {
   = printf( "%-*s ", sizes[col], PQfname( result, col ));
  }
  printf( "\n" );

/*
**  Print the separator line*/
  memset( separator, '-', MAX_PRINT_LEN );

  for(= col =3D 0; col < PQnfields( result ); col++ )
  {
 &n= bsp;  printf( "%*.*s ", sizes[col], sizes[col], separator );
&nbs= p; }

  printf( "\n" );

/*
**  Now loop through= each of the tuples returned by
**  our query and print the resul= ts.
*/
  for( row =3D 0; row < PQntuples( result ); row++ )=
  {
    for( col =3D 0; col < PQnfields( re= sult ); col++ )
    {
     = if( PQgetisnull( result, row, col ))
     &n= bsp;  printf( "%*s", sizes[col], "" );
    &n= bsp; else
        printf( "%*s ", s= izes[col], PQgetvalue( result, row, col ));
    }
&n= bsp;   printf( "\n" );

  }
  printf( "(%d r= ows)\n", PQntuples( result ));
  free( sizes );
}
/////////= ////Debug
//////////This is the function that prints out the data
v= oid print_binary_result_set( PGresult * result )
{
  int =           row;
  int= 32         * idno;
  char = ;         * runname;

 = ; for( row =3D 0; row < PQntuples( result ); row++ )
  {
&n= bsp;     idno      =3D (int3= 2 *)PQgetvalue( result, row,0);
      runname=      =3D (char *)PQgetvalue( result, row,1);
 = ;     printf( "%d %8.8s  \n",* idno , runname);<= br>  }
}

void process_query( PGconn * connection, const ch= ar * query_text )
{
  PGresult      *=        result;

#if 0
  if(( = result =3D PQexec( connection, query_text )) =3D=3D NULL )
  {    printf( "%s\n", PQ:errorMessage( connection ));
&n= bsp;   return;
  }
#else

  exec_sql( con= nection, TRUE, "BEGIN TRANSACTION" );
 //////////////Debug
&n= bsp;exec_sql( connection, TRUE, "DECLARE mycursor BINARY CURSOR FOR SELEC= T * FROM testing" );
  result =3D exec_sql( connection, FALSE, "F= ETCH ALL FROM mycursor" );

#endif
  if( PQresultStatus( re= sult ) =3D=3D PGRES_TUPLES_OK )
  {
    &n= bsp; if( PQbinaryTuples( result ))
      = ;  print_binary_result_set( result );
    &nb= sp; else
        print_result_set( = result );
  }
  else if( PQresultStatus( result ) =3D=3D = PGRES_COMMAND_OK )
  {
    printf( "%s", PQcmdS= tatus( result ));

    if( strlen( PQcmdTuples( resu= lt )))
      printf( " - %s rows\n", PQcmdTup= les( result ));
    else
    &nb= sp; printf( "\n" );
  }
  else
  {
  = ;    printf( "%s\n", PQresultErrorMessage( result ));
&= nbsp; }

  PQclear( result );

  exec_sql( connecti= on, TRUE, "END TRANSACTION" );

}

int main( int argc, char *= argv[] )
{
  PGconn * connection;

  if( argc !=3D= 2 )
  {
    printf( "usage  : %s \"connec= tion-string\"\n", argv[0] );
    printf( "example: %s \= "user=3Dmyname password=3Dcows\"\n", argv[0]);
    exit= ( 1 );
  }

  if(( connection =3D PQconnectdb( argv[1]= )) =3D=3D NULL )
  {
    printf( "Fatal error = - unable to allocate connection\n" );
    exit( 1 );  }

  if( PQstatus( connection ) !=3D CONNECTION_OK )    printf( "%s\n", PQerrorMessage( connection ));
&n= bsp; else

/////Debug
    process_query( connecti= on, "SELECT * FROM testing" );

  PQfinish( connection );
<= br>  exit( 0 );
}









----=_vm_0011_W5749323096_10492_1211159632--