c++postgresqllibpqxx

How to insert binary data into a PostgreSQL BYTEA column using the C++ libpqxx API?


I'd like to insert some binary data into a BYTEA column, but I find the Doxygen output is lacking in details, and http://pqxx.org/ has been down for the past few days.

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.


Solution

  • Figured it out. Here is an example showing how to insert a bunch of binary objects into a table:

    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();
    

    Here is how to get the binary data back out of the database:

    pqxx::result result = work.exec( "SELECT * FROM mytable" );
    for ( const auto &row : result )
    {
        pqxx::binarystring blob( row["binfile"] );
        void * ptr = blob.data();
        size_t len = blob.size();
        ...
    }