Can you give an example of inserting binary data in PostgreSQL database from remote machine using libpq. My second question is: Is there any other API more efficient than libpq with C++. Thanks
2 Answers
There are 2 types of blobs in PostgreSQL — BYTEA and Large Objects. I'd recommend against using large objects as you can not join them to tables.
For BYTEA you'd use something like this in libpq:
PGresult* put_data_to_tablename(
PGconn* conn,
int32_t id,
int data_size,
const char* const data
) {
PGresult* result;
const uint32_t id_big_endian = htonl((uint32_t)id);
const char* const paramValues[] = { &id_big_endian, data };
const int nParams = sizeof(paramValues) / sizeof(paramValues[0]);
const int paramLenghts[] = { sizeof(id_big_endian), data_size };
const int paramFormats[] = { 1, 1 }; /* binary */
const int resultFormat = 0; /* text */
result = PQexecParams(
conn,
"insert into tablename (id, data) values ($1::integer, $2::bytea)",
nParams,
NULL, /* Types of parameters, unused as casts will define types */
paramValues,
paramLenghts,
paramFormats,
resultFormat
);
return result;
}
Comments
Using libpqxx is the C++ way to do it, while libpq is the C API.
Here is a full example of how to do it using pqxx: How to insert binary data into a PostgreSQL BYTEA column using the C++ libpqxx API?
In short, the relevant C++ lines using libpqxx look like this:
void * bin_data = ...; // obviously do what you need to get the binary data...
size_t bin_size = ...; // ...and the size of the binary data
pqxx::binarystring bin( bin_data, bin_size );
pqxx::result r = work.prepared( "test" )( bin ).exec();
work.commit();
1 Comment
user3791372
Worth noting there are aging issues with libpqxx, and no, it isn't the "official" postgresql c++ api as it states on the site.
