News: Accessing a PostgreSQL Database in your C/C++ Program

Accessing a PostgreSQL Database in your C/C++ Program

Introduction

In this post we will look at how to access a PosgreSQL database in your C/C++ application. It's not as hard as you might think, but you need to understand the procedure and the functions used.

Functions and method that will be used

To access the database from your C/C++ program, we are going to use a library called libpq-fe. The most basic functions that we'll need in order to access the database are the following:

  • PQconnectdb
  • PQexec
  • PQgetvalue

Getting dirty

The following steps need to be followed in sequence (or out of sequence, but the application needs to understand what you're doing, thus this is only a logical breakdown of what is necessary). So let's start with some hands on...

Step 1 Include the library

This is a rather obvious step, but here it goes:

#include <postgresql/libpq-fe.h>

Depending on the compiler you're using, you might need to drop the .h, and only use #include <postgresql/libpq-fe>

Step 2 Declare the connection

We need to declare a database connection in our program. This is used as a handle throughout the program:

PGconn *dbconn;

Step 3 Initiate the connection

Let's now connect to a physical database:

dbconn = PQconnectdb("dbname = mydatabase");

Off course, mydatabase can be any database you choose. This is only a sample database we're using.

Step 4 Test the connection

You can test the connection to see in what stage or what the connection status is. We will only test to see if the connection was successful or not.

if (PQstatus(dbconn) == CONNECTION_BAD) {
        printf("Unable to connect to database\n");
}

Step 5 Declare a query result holder

Now the whole point of accessing the database in your application is to get or store information to and from the database (with the application as a "front-end"). When retrieving data from the database, these data are stored in a variable of the type PGresult*

PGresult *query;

Step 6 Execute a query

So now we execute a simple SELECT statement and store the data in query:

query = PQexec(dbconn, "select * from mytable");

Step 7 Use the values

Now the data is stored in query as a table, but we cannot use it just as is. If we want to use only a single value or row, we have to retrieve it from query. This is done using the PQgetvalue() function.

printf ("%s\n", PQgetvalue(query, 0, 1));

The numbers (0 and 1) indicate the row and column, with counting starting at 0. In other words, this line will display the value in the first row, the second column,

Step 8 Close the connection

Afterwards, like anything else, we should close the connection gracefully:

PQfinish(dbconn);

Warnings

  • This post is only for educational purposes for getting to grips with the very basics of using a PosgreSQL database in a C/C++ program. There might be other ways of doing the same thing as described in this post, and a whole lot more options than what was described. Always consult the official vendor documentation.

1 Comment

I ended up going this route instead of fighting with the libpqxx install for windows. Thanks!

Edit:

I had an issue that seems common with this implementation so I want to also add how to fix it. Its an error stating something like "error LNK2019: unresolved external symbol PQstatus referenced in function main"

I am using vs 2010 and postgres 9.2 x64

Under explorer go to your project properties (alt + enter if your project is selected) and change:
C/C++ -> General -> Additional Include Directories
C:\Program Files\PostgreSQL\9.1\include

Linker -> General -> Additional Library Directories
C:\Program Files\PostgreSQL\9.1\lib

Linker -> Input -> Additional Dependencies
libpq.lib

Also if your postgres is x64 build you will need to go to solution properties:
Configuration Properties -> Platfrom -> New
and change it to x64

After that everything will compile.
Another thread with more info:
http://www.askyb.com/cpp/c-postgresql-example/

Share Your Thoughts