Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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 ?


Pereant, qui ante nos nostra dixerunt!

In reply to DBD::mysql "Column count doesn't match value count" by Random_Walk

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others chilling in the Monastery: (10)
    As of 2018-01-16 16:01 GMT
    Find Nodes?
      Voting Booth?
      How did you see in the new year?

      Results (182 votes). Check out past polls.