Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

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 ?

Cheers,
R.

Pereant, qui ante nos nostra dixerunt!

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

Title:
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!
  • 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
  • Outside of code tags, you may need to use entities for some characters:
            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?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others scrutinizing the Monastery: (9)
    As of 2014-07-14 08:40 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      When choosing user names for websites, I prefer to use:








      Results (257 votes), past polls