Re: [GENERAL] Perl DBI + binary data in psql not working

Paul Schilling writes:
 > I am trying to use Apache::Session + psql and ran into a problem with
 > binary data from Storable::nfreeze  To demonstrate what is happening I
 > wrote this quick script:
 >
 > #!/usr/bin/perl
 > use DBI;
 > use strict;
 > use Storable qw(nfreeze thaw);
 >
 > my $dbh = DBI->connect("dbi:Pg:dbname=pfschill", '', '');
 > my $st = $dbh->prepare_cached('
 >     INSERT INTO sessions (id, length, a_session) VALUES (?,?,?)');
 >
 > my $data = {'stuff' => 'other stuff'};
 > my $serialized = nfreeze $data;
 > my $session    = "1234";
 >
 > $st->bind_param(1, $session);
 > $st->bind_param(2, length $serialized);
 > $st->bind_param(3, $serialized);
 > $st->execute;
 >
 > Running this you get:
 > DBD::Pg::st execute failed: ERROR:  parser: parse error at or near ""
 > Database handle destroyed without explicit disconnect.
 >

Paul, the problem is that Postgres can't seem to save binary data in
TEXT fields. Apache::Session uses (n)freeze which produces binary data
for a TEXT field. So you can't use vanilla Apache::Session with
Postgres (ie. DBD::Pg) :(

The solution is to patch DBIStore.pm from Apache::Session 1.0.x to use
base64 encoding/decoding:

< use MIME::Base64 qw(encode_base64 decode_base64);
138,139c137,138
<     my $serialized = encode_base64(nfreeze $session->{data});
<
---
>     my $serialized = nfreeze $session->{data};
>
163d160
<     my $serialized = encode_base64(nfreeze $session->{data});
165,167c162
<     if (length $serialized > 8000) {
<       die "session data too big";
<     }
---
>     my $serialized = nfreeze $session->{data};
202c197
<     $session->{data} = thaw decode_base64($results->[0]);
---
>     $session->{data} = thaw $results->[0];


You need the MIME::Base64 package from CPAN.

You might also realize that Postgres has an 8k limit on a row, so
saving big sessions is also a problem.

Jeffrey, I have cc'ed this to you. Maybe you can put two options for
the tie in one of the next version of Apache::Session

    DataSourceCantStoreBinary    and
    DataSourceRowLimit

It would be also possible to write a subclass to DBIStore.pm

Regards,

Dirk