Thread: libpq (C++) - Insert binary data

libpq (C++) - Insert binary data

From
GOO Creations
Date:
  Hi there,

I'm sitting for days now, and I can't get this to work:

I want to insert binary data (bytea) into my postgres DB via the c++ libpq.

What I have is a char* (actually a QByteArray) and I want to insert it
into the DB and the retrieve it from there again.

I can't find any good examples or tutorials online, so can anyone help
me (or direct met to a tutorial) on how to insert binary data and
retrieve it again?

Thank you very much
Christoph

Re: libpq (C++) - Insert binary data

From
Vincenzo Romano
Date:
2010/9/30 GOO Creations <goocreations@gmail.com>:
>  Hi there,
>
> I'm sitting for days now, and I can't get this to work:
>
> I want to insert binary data (bytea) into my postgres DB via the c++ libpq.
>
> What I have is a char* (actually a QByteArray) and I want to insert it into
> the DB and the retrieve it from there again.
>
> I can't find any good examples or tutorials online, so can anyone help me
> (or direct met to a tutorial) on how to insert binary data and retrieve it
> again?
>
> Thank you very much
> Christoph

Try chapter  31.3.4 from the v9 documentation.
http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING

--
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS

Re: libpq (C++) - Insert binary data

From
Grzegorz Jaśkiewicz
Date:
If you use QT, it has PG connector classes I believe (it had in 3.x).

Re: libpq (C++) - Insert binary data

From
Vincenzo Romano
Date:
2010/9/30 Vincenzo Romano <vincenzo.romano@notorand.it>:
> 2010/9/30 GOO Creations <goocreations@gmail.com>:
>>  Hi there,
>>
>> I'm sitting for days now, and I can't get this to work:
>>
>> I want to insert binary data (bytea) into my postgres DB via the c++ libpq.
>>
>> What I have is a char* (actually a QByteArray) and I want to insert it into
>> the DB and the retrieve it from there again.
>>
>> I can't find any good examples or tutorials online, so can anyone help me
>> (or direct met to a tutorial) on how to insert binary data and retrieve it
>> again?
>>
>> Thank you very much
>> Christoph
>
> Try chapter  31.3.4 from the v9 documentation.
> http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING

Sorry, I meant  30.3.5 in v8:
http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-BYTEA

It looks like there's no such a thing in v9, though!


--
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS

Re: libpq (C++) - Insert binary data

From
GOO Creations
Date:
  I've worked through that already, without any success.
This is what I have until now to insert data

     char *query = "insert into table1 (bytes) values ($1)";
     QByteArray chip = <assignment of bytes>;
     const char *data = chip->data();
     const char* params[]={data};
     const int params_length[]={chip->length()};
     const int params_format[]={1};
     result = PQexecParams(mDatabase, query, 1, in_oid, params,
params_length, params_format, 0);

The first problem I have is that I'm not sure if const int
params_length[]={chip->length()}; is the correct way to provide the
length. Second of all, is this actually the correct way of doing it,
isn't there a beter way?

Christoph




On 2010/09/30 05:30 PM, Vincenzo Romano wrote:
> 2010/9/30 GOO Creations<goocreations@gmail.com>:
>>   Hi there,
>>
>> I'm sitting for days now, and I can't get this to work:
>>
>> I want to insert binary data (bytea) into my postgres DB via the c++ libpq.
>>
>> What I have is a char* (actually a QByteArray) and I want to insert it into
>> the DB and the retrieve it from there again.
>>
>> I can't find any good examples or tutorials online, so can anyone help me
>> (or direct met to a tutorial) on how to insert binary data and retrieve it
>> again?
>>
>> Thank you very much
>> Christoph
> Try chapter  31.3.4 from the v9 documentation.
> http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING
>


Re: libpq (C++) - Insert binary data

From
Steve Atkins
Date:
On Sep 30, 2010, at 8:26 AM, GOO Creations wrote:

> Hi there,
>
> I'm sitting for days now, and I can't get this to work:
>
> I want to insert binary data (bytea) into my postgres DB via the c++ libpq.
>
> What I have is a char* (actually a QByteArray) and I want to insert it into the DB and the retrieve it from there
again.
>
> I can't find any good examples or tutorials online, so can anyone help me (or direct met to a tutorial) on how to
insertbinary data and retrieve it again? 

Is there any reason you're not using the Qt database API? It'll map onto what you're doing much more easily than using
libpq.

(Also, libpq isn't a C++ API, so maybe you're thinking of something else, like libpqxx?)

Cheers,
  Steve


Re: libpq (C++) - Insert binary data

From
GOO Creations
Date:
Yes there is a reason I'm not using Qt's libraries. Qt doesn't come
out with PSQL as default driver (meaning you have to manually download
the driver for Qt postgres). I'm developing a plugin for an app that
restricts Qt, no extra depedncies are allowed. But the app has libpq as
dependcy, so I'm able to use that.

Chris

On 2010/09/30 05:38 PM, Steve Atkins wrote:
> On Sep 30, 2010, at 8:26 AM, GOO Creations wrote:
>
>> Hi there,
>>
>> I'm sitting for days now, and I can't get this to work:
>>
>> I want to insert binary data (bytea) into my postgres DB via the c++ libpq.
>>
>> What I have is a char* (actually a QByteArray) and I want to insert it into the DB and the retrieve it from there
again.
>>
>> I can't find any good examples or tutorials online, so can anyone help me (or direct met to a tutorial) on how to
insertbinary data and retrieve it again? 
> Is there any reason you're not using the Qt database API? It'll map onto what you're doing much more easily than
usinglibpq. 
>
> (Also, libpq isn't a C++ API, so maybe you're thinking of something else, like libpqxx?)
>
> Cheers,
>    Steve
>
>


Re: libpq (C++) - Insert binary data

From
Merlin Moncure
Date:
On Thu, Sep 30, 2010 at 11:42 AM, GOO Creations <goocreations@gmail.com> wrote:
>  Yes there is a reason I'm not using Qt's libraries. Qt doesn't come out
> with PSQL as default driver (meaning you have to manually download the
> driver for Qt postgres). I'm developing a plugin for an app that restricts
> Qt, no extra depedncies are allowed. But the app has libpq as dependcy, so
> I'm able to use that.

here is my response from the previous time I answered the question.
There a number of ways to deal with this (my favorite by far is
libpqtypes, but I'm quite biased!):

On Wed, Jul 21, 2010 at 9:27 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Jul 19, 2010 at 8:14 PM, vinicius_bra <viniciusams@yahoo.com.br> wrote:
>>
>> Hi All,
>>
>> I'm developing a system in C and I have a unsigned char pointer that
>> represents a struct and I like to store it in a bytea column in postgreSQL.
>> How can I do it?
>> Example:
>
> you have several options:
> *) encode the memory for the structure PQescapeStringConn and send to
> PQexec (my least favorite method)
> *) set up a call to PQexecParams (more work, but faster and no escaping)
> *) make a composite type on the server and send your structure in a
> more classical SQL way
> *) use libpqtypes (this is the most set up work, but the best in the
> long run) :-)
>
> w/libpqtypes:
> PGbytea b = {sizeof(s), &s};
> PGresult *res = PQexecf(conn, "insert into t values (%bytea)", b);
> PQclear(res);
>
> merlin
>

Re: libpq (C++) - Insert binary data

From
Christian Ullrich
Date:
* GOO Creations wrote:

> This is what I have until now to insert data
>
> char *query = "insert into table1 (bytes) values ($1)";
> QByteArray chip = <assignment of bytes>;
> const char *data = chip->data();
> const char* params[]={data};
> const int params_length[]={chip->length()};
> const int params_format[]={1};
> result = PQexecParams(mDatabase, query, 1, in_oid, params,
> params_length, params_format, 0);
>
> The first problem I have is that I'm not sure if const int
> params_length[]={chip->length()}; is the correct way to provide the
> length. Second of all, is this actually the correct way of doing it,
> isn't there a beter way?

It is certainly the simplest way of doing it in plain libpq, as long as
you're using the binary format (which you are doing here). According to
the documentation, QByteArray::length() returns the number of bytes in
the array, so it is the correct size.

I'm not sure if the (internal) binary format of bytea is guaranteed not
to change in future versions of PostgreSQL. Currently, as you obviously
found out yourself, it's pretty simple -- no transformation at all, the
binary format is just the data.

To make sure that your application supports any changes in future
versions, you should consider using the "hex" text format instead. See
section 8.4.1 of the manual for version 9.0.

--
Christian

Re: libpq (C++) - Insert binary data

From
GOO Creations
Date:
  thanks for this tip, but as far as I understand it libpqtypes is not
part of the original lipq libary .
This will result in the same problem as with the Qt libraries, it will
add another dependecy to my plugin, which is not allowed.

Chris

On 2010/09/30 10:59 PM, Merlin Moncure wrote:
> On Thu, Sep 30, 2010 at 11:42 AM, GOO Creations<goocreations@gmail.com>  wrote:
>>   Yes there is a reason I'm not using Qt's libraries. Qt doesn't come out
>> with PSQL as default driver (meaning you have to manually download the
>> driver for Qt postgres). I'm developing a plugin for an app that restricts
>> Qt, no extra depedncies are allowed. But the app has libpq as dependcy, so
>> I'm able to use that.
> here is my response from the previous time I answered the question.
> There a number of ways to deal with this (my favorite by far is
> libpqtypes, but I'm quite biased!):
>
> On Wed, Jul 21, 2010 at 9:27 AM, Merlin Moncure<mmoncure@gmail.com>  wrote:
>> On Mon, Jul 19, 2010 at 8:14 PM, vinicius_bra<viniciusams@yahoo.com.br>  wrote:
>>> Hi All,
>>>
>>> I'm developing a system in C and I have a unsigned char pointer that
>>> represents a struct and I like to store it in a bytea column in postgreSQL.
>>> How can I do it?
>>> Example:
>> you have several options:
>> *) encode the memory for the structure PQescapeStringConn and send to
>> PQexec (my least favorite method)
>> *) set up a call to PQexecParams (more work, but faster and no escaping)
>> *) make a composite type on the server and send your structure in a
>> more classical SQL way
>> *) use libpqtypes (this is the most set up work, but the best in the
>> long run) :-)
>>
>> w/libpqtypes:
>> PGbytea b = {sizeof(s),&s};
>> PGresult *res = PQexecf(conn, "insert into t values (%bytea)", b);
>> PQclear(res);
>>
>> merlin
>>


Re: libpq (C++) - Insert binary data

From
John R Pierce
Date:
  On 09/30/10 11:58 PM, GOO Creations wrote:
>  thanks for this tip, but as far as I understand it libpqtypes is not
> part of the original lipq libary .
> This will result in the same problem as with the Qt libraries, it will
> add another dependecy to my plugin, which is not allowed.

take the source to libpqtypes, and static link it with your plugin.



Re: libpq (C++) - Insert binary data

From
Merlin Moncure
Date:
On Fri, Oct 1, 2010 at 3:08 AM, John R Pierce <pierce@hogranch.com> wrote:
>  On 09/30/10 11:58 PM, GOO Creations wrote:
>>
>>  thanks for this tip, but as far as I understand it libpqtypes is not part
>> of the original lipq libary .
>> This will result in the same problem as with the Qt libraries, it will add
>> another dependecy to my plugin, which is not allowed.
>
> take the source to libpqtypes, and static link it with your plugin.

yeah -- you would have to enable that via configure.  pulling in
libptypes just to pass bytea is probably overkill though considering
it can be done in stock libpq w/o too much effort.  To justify it, you
would want to be leveraging some of the other library features
(especially, array/composite passing, for which there is no reasonable
method via stock libpq).

merlin