CODEWITHSUNDEEP
mysqlperlhash
norbdum
norbdum

Reputation: 2491

insert hash having values as array to MYSQL database using Perl

I have created database headers to be the keys of hash and trying to insert hash values (datatype: array) into the mysql database. But it gives the syntax error. Following is the code please suggest what to do???

my @keys = keys %hash; #keys used as database headers
my @values=values %hash; #value to be inserted
my $sth=$dbh->prepare("INSERT INTO `$table`(@keys) VALUES (@values)");
$sth->execute() or die "ERROR writing to database: $DBI::errstr $dbh->errstr";

Here is the hash (using Data::Dumper)

$VAR1 = bless( { '120493acNo' => [ '1' ], 'a120064amount' => [ '133' ], '120310amount' => [ '23' ]}, 'CGI' );

Upvotes: 0

Views: 2898

Answers (3)

pilcrow
pilcrow

Reputation: 58524

You want something like this:

my $dbh = DBI->connect(..., {RaiseError => 1});

my $sql = 'INSERT INTO ' . $dbh->quote_identifier($table_name) .
                ' (' . join(',', map { $dbh->quote_identifier($_) } @fields) . ') ' .
          'VALUES (' . join(',', map { '?' }) @values)                     . ')';

$dbh->do($sql, undef, @values);

That will

  1. Turn on RaiseError, so you don't have to check for DBI errors explicitly
  2. Safely quote the identifiers $table_name and all @fields
  3. Use placeholders for the @values, so you don't have to worry about quoting them
  4. Properly comma-separate sequences of field names and placeholders.

There are CPAN modules that conceal logic like the above behind easy-to-use interfaces.

Upvotes: 5

Dave Cross
Dave Cross

Reputation: 69224

As other people have pointed out, your problem is here.

my $sth=$dbh->prepare("INSERT INTO `$table`(@keys) VALUES (@values)");

Let's have a look at how you might have worked that out for yourself.

You're trying to create an SQL statement from some text and some variables. Whenever you do that it's a good idea to have a look at the SQL that is being generated to see if it looks reasonable.

So you could have done something like this:

my $sql = "INSERT INTO `$table`(@keys) VALUES (@values)";
print $sql;

That would shown you that your SQL looks something like this:

INSERT INTO `my_table` (col1 col2 col3) VALUES (value1 value2 value3)

Hopefully, you can see the problem immediately. But if you can't you could even copy that statement and paste it into your database's command line tool. That would give you more clues as to what the problem is.

Upvotes: 1

D Mac
D Mac

Reputation: 3809

I'm going to go with my guess: since you don't show us any details, it's tough to be sure.

It looks like you are simply taking the array, stuffing it into a string, and making that your query. In Perl, an array expressed as a string is just a space separated list of values:

@x = (1,2,3,4,5);
$y = "@x";
print "$y\n";

shows that $y here is "1 2 3 4 5";

But MySQL requires lists of column names and values in the INSERT statement to be separated by commas. So that is probably where you are going to be getting a syntax error:

INSERT INTO table_name (cola colb colc) VALUES (value value valuec); -- < ILLEGAL SYNTAX

Just separate with commas and you'll be fine (unless there's something else wrong as well):

$stmt = "INSERT INTO `$table` (" . join(',', @keys) . ") VALUES (" 
    . join(',', @values) . ")";
my $sth=$dbh->prepare($stmt);

Upvotes: 1

Related Questions