PostgreSQL, libpqxx and prepared statement example

As I promised, if people are finding interesting introductory article,  I will write more about PostgreSQL and libpqxx. But before I start with programming – rant.
I am still looking for work and finding none. Today went to Discovery Health, had very pleasant 45 minutes chat with their architect and BA and promise that we will talk again. Later guy from employment agency, who arranged meeting, calls and says that they do not want me since I do not have ANSI C in CV?! Like somebody from management decided to override decision of interviewers. If ANSI C in CV is precondition, why they wasted my time and invited me for interview? I wish them the same from their customers. BTW I am usually employed as senior developer and not as C developer or, Perl developer.
Back to programming, this time C++, had enough of ANSI C for today. Environment is Linux, I am using the same PostgreSQL and libpqxx as last time and to compile example we will use:

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

This time I used test092.cxx, run dpkg with -L switch on libpqxx3-doc to see where is it. It tests passing binary parameter to prepared statement. Test macros are replaced with printing of tested values to standard output and setup of connection/transaction is included. Here is the code:

#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"); if (C.is_open()) { cout << "We are connected to" << C.dbname() << endl; } else { cout << "We are not connected!" << endl; return 0; } work T(C); const char databuf[] = "Test\0data"; const string data(databuf, sizeof(databuf)); cout << "data.size() = " << data.size() << endl; cout << "databuf size = " << sizeof(databuf) << endl; const string Table = "pqxxbin", Field = "binfield", Stat = "nully"; T.exec("CREATE TEMP TABLE " + Table + " (" + Field + " BYTEA)"); if (!T.conn().supports(connection_base::cap_prepared_statements)) { cout << "Backend version does not support prepared statements. Skipping." << endl; return 0; } T.conn().prepare(Stat, "INSERT INTO " + Table + " VALUES ($1)") ("BYTEA", pqxx::prepare::treat_binary); T.prepared(Stat)(data).exec(); const result L( T.exec("SELECT length("+Field+") FROM " + Table) ); cout << "data.size() = " << data.size() << endl; cout << "L[0][0].as<size_t>() = " << L[0][0].as<size_t>() << endl; const result R( T.exec("SELECT " + Field + " FROM " + Table) ); const binarystring roundtrip(R[0][0]); cout << "roundtrip.str() = " << roundtrip.str() << endl; cout << "data = " << data << endl; cout << "roundtrip.size() = " << roundtrip.size() << endl; cout << "data.size() = " << data.size() << endl; T.exec("CREATE TEMP TABLE tuple (one INTEGER, two VARCHAR)"); pqxx::prepare::declaration d( T.conn().prepare("maketuple", "INSERT INTO tuple VALUES ($1, $2)") ); d("INTEGER", pqxx::prepare::treat_direct); d("VARCHAR", pqxx::prepare::treat_string); pqxx::prepare::invocation i( T.prepared("maketuple") ); const string f = "frobnalicious"; i(6); i(f); i.exec(); const result t( T.exec("SELECT * FROM tuple") ); cout << "t.size()= " << t.size() << endl; cout << "sz = " << 1u << endl; cout << "t[0][0].as<string>() = " << t[0][0].as<string>() << endl; cout << "l = " << 6 << endl; cout << "t[0][1].c_str() = " << t[0][1].c_str() << endl; cout << "f = " << f << endl; return 0; }

After connection is successfully obtained, transaction T is constructed. Temp table is created and after confirmation that prepared statements are supported, testing goes on. We have prepare::declaration and prepare::invocation, available in reference generated with doxygen. Adding parameters is in iterative fashion and feels natural, as they say in documentation like varargs from C. Library is well designed and easy to use, documentation, tutorial and tests are supplied. Lengths and contents should match and test succeeds.