When we retrieve data, we potentially have a bit of a problem. In general we will not know in advance how many rows will be retrieved. If we were to execute a SELECT statement using '*' as the column name to retrieve all columns, we may not even know how many fields or what type of data there is in the rows we are retrieving. Catering for these circumstances is what makes this part of the API more complex. Don't worry, there is no rocket science here, just a few more API calls to get to know.

Let's first convert our del1.c test program into a query that returns data, and while we are at it we will restructure it slightly, so it is easier to add new functionality after the SQL statement is executed. This new file is sel1.c:

#include <stdlib.h>
#include <stdio.h>
#include <libpq-fe.h>

PGconn *conn = NULL;

void tidyup_and_exit();

int main()
{
PGresult *result;
const char *connection_str = "host=gw1 dbname=rick";

conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
  fprintf(stderr, "Connection to %s failed, %s", connection_str,
      PQerrorMessage(conn));
  tidyup_and_exit();
} else {
  printf("Connected OK\n");
}

result = PQexec(conn, "SELECT age, 
fname FROM children WHERE age < '6'");

if (!result) {
  printf("PQexec command failed, no error code\n");
  tidyup_and_exit();
} else {
  switch (PQresultStatus(result)) {
case PGRES_COMMAND_OK:
  printf("Command executed OK, %s rows affected\n", 
  PQcmdTuples(result));
  break;
case PGRES_TUPLES_OK:
  printf("Query may have returned data\n");
  break;
default:
  printf("Command failed with code %s, error message %s\n",
   PQresStatus(PQresultStatus(result)),
   PQresultErrorMessage(result));
  PQclear(result);
  tidyup_and_exit();
  break;
  }
} 

/* New code will get added here */

if (result) PQclear(result);
PQfinish(conn);
return EXIT_SUCCESS;
}


void tidyup_and_exit() {
if (conn != NULL) PQfinish(conn);
exit(EXIT_FAILURE);

}

What we have done is to add a new routine, tidyup_and_exit, which allows us to abandon our program when database actions fail. This is obviously not how we should write it for production code, since aborting an application because a single SQL statement failed is a bit drastic to say the least, but for test purposes it's easier to work with the code this way. We have also changed the DELETE to a SELECT statement that returns some data.

If we run this version of the program, we can see that our code is correctly identifying that data may have been returned:

[rick@gw1 psql]$ ./sel1
Connected OK
Query may have returned data

The first thing we can do is to find out how many rows were actually returned. We can do this with a call to Pqntuples (remember PostgreSQL refers to rows as 'tuples'):

int PQntuples(PGresult *result);

Changing sel1.c into sel2.c, we just need to change one line where we check the return code from PQresultStatus:

case PGRES_TUPLES_OK:
  printf("Query was OK and returned %d rows\n", PQntuples(result));
  break;

When we run the query now, we get the result:

[rick@gw1 psql]$ ./sel2
Connected OK
Query was OK and returned 3 rows

That's all very well, but clearly what we now need to do is access the data being returned. For now we'll start with the quickest and easiest way, which is simply to use one of libpq's special functions for outputting all the data to a file stream. It has the benefit of being easy to use, and is great for debugging.

The function we need is PQprint, which looks like this:

void PQprint(FILE *stream, PGresult *result, PQprintOpt *options);

This is easy to use - we need to provide an output stream, the result pointer we got back from executing our SQL, and a pointer to an options structure.

The options structure as defined in libpq-fe.h looks like this:

typedef struct _PQprintOpt
{
pqbool header;     /* print output field headings and row count */
pqbool align;     /* fill align the fields */
pqbool standard;    /* old brain dead format */
pqbool html3;     /* output html tables */

pqbool expanded;    /* expand tables */
pqbool pager;     /* use pager for output if needed */
char *fieldSep;  /* field separator */
char *tableOpt;  /* insert to HTML <table ...> */
char *caption;  /* HTML <caption> */
char **fieldName; /* null terminated array of replacement 
            field names */
} PQprintOpt;

These options allow you some control over how the result data is output. You may notice that in the header file there are several other output functions for writing to streams - generally you should use PQprint, which has superceded some earlier methods.

Now we can adapt our program to output the data we have retrieved to an output stream. We will send the output to /dev/tty, which directs it to the controlling terminal. This file is sel3.c, but we only show the modified lines here:

At the start of main, we need two new variables:

FILE *output_stream;
PQprintOpt print_options;

Then once the data has been retrieved, we can print it out:

output_stream = fopen("/dev/tty", "w");
if (output_stream == NULL) {
  PQclear(result);
  tidyup_and_exit();
}

memset(&print_options, '\0', sizeof(print_options));
print_options.header = 1;    /* print headers */
print_options.align = 1;    /* align fields */
print_options.html3 = 0;    /* output as html tables */
print_options.fieldSep = "|";  /* field separator */
print_options.fieldName = NULL; /* alternate field names */

PQprint(output_stream, result, &print_options);

Notice that we don't need to explicitly set all the fields of the PQprintOpt structure, the memset provides a reasonable default for the values we do not need. However you should be aware that at the time of writing it is important to specify a field separator for fieldSep.

When we run this version of the program, we get:

[rick@gw1 psql]$ ./sel3
Connected OK
Query was OK and returned 3 rows
age|fname
---+------
5|Adrian
4|Allen
1|fred
(3 rows)

our first bit of embedded SQL code that retrieves data.

Unfortunately, there are a couple of snags with this. Firstly, outputting the data to a file stream is great for debugging, but not so good for actually processing the data. Secondly, we are retrieving all the data in one go, which is fine for small amounts of data, but will quickly become unwieldy for larger data sets.

Continued...