[postgis-users] Re: binary cursor returning truncated data
e.mauviere at geoclip.fr
Tue Apr 18 12:35:42 PDT 2006
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'));
$data =pg_query("SELECT binfield FROM test");
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.
$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
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.
> 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;"
>> 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?
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users