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

Question

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.

Answer 1

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

Answer 2

There is not pqxx::bynarystring in insertion. I used the following solution to do that:

=== STORING AN wxImage IN DATABASE ====

//Getting image string data
wxImage foto = (...);
wxMemoryOutputStream stream;
foto.SaveFile(stream,wxBITMAP_TYPE_PNG);
wxStreamBuffer* streamBuffer = stream.GetOutputStreamBuffer();
size_t tamanho = streamBuffer->GetBufferSize();
char* fotoData = reinterpret_cast<char*>(streamBuffer->GetBufferStart());
string dados(fotoData, tamanho);

//Performing the query
conn->prepare("InsertBinaryData", "INSERT INTO table1(bytea_field) VALUES (decode(encode($1,'HEX'),'HEX'))") ("bytea",pqxx::prepare::treat_binary);

pqxx::work w = (...);
w.prepared(dados).exec();

=== RETRIEVING AN wxImage FROM DATABASE ====

pqxx::result r = w.exec("SELECT bytea_field FROM table1 WHERE (...)");
w.commit();

const result::tuple row = r[0];
const result::field tfoto = row[0];

pqxx::binarystring bs(tfoto);
const char* dadosImg = bs.get();
size_t size = bs.length();

wxMemoryInputStream stream(dadosImg,size);
wxImage imagem;

imagem.LoadFile(stream);

I hope it be helpfull.