in reply to Putting mod Storable output into MySQL

The problem is that: your column name (for example, "key") clashed with mysql keywords. I can reproduce your issue, if I follow your column names, but once I rename the columns to a and b, the problem is gone:

use Storable qw(freeze);
use DBI;
use strict;
use warnings;

my $dsn = "DBI:mysql:database=test;host=foo";
my $dbh = DBI->connect($dsn, 'root', 'password', {RaiseError => 1});
my $data = {'a'=>1, 'b'=>{'c'=>2, 'd'=>3}, 'e'=>[1,2,3]};
my $frozen = freeze($data);
my $command = "insert into test(a,b) values(1,?)";
my $sth = $dbh->prepare($command);
$sth->execute($frozen);

[download]

Your problem can be easily reproduced (without Storable):

use DBI;
use strict;
use warnings;

my $dsn = "DBI:mysql:database=test;host=foo";
my $dbh = DBI->connect($dsn, 'root', 'abcd', {RaiseError => 1});
my $frozen = "1";
my $command = "insert into test(key,b) values(1,?)";
my $sth = $dbh->prepare($command);
$sth->execute($frozen);

[download]

Replies are listed 'Best First'.
Re^2: Putting mod Storable output into MySQL
by cormanaz (Deacon) on Oct 15, 2005 at 18:16 UTC
    Yes that did it. Thanks. I renamed key to id and blob to datastructure.

    Now that I've gotten the blob into MySQL I'm having trouble getting it back out. When I do it like so

    $command ='select datastructure from blobtest where id=1';
$sth = $dbh->prepare($command);
@reply = $sth->execute || die "Could not execute MySQL statement: $com
+mand";
my $reconstituted = thaw($reply[0][0]);

    [download]
    $reply[0][0] contains an integer 1. I don't think a placeholder is appropriate in this case, is it?
[reply]
[d/l]

      This is what you should do (the commented out part shows what was inserted):

      use Storable qw(freeze thaw);
use DBI;
use Data::Dumper;
use strict;
use warnings;

my $dsn = "DBI:mysql:database=test;host=foo";
my $dbh = DBI->connect($dsn, 'root', 'abcd', {RaiseError => 1});
#my $data = {'a'=>1, 'b'=>{'c'=>2, 'd'=>3}, 'e'=>[1,2,3]};
#my $frozen = freeze($data);
#my $command = "insert into test(a,b) values(10,?)";
#my $sth = $dbh->prepare($command);
#$sth->execute($frozen);
my $command = "select b from test where a = 10";
my $sth = $dbh->prepare($command);
$sth->execute();
my $result = $sth->fetchrow_arrayref();
my $data = thaw($result->[0]);
print Dumper($data);

      [download]

      This prints:

      $VAR1 = {
          'e' => [
                   1,
                   2,
                   3
                 ],
          'a' => 1,
          'b' => {
                   'c' => 2,
                   'd' => 3
                 }
        };

      [download]
[reply]
[d/l]
[select]
        Duh. Thanks a million kind monk :-)
[reply]

In Section Seekers of Perl Wisdom