Bug #39897 for DBD-ODBC: String data, right truncation (SQL-01004) in DBD-ODBC 1.17, not in .16

Wed Oct 08 15:40:14 2008 Scott.Stricker [...] NGC.com - Ticket created

Subject:

String data, right truncation (SQL-01004) in DBD-ODBC 1.17, not in .16

My environment is - windows xp w/ SP2 - SQL Server 2005 Developers edition - ActiveState perl installed from ActivePerl-5.10.0.1004-MSWin32-x86-287188.msi - DBD-ODBC 1.17 The attached file contains a sql file used to create the table, a text file used to populate it with Show quoted text

> bcp perltest..agents in agents.txt -c -U username -P password

and a perl script ag.pl that fetches the id and name from the table. Running the script using DBD-ODBC 1.17, I get an error DBD::ODBC::st fetchrow_array failed: [Microsoft][SQL Native Client]String data, right truncation (SQL-01004) at C:\ag.pl line 16. after a number of lines are fetched. If I add the line $dbh->{LongReadLen} = 20000; to ag.pl, I don't get the error. I also don't get the error with the 1.16 version of the odbc module or if I talk to the database with, for example, the DBD-ADO module.

Subject:

Agents.zip

Download Agents.zip
application/zip 3.1m

Message body not shown because it is not plain text.

Fri Oct 10 03:45:39 2008 bohica [...] ntlworld.com - Correspondence added

Sorry for nor responding earlier but I've been away. I'll take a look at this today. Martin -- Martin J. Evans Wetherby, UK

Fri Oct 10 03:45:40 2008 The RT System itself - Status changed from 'new' to 'open'

Fri Oct 10 06:00:41 2008 bohica [...] ntlworld.com - Correspondence added

On Wed Oct 08 15:40:14 2008, stricsc wrote: Show quoted text

> My environment is > - windows xp w/ SP2 > - SQL Server 2005 Developers edition > - ActiveState perl installed from > ActivePerl-5.10.0.1004-MSWin32-x86-287188.msi > - DBD-ODBC 1.17 > > The attached file contains a sql file used to create the table, a text > file used to populate it with

> > bcp perltest..agents in agents.txt -c -U username -P password

> and a perl script ag.pl that fetches the id and name from the table. > > Running the script using DBD-ODBC 1.17, I get an error > DBD::ODBC::st fetchrow_array failed: [Microsoft][SQL Native > Client]String data, right truncation (SQL-01004) at C:\ag.pl line 16. > after a number of lines are fetched. > > If I add the line > $dbh->{LongReadLen} = 20000; > to ag.pl, I don't get the error. > > I also don't get the error with the 1.16 version of the odbc module or > if I talk to the database with, for example, the DBD-ADO module.

Thanks for all the data to reproduce this issue. The problem is due to a change since 1.16 and a border-line case. Your column is a varchar(200). 1.16 only applied LongReadLen/LongTruncOk to LONG columns (e.g. long_varchar). 1.17 applies LongReadLen/LongTruncOk to SQL_VARCHAR columns too and the column in your data that fails is 81 characters in size (default LongReadLen is 80). Previously in 1.16 all of your varchar(200) would have been retrieved. I have reverted the accidental change to varchars and it should work as in 1.16 now. If you'd like a copy of DBD::ODBC 1.17_1 to try let me know. Martin -- Martin J. Evans Wetherby, UK

Fri Oct 10 08:30:59 2008 Scott.Stricker [...] NGC.com - Correspondence added

Subject:	RE: [rt.cpan.org #39897] String data, right truncation (SQL-01004) in DBD-ODBC 1.17, not in .16
Date:	Fri, 10 Oct 2008 07:28:35 -0500
To:	<bug-DBD-ODBC [...] rt.cpan.org>
From:	"Stricker, Scott (C2MS)" <scott.stricker [...] ngc.com>

Martin: Thanks for looking into this. I'd be happy to test the 17_1 version if I can relatively easily build it into my ActiveState environment. E. Scott Stricker Senior Software Engineer Northrop Grumman DMS MailStop 3S02 2340 Dulles Corner Blvd. Herndon, VA 20171 703-561-3671 Show quoted text

-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Friday, October 10, 2008 6:01 AM To: Stricker, Scott (C2MS) Subject: [rt.cpan.org #39897] String data, right truncation (SQL-01004) in DBD-ODBC 1.17, not in .16 <URL: http://rt.cpan.org/Ticket/Display.html?id=39897 > On Wed Oct 08 15:40:14 2008, stricsc wrote:

> My environment is > - windows xp w/ SP2 > - SQL Server 2005 Developers edition > - ActiveState perl installed from > ActivePerl-5.10.0.1004-MSWin32-x86-287188.msi > - DBD-ODBC 1.17 > > The attached file contains a sql file used to create the table, a text

> file used to populate it with

> > bcp perltest..agents in agents.txt -c -U username -P password

> and a perl script ag.pl that fetches the id and name from the table. > > Running the script using DBD-ODBC 1.17, I get an error DBD::ODBC::st > fetchrow_array failed: [Microsoft][SQL Native Client]String data, > right truncation (SQL-01004) at C:\ag.pl line 16. > after a number of lines are fetched. > > If I add the line > $dbh->{LongReadLen} = 20000; > to ag.pl, I don't get the error. > > I also don't get the error with the 1.16 version of the odbc module or

> if I talk to the database with, for example, the DBD-ADO module.

Thanks for all the data to reproduce this issue. The problem is due to a change since 1.16 and a border-line case. Your column is a varchar(200). 1.16 only applied LongReadLen/LongTruncOk to LONG columns (e.g. long_varchar). 1.17 applies LongReadLen/LongTruncOk to SQL_VARCHAR columns too and the column in your data that fails is 81 characters in size (default LongReadLen is 80). Previously in 1.16 all of your varchar(200) would have been retrieved. I have reverted the accidental change to varchars and it should work as in 1.16 now. If you'd like a copy of DBD::ODBC 1.17_1 to try let me know. Martin -- Martin J. Evans Wetherby, UK

Fri Oct 10 09:28:16 2008 bohica [...] ntlworld.com - Correspondence added

On Fri Oct 10 08:30:59 2008, stricsc wrote: Show quoted text

> Martin: > > Thanks for looking into this. I'd be happy to test the 17_1 version if > I can relatively easily build it into my ActiveState environment.

ftp://ftp.easysoft.com/pub/DBD-ODBC-1.17_1.tar.gz I cannot say it will be "relatively easily" - depends. If you are lucky (and it risks no change to you existing modules): o download o untar o open command prompt in the MS visual C environment or run vcvars32.bat o perl Makefile.PL o nmake o nmake test The test now contains an rt_39897.t test for the problem you reported. If you are unlucky, you won't have a C compiler or it won't be the MS one that is compatible with activate state builds but you should know that now or pretty soon. If I don't hear back to the contrary I'll mark this bug fixed in 1.17_1 which I intend to release to cpan soon. Martin -- Martin J. Evans Wetherby, UK

Fri Oct 10 10:10:19 2008 Scott.Stricker [...] NGC.com - Correspondence added

Subject:	RE: [rt.cpan.org #39897] String data, right truncation (SQL-01004) in DBD-ODBC 1.17, not in .16
Date:	Fri, 10 Oct 2008 09:07:54 -0500
To:	<bug-DBD-ODBC [...] rt.cpan.org>
From:	"Stricker, Scott (C2MS)" <scott.stricker [...] ngc.com>

Martin: You do good work! Thanks for the fix and the notes on how to build for the ActiveState environment. Test cases ran fine except for the following... t/20SqlServer...........ok 20/53# DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (SQL-HY000) at t/20SqlServer.t line 101. # DSN=dbi:ODBC:DSN=CPM_DB;MARS_Connection=yes; # # NOTE: You failed this test because your SQL Server driver # is too old to handle the MARS_Connection attribute. This test cannot # easily skip this test for old drivers as there is no definite SQL Server # driver version it can check. # And testing with my software indicates no problems. For me, hard part was finding vcvars32.bat (was in C:\Program Files\Microsoft Visual Studio 9.0\VC\bin) and Setting up DBI_DSN (=dbi:ODBC:CPM_DB in my environment), DBI_USER and DBI_PASS. Never had occasion to use any of that before. Thanks again for the quick turnaround. Any idea when it will be available in the ActiveState repositories, and do you forsee it being versioned as 1.17.1? E. Scott Stricker Senior Software Engineer Northrop Grumman DMS MailStop 3S02 2340 Dulles Corner Blvd. Herndon, VA 20171 703-561-3671 Show quoted text

-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Friday, October 10, 2008 9:28 AM To: Stricker, Scott (C2MS) Subject: [rt.cpan.org #39897] String data, right truncation (SQL-01004) in DBD-ODBC 1.17, not in .16 <URL: http://rt.cpan.org/Ticket/Display.html?id=39897 > On Fri Oct 10 08:30:59 2008, stricsc wrote:

> Martin: > > Thanks for looking into this. I'd be happy to test the 17_1 version > if I can relatively easily build it into my ActiveState environment.

ftp://ftp.easysoft.com/pub/DBD-ODBC-1.17_1.tar.gz I cannot say it will be "relatively easily" - depends. If you are lucky (and it risks no change to you existing modules): o download o untar o open command prompt in the MS visual C environment or run vcvars32.bat o perl Makefile.PL o nmake o nmake test The test now contains an rt_39897.t test for the problem you reported. If you are unlucky, you won't have a C compiler or it won't be the MS one that is compatible with activate state builds but you should know that now or pretty soon. If I don't hear back to the contrary I'll mark this bug fixed in 1.17_1 which I intend to release to cpan soon. Martin -- Martin J. Evans Wetherby, UK

Fri Oct 10 10:31:43 2008 bohica [...] ntlworld.com - Correspondence added

On Fri Oct 10 10:10:19 2008, stricsc wrote: Show quoted text

> Martin: > > You do good work! Thanks for the fix and the notes on how to build for > the ActiveState environment. > > Test cases ran fine except for the following... > > t/20SqlServer...........ok 20/53# DBD::ODBC::st execute failed: > [Microsoft][ODBC SQL Server Driver]Connection is busy with results for > another hstmt (SQL-HY000) at t/20SqlServer.t line 101. > # DSN=dbi:ODBC:DSN=CPM_DB;MARS_Connection=yes; > # > # NOTE: You failed this test because your SQL Server driver > # is too old to handle the MARS_Connection attribute. This test cannot > # easily skip this test for old drivers as there is no definite SQL > Server > # driver version it can check.

I would presume because your SQL server ODBC driver does not support MARS_Connection attribute - although I'm a little surprised since I thought the code you sent me suggested you were using the native client. Anyway, it only means you cannot use multiple active statements by defining the MARS_Connection attribute. Show quoted text

> > And testing with my software indicates no problems. > > For me, hard part was finding vcvars32.bat (was in C:\Program > Files\Microsoft Visual Studio 9.0\VC\bin) and > Setting up DBI_DSN (=dbi:ODBC:CPM_DB in my environment), DBI_USER and > DBI_PASS. Never had occasion to use any of that before.

Quite surprised you got that far. I've had loads of problems making perl XS modules work with activestate perl when I've had to build them. They compile and dlls are produced but they won't run. In the end I gave up and built my own perl for windows. Show quoted text

> Thanks again for the quick turnaround.

no problem. 1.17 was a massive change from 1.16 so I expected some issues which is why I woke up when I saw this issue. Show quoted text

> Any idea when it will be available in the ActiveState repositories, and > do you forsee it being versioned as 1.17.1?

I'm afraid not as I don't control activestate and in any case I'm not sure they build development (ones ending in _N) releases. If I could persuade more people to test development releases in real conditions then I'd release a full (non-development) release more regularly. This time around I'm likely to go to 1.18 pretty quickly as this rt and the other one you posted in could be quite annoying for some people. Martin -- Martin J. Evans Wetherby, UK

Fri Oct 10 11:07:55 2008 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'

Fri Oct 10 11:07:56 2008 bohica [...] ntlworld.com - Broken in 1.17 deleted