$sth->execute problem

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

monks,
I am having a weird problem with a $sth->execute problem! It is supposed to execute an SQL INSERT statement. It works ok on my machine but once I put it onto the server it throws this error:

DBD::mysql::st execute failed: called with 1 bind variables when 0 are
+ needed at /cgi-bin/insert.pl line 44.
[download]

,at the same time i have the data inserted into the DB fine.I'm sure this isnt a bug just dont know how to get rid off!
This is the code:

my $street        = "http://www.streetmap.co.uk/streetmap.dll?postcode
+2map?$zip&$zip";

#server vars and connection to the server                             
+                   
my $server    =    'localhost';
my $db        =    'somedb';
my $username=    'someusr';
my $password=    'somepass';
my $dbh    =DBI->connect("dbi:mysql:$db:$server", $username, $password
+,{RaiseError =>1});

#SQL statement
my $SQL1    = "INSERT INTO property (address_1, address_2, county, cit
+y, zip, description, status, bedroom, map) VALUES ('$address_1', '$ad
+dress_2', '$county', '$city', '$zip', '$description', '$status','$bed
+room',?)";
my $SQL2    = "INSERT INTO picture (property_id,url) VALUES ('property
+_id'='pic_id','$file')";


#Prepare/execute and disconnect from db
foreach my $sql ($SQL1,$SQL2) {
    my $sth    = $dbh->prepare($sql) or die $dbh->errstr;
    $sth->execute($street) or die $dbh->errstr;
}
$dbh->disconnect;
[download]

any help will do!Thanks
r_mehmed

Comment on $sth->execute problem Select or Download Code

Replies are listed 'Best First'.
Re: $sth->execute problem by pfaut (Priest) on Jan 28, 2003 at 22:30 UTC
Your second SQL statement takes no parameters but you are passing an argument to execute(). execute() attempts to bind its arguments to query parameters. Since the second statement doesn't have any, you get this error. You aren't gaining anything by trying to execute your statements in a loop so don't do it that way. Why are you embedding some values in the SQL string and using parameters for others? You should use parameters for all of your values. That way you won't have to worry about quoting. `--- print map { my ($m)=1<<hex($_)&11?' ':''; $m.=substr('AHJPacehklnorstu',hex($_),1) } split //,'2fde0abe76c36c914586c';` [download]	[reply] [d/l]
Re: $sth->execute problem by scain (Curate) on Jan 28, 2003 at 22:26 UTC
$SQL2 doesn't have a placeholder in it (ie, no '?'), but in your foreach loop, it is getting executed with $street. Scott Project coordinator of the Generic Model Organism Database Project	[reply]
Re: $sth->execute problem by rdfield (Priest) on Jan 29, 2003 at 09:33 UTC
It will help your debugging effort in the long run if you make your DBI error messages a bit more informative, by including things like the SQL being executed and any parameters being used, e.g. `$sth->execute($street) or die "STMT= execute;SQL= $sql;\$street= $stre +et;ERROR= " . $dbh->errstr;` [download] I'll admit it's a bit more work up front, but the time saved when debugging will more than make up for it. rdfield	[reply] [d/l]
Re: $sth->execute problem by OM_Zen (Scribe) on Jan 29, 2003 at 04:41 UTC
HI , The second Insert statement has no place holder ? and hence when it is called with $street it says that the needed bind variables is actually a 0 but you have supplied with one variable	[reply]