Re: PDO:PGSL cannot use native bind to insert binary data into "bytea" fields - also problem with bytea fetches...

Thanks for the report Thies, I'll look into it. --Wez. On 10/19/05, Thies C. Arntzen <thies@thieso.net> wrote: > hey, > > pdo ist cool... but has it's rough edges. here are my 2 problems: > > <?php > $dsn = 'pgsql:user=portal-dev port=5433 host=localhost dbname=portal- > dev password=php'; > $pdo = new PDO($dsn); > $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > > $pdo->query('drop table blob'); > $pdo->query('create table blob (data bytea)'); > > $stmt = $pdo->prepare("insert into blob (data) values (:data)"); > $stmt->bindParam(':data', $data, PDO::PARAM_LOB); > > $data = "Hallo\0Pallo"; $stmt->execute(); > $data = ''; $stmt->execute(); > $data = NULL; $stmt->execute(); > > foreach ($pdo->query('select * from blob')->fetchAll > (PDO::FETCH_ASSOC) as $r) { > print_r($r); > if (NULL === $r[ 'data' ]) { > echo "NULL\n"; > continue; > } > $data = fread($r[ 'data' ], 8192); > echo "from database: ".strlen($data)." <$data>\n"; > } > ?> > > this code inserts 3 rows into the database and illustrates 2 problems: > > 1. you cannot insert binary data using native bound variables: > the string "Hallo\0Pallo" gets truncated on the \0 character when > inserted into the database - so i cannot store binary data. > > my analysis shows that this is because if you don't supply the > paramTypes parameter to PQprepare and therefore postgres will assume > all parameters to be \0 terminated strings in PQexecPrepared. problem > ist that when doing the prepare call we don't yet know the types of > the bound parameters - tough one... > > workaround is not to use native binds if you want to insert binary > data (which works) - for large images we might hit the sql-string > length limit this way - dunno... > > 2. if i insert an zero length string my select returns an "unreadble" > file-stream ("Resource id #0"); > > > re, thies > > > > >