DBD::mysql::st execute failed: called with 181 bind variables when 172 are needed

Question

I am getting this error

DBD::mysql::st execute failed: called with 181 bind variables when 172 are needed at line 110, <$fh> line 1.

I think the problem lies within this section of the code

while ( my $row = <$fh> ) {

    chomp $row;

    my @DNA = split('\|', $row);  

    my $participant_id = $DNA[0];
    $participant_id =~ s/\>//g;

    my $array = $DNA[1];

    my $length = length $array;

    $array =~ tr/a-z/A-Z/;
    $array =~ s/(...)/$1 /g; 
    $array =~ s/\s+/,/g;

    my @DNA1 =   split (',', $array);
    unshift @DNA1, $participant_id;

    $sth4->execute(@DNA1);  # Line 110  
}

$sth4->finish;

Useful error message. Your `@DNA` has more columns than the corresponding SQL statement knows about. Find where the SQL statement is defined ( look for $sth4 further up in the code ), and see if you can figure out what to do. You probably need to change the SQL to accommodate your data size. — xxfelixxx, Jun 28 '16 at 11:26
You can see what SQL it is trying to run by turning on tracing: http://stackoverflow.com/questions/5885561/dump-prepared-sql-query-from-dbi-statement-in-perl — xxfelixxx, Jun 28 '16 at 11:38

Borodin · Answer 1 · 2016-06-28T12:19:19.793

With 172 placeholders I assume the SQL statement has been produced automatically

You need to look at the code around something like

my $sth4 = $dbh->prepare(...);

which contains 172 placeholders ? whereas your statement

my @DNA1 = split (',', $array);

results in @DNA1 having 181 elements

The problem is in these lines

$array =~ tr/a-z/A-Z/;
$array =~ s/(...)/$1 /g; 
$array =~ s/\s+/,/g;

which clearly don't do what you think they do

Show the contents of $array (in a new question) and describe the transformation that you really need and we will be able to help

By the way, those are some awful variable names. $array is clearly not an array, and @DBA1 is just some .. database list .. thing, and has capital letters for no good reason

DBD::mysql::st execute failed: called with 181 bind variables when 172 are needed

1 Answers1