DBI: bind_param casts string to ntext -> nvarchar(max) and ntext are incompatible

dbi, ntext, perl, sql

Solution

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();

Problem

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?

Original source