binary cursor returning truncated data

This is a multi-part message in MIME format.

------=_NextPart_000_0201_01C65E4D.33A9DA20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello,

i am trying to retrieve binary content of a bytea field (or postgis =
geometry field) with a binary cursor with PHP :

$sql=3D"BEGIN;DECLARE mycursor BINARY CURSOR FOR SELECT shape FROM =
$layerName; fetch all in mycursor;"
.....pg_query...=20

But my shape field shows only two bytes of data.

Does someone know if it is a current PHP restriction ?

Thanks by advance,

Eric
------=_NextPart_000_0201_01C65E4D.33A9DA20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: binary cursor returning truncated data

On Wed, Apr 12, 2006 at 04:21:49PM +0200, Eric Mauvire wrote:
> i am trying to retrieve binary content of a bytea field (or postgis
> geometry field) with a binary cursor with PHP :
>
> $sql="BEGIN;DECLARE mycursor BINARY CURSOR FOR SELECT shape FROM $layerName; fetch all in mycursor;"
> ....pg_query...
>
> But my shape field shows only two bytes of data.

The PHP source code (ext/pgsql/pgsql.c) doesn't have any calls to
PQfformat or PQbinaryTuples so PHP appears to expect text data.
Looks like you'll have to fetch data in text format and use
pg_unescape_bytea.

Maybe the PHP developers would consider adding support for binary
data (or maybe they already have and rejected the idea; I don't
know). Try asking on a PHP mailing list.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: binary cursor returning truncated data

Thank you Michael,
for that confirmation

I reported the bug to the PHP team, and they issued a patch.

I would also suggest to postgres developpers to simplify their code and g=
o=20
back to SQL standards :)
That BINARY CURSOR thing should be avoided. With MySQL for instance, a=20
cursor will read the same way any type of data, binary or text.

Best regards,

Eric Mauvi=E8re

----- Original Message -----=20
From: "Michael Fuhr"
To: "Eric Mauvi=E8re"
Cc:
Sent: Sunday, April 16, 2006 6:39 PM
Subject: Re: [PHP] binary cursor returning truncated data


> On Wed, Apr 12, 2006 at 04:21:49PM +0200, Eric Mauvire wrote:
>> i am trying to retrieve binary content of a bytea field (or postgis
>> geometry field) with a binary cursor with PHP :
>>
>> $sql=3D"BEGIN;DECLARE mycursor BINARY CURSOR FOR SELECT shape FROM=20
>> $layerName; fetch all in mycursor;"
>> ....pg_query...
>>
>> But my shape field shows only two bytes of data.
>
> The PHP source code (ext/pgsql/pgsql.c) doesn't have any calls to
> PQfformat or PQbinaryTuples so PHP appears to expect text data.
> Looks like you'll have to fetch data in text format and use
> pg_unescape_bytea.
>
> Maybe the PHP developers would consider adding support for binary
> data (or maybe they already have and rejected the idea; I don't
> know). Try asking on a PHP mailing list.
>
> --=20
> Michael Fuhr
>
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>=20




---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: binary cursor returning truncated data

> for that confirmation
>
> I reported the bug to the PHP team, and they issued a patch.
>
> I would also suggest to postgres developpers to simplify their code and
> go back to SQL standards :)
> That BINARY CURSOR thing should be avoided. With MySQL for instance, a
> cursor will read the same way any type of data, binary or text.

I think you're rather confused. The BINARY CURSOR is for emitted binary
data, not reading it. If you declare a normal cursor over a binary
(bytea) field, you will get its text representation quite happily.

Chris



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: binary cursor returning truncated data

Hello Christopher,

i simply want to retrieve the content of a binary field in a Postgresql=20
table, and use that information in a PHP script.
That binary field contains binary information that must be read with PHP=20
binary functions (unpack).

The current way : POSTGRES cursor -> text version of the binary field in=20
PHP -> conversion back to a binary string with pg_unescape_bytea
is tedious and brings performance issues.

best regards,

Eric Mauvi=E8re

----- Original Message -----=20
From: "Christopher Kings-Lynne"
To: "Eric Mauvi=E8re"
Cc:
Sent: Tuesday, April 18, 2006 10:28 AM
Subject: Re: [PHP] binary cursor returning truncated data


>> for that confirmation
>>
>> I reported the bug to the PHP team, and they issued a patch.
>>
>> I would also suggest to postgres developpers to simplify their code an=
d=20
>> go back to SQL standards :)
>> That BINARY CURSOR thing should be avoided. With MySQL for instance, a=
=20
>> cursor will read the same way any type of data, binary or text.
>
> I think you're rather confused. The BINARY CURSOR is for emitted binar=
y=20
> data, not reading it. If you declare a normal cursor over a binary=20
> (bytea) field, you will get its text representation quite happily.
>
> Chris
>
>
>
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 5: don't forget to increase your free space map settings
>
>=20




---------------------------(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

Re: binary cursor returning truncated data

Is this issue still open?

Some guys say me that they experience similar troubles and this may
prevent them from migrating from OneBigWellKnownDBMS to Postgres.

On 4/18/06, Eric Mauvi=E8re wrote:
> Hello Christopher,
>
> i simply want to retrieve the content of a binary field in a Postgresql
> table, and use that information in a PHP script.
> That binary field contains binary information that must be read with PHP
> binary functions (unpack).
>
> The current way : POSTGRES cursor -> text version of the binary field in
> PHP -> conversion back to a binary string with pg_unescape_bytea
> is tedious and brings performance issues.
>
> best regards,
>
> Eric Mauvi=E8re
>
> ----- Original Message -----
> From: "Christopher Kings-Lynne"
> To: "Eric Mauvi=E8re"
> Cc:
> Sent: Tuesday, April 18, 2006 10:28 AM
> Subject: Re: [PHP] binary cursor returning truncated data
>
>
> >> for that confirmation
> >>
> >> I reported the bug to the PHP team, and they issued a patch.
> >>
> >> I would also suggest to postgres developpers to simplify their code and
> >> go back to SQL standards :)
> >> That BINARY CURSOR thing should be avoided. With MySQL for instance, a
> >> cursor will read the same way any type of data, binary or text.
> >
> > I think you're rather confused. The BINARY CURSOR is for emitted binary
> > data, not reading it. If you declare a normal cursor over a binary
> > (bytea) field, you will get its text representation quite happily.
> >
> > Chris
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
> >
>
>
>
>
> ---------------------------(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
>


--=20
Best regards,
Nikolay

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings