Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 49061
Status: resolved
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: oystein.torget [...] dnv.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: 1.15
Fixed in: (no value)



Subject: SQL Server connection broken after query with many bind paramters
Download (untitled) / with headers
text/plain 889b
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(); }
Download (untitled) / with headers
text/plain 1.5k
On Thu Aug 27 05:05:21 2009, oyse wrote: Show quoted text
> 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
Sorry it has taken 11 days to reply but I've been on holiday. Your test table and perl script works fine with DBD::ODBC and a number of SQL Server drivers I use here but fails with some Microsoft SQL Server drivers. I believe this is a bug in the sql server driver you are using. As for the database handle being left unusable that depends on whether it is unusable in DBI/ODBC terms or unusable because the driver has now broken the protocol it uses on the socket - neither DBI or DBD::ODBC can do anything about the latter. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 1.7k
On Mon Sep 07 09:05:18 2009, MJEVANS wrote: Show quoted text
> On Thu Aug 27 05:05:21 2009, oyse wrote:
> > 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
> > Sorry it has taken 11 days to reply but I've been on holiday. Your test > table and perl script works fine with DBD::ODBC and a number of SQL > Server drivers I use here but fails with some Microsoft SQL Server > drivers. I believe this is a bug in the sql server driver you are using. > As for the database handle being left unusable that depends on whether > it is unusable in DBI/ODBC terms or unusable because the driver has now > broken the protocol it uses on the socket - neither DBI or DBD::ODBC can > do anything about the latter. > > Martin
Thank you for the reply. Could you post a list of all the MS SQL Server drivers that the test script fails for? Then I could check if the driver that we use corresponds to one where the test fails.
Download (untitled) / with headers
text/plain 1018b
On Tue Sep 08 02:32:07 2009, oyse wrote: Show quoted text
> > Thank you for the reply. Could you post a list of all the MS SQL Server > drivers that the test script fails for? Then I could check if the driver > that we use corresponds to one where the test fails.
Both of your test programs fail with the MS SQL Server ODBC Driver 2000.85.1132.00. I have at least 7 other versions of the driver but no time to run through them all. Perhaps you could tell me which driver and version you are using. Neither of your test programs fail in the same way with the MS SQL Server Native Client 2005.90.1399.00. In this case, the test_number_of_bind.pl script fails with "too many parameters provided in the RPC request. The maximum is 2100". The test_query_with_binding.pl script completes with "query length 65552". I will leave your 2 issues open for a while whilst we discuss this but I don't believe either of them are bugs in DBD::ODBC neither to I believe there is any way around the problem. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 474b
I currently have the SQL Server 2000.85.1132.00 driver. As both the test cases work as expected with a newer driver it seems likely that the problem is the ODBC driver. In that case I see no reason to keep the bugs open and it is ok for me if they are closed. Perhaps an entry about this could be added to the distribution FAQ or some other appropriate place in cases others come across the same problem? If you want to I can write the entry and submit it as a patch.
Download (untitled) / with headers
text/plain 703b
On Wed Sep 16 03:12:59 2009, oyse wrote: Show quoted text
> I currently have the SQL Server 2000.85.1132.00 driver. > > As both the test cases work as expected with a newer driver it seems > likely that the problem is the ODBC driver. In that case I see no > reason to keep the bugs open and it is ok for me if they are closed. > > Perhaps an entry about this could be added to the distribution FAQ or > some other appropriate place in cases others come across the same > problem? If you want to I can write the entry and submit it as a patch.
I would gratefully accept a FAQ entry including the errors you got and worth mentioning the limit specified in the error I got. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 904b
On Wed Sep 16 03:56:22 2009, MJEVANS wrote: Show quoted text
> On Wed Sep 16 03:12:59 2009, oyse wrote:
> > I currently have the SQL Server 2000.85.1132.00 driver. > > > > As both the test cases work as expected with a newer driver it seems > > likely that the problem is the ODBC driver. In that case I see no > > reason to keep the bugs open and it is ok for me if they are closed. > > > > Perhaps an entry about this could be added to the distribution FAQ or > > some other appropriate place in cases others come across the same > > problem? If you want to I can write the entry and submit it as a patch.
> > I would gratefully accept a FAQ entry including the errors you got and > worth mentioning the limit specified in the error I got. > > Martin
I am writing this off as an ODBC driver issue and lacking any input on a FAQ entry there is nothing further I can do. Martin -- Martin J. Evans Wetherby, UK
From: oystein.torget [...] dnv.com
On Wed Oct 21 16:05:53 2009, MJEVANS wrote: Show quoted text
> On Wed Sep 16 03:56:22 2009, MJEVANS wrote:
> > On Wed Sep 16 03:12:59 2009, oyse wrote:
> > > I currently have the SQL Server 2000.85.1132.00 driver. > > > > > > As both the test cases work as expected with a newer driver it seems > > > likely that the problem is the ODBC driver. In that case I see no > > > reason to keep the bugs open and it is ok for me if they are closed. > > > > > > Perhaps an entry about this could be added to the distribution FAQ or > > > some other appropriate place in cases others come across the same > > > problem? If you want to I can write the entry and submit it as a
patch. Show quoted text
> > > > I would gratefully accept a FAQ entry including the errors you got and > > worth mentioning the limit specified in the error I got. > > > > Martin
> > I am writing this off as an ODBC driver issue and lacking any input on a > FAQ entry there is nothing further I can do. > > Martin
Feel free to close the issue. I have attached FAQ entries for the two reported bugs. Sorry for the delay. - Øystein
Download faq_entry.txt
text/plain 1.4k
=head2 "ConnectionWrite (send())" error and bind parameters The following error can happen when using more than 2097 bind parameters in a single query with SQL Server 2000: [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) This error is most likely due to a bug in the Microsoft SQL Server ODBC driver as it fails for some version of the driver and not others. It is known to fail for version 2000.85.1132.00 Se bug report https://rt.cpan.org/Public/Bug/Display.html?id=49061 for more details. =head2 SQL query length limited to about 65500 characters in SQL Server 2000 When using bind parameters and a query longer than about 65500 characters the query will fail with some versions of the SQL Server 2000 ODBC driver. The error message from the server can vary. Below is an example: [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) Removing the use of binding will allow queries that are longer than 65500 characters. This bug is known to affect Microsoft SQL Server ODBC driver version 2000.85.1132.00. See bug report https://rt.cpan.org/Public/Bug/Display.html?id=49065
Download (untitled) / with headers
text/plain 127b
Thank you for the FAQ entries, I have added them and they will be in the next release. Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.