DBI: bind_param prints a string to ntext & # 8594; nvarchar (max) and ntext are incompatible

Question

I have a problem with perl DBI bind_param. The following SQL works:

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

So far the following:

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

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

It seems that bind_param , which is called by execute , returns 'string' in ntext. How can I get around this?

Answer 1

Consider value type binding before calling SQL:

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