| Subject: | SQL Server connection broken after query with many bind paramters |
When using more than 2097 bind parameters in a single query, the query
will fail and database handle will be unusable afterwards and the error
below is given:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
(SQL-01000) [state was 01000 now 08S01]
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error.
Check your network documentation. (SQL-08S01)
I am unsure if SQL Server supports more bind parameters[1], but the
database handle should probably not be unusable afterwards.
The attached script demonstrates the error. It requires a SQL server
database with a single table called TEST_QUERYSIZE. The table should
have the columns TESTID and TEST_COLUMN. The type these columns should
not matter.
The error occurs on SQL Server 2000. I have not tested it on newer
versions.
[1] http://msdn.microsoft.com/en-us/library/aa933149(SQL.80).aspx
| Subject: | test_number_of_bind.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_098 ;
my $question_marks = join( ', ', map { '?' } @values );
print "\nNumber of bind parameters: " . scalar @values;
print "\n";
my $select_sql_bind = <<END_SQL;
SELECT
TESTID
TEST_COLUMN
FROM
TEST_QUERYSIZE
WHERE
TEST_COLUMN IN ( $question_marks )
END_SQL
my $result;
eval {
$result = $dbh->selectall_arrayref( $select_sql_bind, {}, @values );
};
if( $@ ){
print $dbh->errstr();
}
eval {
$dbh->selectall_arrayref( 'SELECT COUNT(*) FROM TEST_QUERYSIZE' );
};
if( $@ ){
print $dbh->errstr();
}