Originally Published: Wednesday, 18 July 2001 Author: Neil Matthew and Richard Stones
Published to: develop_articles/Development Articles Page: 2/4 - [Printable]

Excerpt from Professional Linux Programming: Chapter 4; PostgreSQL Interfacing

In this articles excerpt from the brilliant Professional Linux Programming (Wrox Press) the authors look at two ways of accessing a PostgreSQL database from C code. Firstly with a conventional library based method, then at how SQL can be embedded more directly into C code. The excerpt also contains major parts of a complete application. Linux.com wants to thank Wrox Press for making this valuable educational material available to the Linux.com community!

Executing SQL Statements  << Page 2 of 4  >>

Executing SQL Statements

Executing a query against the server is surprisingly simple. There is only one function to call, and three functions to check the result and access error information. To execute an SQL command you call:

PGresult *PQexec(PGconn *conn, const char
        *sql_string);

This routine can return a NULL pointer in exceptional circumstances, so this must be trapped - otherwise the result can be obtained by passing the result pointer to another routine:

ExecStatusType *PQresultStatus(PGresult *result);

The result is an enum of type ExecStatusType, with one of the following values:

PGRES_EMPTY_QUERY

Nothing was done.

PGRES_COMMAND_OK

The command completed successfully, but no data could have been returned because the command was not a SELECT command.

PGRES_TUPLES_OK

The command completed successfully, and some data may have been returned.

PGRES_COPY_OUT

A copy to an external file was in progress.

PGRES_COPY_IN

A copy from an external file was in progress.

PGRES_BAD_RESPONSE

Something unexpected happened.

PGRES_NONFATAL_ERROR

A non-fatal error occurred.

PGRES_FATAL_ERROR

A fatal error has occurred.

Notice the careful definition of PGRES_TUPLES_OK. Receiving this response means that a SELECT SQL statement executed successfully, but it doesn't mean that any data has been returned. We shall find out in the next section how to check for returned data. The COPY errors relate to the database being loaded or backed up.

If you want a textual error message, then you need:

const char *PQresultErrorMessage(PGresult *result);

Notice that this is different from error relating to connections, where we use PQerrorMessage to get a textual error message.

It's often useful to know the number of rows that have been affected by an SQL command. This is particularly true for DELETE commands, because if you execute a DELETE command that is syntactically correct, but doesn't actually delete any rows, then PostgreSQL considers the command to have executed successfully.

For INSERT, UPDATE and DELETE commands, we can find the number of rows affected with PQcmdTuples.

const char *PQcmdTuples(PGresult *result);

Notice that this returns a char *result, containing a NULL-terminated string of digits in character format, not the integer you might have expected. We will see how to obtain the number of rows returned by a SELECT statement later, since this is rather more complex.

After we have finished with a result object we need to tell the library, so that its allocated memory can be released. Just like connection objects, failure to do this will result in memory leaks in your application.

void PQclear(PQresult *result);

One other function that fits into this section that can be useful for debugging:

const char *PQresStatus(ExecStatusType status);

converts a status enum into a descriptive string.

We now know just enough to write our first C program that executes SQL. Since we don't yet know how to retrieve results from a query, we will stick to executing a DELETE. Here is our first C routine that does something useful, del1.c, which extends our original con1.c. This time we are connecting to a database rick, on a server called gw1.

Throughout this chapter we will be experimenting with a single table in our database called children, which we created in the previous chapter. If you need to re-create the table, the SQL to type into psql is:

CREATE TABLE children (
childno SERIAL,
fname VARCHAR,
age INTEGER
);
The lines we changed between con1.c and del1.c are highlighted:
#include <stdlib.h>
#include <stdio.h>
#include <libpq-fe.h>

int main()
{

PGconn *conn;
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));
} else {
printf("Connected OK\n");
}
result = PQexec(conn, "DELETE FROM children WHERE fname
 = 'freda'");
if (!result) {
printf("PQexec command failed, no error code\n");
} 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));
break;
}
PQclear(result);
}

PQfinish(conn);

return EXIT_SUCCESS;

}

If we ensure that there is a row in a table children with an fname column of freda, then when we compile and execute this program we see:

[rick@gw1 psql]$ ./del1
Connected OK
Command executed OK, 1 rows affected
Now there is no row to be deleted matching this criterion. If we execute the program again, it still executes successfully, but this time no rows are affected:
[rick@gw1 psql]$ ./del1
Connected OK
Command executed OK, 0 rows affected
You must be careful to distinguish between a statement that works, but affects no rows, and a statement that fails because it is syntactically incorrect.

Obtaining Results from Queries

We now come to both the most used part of libpq, and also the most complex - retrieving data.

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 returnsdata, 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 wecheck 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 resultpointer 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]$ ./sel
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.





Executing SQL Statements  << Page 2 of 4  >>