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.

• The first is a traditional style library, called libpq. To use this your code calls library functions to access the database.
• The second way is called embedded SQL, or ecpg in PostgreSQL terms, where SQL statements are embedded in the C code, and processed by a pre-processor before the resulting C code is compiled. The approach is broadly similar to the C pre-processor that handles #include and #define before the main C compiler sees the program. This will be familiar to users of some commercial products such as Oracle's PRO*C and Informix's ESQLC because all of these follow, to a greater or lesser extent, the ANSI standard for embedding SQL.

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:

• Managing connections
• Executing SQL statements
• Obtaining results from queries

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:

• Include the header file libpq-fe.h
• Add the pgsql include directory to the include path when you compile
• Link with the pq library

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:

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.

Continued...