cormanaz has asked for the wisdom of the Perl Monks concerning the following question:

Good day Monks.

I want to take a data structure and stick it into a MySQL table for storage. I am trying to do it using mod Storable like so 

my $frozen = freeze($data);
dosql("insert into blobtest (key,test) values (1,\'$frozen\')");

[download]
But I get the following error no matter whether the "test" column is defined as text or a blob, and whether $frozen in the SQL statement is single-quoted or not:
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key,test) values (1,'1234' at line 1 at C:\projects\threadagent\testdbthreadstore.pl line 34. Could not execute MySQL statement: insert into blobtest (key,test) values (1,'1234
What am I doing wrong?

TIA.......Steve

Replies are listed 'Best First'.
Re: Putting mod Storable output into MySQL
by pg (Canon) on Oct 15, 2005 at 17:03 UTC

    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]
[reply]
[d/l]
[select]
      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]
Re: Putting mod Storable output into MySQL
by merlyn (Sage) on Oct 15, 2005 at 15:20 UTC
[reply]
      Yes I do, presuming I'm doing the placeholder operation correctly 
      my $frozen = freeze($data);
my $command = "insert into blobtest (key,blob) values (1,?)";
my $sth = $dbh->prepare($command);
$sth->execute($frozen) || die "Could not execute MySQL statement: $com
+mand";

      [download]
      This last line gives the same error.
[reply]
[d/l]
Re: Putting mod Storable output into MySQL
by szbalint (Friar) on Oct 15, 2005 at 15:04 UTC
    Um, you need not escape ' if used inside a string quoted by " ", afaik. I could be wrong of course...
[reply]

Back to Seekers of Perl Wisdom