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.

Stéphane · Accepted Answer · 2013-04-21T03:02:19.517

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

I like your solution, but i don't like using the conn.prepare. It limits me when i have `n` element to insert with one query (performance issue). I'm working on a solution (with pqxx 5.0.1). — LAL, Aug 30 '17 at 22:10

score 1 · Answer 2 · answered Mar 20 '14 at 05:27

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.

score 0 · Answer 3 · answered Aug 03 '16 at 16:25

0

Instead of using prepared SQL statement with conn.prepare as in Stéphane's answer if you want you can simply escape the binary data with one of overloads of pqxx::escape_binary function. Here is the documentation.

answered Aug 03 '16 at 16:25

bobeff

3,543
3
34
62

score 0 · Answer 4 · answered Feb 13 '17 at 07:01

Since this question is the top search result for inserting a BYTEA via libpqxx, here's another way to do it using a parameterized query, which is more appropriate if performing a single insert.

// Assuming pre-existing pqxx::connection c, void * bin_data, size_t bin_size...
pqxx::work txn(c);
pqxx::result res = txn.parameterized("INSERT INTO mytable(name, binfile) VALUES ($1, $2)")
                                    (name)
                                    (pqxx::binarystring(bin_data, bin_size))
                                    .exec();
txn.commit();

Note that the parameters should not be escaped with the quote or esc methods.

Also note that the accepted answer of using pqxx::connection::prepare causes the prepared query to exist for the entire lifetime of the connection. If the connection will be long-lasting and the prepared query is no longer needed, it should probably be deleted by calling pqxx::connection::unprepare.

score 0 · Answer 5 · answered Aug 30 '17 at 22:52

I've come up with a flexible solution using binary data inside std::string.

I propose this new solution because current answers are old (2013) and I was looking for a multi insert query using pqxx 5.0.1.

With the solution bellow you have the flexibility to use a for loop to append multiple binary data in a single insert query.

CustomStruct data = .... ; // have some binary data

// initialise connection and declare worker
pqxx::connection conn = new pqxx::connection(...);    
pqxx::work w(conn); 

// prepare query
string query += "INSERT INTO table (bytea_field) VALUES ("
// convert your data in a binary string. 
pqxx::binarystring blob((void *)&(data), data.size());
// avoid null character to bug your query string. 
query += "'"+w.esc_raw(blob.str())+"');";

//execute query
pqxx::result rows = w.exec(query);

When we want to retrieve data from the database you should have the scope of your datatype (CustomStruct for instance) and you should be able to cast it back in the binary format of your choice.

// assuming worker and connection are declared and initialized.
string query = "SELECT bytea_field FROM table;";
pqxx::result rows = w.exec(query);
for(pqxx::result::iterator col = rows.begin(); col != rows.end(); ++col)
{
    pqxx::binarystring blob(col[0]);
    CustomStruct *data = (CustomStruct*) blob.data();
    ...
}

This was tested with pqxx 5.0.1, c++11 and postgresSQL 9.5.8

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

5 Answers5