[Libpqxx-general] PQexecParams

[Jeroen Vermeulen]

Michael Akinde wrote:
> Question:
> 
> How would you go about using prepared statements to load binary data
 > into the database, without escaping the data and converting it to a
 > string. There are no tests/examples showing how to do this, and the
 > couple of attempts I have tried now don't work (0 bytes gets inserted
 > into the db). If there is some obvious way of doing this, I am
 > totally missing it.

Hmm... this could be a bit more explicit in the reference docs.  Store 
your binary data in a std::string, and pass it to your prepared 
statement with the pqxx::prepare::treat_binary treatment specifier.

I'm documenting the issue now, and adding this example to the unit tests 
(expanded a bit here for shorter, email-compatible lines):

   // Test prepared statement with a binary parameter.
   C.prepare("GimmeBinary", "SELECT $1::bytea")
	("bytea", pqxx::prepare::treat_binary);

   const string bin_data("x \0 \x01 \x02 \xff y", 11);
   assert(bin_data.size() == 11);
   assert(bin_data[2] == '\0');
   assert(bin_data[bin_data.size()-1] != '\0');

   const result resultset = T.prepared("GimmeBinary")(bin_data).exec();
   const output = binarystring(resultset[0][0]);

   PQXX_CHECK_EQUAL(
	output.str(),
	bin_data,
	"Binary parameter was mangled somewhere along the way.");

This prepares a statement GimmeBinary that takes a binary (bytea) 
argument and simply returns that value.  It then executes that 
statement, passing it a binary string that can't possibly come through 
correctly unless binary data is properly supported.  It captures that 
data in a binarystring object, which in turn can produce a std::string 
holding the same data.  It verifies that that string holds the original, 
binary data.


Jeroen