Thread: libpq (C++) - Insert binary data
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
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
If you use QT, it has PG connector classes I believe (it had in 3.x).
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
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
>
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
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 > >
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
>
* 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
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
>>
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.
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