| Subject: | SQL query length limited to about 65500 characters when binding used with SQL Server |
The driver fails to execute a query if the query is longer than about
65500 characters and the query uses at least one bind parameter.
The error message from the driver can vary. The attached script creates
the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'TES@P1I'. (SQL-42S22) [state was 42S22 now 42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (SQL-42000)
Queries that are longer than 65500 characters are accepted, but only if
they do not contain binding parameters.
The problem occurs on SQL Server 2000.
The attached script demonstrates the problem.
| Subject: | test_query_size_with_binding.pl |
#!perl
use strict;
use warnings;
use DBI;
my $dns = "driver={SQL Server};database=test_querysize;pwd=test;uid=test;server=localhost";
my $dbh = DBI->connect( "DBI:ODBC:${dns}", undef, undef, { PrintError => 0, RaiseError => 1 } );
my @values = 1 .. 2_663 ;
my $where_sql = join( ' OR ', map { " TEST_COLUMN = '$_'" } @values );
my $select_sql = <<END_SQL;
SELECT
TESTID
TEST_COLUMN
FROM
TEST_QUERYSIZE
WHERE
$where_sql
AND TESTID = ?
END_SQL
print "Query length: " . length( $select_sql ) . "\n";
my $result;
eval {
$result = $dbh->selectall_arrayref( $select_sql, {}, ( 1 ) );
};
if( $@ ){
print $dbh->errstr();
}