Client 3 - Processing Queries

Query processing is simple in libpgeasy. To execute a SQL command, you call the doquery() function. The function prototype for doquery() is

PGresult * doquery( char * query );

Notice that doquery() does not expect a PGconn *?libpgeasy can deal with only a single database connection and it implicitly uses the one returned by connectdb(). doquery() returns a PGresult *. This is the same data structure you saw in the previous chapter?it represents the result set of the query.

After you have executed a command, you will need to process the result set. libpgeasy provides a number of functions for dealing with a result set?of course, you can use any of the libpq functions as well.

If you are reasonably sure that your query succeeded, you can use the fetch() function to retrieve a single row from the result set. Here is the function prototype for fetch():

int fetch( void * param, ... );

The fetch() function returns the index of the row that you just fetched. The first row returned is row 0, the second row is row 1, and so on. When the result set is exhausted, fetch() will return END_OF_TUPLES. If the query returns zero rows, the first call to fetch() will return END_OF_TUPLES. When you call fetch(), you pass a list of pointers. Each argument should point to a buffer large enough to hold the corresponding field from the result set. You must pass one pointer for each column returned by the query. If you aren't interested in the value of a column, you can pass a NULL pointer.

This might be a good point to see an example:

/* client3a.c */

#include <stdlib.h>

#include <libpq-fe.h>

#include <libpgeasy.h>

int main( int argc, char * argv[] )


  char      tape_id[8+1];

  char      title[80+1];

  char      duration[80+1];

  PGconn *  connection;

  connection = connectdb( argv[1] ? argv[1] : "" );

  on_error_stop( );

  doquery( "SELECT * FROM tapes" );

  while( fetch( tape_id, title, duration ) != END_OF_TUPLES )


    printf( "%s - %-40s - %s\n", tape_id, title, duration );



  exit( EXIT_SUCCESS );


In client3a.c, I select all columns (and all rows) from the tapes table. Here is the definition of tapes:

movies=# \d tapes

                Table "tapes"

 Attribute |         Type          | Modifier


 tape_id   | character(8)          | not null

 title     | character varying(80) | not null

 duration  | interval              |

I've allocated three buffers; one for each column in the table. The tape_id column is eight characters long. The buffer that I allocated for tape_id is 8+1 bytes[2] long?the extra byte is for the null terminator (remember that C strings are terminated with a zero, or null, byte). title is a varchar with a maximum of eighty characters; my buffer is 80+1 bytes long. The duration column is an interval; it will be automatically converted into a null-terminated character string. You don't know exactly how long the text form of an interval will be, but 80+1 bytes should be enough.

[2] I tend to declare my string buffers using this n+1 format. I could obviously declare the tape_id variable as "char tape_id[9];". When I see [9], I wonder if I forgot to include space for the null-terminator. When I see [8+1], I know I did the right thing.

I haven't included any error-handling code in this program, so I'll ask libpgeasy to intercept any error conditions by calling on_error_stop(). As I mentioned earlier, on_error_stop() is the default error-handling mode, but including an explicit call makes the behavior obvious to anyone reading your code.

Next, I'll call doquery() to send the command to the server.

When doquery() returns, it has assembled the result set and I can call fetch() repeatedly to process each row. When I call the fetch() function, I pass in three addresses. fetch() matches each buffer that I provide with a column in the result set. The tape_id column is placed in my tape_id buffer, the title column is placed in my title buffer, and the duration column is placed in my duration buffer. If I am not interested in retrieving a field, I can pass in a NULL pointer for that field.

Some readers might find my call to fetch() a little confusing at first. It may clarify things to rewrite the call to fetch() as follows:

while( fetch( &tape_id[0], &title[0], &duration[0] ) != END_OF_TUPLES )

Writing the code this way makes it a little more obvious that I am passing the address of the first byte of each buffer to fetch().

After fetch() returns, I print the row. In case you aren't too familiar with the syntax, "%-40s" tells printf() to print the title within a left-justified 40-character column[3].

[3] The title column is 80 characters wide but I am only printing the first 40 characters to conserve screen real estate.

Let's run this program:

$ ./client3a dbname=movies

AB-12345 - The Godfather                            -

AB-67472 - The Godfather                            -

MC-68873 - Casablanca                               -

OW-41221 - Citizen Kane                             -

AH-54706 - Rear Window                              -

OW-42200 - Sly                                      - 01:36

KJ-03335 - American Citizen, An                     -

OW-42201 - Stone Cold                               - 01:52

There is one very important point to understand when you use the fetch() function. When you call fetch(), you are passing in buffer pointers?fetch() has no way to know how large those buffers are. If you give fetch() a pointer to a four-byte buffer, but you really need 80 bytes to hold a value, fetch() will happily copy 80 bytes. The most likely effect of this is that your program will immediately crash?if you are lucky. If you aren't lucky, your program will exhibit random failures that are really hard to track down. Sometimes, ignorance is not bliss.

Working with Binary Cursors

You can use the libpgeasy library to retrieve binary[4] data as well as text-form data. Using binary data can give you a performance boost in a few cases, but you usually use binary cursors to retrieve, well…binary data (such as JPEG files, audio files, and so on). Let's modify this simple application a little to see how binary data is handled (the examples are getting a little longer now, so I'll start including line numbers):

[4] Binary is really a misnomer. Declaring a binary cursor really means that you will get results in the form used to store the data in PostgreSQL. If you don't use a binary cursor, PostgreSQL will convert all values into null-terminated strings.

1 /* client3b.c */


  3 #include <stdlib.h>

  4 #include <libpq-fe.h>

  5 #include <libpgeasy.h>


  7 int main( int argc, char * argv[] )

  8 {

  9   int       customer_id;

 10   char      customer_name[80+1];

 11   PGconn *  connection;


 13   connection = connectdb( argv[1] ? argv[1] : "" );


 15   on_error_stop( );


 17   doquery( "BEGIN WORK" );

 18   doquery( "DECLARE customer_list BINARY CURSOR FOR "

 19            "SELECT id, customer_name FROM customers" );



 22   doquery( "FETCH ALL FROM customer_list" );


 24   while( fetch( &customer_id, customer_name ) != END_OF_TUPLES )

 25   {

 26     printf( "%d: %-40s\n", customer_id, customer_name );

 27   }


 29   doquery( "COMMIT" );


 31   disconnectdb();

 32   exit( EXIT_SUCCESS );

 33 }

This example is a little more complex than the previous one. To retrieve binary values, I have to DECLARE a BINARY CURSOR within the context of a transaction block. At line 17, I create a new transaction; the transaction will end at line 29. At line 18, I declare a binary cursor. Rather than processing the (direct) results of a SELECT statement, I loop through the results of a FETCH ALL.

In the previous example (client3a.c), I used the fetch() function to retrieve the text form for each value. In client3b.c, I am retrieving binary values. The fetch() function doesn't know anything about data types?it just copies bytes from the result set into the buffer that was provided.

If you compare the call that I made to printf() in client3b to the corresponding call in client3a, you will see that the difference between text and binary form is reflected in the format string. With text format data, you can always use %s to print result values. With binary data, the format string depends on the underlying column types.

The id column is defined as an int. You want fetch() to copy the id column into the customer_id variable. Because this is a binary cursor, the id column will come to us in binary (or int) form; therefore, customer_id is declared as an int. The customer_name column is defined as a varchar(50)?a character column comes to you as a null-terminated string regardless of whether you are retrieving from a binary or text-form cursor.

Now let's run this client:

$ ./client3b dbname=movies

1: Jones, Henry

2: Rubin, William

3: Panky, Henry

4: Wonderland, Alice N.

5: Funkmaster, Freddy

7: Gull, Jonathon LC

8: Grumby, Jonas

Byte Ordering and NULL Values

There are two more things you have to worry about when working with a binary cursor.

If the client application is not on the same host as the server, you must be concerned about byte ordering. As I mentioned in the previous chapter, different processors (CPUs) order the bytes within numeric data types in different ways. If the client is running on a big-endian host and the server is running on a little-endian host (or vice versa), the non-character data that you receive through a binary cursor will require byte-order conversion.

The next problem that you will encounter when using a binary cursor is the NULL value. If you are using a text-form cursor, PostgreSQL simply returns an empty string whenever it encounters a NULL value in the result set. That won't work if you are retrieving an int value (or any of the noncharacter data types). You should really use the fetchwithnulls() function whenever you use a binary cursor. The function prototype for fetchwithnulls() is

int fetchwithnulls( void * param, ... );

When you call fetchwithnulls(), you provide two buffers for each field in the result set. The first buffer receives the field value; the second receives a NULL indicator (in the form of an int). If the field in question contains a NULL value, the NULL indicator will be set to 1 and the value returned (in the first buffer) is meaningless. If the field contains a non-NULL value, the NULL indicator is set to 0 and you can use the value returned in the first buffer.

 1 /* client3c.c */


 3 #include <stdlib.h>

 4 #include <libpq-fe.h>

 5 #include <libpgeasy.h>


 7 int main( int argc, char * argv[] )

 8 {

 9   int       id;             /* customer_id column   */

10   char      name[80+1];     /* customer_name column */

11   float     balance;        /* balance column       */

12   int       nulls[3];       /* NULL indicators      */

13   PGconn *  connection;


15   connection = connectdb( argv[1] ? argv[1] : "" );


17   on_error_stop( );


19   doquery( "BEGIN WORK" );


21   doquery( "DECLARE customer_list BINARY CURSOR FOR "

22            "SELECT "

23            "id, customer_name, CAST(balance AS real) "

24            "FROM customers" );


26   doquery( "FETCH ALL FROM customer_list" );


28   while( fetchwithnulls( &id,      &nulls[0],

29                          &name[0], &nulls[1],

30                          &balance, &nulls[2] )

31          != END_OF_TUPLES )

32   {

33     if( nulls[2] )

34       printf( "%4d: %-40s   NULL\n", id, name );

35     else

36       printf( "%4d: %-40s %6.2f\n", id, name, balance );

37   }


39   doquery( "COMMIT" );


41   disconnectdb();

42   exit( EXIT_SUCCESS );

43 }

In this client application (client3.c), you are retrieving data using a binary cursor. At line 12, you allocate an array of three null indicators. At lines 28[nd]31, you pass a pointer to each null indicator (and the value buffers) to the fetchwithnulls() function.

By the time fetchwithnulls() has returned, it has set each of the null indicators?1 if the corresponding field is NULL, 0 if the corresponding field is non-NULL.

In this example, you know that the customer_id and customer_name columns cannot be NULL; when you created the customers table, you specified that these two columns were not null. You must provide fetchwithnulls() with the address of a null indicator, even if a result field cannot possibly contain a NULL value.

Working with Result Sets in libpgeasy

In this chapter, you may have noticed that I never bother to free any of the query result sets when I have finished with them. When you use the libpq API, you have to be sure to call PQclear() when you are finished processing a result set?if you don't, your application will have a memory leak. The libpgeasy library manages the result set for you. Each time you execute a new query (by calling doquery()), the previous result set is cleared.

libpgeasy provides a few functions that you can use to manipulate the result set. If you call the reset_fetch() function, the result set will be "rewound" to the beginning. If you fetch after calling reset_fetch(), you will find yourself back at the first row in the result set.

libpgeasy provides three more (poorly documented) functions that you can use to manage multiple result sets.

The get_result() function returns a pointer to the current result set (that is, get_result() returns a PGresult *). When you call get_result(), you are telling libpgeasy that you are going to manage the result set and it will not be automatically cleared the next time you call doquery(). When you want to use a result set that you have saved, pass the PGresult * to set_result(). After calling set_result(), any calls to fetch() (or fetchwithnulls()) will use the new result set.

When you want libpgeasy to manage its own result sets again, call unset_result() with the pointer you got from the first call to get_result(). Don't forget to clear the other result sets using PQClear().

    Part II: Programming with PostgreSQL