Need help with DBI bind value error

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

Hi all, Since its now time for me to learn the correct way to do perl scripts i have started to use strict´. not very easy i most say..... but now i have a problem:

  my ($grp_name, $grp_description) = @_;
   my %form_values="";

   my $sqlcount = "SELECT COUNT(*) FROM grp_def WHERE grp_name='$grp_n
+ame' ";
   my $sth = $dbh->prepare($sqlcount) || die "Cannot prepare: " . $dbh
+->errstr();
   $sth->execute($form_values{'grp_name'}) or die "Cannot execute: " .
+ $sth->errstr();
   my $occurences = $sth->fetchrow_arrayref->[0];

   print "result: $occurences\n";
[download]

and here is my error:

DBD::DB2::st execute failed: execute called with 1 bind variables when
+ 0 are needed at miller.pl line 71.
Cannot execute: execute called with 1 bind variables when 0 are needed
+ at miller.pl line 71.
[download]

I dont know where to start to look for the solution, if its DBI or somthing else i HOPE you can help me ,Miller

edited: Thu Jun 12 14:39:08 2003 by jeffa - title change (was: strict and DBI)

Comment on Need help with DBI bind value error Select or Download Code

Replies are listed 'Best First'.
Re: Need help with DBI bind value error by broquaint (Abbot) on Jun 12, 2003 at 10:13 UTC
This is an issue with `DBI` placeholders and not `strict`. Your problem is that you're calling `execute()` with a placholder value where your SQL doesn't have a placeholder in it. Your code should probably look more like this (formatting added for my own benefit :) `my $sqlcount = 'SELECT COUNT() FROM grp_def WHERE grp_name = ?'; my $sth = $dbh->prepare($sqlcount) or die "Cannot prepare: " . $dbh->errstr(); $sth->execute($grp_name) or die "Cannot execute: " . $sth->errstr(); my $occurences = $sth->fetchrow_arrayref->[0]; print "result: $occurences\n";` [download] There I've moved `$grp_name` out of the SQL and moved into the `execute()` and replaced it with a placeholder. For more info on placeholders see the Placeholders and Bind Values* section of the DBI docs. HTH `_________ broquaint`	[reply] [d/l]
Re: Need help with DBI bind value error by davorg (Chancellor) on Jun 12, 2003 at 10:08 UTC
The error is pretty clear. You are calling `execute` passing it a value to bind to a bind point (`$form_values{'grp_name'}`), but the SQL statement that you have prepared doesn't contain any bind points to bind that data to. You need to either remove that parameter from your call to `execute` or add a bind point (i.e. a '?') to the SQL statement. -- <http://www.dave.org.uk> "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]
Re: Need help with DBI bind value error by bigj (Monk) on Jun 12, 2003 at 11:32 UTC
And in addition to the others, there is yet another little mistake in your script: `my %form_values="";` [download] If you want to initialize an empty error, it's better to use `my %form_values; # just that, now it's empty # or my %form_values = (); # so that everybody knows you really wanted that` [download] Your instead is interpreted by perl as `my %form_values = ("" => undef);` [download] what is perhaps not what you meant. BTW: Perl would have also told you that if you have switched warnings on :-) Greetings, Janek	[reply] [d/l] [select]
Re: Need help with DBI bind value error by Itatsumaki (Friar) on Jun 12, 2003 at 16:52 UTC
The above answers are all very much right in identifying the problem as your usage of place-holders. One other minor point about your program design that might help you: you pass `param('grp_name')` to DBI without checking its validity or existence. If that parameter is undefined, you will get an error (actually, the same one you got here but with the numbers reversed. You might want to consider doing something as simple as this: `my $grp_name = param('grp_name'); if (exists($grp_name)) { $sth->execute($grp_name); }` [download] Or you could get more complicated and check the validity of param('grp_name') with an appropriate reg-ex. In short: it's usually a good idea to validate your parameters before passing them to the database. Hope it helps, -Tats Update: Fixed grammar error	[reply] [d/l] [select]
Re: Need help with DBI bind value error by miller (Acolyte) on Jun 13, 2003 at 08:53 UTC
thank you very much for all the help..... now i can moce forward.... but i still need to read some more about the DBI module	[reply]