[postgis-users] Re: binary cursor returning truncated data

[Eric Mauvière]

Hi Markus, 

thank you for your answer, actually PHP can cope with binary content, i am reading without any problem wkb content from MySql binary fields,
but it is currently not possible with Postgresql.

Here is a simple test, with byteA type fields rather than geometry type fields:

CREATE TABLE test(binfield byteA) ;
INSERT INTO test VALUES (decode('0103AA000812','hex'));

//First try
$data =pg_query("SELECT binfield FROM test");
$res=pg_fetch_result($data,0);
echo $res;  // =>"\001\003\252\000\010\022"
echo bin2hex(pg_unescape_bytea($res)); //=> "0103aa000812"
//By default, Potsgresql returns binary field values as strings, so we need to unescape them to restore original binary data. Tedious.
Performance issue here.

//second try
$sql="BEGIN;DECLARE mycursor BINARY CURSOR FOR SELECT binfield FROM
test;fetch all in mycursor;";
//the BINARY CURSOR option forces PostgreSQ to return binary data
$data =pg_query($sql);
$res=pg_fetch_result($data,0); 
echo strlen($res);  //=> 3
echo bin2hex($res); //=> 0103aa

//BUG : data returned is truncated, as soon as a "00" byte is encountered, 

I submitted the bug to the PHP team, they very quickly wrote a patch. I suppose it will be available on next PHP release.

I also reported that issue and suggested on postgresql-php list that Postgres developpers should simplify their code and go back to SQL standards :)
They didn't seem to appreciate that kind of irony...
I consider that this weird BINARY CURSOR thing, specific to Postgres, should be avoided. With MySQL for instance, a cursor will read the same way any type of data, binary or text. 

Best regards,

Eric Mauvière
===========
www.geoclip.net

> From: Markus Schaber <schabi at logix-tt.com>
> Subject: Re: [postgis-users] binary cursor returning truncated data
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <4444D1D8.6070505 at logix-tt.com>
> Content-Type: text/plain; charset=ISO-8859-1
> 
> Hi, Eric,
> 
> Eric Mauvière 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.
>>  
>> Does someone know if it is a current PHP restriction ?
> 
> For geometries, the third byte is likely to be a 0-byte. So I think that
> PHP cannot cope with binary contents.
> 
> Can you try with a binary cursor and bytea type, and if it fails there,
> too, ask on a postgresql-php list as they may be more esperienced with this?
> 
> HTH,
> Markus
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060418/17bdbab5/attachment.html>