Originally Published: Wednesday, 18 July 2001 Author: Neil Matthew and Richard Stones
Published to: develop_articles/Development Articles Page: 1/1 - [Std View]

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!

Professional Linux Programming contains valuable information for both Experienced Linux programmers and aspiring developers. Learn more about the book at the Wrox Publisher Web Site here.

Linux.com Live! also hosted a Live class with the author's of this book just last last week. You can read the transcript here

PostgreSQL Interfacing

Now that we know the basics of how to use SQL to interactively access PostgreSQL from the psql interpreter, we can move on to accessing a database from program code. The good news is that it is very similar, and you can put all that command line knowledge to work almost immediately.

Accessing PostgreSQL from Code

PostgreSQL is accessible from many different programming languages. We know of at least:

It's probable that there are even more languages supported that we don't know about.

There is also an ODBC driver, which opens the door to access from many other systems, including clients on MS-Windows that can talk to ODBC data sources, such as Access.

Even though the main examples in this book will be coded in C, and that is the language from which we wish to access our PostgreSQL database, there are still two ways we could approach the problem of accessing PostgreSQL from our code.

In this chapter we will see both ways in use, and you will be able to choose the method that is most appropriate for your needs, or with which you feel most comfortable.

Libpq

In general the functions in libpq fall into one of three groups:

We will look at each of these in turn. The libpq library has accumulated some obsolete functions over the years - these are maintained for backward compatibility. We will generally ignore these, and present only the ones that should be used in newer programs. If you do look through some older libpq code and see some unfamiliar library calls, you can always look them up in the documentation downloadable from the PostgreSQL web site, http://www.postgresql.org.

To use any of the libpq functions you must:

In case you are wondering, the 'fe' in libpq-fe stands for 'front end' Therefore, to compile a file that uses libpq, you would generally use a compile command such as:

$ gcc -o program -I/usr/include/pgsql program.c -lpq

depending of course on the exact installation directories on your system. If they are in a different place you may need to alter the include directory, and specify an alternative library directory, by adding an additional option of the form -L/usr/local/pgsql/lib.

Database Connection Routines

The preferred method of connecting to a PostgreSQL database is using the PQconnectdb command. Incidentally, you should use the '-i' option when starting the postmaster so that it listens for TCP/IP sockets as well as UNIX domain sockets.

PGconn *PQconnectdb(const char *conninfo);

The conninfo string is a general-purpose string that can contain a sequence of parameters and values, each separated by white space. Where a value needs to contain white space itself, then it must be enclosed in single quotes. Parameters that are not set explicitly default to NULL, and the library function will generally use default values, or values defined in environmental variables instead. The parameters that may be set are:

host

the name of the host to connect to. By default this will be the localhost.

port

the port number to connect on. By default this will be the standard PostgreSQL port which is 5432.

dbname

the name of the database to connect to. By default the same as the current Linux login name.

user

the user name to use. By default the same as the login name.

password

the password to use.

options

any tracing options required.

tty

the file or terminal for debug output from the backend processor.

Each parameter is followed by an equals sign, then the value to which it should be set. So to connect to a database template1, on a machine gw1, we would use a command like this:

conn = PQconnectdb("host=gw1 dbname=template1");

A NULL pointer is only returned if the library fails to allocate a connection object. Even if you get a non-NULL pointer back you must still check if the connection was successful using the PQstatus function.

ConnStatusType PQstatus(PGconn *conn);

This returns one of two enums, either CONNECTION_OK or CONNECTION_BAD, which have the obvious meanings. Once a good connection has been established, it will usually remain 'good', unless there are network problems, or the remote database is shut down.

If there is a problem with the connect, a meaningful error message can be retrieved with:

char *PQerrorMessage(PGconn *conn);

This returns a pointer to static space, so the text may be overwritten if you make further calls to libpq routines. When you have finished with a connection, either because your program has finished or because the connection failed, you must call:

void PQfinish(PGconn *conn);

to close the connection. You must always call this routine, even if the connection failed. This is because it not only closes open connections, but it also releases memory and other resources associated with the connection. Failing to correctly close the connection will cause your program to accidentally consume system resources.

Once the connection object has been 'finished', the connection pointer no longer points anywhere meaningful, and must not be passed as a parameter to any more routines. A good defensive coding technique would be to set the connection pointer to NULL immediately after calling PQfinish.

Now we know those few routines, we are in a position to write our first C program to connect to a PostgreSQL server. It's not very useful, all it does is test the connection, but it's a first step. Remember to change the server name and login to your own local values, and you must have created a database with the same name as your login id, which we saw how to do in the last chapter.

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

int main()
{
PGconn *conn;
const char *connection_str = "host=localhost
   dbname=template1";
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");
}
PQfinish(conn);
return EXIT_SUCCESS;
}

This should be quite easy to follow. We set up a connection string to connect to the database template1 on the server localhost, attempt a connection, print an error if it fails, then close the connection again before exiting.

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.

Cursors

Suppose our program was accessing a large database, with thousands of rows, and we executed a query that returned all those rows. Our program could suddenly need a very large amount of memory indeed, to store all those results. Since potentially this is all happening across a network as well, clearly we need a way of retrieving the data in smaller quantities, say a row at a time. There is a standard way of doing this, and it is how you would normally fetch data from an SQL database into a C program, (or indeed programs coded in many other languages). What we need is a cursor.

A cursor is a feature we have not met so far, because they are normally only applicable either when using SQL embedded in an external program, or within procedural language function stored in the database, often referred to as 'stored procedures'. Cursors are not generally used from the command line. The SQL92 standard only defines cursors for use in embedded programs, so in most database environments this is the only place you can use them, although in an extension to the SQL standard PostgreSQL does allow them from the command line as well.

A cursor is a way of scrolling through a set of results, fetching returned data in discrete blocks. To use a cursor, you declare it - with a name that has an associated SELECT statement. You then FETCH the results, usually one row at a time, though you can fetch many rows at a time.

The SQL92 standard, and many other implementations of cursors, require an additional step, an OPEN cursor command, between the DECLARE and the FETCH. The PostgreSQL libpq does not need it, the DECLARE is taken as an implicit command. When we get onto ecpg (the alternative way of embedding SQL in C code), you will see that we need to write the OPEN CURSOR command in the source code.

In pseudo code, the sequence looks something like:

BEGIN A TRANSACTION
DECLARE CURSOR mycursor FOR
    SELECT-statement
[OPEN mycursor]

DO {
   FETCH some data from mycursor
   Process the row(s) retrieved
} WHILE the FETCH command found data

CLOSE mycursor
COMMIT WORK

There are two new SQL commands here, DECLARE CURSOR and FETCH, both of which we need to look at before we can write some program code that fetches data using a cursor.

The syntax for declaring a cursor is very straightforward:

DECLARE cursor_name [BINARY] CURSOR FOR
    <SELECT-statement>

This creates, and implicitly opens in libpq, a cursor with the given name. Notice that the cursor is bound to a single SELECT statement. This cursor name is now effectively another way of referring to the SELECT statement. We only need the BINARY option when we wish to retrieve binary data stored in a column, an advanced topic that you will rarely need, so we are not considering it here. SQL92 experts will have noticed some keywords from the SQL standard are missing, notably SCROLL and FOR READ ONLY or FOR UPDATE. In PostgreSQL all cursors can scroll, so the keyword SCROLL, whilst accepted, has no effect. PostgreSQL also only supports 'read only' cursors, so we cannot use a cursor for updating the database, so the FOR clause is equally redundant, though the syntax FOR READ ONLY is accepted for compatibility with the standard.

The syntax of FETCH is very simple:

FETCH [FORWARD|BACKWARD] [number|ALL|NEXT] [IN
 cursor_name];

Normally FORWARD or BACKWARD is omitted, the default is FORWARD. We use a number, or ALL, to tell the FETCH that we wish to retrieve all, or just a fixed number, of rows. The keyword NEXT is the same as giving a number of 1. The SELECT statement specified when we declared the cursor determines the actual rows that can be fetched.

Now we have seen the principle, it's time to try it out in practice. This is sel4.c, which fetches data using a cursor:

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <libpq-fe.h>
PGconn *conn = NULL;
void tidyup_and_exit();
int execute_one_statement(const char *stmt_to_exec,
    PGresult **result);

int main()
{
PGresult *result;
int stmt_ok;
const char *connection_str = "host=gw1 dbname=rick";
FILE *output_stream;
PQprintOpt print_options;
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");
}
stmt_ok = execute_one_statement("BEGIN WORK",
 &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("DECLARE
 age_fname_cursor CURSOR FOR
SELECT age, fname FROM children
WHERE age < '6'", &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("FETCH ALL IN
 age_fname_cursor", &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("COMMIT WORK",
    &result);
}
}
}
if (stmt_ok) PQclear(result);
PQfinish(conn);
return EXIT_SUCCESS;
}

int execute_one_statement(const char *stmt_to_exec,
   PGresult **res_ptr) {
int retcode = 1;
const char *str_res;
PGresult *local_result;
printf("About to execute %s\n", stmt_to_exec)
local_result = PQexec(conn, stmt_to_exec);
*res_ptr = local_result;
if (!local_result) {
printf("PQexec command failed, no error code\n");
retcode = 0;
} else {
switch (PQresultStatus(local_result)) {
case PGRES_COMMAND_OK:
str_res = PQcmdTuples(local_result);
if (strlen(str_res) > 0) {
printf("Command executed OK, %s rows affected\n",
 str_res);
} else {
printf("Command executed OK, no rows affected\n");
}
break;
case PGRES_TUPLES_OK:
printf("Select executed OK, %d rows found\n",
    PQntuples(local_result));
break;
default:
printf("Command failed with code %s, error message
   %s\n",
PQresStatus(PQresultStatus(local_result)),
PQresultErrorMessage(local_result));
PQclear(local_result);
retcode = 0;
break;
}

}
return retcode;
} /* execute_one_statement */

void tidyup_and_exit() {
if (conn != NULL) PQfinish(conn);
exit(EXIT_FAILURE);
}
The main changes from the previous version are highlighted. We have removed the printing of the output, shortly we will see a more useful way of accessing the retrieved data than simply printing it out. When we run this, we get:
Connected OK
About to execute BEGIN WORK
Command executed OK, no rows affected
About to execute DECLARE age_fname_cursor CURSOR FOR
   SELECT age, fname FROM children WHERE age < '6'
Command executed OK, no rows affected
About to execute FETCH ALL IN age_fname_cursor
Select executed OK, 3 rows found
About to execute COMMIT WORK
Command executed OK, no rows affected
It is now trivial to fetch the rows one at a time, simply by changing the ALL to a 1 in the FETCH statement, and checking that rows are actually returned. A FETCH, just like a SELECT, can succeed, but return no data.

The changed lines in sel5.c, are:

conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s",
   connection_str, tidyup_and_exit();
} else {
printf("Connected OK\n");
}
stmt_ok = execute_one_statement("BEGIN WORK",
   &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("DECLARE
 age_fname_cursor CURSOR FOR SELECT age, fname FROM children
 WHERE age < '6'", &result);
stmt_ok = execute_one_statement("FETCH 1 IN
   age_fname_cursor", &result);
while(stmt_ok && PQntuples(result) > 0) {
PQclear(result)
stmt_ok = execute_one_statement("FETCH NEXT IN
   age_fname_cursor",
&result);
}
stmt_ok = execute_one_statement("COMMIT WORK",
  &result);
}
if (stmt_ok) PQclear(result);
PQfinish(conn);
return EXIT_SUCCESS;
}
The output is:
[rick@gw1 psql]$ ./sel5
Connected OK
About to execute BEGIN WORK
Command executed OK, no rows affected
About to execute DECLARE age_fname_cursor CURSOR FOR
   SELECT age, fname FROM children WHERE age < '6'
Command executed OK, no rows affected
About to execute FETCH 1 IN age_fname_cursor
Select executed OK, 1 rows found
About to execute FETCH NEXT IN age_fname_cursor
Select executed OK, 1 rows found
About to execute FETCH NEXT IN age_fname_cursor
Select executed OK, 1 rows found
About to execute FETCH NEXT IN age_fname_cursor
Select executed OK, 0 rows found
About to execute COMMIT WORK
Command executed OK, no rows affected

As you can see, it's actually very easy to retrieve our data one row at a time. The only drawback, which usually doesn't matter, is that we don't know until we have retrieved all the data how many rows there were. This is because PQntuples(result), not unreasonably for a FETCH of one row, has a value of one when a row is retrieved.

Now we have our data being retrieved in a more manageable format, we can progress to access individual parts of that information.

Getting column information

The first piece of information that it's useful to extract from the returned data, is the column information (both the column names and data types). This is quite easy to do with three functions, one to discover how many columns there are, one for the name of each column, and one for the data size of that column. Of course, you could specify by name each of the columns you want, but then, in theory, you know in advance the type of each column that will be returned.

In general, it is a good idea to specify by name each column you require. The reason for this is to prevent your code from being 'surprised' if the database has new columns added. If columns are to be deleted, then at least a 'grep' through the code will show that the names of the columns to be deleted are used in the code. Assuming the column type in code isless clear cut - it may be that determining the type at run time means your code can then automatically take account of any changes of column type. Conversely you are writing more code, which increases the risk of a bug and slightly decreases performance.

We find the number of columns in the returned result with PQnfields:

int PQnfields(PGresult *result);

We find the name of an individual column using PQfname function, and passing the field_index, where the first column is at index 0:

char *PQfname(PGresult *result, int field_index);

We can get an idea of the size of the data with PQfsize. We use the word 'idea' because it returns only the amount of space that PostgreSQL has used internally, and even then is -1 for variable length fields, such as VARCHAR.

int PQfsize(PGresult *result, int field_index);

The obvious omission in this set is the type of the column being returned. Unfortunately, the routine that appears to do this, PQftype, returns an Oid type (actually a typedef for an unsigned integer). This gives only an internal representation of the type, and is not externally documented anywhere, which makes it almost useless. For this reason we will not use it here, though hopefully in a later release PostgreSQL, or at least the libpq library, will develop a more useful routine for discovering the type being returned.

We can now use this knowledge to extend our sel5.c program into sel6.c, by retrieving the column information. It doesn't matter which row of the retrieved data we use to extract the column header information from, indeed even if the SELECT statement returned no rows, we could still access the column information.

The changes are very minor, so we just show the additions here, rather than repeat all the code.

First we add a prototype for our new function:

void show_column_info(PGresult *result);

Then we call it when data is retrieved. We allow it to be called each time data is returned, to show that this works, though of course we would not do this in production code.

if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("FETCH 1 IN
  age_fname_cursor",
&result);
if (stmt_ok) show_column_info(result);
while(stmt_ok && PQntuples(result) > 0) {
show_column_info(result);
PQclear(result);
stmt_ok = execute_one_statement("FETCH NEXT IN
    age_fname_cursor",
&result);
}
stmt_ok = execute_one_statement("COMMIT WORK",
  &result);
}

Finally, here is the implementation of show_column_info:

void show_column_info(PGresult *result) {
int num_columns;
int i;
if (!result) return;
num_columns = PQnfields(result);
printf("%d columns in the result set\n",
    num_columns);
for(i = 0; i < num_columns; i++) {
printf("Field %d, Name %s, Internal size %d\n",
i,
PQfname(result, i),
PQfsize(result, i));
}
} /* show_column_info */

When we execute this, we get output like this:

About to execute FETCH NEXT IN age_fname_cursor
Select executed OK, 1 rows found
2 columns in the result set
Field 0, Name age, Internal size 4
Field 1, Name fname, Internal size -1

We have abbreviated the full output, to save space. Notice that the size of fname is reported as -1, because it is a variable size field type, a VARCHAR.

Accessing the retrieved data

Last, but certainly not least, we need to access the data we have retrieved. As we mentioned before, type information of the data being returned is not available in any sensible fashion, so you may be wondering how we are going to manage this in code. The answer is very simple - libpq always returns a string representation of the returned data, which we can convert ourselves. (Actually this isn't quite true, for BINARY cursors binary data is returned, but very few users will need such advanced PostgreSQL features.)

What we can discover is the length of the representation of the data that will be returned when we fetch the data, this is done with PQgetlength:

int PQgetlength(PGresult *result, int tuple_number, int
        field_index);

Notice that this has a tuple_number field, which you will recall is PostgreSQL speak for a row. This is because we might have not used a cursor (as we saw earlier) and retrieved all the data in one go, or asked for more than one row at a time, as we did in the last example. Without this parameter, retrieving several rows at once would have been pointless, since we could not have accessed the data in any but the last row retrieved.

We get the string representation of the data with PQgetvalue:

char *PQgetvalue(PGresult *result, int tuple_number, int
        field_index);

This returns a NULL terminated string. The actual string is inside a PGresult structure, so you must copy the data out if you want it accessible after doing anything else with the result structure. At this point the astute amongst you may have spotted a snag - how do you distinguish between an empty string being returned because the string in the database had no length, and an empty string being returned because the database column was a NULL value (which we're sure you remember means 'unknown', rather than empty). The answer is a special function, PQgetisnull, which is used to separate the two database values:

int PQgetisnull(PGresult *result, int tuple_number, int
        field_index);

This returns 1 if the field was NULL in the database, otherwise 0.

Now, at last, we are in a position to write our final version of our test code, which returns data from the database row by row, displaying the column information and data as it goes. Before we run this, we set one of the rows we will retrieve to have a NULL value, so we can check our code detects NULLs correctly. Depending on the data you put into the children table, you may have to use a different childno. I had a childno of 9, with an age of 1, where we set the fname field to NULL, by executing this statement in psql:

UPDATE children set fname = NULL where childno = 9;

Now here is the final version of our SELECT from C code, sel7.c. The principal changes are highlighted, and some 'debug' type lines have also been removed, in order to clean up the output a little:

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <libpq-fe.h>
PGconn *conn = NULL;
void tidyup_and_exit();
int execute_one_statement(const char *stmt_to_exec,
    PGresult **result);
void show_column_info(PGresult *result);
void show_one_row_data(PGresult *result);
int main()
{
PGresult *result;
int stmt_ok;
char *connection_str = "host=gw1 dbname=rick";
FILE *output_stream;
PQprintOpt print_options;
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");
}
stmt_ok = execute_one_statement("BEGIN WORK",
  &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("DECLARE
 age_fname_cursor CURSOR FOR
SELECT age, fname FROM children WHERE
age < '6'", &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("FETCH 1 IN
   age_fname_cursor",
&result);
if (stmt_ok) show_column_info(result);
while(stmt_ok && PQntuples(result) > 0) {
show_one_row_data(result);
PQclear(result);
stmt_ok = execute_one_statement("FETCH NEXT IN
   age_fname_cursor",
&result);
}
stmt_ok = execute_one_statement("COMMIT WORK",
  &result);
}
}
if (stmt_ok) PQclear(result);
PQfinish(conn);
return EXIT_SUCCESS;
}

int execute_one_statement(const char *stmt_to_exec,
    PGresult **res_ptr) {
int retcode = 1;
const char *str_res;
PGresult *local_result;
printf("About to execute %s\n", stmt_to_exec);
local_result = PQexec(conn, stmt_to_exec);
*res_ptr = local_result;
if (!local_result) {
printf("PQexec command failed, no error code\n");
retcode = 0;
} else {
switch (PQresultStatus(local_result)) {
case PGRES_COMMAND_OK:
str_res = PQcmdTuples(local_result);
if (strlen(str_res) > 0) {
printf("Command executed OK, %s rows affected\n",
    str_res);
} else {
printf("Command executed OK, no rows affected\n");
}
break;
case PGRES_TUPLES_OK:
printf("Select executed OK, %d rows found\n",
    PQntuples(local_result));
break;
default:
printf("Command failed with code %s, error message
   %s\n",
PQresStatus(PQresultStatus(local_result)),
PQresultErrorMessage(local_result));
PQclear(local_result);
retcode = 0;
break;
}
}
return retcode;
} /* execute_one_statement */
void show_column_info(PGresult *result) {
int num_columns = 0;
int i;
if (!result) return;
num_columns = PQnfields(result);
printf("%d columns in the result set\n",
   num_columns);
for(i = 0; i < num_columns; i++) {
printf("Field %d, Name %s, Internal size %d\n",
i,
PQfname(result, i),
PQfsize(result, i));
}
} /* show_column_info */

void show_one_row_data(PGresult *result) {
int col;
for(col = 0; col < PQnfields(result); col++) {
printf("DATA: %s\n", PQgetisnull(result, 0, col) ?
   "<NULL>": PQgetvalue(result, 0, col));
}
} /* show_one_row_data */
void tidyup_and_exit() {
if (conn != NULL) PQfinish(conn);
exit(EXIT_FAILURE
}
Notice we check for NULLs in all columns. When we run this, we get:
Connected OK
2 columns in the result set
Field 0, Name age, Internal size 4
Field 1, Name fname, Internal size -1
DATA: 4
DATA: Adrian
DATA: 4
DATA: Allen
DATA: 1
DATA: <NULL>

And that concludes our tour of the libpq library. We have seen how we can use the libpq library to access data in the database, retrieving it row by row using cursors. We have also seen how to extract column information, and handle NULL values in the database.

ECPG

Now it's time to look at the alternative way of combining SQL and C, by embedding SQL statements in the C code, and then pre-processing them into something the C compiler can understand, before invoking the C compiler. There is still a library to interface C calls to the database, but the details are hidden away behind a pre-processor.

PostgreSQL's ecpg follows the ANSI standard for embedding SQL in C code, and what follows will be familiar to programmers who have used systems such as Oracle's PRO*C or Informix's ESQL-C. At the time of writing some of the less used features of embedded SQL are not supported, and the standard documentation for ecpg that ships with PostgreSQL is somewhat limited.

Since we have now worked through many of the basics of SQL, this section will actually be quite short. The first problem that has to be tackled is how to delimit sections in the file that the ecpg pre-processor needs to process. This is done with the special sequence in the source that starts 'exec sql', then contains the SQL you want to execute, and ends with a ';'. Depending on the exact syntax, as we shall see in a moment, this can either be a single line that needs to be processed, or it can be used to mark a section that needs pre-processing.

If we want to write a simple C program that performs a single UPDATE statement in the middle of some C code, we need to do only one thing in the source code - embed the UPDATE SQL statement.

What could be easier? Let's write a very simple C program with some embedded SQL that updates a table. By convention these have a file extension of pgc. Here is upd1.pgc:

#include <stdlib.h>
exec sql include sqlca;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
exec sql UPDATE children SET fname = 'Gavin' WHERE
  childno = 9;
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}

At first sight, this hardly looks like C at all. However, if you ignore the lines that start exec sql, you can see it is just a minimal C program. To compile this program we need a two-stage process. First we must run the ecpg pre-processor, then we compile the resulting C file, linking it with the ecpg library. To compile this you may need to add a -I optionto ecpg, to tell it where to look for the include file, depending on your installation. For this program, upd1.pgc, the commands are:

$ ecpg -t -I/usr/include/pgsql upd1.pgc
$ gcc -o upd1 -I/usr/include/pgsql upd1.c -lecpg -lpq

The ecpg command pre-processes the file, leaving a .c file, which we then compile in the normal way, linking with two PostgreSQL libraries. The '-t' on the command line for ecpg tells ecpg that we wish to manage our own transactions with explicit BEGIN WORK and COMMIT WORK statements in the source file. By default ecpg will automatically start a transaction when you connect to the database. There is nothing wrong with this, it's just that the authors prefer to explicitly define transaction blocks.

You will notice the connect string is 'rick@gw1'. This requests a connection to the database 'rick' on server 'gw1'. No password is needed since that's a local machine, and I am already logged in as user rick. However in the general case you can specify the connection in a URL style format, in which case the format is

        <protocol>:<service>://<machine>:<port>/<dbname>
        as <connection name> as <login name> using
        <password for login>

A concrete example makes this much clearer. Suppose we want to connect using tcp to the postgresql service on the dbs6 machine, port 5432, connecting to the database rick, using the database login name neil, who has a password secret. The connect line we would put in our programwould be:

exec sql connect to tcp:postgresql://dbs6:5432/rick as
 connect_2 user neil using secret;

If we want to separate out the different elements, then we can use the same style of connect request, but using ''host variables'', which you will notice always start with a ':'. We will see more about host variables later in the chapter; for now just imagine them as normal C variables.

exec sql BEGIN DECLARE SECTION;
char connect_str[256];
char as_str[25];
char user_str[25];
char using_str[25];
exec sql END DECLARE SECTION;
strcpy(connect_str,
 "tcp:postgresql://localhost:5432/rick");
strcpy(as_str, "connect_2");
strcpy(user_str, "neil");
strcpy(using_str, "secret");
exec sql connect to :connect_str as :as_str user
   :user_str using :using_str ;
if (sqlca.sqlcode != 0) {
pg_print_debug(__FILE__, __LINE__, sqlca, "Connect
 failed");
return DVD_ERR_BAD_DATABASE;
}

Now we have seen the basics, let's look in slightly more detail at what ecpg does.

The first feature that we almost always need when writing an ecpg program is to include a header file that gives us access to errors and status information from PostgreSQL. Since we need this file to be pre-processed by the ecpg processor, before the C compiler runs, a normal include will not do. What we need is to use the exec sql include command. Since there is just a single file called sqlca, which we almost always need to include, pgc files usually start with:

exec sql include sqlca;

This causes the ecpg command to include the file sqlca.h, which is (by default) found in the /usr/include/pgsql directory, though depending on your installation this may of course be different. This important include file declares an sqlca structure, and variable of the same name, that allows us to determine results from our SQL statements. The sqlca structure is a standard structure used when embedding SQL in C code, though implementations vary slightly. For our install of PostgreSQL the structure is declared to be:

struct sqlca
{
char sqlcaid[8];
long sqlabc;
long sqlcode;
struct
{
int sqlerrml;
char sqlerrmc[70];
} sqlerrm;
char sqlerrp[8];
long sqlerrd[6];
char sqlwarn[8];
char sqlext[8];
};

Actually interpreting the contents of sqlca can seem a little odd. The implementation of ecpg that comes with PostgreSQL does not implement as much of the sqlca functionality as some commercial packages such as Oracle. This means some members of the structure are unused, however all the important functions are implemented, so it is perfectly usable.

When processing an sqlca structure you first need to check sqlca.sqlcode. If it is less than zero then something serious went wrong, if it's zero all is well, and if it's 100 then no data was found, but that was not an error.

When an INSERT, UPDATE or SELECT statement succeeds, sqlca.sqlerrd[2]will contain the number of rows that were affected.

If sqlca.sqlwarn[0] is 'W', then a minor error occurred, usually data was retrieved successfully, but was not transferred correctly into a host variable (we will meet these later in the chapter).

When an error occurs sqlca.sqlerrm.sqlerrmc contains a string describing the error.

Commercial packages use more fields, that can tell you a notional 'cost' and other information, but these are not currently supported in PostgreSQL. However since such information is only occasionally useful, it's omission is not generally missed.

Let's just summarize that explanation:

sqlca.sqlcode

Contains a negative value for serious errors, zero for success, 100 for no data found.

sqlca.sqlerrm.sqlerrmc

Contains a textual error message.

sqlca.sqlerrd[2]

Contains the number of rows affected.

sqlca.sqlwarn[0]

Is set to 'W' when data was retrieved, but not correctly transferred to the program.

Let's try this out, by modifying our upd1.pgc file to include sqlca, and also deliberately making it fail, by using an invalid table name:

#include <stdlib.h>
#include <stdio.h>
exec sql include sqlca;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
exec sql UPDATE XXchildren SET fname = 'Emma' WHERE age
 = 0;
printf("error code %d, message %s, rows %d, warning
 %c\n", sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2],
  sqlca.sqlwarn[0]);
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}

This is upd2.pgc. The highlighted lines show the important changes. Compile it as before:

$ ecpg -t -I/usr/include/pgsql upd2.pgc
$ gcc -g -o upd2 -I /usr/include/pgsql/ upd2.c -lecpg -lpq

This time when we run it, an error is generated:

error code -400, message Postgres error: ERROR:
   xxchildren: Table does not exist.
line 10., rows 0, warning

As you can see, it's a little basic but does the job.

Now we have seen the basics, we can get to important issue - how do we access data that SQL statements embedded in .pgc files return?

The answer is actually quite simple, and relies on variables called host variables, which are accessible to both the statements delimited by exec sql ... ; and to the ordinary C compiler.

We do this by having a declare section, usually near the start of the file, that is processed by both the ecpg processor, and the C compiler. This is achieved by declaring C variables inside a special declare section, which also tells the ecpg processor to process them. We use the delimiting statements:

exec sql begin declare section;

and

exec sql end declare section;

Suppose we wanted to declare two variables, child_name and child_age, that are intended to be accessible in both the embedded SQL and in the Ccode for use in the rest of the program. What we need is:

exec sql begin declare section;
int child_age;
VARCHAR child_name[50];
exec sql end declare section;

You will notice two odd things here, firstly the 'magic number' 50 as a string length, and secondly that VARCHAR is not a normal C type. We are forced to use literal numbers here, because this section of code is being processed by ecpg before the C compiler runs, so it is not possible to use either a #define or a constant. The reason for VARCHAR is because the SQL type of the fname column in children is not a type that maps directly to a C type. We must use the PostgreSQL type in our declaration, which is then converted into a legal C structure by the ecpg pre-processor, before the C compiler sees it. The result of this line in the source file is to create a structure called child_name, with two members, a char array 'arr', and an integer len, to store the length. So what the C compiler sees from this one line is actually:

struct varchar_child_name {int len; char arr[50];}
        child_name;

Now we have two variables, visible both in SQL and in C. We use a slight extension of the SQL syntax, the 'into' keyword, to retrieve data from the table into named variables, which are denoted by having a ':' prepended to the name. This is so they cannot be confused with values or table names. Notice this 'into' is not the same as the extension some vendors support to allow interactive selecting of data from one table into another. The 'into' keyword has a slightly different meaning when using embedded SQL.

exec sql SELECT fname into :child_name FROM children
        WHERE age = :child_age;

The epgc pre-processor converts this to C, which we compile in the normal way. So our complete code is now in selp1.pgc, and looks like this:

#include <stdlib.h>
#include <stdio.h>
exec sql include sqlca;
exec sql begin declare section;
int child_age;
VARCHAR child_name[50];
exec sql end declare section;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
child_age = 14;
exec sql SELECT fname into :child_name FROM children
    WHERE age = :child_age;
printf("error code %d, message %s, rows %d, warning
  %c\n", sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2],
  sqlca.sqlwarn[0]);
if (sqlca.sqlcode == 0) {
printf("Child's name was %s\n", child_name.arr);
}
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}

The important changes are highlighted. Notice we need to use child_name.arr to access the returned data. However you only need to use VARCHAR declarations when you want to get data out of the database - when you want to store data into a VARCHAR field you should use a NULL terminated C string in the normal way.

However there is a potential problem with this program. You will see that we had to declare our child_name VARCHAR to be a fixed size, even though we could not know in advance how large the answer might have been. What will happen if we make child_name only 3 long, and the name stored in the database is longer than this? In this case ecpg will only retrieve the first 3 characters, and will set the warning flag. If we change the declaration to VARCHAR child_name[3] and run the program we get:

error code 0, message , rows 1, warning W
Child's name was Jen

(You may also see some corruption, we will explain why in a moment.)

As you can see, the sqlca.sqlwarn[0] warn character was set to 'W', and the returned name truncated. However since our declaration of child_name is translated into a structure containing a character array of exactly3 characters, there is no location for the string terminator to be stored=2E It's lucky our printout worked at all, though we could have been decidedly cleverer with the printf format string. To be certain of getting aVARCHAR into a normal C string we should always check that sqlca.sqlwarn[0] is not set, and then copy the string away to a separate location, adding the NULL terminator explicitly. A more secure version of the program is selp3.c, which has the following changes:

#include <stdlib.h>
#include <stdio.h>
exec sql include sqlca;
exec sql begin declare section;
int child_age;
VARCHAR child_name[50];
exec sql end declare section;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
child_age = 14;
exec sql SELECT fname into :child_name FROM children
    WHERE age = :child_age;
printf("error code %d, message %s, rows %d, warning
  %c\n", sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2],
  sqlca.sqlwarn[0]);
if (sqlca.sqlcode == 0) {
child_name.arr[sizeof(child_name.arr) -1] = '\0';
printf("Child's name was %s\n", child_name.arr);
}
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}

Now we can retrieve data, it's time to see how we use cursors with ecpg where we want to specify, at run time, the condition for the SELECT, and also retrieve data into C variables. Unlike the libpq example, ecpg, (at least in the version used while writing this chapter), required an explicit OPEN statement to open the cursor, before data could be fetched. This example is selp4.pgc, it's noticeably shorter than the libpq equivalent:

 

#include <stdlib.h>
#include <stdio.h>
exec sql include sqlca;
exec sql begin declare section;
int child_age;
VARCHAR child_name[50];
int req_age;
exec sql end declare section;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
req_age = 6;
exec sql DECLARE mycursor CURSOR FOR SELECT age, fname
 FROM children
WHERE age > :req_age;
exec sql OPEN mycursor;
exec sql FETCH NEXT IN mycursor into :child_age,
    :child_name;
if (sqlca.sqlcode < 0)
printf("error code %d, message %s, rows %d, warning
    %c\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc,
   sqlca.sqlerrd[2], sqlca.sqlwarn[0]);
while (sqlca.sqlcode == 0) {
if (sqlca.sqlcode >= 0) {
child_name.arr[sizeof(child_name.arr) -1] = '\0';
printf("Child's name and age %s, %d\n", child_name.arr,
child_age);
}
exec sql FETCH NEXT IN mycursor into :child_age,
    :child_name;
if (sqlca.sqlcode < 0) printf("error code %d, message
    %s, rows %d, warning %c\n", sqlca.sqlcode,
  sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2],
   sqlca.sqlwarn[0]);
}
exec sql CLOSE mycursor;
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}

When we run this, we get the expected output:

Child's name and age Andrew, 10
Child's name and age Jenny, 14
Child's name and age Alex, 11

You may be thinking that all this messing with VARCHARS is a bit pointless, and providing your strings are known to be reasonably consistent in size, it would be much easier to use fixed length strings. Unfortunately this gives rise to a different problem - PostgreSQL does not store the \0 in CHAR columns. What it does do is fill the field to the maximum size with spaces. So if you store "Foo" in a CHAR(10), when you get the data back you actually get "Foo '', and you have to strip the spaces yourself. It does however add a \0 when you retrieve the string, so you do get a conventional C string returned to you.

There is one last ecpg feature we need to look at, how to detect NULL values. Doing this in ecpg (and indeed the standard way for embedded SQL) is slightly more complex than in libpq, but it's not difficult. Remembering that NULL means unknown, it's clear we can't use a magic string, or special integer value to show NULL, since any of these values could actually occur in the database.

What we have to do is to declare an extra variable, often called an indicator variable, that goes alongside the variable we will use to retrieve the data. This additional indicator variable is set to indicate if the data value retrieved was actually NULL in the database. These are often named ind_nameofrealvariable, or sometimes nameofrealvariable _ind, but could have any name. They are always integers - a negative value indicating that the associated variable has a NULL value.

For example, suppose in our earlier example we needed to detect if age was NULL. What we would do is declare an extra variable in the declare section like this:

int ind_child_age;

Then when we do the FETCH from the cursor, we specify both the real variable, and the indicator variable, joined by a colon, like this:

exec sql FETCH NEXT IN mycursor into
        :child_age:ind_child_age, :child_name;

Then if ind_child_age is not negative, we know that child_age is correctly filled in - otherwise the data in it is not valid because the database value was a NULL. For our final example of ecpg, let's convert our example so it correctly detects NULL values.

First we update our 'children' table, so we have examples of both NULL ages and fnames. The test data we start with looks like this:

SELECT * from children;
childno|fname |age
-------+------+---
1|Andrew| 10
2|Jenny | 14
3|Alex | 11
4|Adrian| 5
19| | 17
16|Emma | 0
18|TBD |
20|Gavin | 4
(8 rows)

As you can see, we have a seventeen year old with an unknown name, and an unborn child whose name is still to be decided, and doesn't have an age yet.

This is selp5.pgc. By way of example, we have also used the alternate form of connection string.

#include <stdlib.h>
#include <stdio.h>
exec sql include sqlca;
exec sql begin declare section;
int child_age;
int ind_child_age;
VARCHAR child_name[50];
int ind_child_name;
exec sql end declare section;
main() {
exec sql connect to tcp:postgresql://localhost:5432/rick
 as rick user rick using secretpassword;
exec sql BEGIN WORK;
exec sql DECLARE mycursor CURSOR FOR SELECT age, fname
   FROM children;
exec sql OPEN mycursor;
exec sql FETCH NEXT IN mycursor into
   :child_age:ind_child_age,
   :child_name:ind_child_name;
if (sqlca.sqlcode < 0)
printf("error code %d, message %s, rows %d, warning
 %c\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc,
   sqlca.sqlerrd[2], sqlca.sqlwarn[0]);
while (sqlca.sqlcode == 0) {
if (sqlca.sqlcode >= 0) {
if (ind_child_name >= 0) {
child_name.arr[sizeof(child_name.arr) -1] = '\0';
} else {
strcpy(child_name.arr, "Unknown");
}
if (ind_child_age >= 0) {
printf("Child's name and age %s, %d\n", child_name.arr,
    child_age);
} else {
printf("Child's name %s\n", child_name.arr);
}
}
exec sql FETCH NEXT IN mycursor into
   :child_age:ind_child_age,
   :child_name:ind_child_name;
if (sqlca.sqlcode < 0)
printf("error code %d, message %s, rows %d, warning
 %c\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc,
   sqlca.sqlerrd[2], sqlca.sqlwarn[0]);
} /* end of while loop */
exec sql CLOSE mycursor;
exec sql COMMIT WORK;
exec sql disconnect