I seek DBI wisdom concerning bind parameters

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

Greetings, fellow monks

I seek some DBI wisdom.

I am using the following code to extract some data from our MS-SQL database using Perl version 5.8.8: (I've snipped out the irrelevant stuff for clarity and conciseness)

use strict;
use warnings;
use DBI;

<snip>

my $dbh=DBI->connect('dbi:ODBC:DSN='.$DSN, $USER, $PSWD) or die "Could
+n't connect to database: ".DBI->errstr; 

my $fieldquery="
            
SELECT
<Snip>

FROM
<Snip>

WHERE
PlanSetup.PlanSetupSer=?
<snip the rest of the where clause>
";

my $sth_fields=$dbh->prepare($fieldquery) or die "Couldn't prepare sta
+tement: " . $dbh->errstr;

<Snip some stuff to get $plansetupser>

$sth_fields->execute($plansetupser) or die "Couldn't execute statement
+: ".$sth_fields->errstr;
[download]

The trouble is, when I try to run this statement, I always get the error message:

Couldn't execute statement: called with 1 bind variables when 0 are needed at line <snip>

I just don't understand why it says that it doesn't need a bind variable when I have definitely put a question mark in the SQL. I've tried various other things instead of the question mark, like :parameter, but these have the same effect. There's clearly something up with my syntax, but I'm stumped as to what it is...

I hope this is enough to go on, and thanks in advance for any help!

Comment on I seek DBI wisdom concerning bind parameters Select or Download Code

Replies are listed 'Best First'.
Re: I seek DBI wisdom concerning bind parameters by graff (Chancellor) on Oct 22, 2015 at 16:39 UTC
While it was kind of you to snip out irrelevant details, the obvious risk is that the problem lies in the parts you've left out - e.g. an extra quote mark or unescaped apostrophe or some such thing in the assignment to $fieldquery, or maybe some code that prepares another query and assigns that to $fieldquery (update: or rather, to $sth_fields) before your "execute" call. For that matter, we don't actually know whether the line number reported in the error message (whatever it was) actually corresponds to the particular "execute" call you showed us.	[reply]
Re^2: I seek DBI wisdom concerning bind parameters by campbell (Beadle) on Oct 23, 2015 at 13:35 UTC
Thanks for the replies, people. You're correct - I did, in fact snip out the piece of code that was creating the problem. Basically, as part of a systems upgrade, we are moving from Sybase (using trans-SQL) to MS-SQL, and there were some functions embedded into the query that caused it fall over before it got as far as resolving the bind parameters. So I've straightened it out and all is well...	[reply]
Re: I seek DBI wisdom concerning bind parameters by fishmonger (Chaplain) on Oct 22, 2015 at 16:23 UTC
The first step I'd take would be to print out `$fieldquery` to verify it's what you expect.	[reply] [d/l]
Re: I seek DBI wisdom concerning bind parameters by 1nickt (Canon) on Oct 23, 2015 at 06:23 UTC
In addition to the recommendation to print out your query string to see that it contains what you expect, I suggest using DBI TRACING to see what the DB is actually getting. The way forward always starts with a minimal test.	[reply]
Re: I seek DBI wisdom concerning bind parameters by SimonPratt (Friar) on Oct 23, 2015 at 10:42 UTC
I agree with previous posters that you have likely snipped the cause of the issue from your example. Is it possible to create a simplified script that demonstrates the problem in a way that doesn't give up restricted information? Also, just in relation to connecting to your DB, I strongly suggest you use something similar to the following: `my $sqlserver = 'SERVERNAME'; my $sqldb = 'DBNAME'; my $dbs = "dbi:ODBC:Driver=SQL Server;Database=$sqldb;Server=$sq +lserver;Trusted_Connection=yes"; my $dbhandle = DBI->connect($dbs, undef, undef, {RaiseError => 1, Aut +oCommit => 1}) or die DBI->errstr();` [download] That `Trusted_Connection=yes` means the user account configured to run the script is the one that is used to authenticate and you don't have to store any passwords anywhere. Specifically setting AutoCommit also makes you consciously think about your work-flow. If you're running essentially bulk operations, committing after every statement can severely limit your performance.	[reply] [d/l] [select]
A reply falls below the community's threshold of quality. You may see it by logging in.