column count error followed by too few bind variables

baperl has asked for the wisdom of the Perl Monks concerning the following question:

hi, I have looked at previous posts and so included

use DBIx::Simple;
use SQL::Abstract;
[download]

but to no avail. I have an INSERT statement in my code (table has 44 columns, first being an auto_increment id) that reads as (I've not included all 43 columns for brevity):

my $q = "INSERT INTO history (`Symbol`,`Company_Name`,`Last_Price`,...
+...,`Mktcap`) VALUES (?,?,?,STR_TO_DATE(?,\'%m/%d/%Y'),TIME(?),?,?,?,
+?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
+)";
[download]

When I only had 24 ?s following TIME(?)(making it a total of 29 values), I was getting the following error:

DBD::mysql::st execute failed: Column count doesn't match value count 
+at row 1 at rtd.pl line 62.
[download]

so, I went in and added 14 ?s towards the end to total it to 43 ?s (as in the above code) and now I get the following error:

DBD::mysql::st execute failed: called with 29 bind variables when 43 a
+re needed at rtd.pl line 65.
[download]

can you please help me fix it.... I'm including the remaining code:


$sth = $dbh->prepare($q) or die $dbh->errstr;

#the below includes 'id' which is auto_increment
my @fields = ('id','Symbol','Company Name','Last Price',....,'Market-c
+ap');
my @symbols = ('AA','C');
useRealtimeQueryFormat();
my @quotes = getquote(@symbols);

while ($cc < 22222) 
{
  $cc++;
  foreach my $q (@quotes) {
    my @h = @{$q};
    join("\t",@h),"\n";
    my $c = @h;
    for ( my $i = 0 ; $i < $c ; $i++ ) 
         {
      my $j = 1+ $i;
      $h[$i] =~ s/ +//;
      $h[$i] =~ s/ %//;
      $h[$i] = ($h[$i] eq 'N/A') ? undef : $h[$i];
          }
    $sth->execute(@h);
  }
}
[download]

Comment on column count error followed by too few bind variables Select or Download Code

Replies are listed 'Best First'.
Re: column count error followed by too few bind variables by runrig (Abbot) on Aug 02, 2011 at 23:25 UTC
You need to debug your code. E.g., what do you think this is doing: `join("\t",@h),"\n";` [download] Why is this line there: `my $j = 1+ $i;` [download] Did you know you can more simply iterate over elements of an array with, e.g.: `for my $element (@h)` [download] use print and/or warn to output messages until your satisfied that all of your variables are what you expect them to be. E.g. I don't know what this does because you didn't provide the source for it, and I'm sure I don't want to see it: `my @quotes = getquote(@symbols);` [download]	[reply] [d/l] [select]
Re^2: column count error followed by too few bind variables by AnomalousMonk (Archbishop) on Aug 02, 2011 at 23:52 UTC
Further to runrig's reply: The presence of the statement `join("\t",@h),"\n";` suggests baperl is not running with strictures (`use strict;`) or warnings (`use warnings;`) enabled, which might offer many suggestions about other problems in the code: `>perl -wMstrict -le "my @h = qw(a bc def ghij); join(qq{\t}, @h), qq{\n}; " Useless use of join or string in void context at -e line 1. Useless use of a constant ( ) in void context at -e line 1.` [download] Update: Removed extraneous statement from example code.	[reply] [d/l] [select]
Re^2: column count error followed by too few bind variables by baperl (Sexton) on Aug 03, 2011 at 01:07 UTC
thanks runrig, I've been staring at this code and testing and trying so many different iterations that I accidentally left the `my $j=...` [download] I will fix the join, and these are the methods I am using `#!/usr/bin/perl -w use strict; use Finance::YahooQuote; use DBI; use DBD::mysql; use DBIx::Simple; use SQL::Abstract;` [download] I suspect that is what you were refering to when you said the source for my @quotes.	[reply] [d/l] [select]
Re: column count error followed by too few bind variables by dreadpiratepeter (Priest) on Aug 02, 2011 at 22:13 UTC
You should always include ALL the code when asking questions. In this case the error is in what you haven't shown. The number of placeholders ('?') in the string you are passing to prepare does not match the number of parameters you are passing to execute. -pete "Worry is like a rocking chair. It gives you something to do, but it doesn't get you anywhere."	[reply]
Re^2: column count error followed by too few bind variables by baperl (Sexton) on Aug 02, 2011 at 23:07 UTC
hi Pete, I updated my post with the remaining code... await your response....thx!	[reply]
Re: column count error followed by too few bind variables by Neighbour (Friar) on Aug 03, 2011 at 08:55 UTC
It might have been mentioned here before, but I'll put it as explicitly as possible: There were 2 errors, the first error you fixed yourself by making sure that `Symbol`,`Company_Name`,`Last_Price`,...,`Mktcap` had the same amount of items as the amount of ?'s in your VALUES-list. The second error means that mysql now expects values when you execute your query (`$sth->execute(@h);`). Apparently, your `@h` contains only 29 values, but there are 43 ?'s that need to be given something (a value or 'undef' if you want it to be NULL). If you want to fill all the extra columns (that you initially added all those ?'s for) to be NULL, why don't you just do that in your query?	[reply] [d/l] [select]


"be consistent"
	PerlMonks