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.

Englishism

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

Englishism

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). – Espinoza

Answer 2

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 only a single insert. 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 a prepared query is no longer needed, it should probably be deleted by calling pqxx::connection::unprepare.

2024 update, using libpqxx 7.9.0:

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

In this solution, the pqxx::binary_cast helper is used to pass binary data held in a C-style pointer to data of know length (in its two argument version) or C++ STL containers such as std::vector<uint8_t> (in its single argument version). This is just a cast, so there is no unnecessary copying of the data.

---

Original answer, written for libpqxx 5.0.0:

// 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. The pqxx::binary_cast helper also wasn't available until libpqxx 7.6.0, so this approach copies the data into a pqxx::binarystring object before passing it to the query, which is an inefficiency that would best be avoided if possible.

Menorca

not sure if available when you wrote this answer, but you can avoid copying with binarystring and instead use binary_cast. Main point, as you say, prepared statements unnecessary. – Lightness

@Lightness thanks! I've updated my answer for more modern versions of libpqxx. – Menorca

Answer 3

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.

Baudoin

Answer 4

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.

Tagmemic

Answer 5

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

Espinoza