Accessing PostgreSQL in C/C++

For some, databases can be pretty intimidating. I remember some of the convoluted code I wrote years ago in order to avoid having to learn how to access a database from my programs. But it's actually not that hard to access a database, even in C/C++.

It seems like just about any application you could want to write requires, or could benefit from being able to access, a database. Certainly, most applications don't need a complete RDBMS like PostgreSQL, but for those that do, there's no substitute. For those applications that don't need a complete RDBMS, accessing an SQLite database might be a good step up from managing a bunch of flat files. In this article, I will demonstrate how easy it is to access a PostgreSQL database. In my next article, I'll demonstrate access to an SQLite database and compare the two.

Before we can have any meaningful conversation about databases, we need to have a simple table definition, and some data to put in it.

For our purposes, this should be good enough:

create table people (
        id              integer,
        firstname       varchar(20),
        lastname        varchar(20),
        phonenumber     char(10)
);

insert into people (id, firstname, lastname, phonenumber) values
        (1, 'Fred', 'Flintstone', '5055551234');

insert into people (id, firstname, lastname, phonenumber) values
        (2, 'Wilma', 'Flintstone', '5055551234');

insert into people (id, firstname, lastname, phonenumber) values
        (3, 'Barny', 'Rubble', '5055554321');

This is just a simple table to hold some of my Prehistoric friends. Miraculously, they managed to get telephone service. Go figure.

For this program, I'm using the C++ library, libpq to access the database. As a C programmer, I prefer native C libraries over C++ libraries But as you will see, the libpq library is very approachable, even for non-C++ programmers.

So, let's take a look at some source code.

 1 #include <stdio.h>
 2 #include <postgresql/libpq-fe.h>
 3 #include <string>
 4 
 5 int     main() {
 6 PGconn          *conn;
 7 PGresult        *res;
 8 int             rec_count;
 9 int             row;
10 int             col;
11 
12 
13 
14         conn = PQconnectdb("dbname=ljdata host=localhost user=dataman password=supersecret");
15 
16         if (PQstatus(conn) == CONNECTION_BAD) {
17                 puts("We were unable to connect to the database");
18                 exit(0);
19         }
20 
21         res = PQexec(conn,
22                 "update people set phonenumber=\'5055559999\' where id=3");
23 
24         res = PQexec(conn,
25                 "select lastname,firstname,phonenumber from people order by id");
26 
27         if (PQresultStatus(res) != PGRES_TUPLES_OK) {
28                 puts("We did not get any data!");
29                 exit(0);
30         }
31 
32         rec_count = PQntuples(res);
33 
34         printf("We received %d records.\n", rec_count);
35         puts("==========================");
36 
37         for (row=0; row<rec_count; row++) {
38                 for (col=0; col<3; col++) {
39                         printf("%s\t", PQgetvalue(res, row, col));
40                 }
41                 puts("");
42         }
43 
44         puts("==========================");
45 
46         PQclear(res);
47 
48         PQfinish(conn);
49 
50         return 0;
51 }

Weighing in at 51 lines, you can tell that this code isn't going to be complicated. At the top, on line 2, we see the obligatory inclusion of the PostgreSQL's library declarations.

On lines 5-10 we see the beginning of main() and some local variable declarations. We'll use row and col in order to loop over the rows and columns that result from our query. The rec_count will hold a count of how many records our query returns; we'll use this value in our loop. The conn variable points to a connection handle that holds all of the information that the library needs to establish, maintain and terminate a connection to our database. Finally, the res variable points to a results handle that contains information about the current database query.

We actually connect to the database on line 14. This is a fairly straightforward piece of code. We simply supply the name of a database, the host it resides on, and our connection credentials. If this function returns CONNECTION_BAD, we know we've not gotten connected and it's time to bail out. We check for this condition in lines 16-19.

In lines 21-22, we perform a simple database update. Here we simply pass in our connection handle, and a string/char[] that contains our SQL update statement. Of course, our SQL statement could have contained any valid SQL. The PQexec() function returns a results handle, which we don't need for this particular SQL statement.

Things get more interesting starting from line 24. Here we begin with an SQL select statement. We're going to get the lastname, firstname and phonenumber fields for every record in our table. Lines 27-30 check to make sure that we actually received some data from our query. In the real world, we might do some error recovery here. But for our purposes, printing an error message and exiting is good enough.

Since we know that we did receive data, it would be nice to know how much data we got. We get a record count on line 32. We'll use this count on line 34 to tell the user how much data to expect.

Lines 37-42 are where we actually process the results of our query. In this case, we have a nested loop that loops over each row of our result set. For each row, we loop over each field. Finally, we print each field followed by a tab character. At the end of each row, we print an additional carriage return so each row appears on it's own line.

There are a few things worth mentioning about the PQgetvalue() function on line 39. First, notice that we passed in the results handle we received from PQexec() when we started the query. Also notice that both the row and col parameters are 0-indexed. That is, the first field is gotten by setting col to 0, not 1.

On lines 46 and 48, we do some housekeeping chores. First, we tell the database that we are finished with the last results and no longer need the results handle. Then, since our program is almost finished, we disconnect from the database on line 48.

We compile this program with a comand like:

g++ -lpq db.cpp -o db

And once the program has been compiled, we have an executable called db. Note that we had to link to the pq library.

As you've probably guessed by now, running the db program results in this output:

We received 3 records.
==========================
Flintstone      Fred    5055551234
Flintstone      Wilma   5055551234
Rubble  Barny   5055559999
==========================

So, as you can see, accessing a PostgreSQL database in C/C++ isn't that hard. I would suggest encapsulating all of the database functionality in stand-alone functions and keep them all in a single source file. Then the rest of your program can simply call your functions to get results and not have to worry about error corrections, etc.

Next time, I'll discuss a similar program that uses a SQLite database. An SQLite database does have some limitations, but doesn't require a database server and is easy to manage. But, as I said earlier, if your program requires the scalability and performance of a RDBMS, PostgreSQL is a good choice and is easy to program against.