5

I have a problem concerning perl DBI's bind_param. The following SQL works:

my $sth = $dbh->prepare("SELECT id FROM table WHERE id = 'string'");
$sth->execute();

While the following doesn't:

my $sth = $dbh->prepare("SELECT id FROM table WHERE id = ?");
$sth->execute('string');

The error the last query causes is:

[ODBC SQL Server Driver][SQL Server]The data types nvarchar(max) and ntext are incompatible in the equal to operator. (SQL-42000)

It seems like bind_param, which gets called by execute, casts 'string' to ntext. How can I work around that?

brian d foy
  • 129,424
  • 31
  • 207
  • 592
sk904861
  • 1,247
  • 1
  • 14
  • 31
  • 1
    Assuming you are using DBD::ODBC (which it looks like) then SQLBindParameter is called with a C type of SQL_C_CHAR and a parameter type of whatever the driver said the parameter was in SQLDescribeParam. If you have an up to date DBI and DBD::ODBC you can set DBI_TRACE=DBD=x.log and run your code and you'll see what it gets and does in x.log. – bohica Oct 29 '12 at 09:17

1 Answers1

7

Consider binding the value type prior the SQL call:

use DBI qw(:sql_types);

my $sth = $dbh->prepare( "SELECT id FROM table WHERE id = ?" );

my $key = 'string';
my $sth->bind_param( 1, $key, SQL_VARCHAR );

$sth->execute();
tstrit
  • 126
  • 2