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

Newbie problem with from database in C

From: clarkhorse(at)clarktx(dot)com
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Newbie problem with from database in C
Date: 2008-05-19 01:13:52
Message-ID: W5749323096104921211159632@webmail12 (view raw or flat)
Thread:
Lists: pgsql-interfaces
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 Edition by Korry and Susan Douglas and ran into a snag as soon as I started trying to read numbers into variables.
I started with int32 datatypes which they used in their example, then tried int and int4 then int 16 and int2 which is what they used in there example

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 changes trying to figure out what is going on


Thanks,

--------------------------------------------------------------------------------------------

/*
** File: client3d.c
*/

#include <stdlib.h>
#include <string.h>
#include <stdarg.h>


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

#define MAX_PRINT_LEN 40

static bool status_ok( PGresult * result )
{
 ExecStatusType status = PQresultStatus( result );

 if( status == PGRES_TUPLES_OK || status == PGRES_COMMAND_OK )
 return( TRUE );
 else
 return( FALSE );
}

static PGresult * exec_sql( PGconn * conn, bool destroy_result, const char * text, ... )
{
 char buf[2048];
 va_list args;
 PGresult * result;

 va_start( args, text );
 vsnprintf( buf, sizeof( buf ), text, args );

 result = PQexec( conn, buf );

 if( destroy_result == TRUE )
 {
 if( status_ok( result ) == 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 = (int *)calloc( PQnfields( result ), sizeof( int ));

 for( col = 0; col < PQnfields( result ); col++ )
 {
 int len = 0;

 for( row = 0; row < PQntuples( result ); row++ )
 {
 if( PQgetisnull( result, row, col ))
 len = 0;
 else
 len = PQgetlength( result, row, col );

 if( len > sizes[col] )
 sizes[col] = len;
 }
 if(( len = strlen( PQfname( result, col ))) > sizes[col] )
 sizes[col] = len;

 if( sizes[col] > MAX_PRINT_LEN )
 sizes[col] = MAX_PRINT_LEN;
 }

/*
** Print the field names.
*/
 for( col = 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 = 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 = 0; row < PQntuples( result ); row++ )
 {
 for( col = 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 = 0; row < PQntuples( result ); row++ )
 {
 idno = (int32 *)PQgetvalue( result, row,0);
 runname = (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 = PQexec( connection, query_text )) == 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 = exec_sql( connection, FALSE, "FETCH ALL FROM mycursor" );

#endif
 if( PQresultStatus( result ) == PGRES_TUPLES_OK )
 {
 if( PQbinaryTuples( result ))
 print_binary_result_set( result );
 else
 print_result_set( result );
 }
 else if( PQresultStatus( result ) == 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 != 2 )
 {
 printf( "usage : %s \"connection-string\"\n", argv[0] );
 printf( "example: %s \"user=myname password=cows\"\n", argv[0]);
 exit( 1 );
 }

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

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

/////Debug
 process_query( connection, "SELECT * FROM testing" );

 PQfinish( connection );

 exit( 0 );
}











Responses

pgsql-interfaces by date

Next:From: Jonah H. HarrisDate: 2008-05-19 01:52:39
Subject: Re: Newbie problem with from database in C
Previous:From: Tom LaneDate: 2008-05-16 14:44:31
Subject: Re: pg_subtrans directory grows over 1 GB, is there a way to control it?

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