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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-interfaces by date

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