Accessing PostgreSQL in C/C++

 in

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.

______________________

Mike Diehl is a freelance Computer Nerd specializing in Linux administration, programing, and VoIP. Mike lives in Albuquerque, NM. with his wife and 3 sons. He can be reached at mdiehl@diehlnet.com

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

ECPG, It's easy

Marcelo Diaz's picture

Excellent article

Interesting.

John McKown's picture

Nice article. The only problem is that the code would need to be rewritten for a different RDMS. It might be nice if you also could write an article on how to use ODBC in the code so that it could then be used with a number of different RDMSs. Of course, this is likely going to really be a series of articles due to the greater complexity of ODBC access.

libpq is C

Matt's picture

libpq is a *pure* C library, not C++. For *actual* C++ use, there is libpqxx, a C++ wrapper for libpq that is worth using.

PostgreSQL is great

Tom H's picture

Thanks for writing about PostgreSQL. In my opinion one of the best dabase systems available among all commercial and open source offerings.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState