Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

DBD::mysql "Column count doesn't match value count"

by Random_Walk (Parson)
on Nov 28, 2006 at 18:19 UTC ( #586545=perlquestion: print w/ replies, xml ) Need Help??
Random_Walk has asked for the wisdom of the Perl Monks concerning the following question:

This has been driving me crazy all day, I have found thousands of hits on the web but nothing that has helped. super search came up pretty dry too.

Trying to prepare with placeholders my SQL does not work, it give the error "Column count doesn't match value count", doing it without placeholders runs no problem. I am using the keys and values from the same hash to do both. I have tried using the $dbh->quote method on all my values just incase but that was no help either.

Here is the ouput of my test script, one entry is inserted by the second non-placeholder method

using prepared: INSERT into monica.event (msg, source, situation, version, date, hostn +ame, last_uid, sub_origin, component, guid, severity, adapter_host) v +alues (????????????) DBD::mysql::st execute failed: Column count doesn't match value count at row 1 at tmp/test_sql line 4 +1. can not execute on DBI::st=HASH(0x20262d0c): Column count doesn't match value count at row 1 using direct: INSERT INTO monica.event (msg, source, situation, version, date, hostn +ame, last_uid, sub_origin, component, guid, severity, adapter_host) V +ALUES ('this is a test event, ignore', 'Testing', 'START', '0.1', '20 +06-11-28 19:13:18', 'test', '', 'SelectDocument', 'IT_SERVICE', '6233 +75574', 'CRITICAL', 'this_box')
Here is a small (well as small as I could get it :) sample showing my trouble
#!/usr/bin/perl use strict; use warnings; use POSIX qw(strftime); use DBI; use DBD::mysql; my $user = '*****'; my $pass = '*****'; my $dsn = 'dbi:mysql:xxxx:xxx:xxxx'; my $table = 'monica.event'; my %slots = ( guid => (int rand 1000000000), component => 'IT_SERVICE', source => 'Testing', sub_origin => 'SelectDocument', situation => 'START', hostname => 'test', adapter_host => 'this_box', date => (strftime "%Y-%m-%d %H:%M:%S", loca +ltime), severity => 'CRITICAL', msg => 'this is a test event, ignore', version => '0.1', last_uid => '' ); # now connect and get a database handle my $dbh = DBI->connect( $dsn, $user, $pass) or die "Can't connect DB: $DBI::errstr\n"; # prepare the placeholder method my $slots = join ', ', keys %slots; my $places = join ', ', ('?' x scalar keys %slots); my $p_sql = "INSERT into $table ($slots) values ($places)"; print "using prepared:\n$p_sql\n\n"; # prepare with place holders; my $psth = $dbh->prepare($p_sql) or die "Can't create statement $DBI::errstr\n"; # and then use them $psth->execute(values %slots) or print "can not execute on $psth: $DBI::errstr\n"; print $/; $slots{guid}++; # is unique key # do the less effifcient way my $joinvals = "'". (join "', '", values %slots) . "'"; my $d_sql = "INSERT INTO $table ($slots) VALUES ($joinvals)"; print "using direct:\n$d_sql\n\n"; my $dsth = $dbh->prepare($d_sql) or die "can prepare sql: $DBI::errstr\n"; $dsth->execute() or die "can not execute sql: $DBI::errstr\n"; print STDERR $/;
What on earth am I doing wrong ?

Cheers,
R.

Pereant, qui ante nos nostra dixerunt!

Comment on DBD::mysql "Column count doesn't match value count"
Select or Download Code
Re: DBD::mysql "Column count doesn't match value count"
by Paladin (Priest) on Nov 28, 2006 at 18:29 UTC

    Shouldn't this:

    INSERT into monica.event (msg, source, situation, version, date, hostn +ame, last_uid, sub_origin, component, guid, severity, adapter_host) v +alues (????????????)

    actually be:

    INSERT into monica.event (msg, source, situation, version, date, hostn +ame, last_uid, sub_origin, component, guid, severity, adapter_host) v +alues (?,?,?,?,?,?,?,?,?,?,?,?)
    Replace the line:
    my $places = join ', ', ('?' x scalar keys %slots);
    with
    my $places = join ', ', (('?') x scalar keys %slots);

      Yes it should ! thank you so much, this is the first time I have used placeholders so I completely missed that. Those two brackets have cost me half my hair today.

      Cheers,
      R.

      Pereant, qui ante nos nostra dixerunt!
      I take the opportunity to point to DBIx::Simple, which provides means to insert this kind of stuff automatically, if you use a double question mark.
      $db->query('INSERT into monica.event (msg, source, situation, version, + date, hostname, last_uid, sub_origin, component, guid, severity, ada +pter_host) values (??)', 'this is a test event, ignore', 'Testing', ' +START', '0.1', '2006-11-28 19:13:18', 'test', '', 'SelectDocument', ' +IT_SERVICE', '623375574', 'CRITICAL', 'this_box');
      Or even, using the SQL::Abstract mode:
      $db->insert('monica.event' => { 'msg' => 'this is a test event, ignore', 'source' => 'Testing', 'situation' => 'START', 'version' => '0.1', 'date' => '2006-11-28 19:13:18', 'hostname' => 'test', 'last_uid' => '', 'sub_origin' => 'SelectDocument', 'component' => 'IT_SERVICE', 'guid' => '623375574', 'severity' => 'CRITICAL', 'adapter_host' => 'this_box' });
      which I like very much.

      DBIxSimple will build the proper SQL statement, and cache the statement handle for you, for if you use it more than once. Which is very neat.

Re: DBD::mysql "Column count doesn't match value count"
by davis (Vicar) on Nov 28, 2006 at 18:32 UTC
    I believe you want to put commas between your placeholders.

    davis
    Kids, you tried your hardest, and you failed miserably. The lesson is: Never try.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://586545]
Approved by ikegami
Front-paged by derby
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (12)
As of 2014-07-30 13:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (234 votes), past polls