execute failed: called with 2 bind variables when 0 are needed

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

#!/usr/bin/perl
use strict;
use warnings;
 
use DBI;
 
use Data::Dumper;

my $dbh = DBI->connect('dbi:ODBC:DSN=SqlExpress;UID=USER;PWD=pass;Regi
+onal=No;') or die "Can't connect: $!";
$dbh->{'LongReadLen'} = 40000;
 
my $sql = 'SELECT crcp_cod_grupo, crcp_cod_prato FROM REST.dbo.CRCP_PR
+ATO WHERE crcp_cod_grupo = "E"';
my $sth = $dbh->prepare($sql);
$sth->execute(1, 10);
while (my @row = $sth->fetchrow_array) {
   print "Grupo: $row[0]  Prato: $row[1]\n";
}
 
$sth->execute(12, 17);
while (my $row = $sth->fetchrow_hashref) {
   print "Grupo: $row->{crcp_cod_grupo}  Prato: $row->{crcp_cod_prato}
+\n";
}
 
$dbh->disconnect;
[download]

Hi there, I'm a newbie in Perl... this is my first attempt ever...I'm getting an error when I run the piece of code above (copied from an example somewhere on the net): DBD::ODBC::st execute failed: called with 2 bind variables when 0 are needed at query02.pl line 14. DBD::ODBC::st fetchrow_array failed: no select statement currently executing (SQ L-HY000) at query02.pl line 15. DBD::ODBC::st execute failed: called with 2 bind variables when 0 are needed at query02.pl line 19. Describe failed during DBI::st=HASH(0x1bfc3b4)->FETCH(NAME,0) at query02.pl line 20. DBD::ODBC::st fetchrow_hashref failed: no select statement currently executing ( SQL-HY000) at query02.pl line 20. Press any key to continue . . . I'm using Microsoft SQL Server Express Can anyone help me, please.. Thanks!!

Comment on execute failed: called with 2 bind variables when 0 are needed Download Code

Replies are listed 'Best First'.
Re: execute failed: called with 2 bind variables when 0 are needed by Corion (Patriarch) on Dec 10, 2013 at 17:25 UTC
`$sth->execute(1, 10);` [download] Here you pass two bind variables to `->execute`. Why?	[reply] [d/l] [select]
Re: execute failed: called with 2 bind variables when 0 are needed by PerlSufi (Friar) on Dec 10, 2013 at 17:37 UTC
In addition to Corion's response, I would mention that if you DO you want to give $sth->execute() parameters, they need to be question marks in your query itself.. like `my $variable = "E"; my $sql = 'SELECT crcp_cod_grupo, crcp_cod_prato FROM REST.dbo.CRCP_PR ATO WHERE crcp_cod_grupo = ?'; my $sth = $dbh->prepare($sql); $sth->execute($variable);` [download] You would then list each variable in the order you have them as question marks in your query Also, I normally put the query in double quotes instead of single quotes.. UPDATE: Are you trying to execute only the rows that you are incorrectly passing as parameters to execute?	[reply] [d/l]
Re: execute failed: called with 2 bind variables when 0 are needed by ig (Vicar) on Dec 11, 2013 at 00:49 UTC
To put it another way, change `$sth->execute(1, 10);` to `$sth->execute();` and your SQL statement will run as is (assuming your database has the queried table and fields).	[reply] [d/l] [select]
Re^2: execute failed: called with 2 bind variables when 0 are needed by afoken (Chancellor) on Dec 11, 2013 at 19:54 UTC
change `$sth->execute(1, 10);` to `$sth->execute();` Please don't. Placeholders prevent SQL injections and allow caching of prepared statements, so: Change the value `"E"` in `$sql` to a single, unquoted `?` Change the execute parameters to `("E")` More information: DBI, Bobby Tables, Re: Counting rows Sqlite, Re^2: Massive Memory Leak, Re^5: Variable interpolation in a file to be read in. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re: execute failed: called with 2 bind variables when 0 are needed by Random_Walk (Prior) on Dec 11, 2013 at 08:10 UTC
#!/usr/bin/perl use strict; use warnings; use DBI; use Data::Dumper; # Warning, untested as I don have ODBC on this laptop # I use $dbc to make the line wrap nicer on the monks ;) my $dbc ='dbi:ODBC:DSN=SqlExpress;UID=USER;PWD=pass;Regional=No;'; my $dbh = DBI->connect($dbc) or die "Can't connect: $!"; $dbh->{'LongReadLen'} = 40000; # I took out crcp_cod_grupo from the select as its already known in th +e WHERE my $sql = ' SELECT crcp_cod_prato'; $sql.= ' FROM REST.dbo.CRCP_PRATO '; $sql.= ' WHERE crcp_cod_grupo = "?"'; # ? is placeholder for bind v +ariable my $sth = $dbh->prepare($sql); # lets look at a few different grupos, this is where the # bin variable gets used. This is more efficient than doing a # prepare, execute for each grupo. for my $crcp_cod_grupo (qw(A B C D E)) { $sth->execute($crcp_cod_grupo); while (my @row = $sth->fetchrow_array) { print "Grupo: $crcp_cod_grupo Prato: $row[0]\n"; } } # I leave the hash version as an excercise for the OP. $dbh->disconnect; [download] Cheers, R. Pereant, qui ante nos nostra dixerunt!	[reply] [d/l]