Re: Maintaining state through storable.pm?

I'm using Storable against an Oracle DB with good success. It takes some finagling but it works reliably and it's surprisingly fast (in fact we're using it as a caching mechanism on our Web server). Last I checked, DBD::Oracle didn't support binary large objects, or BLOBS, only characters (CLOBS). So, we have to turn our object into hex and write it out to the table.

#Freeze the object
my $data = eval { unpack ('h*', Storable::nfreeze($object))} ;

# ORACLE SPECIFIC
# create a hash containing specifying to Oracle what
# sort of data it will need to store. 112 is the value
# for a CLOB, 113 is the value for a BLOB.
# FIXME: make this a BLOB when DBD::Oracle supports that

my %hash = (ora_types => 112, ora_field => 'data');
# END ORACLE

# insert our values into the table
my $query = "INSERT INTO $table (jobID, data) VALUES ('$jid','$data')"
+;

$db_handle->execute($query, \%hash);
 
# To thaw (after retrieving from the DB)
my $object = eval { Storable::thaw(pack('h*',$data)) }
[download]

This may not be the best for your situation, especially since others have suggested off the shelf solutions that appear to address your needs, but this approach does work.

Update: perrin++. I came up with this scheme in early '00, and at that time BLOBS weren't implemented, or at least not in the version of DBD::Oracle we were using. I guess now I can go back and fix the FIXME's :)

Comment on Re: Maintaining state through storable.pm? Download Code

Replies are listed 'Best First'.
Re: Re: Maintaining state through storable.pm? by perrin (Chancellor) on Jun 26, 2002 at 07:21 UTC
Hmmm, DBD::Oracle has support for BLOB data. We used it that way at eToys two years ago. We used the bind_param call instead of passing the parameters to execute. You could also try using a LONG if you can't get BLOB to work.	[reply]
Re: Re: Maintaining state through storable.pm? by redsquirrel (Hermit) on Jun 27, 2002 at 16:26 UTC
`my %hash = (ora_types => 112, ora_field => 'data');` Rather than using the less obvious `112`, this may make the code a bit more self-documenting: `use DBD::Oracle qw(:ora_types); ... my %hash = (ora_types => ORA_CLOB, ora_field => 'data');` [download] --Dave	[reply] [d/l] [select]