PostgreSQL C++ tutorial

5575

Installation and configuration

This tutorial is done on LinuxMint 12 and it will work on Ubuntu 11.10. I did the same on CentOS 6.2 and I’m going to write about it later, installing PostgreSQL 9 and corresponding libpqxx is there rather complicated.

Using terminal we find what is available:

apt-cache search postgresql

those are results we are interested in:

libpqxx-3.0 – C++ library to connect to PostgreSQL

libpqxx-3.0-dbg – C++ library to connect to PostgreSQL (debugging symbols)

libpqxx3-dev – C++ library to connect to PostgreSQL (development files)

libpqxx3-doc – C++ library to connect to PostgreSQL (documentation)

postgresql-9.1 – object-relational SQL database, version 9.1 server

postgresql-client – front-end programs for PostgreSQL (supported version)

postgresql-client-9.1 – front-end programs for PostgreSQL 9.1

postgresql-client-common – manager for multiple PostgreSQL client versions

postgresql-common – PostgreSQL database-cluster manager

postgresql-contrib – additional facilities for PostgreSQL (supported version)

postgresql-contrib-9.1 – additional facilities for PostgreSQL

It will return much more but we do not need them all. Now in terminal we do

sudo apt-get install postgresql-9.1 postgresql-client postgresql-client-9.1 postgresql-client-common postgresql-common postgresql-contrib postgresql-contrib-9.1

or if one like gui, Software Manager or Synaptic will also do. Do not forget contrib packages, you will need them for pgAdmin III.

Again in terminal do:

sudo su postgres

afer entering password you are postgres. As postgres:

psql template1

psql (9.1.3)

Type “help” for help.

template1=# create role testuser login password ‘testpass’ superuser valid until ‘infinity’;

CREATE ROLE

template1=#q

That escaped q quits psql and after one exit you are back to your login. If you like install now pgAdmin III or using psql create DB and table where you are going to practice.

To allow remote connections do:

sudo gedit /etc/postgresql/9.1/main/postgresql.conf

and modify listen_addresses, something like this:

listen_addresses = ‘localhost, 192.168.0.42, 192.168.0.111’

Also in pg_hba.conf we need to enable remote users:

sudo gedit /etc/postgresql/9.1/main/pg_hba.conf

it should look something like this, all the way towards bottom of the file:

# IPv4 local connections:

host all all 127.0.0.1/32 md5

host template1 testuser 192.168.0.0/24 md5

host testpgdb testuser 192.168.0.0/24 md5

After saving changes restart PostgreSQL server:

sudo /etc/init.d/postgresql restart

Please create DB testpgdb with sufficient rights for testuser or rename DB in C++ example.

Now it is time to install libpqxx. From terminal execute:

sudo apt-get install libpqxx-3.0 libpqxx-3.0-dbg libpqxx3-dev libpqxx3-doc

and installation is done.

C++ example

Code is slightly adjusted test 005 which comes with libpqxx3-doc, to see where is what use:

dpkg -L libpqxx3-doc

It connects to local instance of PostgreSQL, if you want remote – please edit connection string. If connection succeeds creates table, inserts data and at the end does one non-transactional select.

#include <iostream>

#include <pqxx/pqxx>

 

using namespace std;

using namespace pqxx;


 

int main(int argc, char** argv) {

connection C(“dbname=testpgdb user=testuser password=testpass hostaddr=127.0.0.1 port=5432”);

string tableName(“tabletwo”);

if (C.is_open()) {

cout << “We are connected to” << C.dbname() << endl;

} else {

cout << “We are not connected!” << endl;

return 0;

}

work Q(C);

try {

Q.exec(“DROP TABLE ” + tableName);

Q.commit();

} catch (const sql_error &) {

}

work T(C);

T.exec(“CREATE TABLE “+tableName+” (id integer NOT NULL, name character varying(32) NOT NULL, salary integer DEFAULT 0);”);

tablewriter W(T, tableName);

string load[][3] = {

{“1″,”John”,”0″},

{“2″,”Jane”,”1″},

{“3″,”Rosa”,”2″},

{“4″,”Danica”,”3″}

};

for (int i=0;i< 4; ++i)

W.insert(&load[i][0], &load[i][3]);

W.complete();

T.exec(“ALTER TABLE ONLY “+tableName+” ADD CONSTRAINT “PK_IDT” PRIMARY KEY (id);”);

T.commit();

nontransaction N(C);

result R(N.exec(“select * from “+tableName));

if (!R.empty()) {

for (result::const_iterator c = R.begin(); c != R.end(); ++c) {

cout << ‘t’ << c[0].as(string()) << ‘t’ << c[1].as(string()) <<‘t’ << c[2].as(string()) <<endl;

}

}

return 0;

}

In order to compile code you will need to tell to g++ where are libpqxx headers (they are not on the path) and also to linker what libs must be used for linking. Something like this:

g++ hello.cxx -o hello -I/usr/local/include/ -lpqxx -lpq

If your libpqxx or libpq are on unusual place you will use -L[path to where they are], there is lot of that on CentOS or Fedora 😉

After executing hello (./hello) you should be rewarded with the following output:

We are connected totestpgdb

NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index “PK_IDT” for table “tabletwo”

1 John 0

2 Jane 1

3 Rosa 2

4 Danica 3

I will write more on subject and explain Red Hat, CentOS, Fedora installation. After that we will look at coding using Eclipse and NetBeans, also PostgreSQL and libpqxx.