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.
