Thread: Newbie problem with from database in C

Newbie problem with from database in C

From
clarkhorse@clarktx.com
Date:
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 );
}









Re: Newbie problem with from database in C

From
"Jonah H. Harris"
Date:
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/


Re: Newbie problem with from database in C

From
clarkhorse@clarktx.com
Date:
 
-----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>

Re: Newbie problem with from database in C

From
clarkhorse@clarktx.com
Date:

>> 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
>




Re: Newbie problem with from database in C

From
"Jonah H. Harris"
Date:
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/