Re: Getting oid with libpq

Hello,

I have stored some large objects (which are images) with the lo_import
function. Now I can read the images with the other functions like lo_read
within libpq. But I have one problem with the oid I have to give to this
method. Inside my program I will do a select like this

Select texture from building_face where face_id > 100;

Textures is the oid field. When I use PQgetValue() I get a complete
different value then when I do a select inside pgAdmin. How can I get the
same oid so that I can use this for reading the data of the images inside my
c++ program. Because it is not working with the oid from PQgetValue.

Best regards,

Johan de Koning

Johan C. de Koning wrote:

> I have stored some large objects (which are images) with the lo_import
> function. Now I can read the images with the other functions like lo_read
> within libpq. But I have one problem with the oid I have to give to this
> method. Inside my program I will do a select like this

> Select texture from building_face where face_id > 100;

> Textures is the oid field. When I use PQgetValue() I get a complete
> different value then when I do a select inside pgAdmin. How can I get the
> same oid so that I can use this for reading the data of the images inside
> my c++ program. Because it is not working with the oid from PQgetValue.

Just a guess, but...

One mistake many people make is to cast the char * returned by
PQgetValue() to the type they expect to get. Which won't work, since it's
a pointer to a value (in this case a number) in a textual format. It
needs to be parsed so you get the actual numeric value.

Also, when you parse the number, be aware that the standard-library
functions (such as sscanf()) take the applicable locale setting into
account--which is not what you want in this case. Best reset to the
default locale before parsing.

Jeroen

Thanks for you answer,

I am not an expert in C/C++ so do you know maybe where I can find some
example code or documentation about the parsing process with sscanf?

Best regards,

Johan de Koning

-----Oorspronkelijk bericht-----
Van: pgsql-interfaces-owner(at)postgresql(dot)org
[mailto:pgsql-interfaces-owner(at)postgresql(dot)org] Namens jtv(at)xs4all(dot)nl
Verzonden: woensdag 10 augustus 2005 12:52
Aan: Johan C. de Koning
CC: pgsql-interfaces(at)postgresql(dot)org
Onderwerp: Re: [INTERFACES] Getting oid with libpq

Johan C. de Koning wrote:

> I have stored some large objects (which are images) with the lo_import
> function. Now I can read the images with the other functions like lo_read
> within libpq. But I have one problem with the oid I have to give to this
> method. Inside my program I will do a select like this

> Select texture from building_face where face_id > 100;

> Textures is the oid field. When I use PQgetValue() I get a complete
> different value then when I do a select inside pgAdmin. How can I get the
> same oid so that I can use this for reading the data of the images inside
> my c++ program. Because it is not working with the oid from PQgetValue.

Just a guess, but...

One mistake many people make is to cast the char * returned by
PQgetValue() to the type they expect to get. Which won't work, since it's
a pointer to a value (in this case a number) in a textual format. It
needs to be parsed so you get the actual numeric value.

Also, when you parse the number, be aware that the standard-library
functions (such as sscanf()) take the applicable locale setting into
account--which is not what you want in this case. Best reset to the
default locale before parsing.

Jeroen

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Johan C. de Koning wrote:

> I am not an expert in C/C++ so do you know maybe where I can find some
> example code or documentation about the parsing process with sscanf?

In C, you'd probably want to use

const char *OldLocale = setlocale(LC_NUMERIC, "C");

Then run the pointer returned by PQgetValue() through atol() to get a
numeric value, and finally restore the previous locale with

setlocale(LC_NUMERIC, OldLocale);

If you're using C++, best thing to do may be to use libpqxx instead of
libpq. It has functions to do the conversions for you, and takes care of
locale and overflow issues as well.

Jeroen

jtv(at)xs4all(dot)nl writes:
> const char *OldLocale = setlocale(LC_NUMERIC, "C");
> Then run the pointer returned by PQgetValue() through atol() to get a
> numeric value, and finally restore the previous locale with
> setlocale(LC_NUMERIC, OldLocale);

Uh ... there is nothing at all locale-sensitive about atol, nor strtoul
which is what you really want to use for an OID.

regards, tom lane

I tried to use atol but i get always 0 as result. Is this because I use a
binary cursor to select the data (there are some PostGIS datatypes inside my
resultset which are binary data).

Best regards,

Johan de Koning

-----Oorspronkelijk bericht-----
Van: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Verzonden: donderdag 11 augustus 2005 15:57
Aan: jtv(at)xs4all(dot)nl
CC: Johan C. de Koning; pgsql-interfaces(at)postgresql(dot)org
Onderwerp: Re: [INTERFACES] Getting oid with libpq

jtv(at)xs4all(dot)nl writes:
> const char *OldLocale = setlocale(LC_NUMERIC, "C");
> Then run the pointer returned by PQgetValue() through atol() to get a
> numeric value, and finally restore the previous locale with
> setlocale(LC_NUMERIC, OldLocale);

Uh ... there is nothing at all locale-sensitive about atol, nor strtoul
which is what you really want to use for an OID.

regards, tom lane

> I tried to use atol but i get always 0 as result. Is this because I use a
> binary cursor to select the data (there are some PostGIS datatypes inside
> my resultset which are binary data).

That changes everything.

With a binary cursor you get all data in a binary format which "I'm sure
is documented somewhere." I believe in the case of oids it's a 32-bit
integer in network (big-endian) byte order. If that is indeed the case,
you should be able to read the oid as "ntohl(*(uint32_t
*)PQgetValue(...))".

On a side note, I don't think you actually need a binary cursor to
transfer binary data (apart from the fact that it's likely to improve
performance).

Jeroen