current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
4

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

share|improve this question

2 Answers 2

up vote 7 down vote accepted

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;
}
share|improve this answer
up vote 1 down vote

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();
share|improve this answer
    
Worth noting there are aging issues with libpqxx, and no, it isn't the "official" postgresql c++ api as it states on the site. –  user3791372 Aug 17 at 12:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.