insert hash having values as array to MYSQL database using Perl

Question

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' );

I didn't downvote but I would guess it's because you failed to show what was in the hash and did not post the error. — jordanm
– jordanm, Commented Oct 18, 2013 at 13:26
I didn't vote the question down, but I would ask you for the specifics of the syntax error. In MySQL, the syntax errors are enormously helpful - they point to the exact place that the syntax error occurred. — D Mac
– D Mac, Commented Oct 18, 2013 at 13:26
Also, it looks like you are passing a list of columns and a list of values into the INSERT without separating with commas, as MySQL requires, but I'm only guessing. Show us the syntax error and that might confirm my guess. — D Mac
– D Mac, Commented Oct 18, 2013 at 13:28

pilcrow · Accepted Answer · 2013-10-18 15:40:50Z

5

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

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

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

edited Oct 18, 2013 at 15:40

answered Oct 18, 2013 at 13:39

pilcrow

59.2k14 gold badges101 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

norbdum Over a year ago

thanks for such a detailed explanation and my mistake for not adding the hash earlier but as u can see that my values are of data type array, what could be the way out? Also what is the undef for in the last line??

pilcrow Over a year ago

@mudBorn, the undef means "use default handle attributes" — see the documentation. If your @values are arrayrefs, you'll need to do something like ->do($sql, undef, map { $_->[0] } @values). Try it, and if it doesn't work, research it a bit and then ask a separate question.

D Mac · Accepted Answer · 2013-10-18 13:35:12Z

1

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);

answered Oct 18, 2013 at 13:35

D Mac

3,8191 gold badge27 silver badges34 bronze badges

2 Comments

friedo Over a year ago

You should really use DBI placeholders rather than interpolating the values directly in the string.

D Mac Over a year ago

I agree, and what you're saying is how I write my code, but I didn't want to take the OP too fa out of his/her comfort zone.

Dave Cross · Accepted Answer · 2013-10-18 14:35:59Z

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.

Collectives™ on Stack Overflow

insert hash having values as array to MYSQL database using Perl

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related