Skip Menu |

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

Report information
The Basics
Id: 38977
Status: resolved
Priority: 0/
Queue: DBD-ODBC
People
Owner: Nobody in particular
Requestors: julian.lishev [...] imperia.net
Cc:
AdminCc:
Bug Information
Severity: Normal
Broken in: (no value)
Fixed in: 1.16_3

History Show all quoted text
  Thu Sep 04 07:04:01 2008 julian.lishev [...] imperia.net - Ticket created
Subject: DBD::ODBC fails to insert a large files (above 400kb) in VARBINARY column.
Date: Thu, 04 Sep 2008 14:02:38 +0300
To: bug-DBD-ODBC [...] rt.cpan.org
From: Julian Lishev <julian.lishev [...] imperia.net>
BUG: --- DBD::ODBC fails to insert a large files (above 400kb) in VARBINARY(MAX) column. Description: ----------- After installation of SQL Server 2005 (old SQL Server 2000 were removed from the same machine) currently installed version of DBD::ODBC (1.14) crashed Perl everytime I run my script. After update to 1.15 (latest verion in ActiveState repository) script stoped to crush with exception, but start waiting in never ending cycle without doing nothing. Using VS 2005 I've compiled ver 1.16 but not luck bug to disappear. The same story with HEAD snapshot of SVN. After short debuging I've found that SQLPutData throws the error (see below what [ODBC SQL Server Driver] and [SQL Server] say about this problem). First bug fix without understanding why exactly this worked (according MSDN documentation that shouldn't work) follows: (see dbdimp.c.patch) In other words, when you call SQLBindParameter() with parameter "&phs->cbValue" that exceed 400k (a well-known limit, above which ODBC based application should use SQLPutData) then an error is trhown. However this change does not prevent SQL Server to insert the whole file and it does NOT trunkate the data as expected(?!) However PHP odbc module works (I wrote a small php script) properly and insert large files in VARBINARY columns without any problems, so I've downloaded PHP source code (ver 4.4.9) to skim for any differences in implementation. Second patch is inspirated from PHP code of ODBC module, where they don't supply real length and use just 0: (see dbdimp.c.patch2) I think with this fix, ODBC works even faster (?!) OS and soft details: ------------------- Windows XP Professional (with latest MS updates); Perl v5.10.0 built for MSWin32-x86-multi-thread; DBD::ODBC: versions: 1.14, 1.15, 1.16 and current SVN snapshot. Driver: SQL Server Version: 2000.85.1132.00 File: SQLSRV32.DLL Date: 4/14/2008 Driver Manager: Version 3.525.1132.0 C:\Windows\system32\odbc32.dll SQL Server: tested with "2005 Standart" and "2005 Express" Edition. Here is are table structure: --------------------------- CREATE TABLE binary_meta ( doc_id INTEGER NOT NULL, meta_name VARCHAR (255), meta_value VARBINARY(MAX), meta_idx INTEGER, from_ver BIGINT, to_ver BIGINT ) Insert statement that fails: --------------------------- INSERT into binary_meta VALUES (?, ?, ?, ?, ?, ?) Selected parts from the log (error log level 5): ----------------------------------------------- bind 3 <== 'PK...' (size cur=1726168/len=1726172/max=0, svtype 6, otype 1, sqltype -4) bind 3 <== 'PK...' (len 1726168/1726170, null 0) bind 3: type=-2, LONG VARBINARY, cs=1726168, dd=0, bl=1726168. bind 3: type=-2, LONG VARBINARY, cs=1726168, dd=0, bl=1726168. SQLBindParameter: idx = 3: param_type=1, name=3, value_type=-2, SQL_Type = -4, column_size=1726168, d_digits=0, value_ptr=3a904c4, buffer_length=1726168, cbValue = -1726268 dbd_st_execute (NEED DATA)... !!dbd_error2(err_rc=-1, what=st_execute/SQLExecute, handles=(28512a0,2851348,2857a40) !SQLError(28512a0,2851348,2857a40) = (HY000, 0, [Microsoft][ODBC SQL Server Driver]Warning: Partial insert/update. The insert/update of a text or image column(s) did not succeed.) !SQLError(28512a0,2851348,2857a40) = (42000, 7125, [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image pointer value conflicts with the column name specified.) !! ERROR: 1 '[Microsoft][ODBC SQL Server Driver]Warning: Partial insert/update. The insert/update of a text or image column(s) did not succeed. (SQL-HY000) [state was HY000 now 42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image pointer value conflicts with the column name specified. (SQL-42000)' (err#2) P.S. Please confirm that you have received this mail Regards, Julian Lishev
Index: dbdimp.c =================================================================== --- dbdimp.c (revision 11739) +++ dbdimp.c (working copy) @@ -3222,6 +3222,8 @@ * SQLPutData. */ SQLLEN vl = value_len; + if (value_type == SQL_C_BINARY && vl > 400*1024) + vl = 400*1024; d_digits = 0; /* not relevant to lobs */ phs->cbValue = SQL_LEN_DATA_AT_EXEC(vl); value_ptr = (UCHAR*) phs;
Index: dbdimp.c =================================================================== --- dbdimp.c (revision 11739) +++ dbdimp.c (working copy) @@ -3223,7 +3223,7 @@ */ SQLLEN vl = value_len; d_digits = 0; /* not relevant to lobs */ - phs->cbValue = SQL_LEN_DATA_AT_EXEC(vl); + phs->cbValue = SQL_LEN_DATA_AT_EXEC(0); value_ptr = (UCHAR*) phs; } @@ -3257,7 +3257,7 @@ rc = SQLBindParameter(imp_sth->hstmt, phs->idx, param_type, value_type, phs->sql_type, column_size, d_digits, - value_ptr, buffer_length, &phs->cbValue); + value_ptr, 0, &phs->cbValue); if (!SQL_SUCCEEDED(rc)) { dbd_error(sth, rc, "_rebind_ph/SQLBindParameter");
  Thu Sep 04 08:05:04 2008 bohica [...] ntlworld.com - Correspondence added
Thank you for the bug report and suggested patches. I confirm I've received/seen this and will investigate further later today. Can I just confirm that you are recommending patch2 and not patch1? Once I have looked at it and decided on an action are you in a position to build a new DBD::ODBC that I send you to confirm the fix? Martin -- Martin J. Evans Wetherby, UK
  Thu Sep 04 08:05:06 2008 The RT System itself - Status changed from 'new' to 'open'
  Thu Sep 04 08:49:31 2008 julian.lishev [...] imperia.net - Correspondence added
Subject: Re: [rt.cpan.org #38977] DBD::ODBC fails to insert a large files (above 400kb) in VARBINARY column.
Date: Thu, 04 Sep 2008 15:48:51 +0300
To: bug-DBD-ODBC [...] rt.cpan.org
From: Julian Lishev <julian.lishev [...] imperia.net>
Hi, I think patch2 is better, just because I've seen something like that in PHP (php-4.4.9\ext\odbc\php_odbc.c: line 1052 and line 1040), but I don't fully understand what is the right fix, because MSDN ( http://msdn.microsoft.com/en-us/library/ms710963(VS.85).aspx ) does not suggest something like that, regarding these parameters. Also they (php) use SQLDescribeParam() to detect "precision" parameter ("column_size" parameter used in SQLBindParameter). Btw take a look at line 1077 (php_odbc.c), the way they use SQLPutData (send data by chunks not at once. I know that we don't need such thing in DBD::ODBC because we have the whole buffer at once). I can help to compile and test patched DBD::ODBC, but I hope you will be able to reproduce this on your own machine and probably find better solution. My patches seems to work, but I don't understand MS/ODBC logic behind this and why this really works. Julian Martin J Evans via RT wrote: Show quoted text
> <URL: http://rt.cpan.org/Ticket/Display.html?id=38977 > > > Thank you for the bug report and suggested patches. > > I confirm I've received/seen this and will investigate further later > today. Can I just confirm that you are recommending patch2 and not patch1? > > Once I have looked at it and decided on an action are you in a position > to build a new DBD::ODBC that I send you to confirm the fix? > > Martin >
  Thu Sep 04 11:58:11 2008 bohica [...] ntlworld.com - Correspondence added
I have reproduced this problem with SQL Server 2005 Express and a sql server driver 2000.85.1117.00 and the following: use DBI; use strict; use warnings; my $h = DBI->connect; eval {$h->do('drop table binary_meta');}; $h->do('create table binary_meta (doc_id INTEGER NOT NULL, meta_name VARCHAR (255), meta_value VARBINARY(MAX), meta_idx INTEGER, from_ver BIGINT, to_ver BIGINT)'); my $s = $h->prepare('INSERT into binary_meta VALUES (?, ?, ?, ?, ?, ?)'); my $x = 'x' x 5000000; $s->execute(1, 'fred', $x, 1, 1, 1); The errors are exactly as you described and the two possible patches you provide also make it work (for this sql server driver). However, Microsoft's latest native client is not happy with your solutions and neither was it happy with the original DBD::ODBC as it gives an invalid precision error. In addition your 2 possibilities break other ODBC drivers. What I think is happening here is that your OLD SQL Server driver does not know about VARBINARY(MAX) (evidenced by the fact it returns 2147483643 for the length which is clearly wrong) and DBD::ODBC was not handling VARBINARY(MAX) properly. I believe the proper solution (which works without breaking other ODBC drivers) is to take the parameter size returned by SQLDescribeParam and use it in the call to SQLBindParameter as the column_size but only for BINARY types as if you do this for timestamps (for instance) you hit another bug in sql server driver. I have made this change and I am in the process of completing testing. However, this may not make you too happy since it won't work with your ancient SQL Server ODBC driver and you'll need to upgrade it or use the newer native client driver. I believe it is fair to ask people to upgrade if they are using varbinary(max) as this column type is new. Let me know your thoughts. Martin -- Martin J. Evans Wetherby, UK
  Fri Sep 05 06:48:31 2008 julian.lishev [...] imperia.net - Correspondence added
Subject: Re: [rt.cpan.org #38977] DBD::ODBC fails to insert a large files (above 400kb) in VARBINARY column.
Date: Fri, 05 Sep 2008 13:46:38 +0300
To: bug-DBD-ODBC [...] rt.cpan.org
From: Julian Lishev <julian.lishev [...] imperia.net>
Yes, I'm using sqlsrv32.dll driver (as the only option before upgrade to SQL 2005), but I've tried sqlncli.dll too and I experience the same problem. First of all I've found that SQLDescribeParam returns: 0 for "precision" in case of VARBINARY(MAX) when sqlncli is used, and 2147483647 under sqlsrv32 driver. As you've already mentioned: if you use current model, implemented in DBD::ODBC + using of SQLDescribeParam to collect "precision" and "scale" parameters.. that's OK, BUT this will break old (sqlsrv32.dll which is up to date: from 4/14/2008) and I can see that already. The only solution that I can confirm to works fine under MS SQL + sqlsrv32 AND sqlncli is to use PHP model (second patch, that using 0-s) + using SQLDescribeParam to collect correct values depending of SQL driver. I'm not sure what will happen with other ODBC drivers, but at least I think DBD::ODBC can apply PHP patch for MS SQL drivers..., right? Or to completely forget support of sqlsrv32.dll.. Regards, Julian Martin J Evans via RT wrote: Show quoted text
> <URL: http://rt.cpan.org/Ticket/Display.html?id=38977 > > > I have reproduced this problem with SQL Server 2005 Express and a sql > server driver 2000.85.1117.00 and the following: > > use DBI; > use strict; > use warnings; > my $h = DBI->connect; > eval {$h->do('drop table binary_meta');}; > $h->do('create table binary_meta (doc_id INTEGER NOT NULL, meta_name > VARCHAR (255), meta_value VARBINARY(MAX), meta_idx INTEGER, from_ver > BIGINT, to_ver BIGINT)'); > my $s = $h->prepare('INSERT into binary_meta VALUES (?, ?, ?, ?, ?, ?)'); > my $x = 'x' x 5000000; > $s->execute(1, 'fred', $x, 1, 1, 1); > > The errors are exactly as you described and the two possible patches you > provide also make it work (for this sql server driver). However, > Microsoft's latest native client is not happy with your solutions and > neither was it happy with the original DBD::ODBC as it gives an invalid > precision error. > > In addition your 2 possibilities break other ODBC drivers. > > What I think is happening here is that your OLD SQL Server driver does > not know about VARBINARY(MAX) (evidenced by the fact it returns > 2147483643 for the length which is clearly wrong) and DBD::ODBC was not > handling VARBINARY(MAX) properly. I believe the proper solution (which > works without breaking other ODBC drivers) is to take the parameter size > returned by SQLDescribeParam and use it in the call to SQLBindParameter > as the column_size but only for BINARY types as if you do this for > timestamps (for instance) you hit another bug in sql server driver. > > I have made this change and I am in the process of completing testing. > However, this may not make you too happy since it won't work with your > ancient SQL Server ODBC driver and you'll need to upgrade it or use the > newer native client driver. I believe it is fair to ask people to > upgrade if they are using varbinary(max) as this column type is new. > > Let me know your thoughts. > > Martin >
  Fri Sep 05 07:02:07 2008 bohica [...] ntlworld.com - Correspondence added
On Fri Sep 05 06:48:31 2008, julian.lishev@imperia.net wrote: Show quoted text
> Yes, I'm using sqlsrv32.dll driver (as the only option before upgrade > to SQL 2005), but I've tried sqlncli.dll too and I experience the > same problem.
I have reporduced your problem but I get a different error depending on whether I am using the SQL Server ODBC driver or the native client. In the former I get the same error as you on the execute and in the latter I get an invalid precision value error on the SQLBindParameter. Show quoted text
> First of all I've found that SQLDescribeParam returns: 0 for > "precision" in case of VARBINARY(MAX) when sqlncli is used, > and 2147483647 under sqlsrv32 driver.
Agreed. Show quoted text
> As you've already mentioned: if you use current model, implemented in > DBD::ODBC + using of SQLDescribeParam to collect "precision" and > "scale" parameters.. that's OK, BUT this will break old > (sqlsrv32.dll which is up to date: from 4/14/2008) and I can > see that already.
Copying the parameter_size from SQLDescribeParam to SQLBindParameter column size is what DBD::ODBC should have done in the first place and it fixes your problem when using the up to date native client that knows about MAX types. Yes, this does not work for older sql server odbc drivers because they don't know about MAX types. The date you quote for your odbc driver is irrelevant - it is the version that matters and your driver version is 2000.xx.yy.zz (old!). Show quoted text
> > The only solution that I can confirm to works fine under MS SQL + > sqlsrv32 AND sqlncli is to use PHP model (second patch, that using > 0-s) + using SQLDescribeParam to collect correct values depending of > SQL driver.
Yes, but that breaks other odbc drivers. Show quoted text
> I'm not sure what will happen with other ODBC drivers, but at least I > think DBD::ODBC can apply PHP patch for MS SQL drivers..., right? Or > to completely forget support of sqlsrv32.dll..
I'm not completely dropping support for sqlsrv32.dll. All I'm saying is that if you use the new MAX types you need a newer sqlsrv32.dll. The sqlsrv32.dll version 2005.xx.yy.zz know about MAX types and work fine with the change as I've made it. Do you think upgrading to a newer driver from a one over 8 years old is such a big issue? - I don't. Martin -- Martin J. Evans Wetherby, UK
  Fri Sep 05 08:22:15 2008 julian.lishev [...] imperia.net - Correspondence added
Subject: Re: [rt.cpan.org #38977] DBD::ODBC fails to insert a large files (above 400kb) in VARBINARY column.
Date: Fri, 05 Sep 2008 15:21:40 +0300
To: bug-DBD-ODBC [...] rt.cpan.org
From: Julian Lishev <julian.lishev [...] imperia.net>
Show quoted text
> I have reporduced your problem but I get a different error depending on > whether I am using the SQL Server ODBC driver or the native client. In > the former I get the same error as you on the execute and in the latter > I get an invalid precision value error on the SQLBindParameter. >
Exactly. Show quoted text
> I'm not completely dropping support for sqlsrv32.dll. All I'm saying is > that if you use the new MAX types you need a newer sqlsrv32.dll. The > sqlsrv32.dll version 2005.xx.yy.zz know about MAX types and work fine > with the change as I've made it. Do you think upgrading to a newer > driver from a one over 8 years old is such a big issue? - I don't.
The strange thing here is that I have installed latest version of SQL Server with all service packs and patches, but I don't see any upgrade of sqlsrv32.dll i.e. I don't know how, neither I don't see from where to get newer version of that file... are you sure there is a 2005.xx.yy.zz version of this file? Julian
  Fri Sep 05 12:27:27 2008 bohica [...] ntlworld.com - Correspondence added
On Fri Sep 05 08:22:15 2008, julian.lishev@imperia.net wrote: Show quoted text
> The strange thing here is that I have installed latest version of SQL > Server with all service packs and patches, but I don't see any upgrade > of sqlsrv32.dll i.e. I don't know how, neither I don't see from where > to get newer version of that file... are you sure there is a > 2005.xx.yy.zz version of this file? > > Julian
I thought I'd seen it but in fact it was 2000.86.xx.xx (the version you get with vista) and I have yet to check that with the change. If it still does not work I'll have to consider looking at the driver version and then taking one of your options but that would be a) tricky b) annoying. Martin -- Martin J. Evans Wetherby, UK
  Mon Sep 08 06:20:14 2008 bohica [...] ntlworld.com - Correspondence added
Ive now tried the most up to data Microsoft SQL Server driver I can find (which is on vista and is version 03.86.xxxx). It still fails. I don't want to force people to the sql server native client so I've put a specific workaround in based on your second patch but only when o the column is VARBINARY and VARCHAR o the driver reports itself as SQLSRV32.DLL o the driver reports the column size is 2147483647 o the data to be sent is greater than 400K I have also taking the chance to rewrite around 30% of the parameter binding code to make it more efficient and fix a few bugs. You can find a test release here: ftp://ftp.easysoft.com/pub/DBD-ODBC-1.16_3.tar.gz Please try this and let me know the outcome so I can close this bug report. Thanks for your help finding this and identifying possible solutions. Martin -- Martin J. Evans Wetherby, UK
  Mon Sep 08 08:11:38 2008 julian.lishev [...] imperia.net - Correspondence added
Subject: Re: [rt.cpan.org #38977] DBD::ODBC fails to insert a large files (above 400kb) in VARBINARY column.
Date: Mon, 08 Sep 2008 15:11:10 +0300
To: bug-DBD-ODBC [...] rt.cpan.org
From: Julian Lishev <julian.lishev [...] imperia.net>
Show quoted text
> I have also taking the chance to rewrite around 30% of the parameter > binding code to make it more efficient and fix a few bugs. You can find > a test release here: > > ftp://ftp.easysoft.com/pub/DBD-ODBC-1.16_3.tar.gz > > Please try this and let me know the outcome so I can close this bug report.
Archive is broken. I've got the following error: tar: Unrecognized archive format: Inappropriate file type or format
  Mon Sep 08 08:19:18 2008 bohica [...] ntlworld.com - Correspondence added
Just spotted that myself - try now. Sorry. Martin -- Martin J. Evans Wetherby, UK
  Mon Sep 08 11:14:21 2008 julian.lishev [...] imperia.net - Correspondence added
Subject: Re: [rt.cpan.org #38977] DBD::ODBC fails to insert a large files (above 400kb) in VARBINARY column.
Date: Mon, 08 Sep 2008 18:06:55 +0300
To: bug-DBD-ODBC [...] rt.cpan.org
From: Julian Lishev <julian.lishev [...] imperia.net>
I have tested the patched code and by my own opinion seems to work fine both under "SQL Server" and "SQL Native Client" drivers. I think you can even add test case to check for this problem :) Btw when are you planning to release 1.17 in CPAN? Hopefully ActiveState put this package in their repositories soon too :) Regards, Julian
  Mon Sep 08 11:37:39 2008 bohica [...] ntlworld.com - Correspondence added
On Mon Sep 08 11:14:21 2008, julian.lishev@imperia.net wrote: Show quoted text
> I have tested the patched code and by my own opinion seems to work fine > both under "SQL Server" and "SQL Native Client" drivers.
Excellent. There is nothing specific there for native client expect a related bug fix to pass parameter size from SQLDescribeParam as column size in SQLBindParameter. There is a workaround for sql server driver as defined in previous comment. Show quoted text
> I think you can even add test case to check for this problem :)
I thought I had added a test case in t/rt_38977.t. If you ran nmake test it should have run it. Show quoted text
> Btw when are you planning to release 1.17 in CPAN? Hopefully ActiveState > put this package in their repositories soon too :)
If no one finds anything major wrong with it then next week. If something major is found it depends how long it takes to fix and what the extent of the change is. Thanks for your help. I am going to mark this fixed in 1.16_3 now. Martin -- Martin J. Evans Wetherby, UK
  Tue Sep 09 03:52:44 2008 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'
  Tue Sep 09 03:52:45 2008 bohica [...] ntlworld.com - Severity Normal added
  Tue Sep 09 03:52:45 2008 bohica [...] ntlworld.com - Fixed in 1.16_3 added