sql server invalid precision on exists check query

Question

Using sql server 2008 I am getting and invalid precision value error in the following perl script:

use DBI;
$idx = '12345';
$query = 'if exists (select * from tbl where idx = ?) select top 10 * from tbl';
my $h = $dbh->prepare($query) or die "Couldn't prepare query: " . $dbh->errstr;
$h->execute($idx) or die "Couldn't execute statement: " . $h->errstr;

Note however that if I try this instead

use DBI;
$query = 'if exists (select * from tbl where idx = \'12345\') select top 10 * from tbl';
my $h = $dbh->prepare($query) or die "Couldn't prepare query: " . $dbh->errstr;
$h->execute() or die "Couldn't execute statement: " . $h->errstr;

then it works. I am really confused at how the ? in the query could possibly be causing an invalid precision error.

Thanks for any help anyone can provide.

Answer 1

Based on this article, please try the following:

You need to import the SQL type constants from DBI and specify that SQL_LONGVARCHAR as the type of the data to be added to the memo field.

To do that you do:

$dbh->bind_param(1, $idx, SQL_LONGVARCHAR);

Answer 2

Binding with a specific type overrides what DBD::ODBC decides. DBD::ODBC will bind the parameter based on what comes back from SQLDescribeParam. Sometimes SQL Server's SQLDescribeParam fails, especially in cases where you are using functions or subselects. The SQL Server ODBC driver takes your SQL and rearranges it to attempt to end up with something like "select idx from tbl" then it looks at the columns to answer SQLDescribeParam calls. I'm betting the SQL Server ODBC driver fails to rearrange your SQL in this case and either SQLDescribeParam failed or returned the wrong information. If you enable tracing in DBD::ODBC we could probably see this happening.