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-21 03:02:19Z

15

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

edited Apr 21, 2013 at 3:02

answered Apr 21, 2013 at 0:04

Stéphane

20.6k27 gold badges109 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LAL Over a year ago

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

David Scarlett · Accepted Answer · 2024-06-04 01:39:43Z

1

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.

edited Jun 4, 2024 at 1:39

answered Feb 13, 2017 at 7:01

David Scarlett

3,3712 gold badges16 silver badges29 bronze badges

2 Comments

TooTone Over a year ago

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.

David Scarlett Over a year ago

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

Andrey · Accepted Answer · 2014-03-20 05:27:15Z

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.

bobeff · Accepted Answer · 2016-08-03 16:25:54Z

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 3, 2016 at 16:25

bobeff

3,7714 gold badges41 silver badges68 bronze badges

Comments

LAL · Accepted Answer · 2017-08-30 22:52:47Z

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

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related