How to insert binary data into a PostgreSQL BYTEA column using libpqxx?

Question

I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx.

What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is lacking in details. The fact that http://pqxx.org/ has been down for the past few days isn't helping me...

How would I go about inserting the contents of somefile.bin into a table with a BYTEA column?

What I have is along these lines:

pqxx::work work( conn );
work.exec( "CREATE TABLE test ( name varchar(20), data BYTEA )" );
work.exec( "INSERT INTO test( name, data ) VALUES ( 'foo', <insert filename.bin here> )" );
work.commit();

If it makes a difference, I'd like to use the new hex format for BYTEA available in PostgreSQL 9.1.

Answer 1

Took a while, but I figured out how to do it. Here is some example code:

pqxx::connection conn( ... );
conn.prepare( "test", "INSERT INTO mytable( name, binfile ) VALUES ($1, $2)" );
pqxx::work work( conn );
for ( ... )
{
    std::string name = "foo";
    void * bin_data = ...; // -- obviously do what you need to get the binary data...
    size_t bin_size = 123; // -- ...and the size of the binary data

    pqxx::binarystring blob( bin_data, bin_size );
    pqxx::result r = work.prepared( "test" )( name )( blob ).exec();
}
work.commit();