Обсуждение: Newbie problem with from database in C
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 );
}
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 );
}
On Sun, May 18, 2008 at 9:13 PM, <clarkhorse@clarktx.com> wrote: > 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. IIRC, you need to convert from network byte order: idno = ntohl(*((int32 *) PQgetvalue(result, row, 0))); -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
-----Original Message-----
From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
Sent: Sunday, May 18, 2008 09:52 PM
To: clarkhorse@clarktx.com
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Newbie problem with from database in C
On Sun, May 18, 2008 at 9:13 PM, wrote: > 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.
IIRC, you need to convert from network byte order:
idno = ntohl(*((int32 *) PQgetvalue(result, row, 0)));
after adding this code line I get
client3z.c:140: warning: assignment makes pointer from integer without a cast
I also added
#include <endian.h>
>> 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. > >IIRC, you need to convert from network byte order: > >idno = ntohl(*((int32 *) PQgetvalue(result, row, 0))); > Thanks it led me in the right direction. I was getting confused with all of the pointers. I finally broke it down. int * idno; int idno2;idno = ((int32 *)PQgetvalue( result, row, 0));idno2 = ntohl( * idno); So now that I look at this I don't understand why your statement didn't work. Okay redid it with idno2 = ntohl(*((int32 *) PQgetvalue(result, row, 0))); and it works. I was trying to put the int in a pointer for int. Thanks again, After googling this a number of different ways I found a number of people who never got a response. Most of their output was the same as mine before applying ntohl Output Translate to --------- ------------ 16777216 1 33554432 2 50331648 3 67108864 4 83886080 5 100663296 6 >-- >Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 >EnterpriseDB Corporation | fax: 732.331.1301 >499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com >Edison, NJ 08837 | http://www.enterprisedb.com/ > >-- >Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-interfaces >
On Tue, May 20, 2008 at 12:43 AM, <clarkhorse@clarktx.com> wrote: >>IIRC, you need to convert from network byte order: >> >>idno = ntohl(*((int32 *) PQgetvalue(result, row, 0))); >> > Thanks it led me in the right direction. > I was getting confused with all of the pointers. I finally broke it down. Oh yeah, I didn't spend enough time looking at whether you had defined it as a pointer or not. My bad! > Thanks again, No problem. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/