On 3/22/06 21:31, "John Siracusa" <[EMAIL PROTECTED]> wrote:
> On 3/22/06 8:48 PM, John Siracusa wrote:
>> Looking here:
>>
>> http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
>>
>> it seems that there's already an ASCII-ified format for bytea columns.
>
> Furthermore, bytea data seems to round-trip just fine without any fancy DBI
> stuff:
>
> # Insert bytes
> $sth = $dbh->prepare('insert into t1 (b) values (?)');
> $s->execute("\001\002\003"); # literal octal bytes
>
> Checking via psql:
>
> test=# select b, encode(b, 'base64'), get_byte(b, 0), get_byte(b, 1),
> get_byte(b, 2) from t1;
> b | encode | get_byte | get_byte | get_byte
> --------------+--------+----------+----------+----------
> \001\002\003 | AQID | 1 | 2 | 3
>
> Select and re-insert those bytes:
>
> $sth = $dbh->prepare('select b from t1 limit 1');
> $sth->execute;
> $val = $sth->fetchrow_array;
>
> $sth = $dbh->prepare('insert into t1 (b) values (?)');
> $sth->execute($val);
>
> Checking via psql:
>
> test=# select b, encode(b, 'base64'), get_byte(b, 0), get_byte(b, 1),
> get_byte(b, 2) from t1;
> b | encode | get_byte | get_byte | get_byte
> --------------+--------+----------+----------+----------
> \001\002\003 | AQID | 1 | 2 | 3
> \001\002\003 | AQID | 1 | 2 | 3
>
> Looks good to me. I just tried it with RDBO and it worked for me there too.
> The table:
>
> CREATE TABLE foos (id SERIAL PRIMARY KEY, b BYTEA);
>
> The Perl:
>
> package Foo;
> use base 'Rose::DB::Object';
> Rose::DB->register_db(driver => 'pg', database => 'test',
> username => 'postgres', password => ...);
> Foo->meta->auto_initialize;
>
> my $o1 = Foo->new(b => "\001\002\003");
> $o1->save;
>
> my $o2 = Foo->new(id => $o1->id);
> $o2->load;
>
> print "OK\n" if($o1->b eq $o2->b);
> print unpack('H*', $o1->b), "\n"; # 010203
> print unpack('H*', $o2->b), "\n"; # 010203
>
> Sean, what was the error you saw using a "scalar" column for bytea data?
John,
Thanks to you and others for looking into this so thoroughly!
No error. I just got truncated insertion (presumably from a \000?). This
wasn't restricted to RDBO, but also seen in DBIx::SQLEngine. Since you
pointed out that I shouldn't expect this to work (that I need to basically
do the bytea encoding prior to the insert), I'm not surprised. However,
using DBI with the the explicit column type in the binding removes the need
to do any encoding. I'm going to check with the DBD::Pg folks to see why
quoting for bytea columns doesn't do the appropriate encoding without
explicitly binding the column type. I'm just curious about why one would
use a bytea column (from DBD::Pg) without expecting the data to be binary
and have the quote/encoding done all the time.
Sean
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
