Bug #61630 for DBD-ODBC: Invalid precision value (SQL-HY104) error on inserts

Sat Sep 25 13:32:07 2010 mertap [...] upcmail.cz - Ticket created

Subject:	Invalid precision value (SQL-HY104) error on inserts
Date:	Sat, 25 Sep 2010 19:31:49 +0200
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Petr Merta <mertap [...] upcmail.cz>

Hi, I'd like to report what I believe is a bug in DBD::ODBC code, causing HY104 errors on varchar INSERTs when running against Microsoft SQL server. Working demo code is given as attachment, changing the connection string should be enough for making it run. The bug appeared when running on Windows (2k3, vista, win7), with various perl distributions and versions (Strawberry Perl both 32/64bit, ActivePerl, Cygwin). DBD::ODBC version primarily tested was 1.25, but the bug showed up also in 1.24 and 1.21. As for database environment, the bug seems rather independent of version of database server and client connectivity drivers. The sample code was tested against MS SQL Server 2K, 2k5, 2k8 and 2k8r2, with client drivers ranging from default win2k SQL server drivers to SQL Server Native Client 10. Interesting enough, the code works as expected when run on rather antique cygwin installation (1.5.25, perl 5.10.0, dbi 1.607, dbd::odbc 1.18). (complete detailed info on tested versions is also given in comments in the end of the sample code) There are two INSERTs in demo code. The first one is plain INSERT into one of the tables; it succeeds. The second INSERT is combined with SELECT from the other table, and this is the one which fails with HY104. Should you need any further information, please let me know. Regards, -- Petr Merta, mertap@upcmail.cz

Message body is not shown because sender requested not to inline it.

Sat Sep 25 17:11:08 2010 bohica [...] ntlworld.com - Correspondence added

On Sat Sep 25 13:32:07 2010, mertap@upcmail.cz wrote: Show quoted text

> Hi, > > > I'd like to report what I believe is a bug in DBD::ODBC code, causing > HY104 > errors on varchar INSERTs when running against Microsoft SQL server. > > Working demo code is given as attachment, changing the connection > string > should be enough for making it run. > > The bug appeared when running on Windows (2k3, vista, win7), with > various perl > distributions and versions (Strawberry Perl both 32/64bit, ActivePerl, > Cygwin). DBD::ODBC version primarily tested was 1.25, but the bug > showed up > also in 1.24 and 1.21. > > As for database environment, the bug seems rather independent of > version of > database server and client connectivity drivers. The sample code was > tested > against MS SQL Server 2K, 2k5, 2k8 and 2k8r2, with client drivers > ranging > from default win2k SQL server drivers to SQL Server Native Client 10. > > Interesting enough, the code works as expected when run on rather > antique > cygwin installation (1.5.25, perl 5.10.0, dbi 1.607, dbd::odbc 1.18). > > (complete detailed info on tested versions is also given in comments > in the > end of the sample code) > > There are two INSERTs in demo code. The first one is plain INSERT into > one of > the tables; it succeeds. The second INSERT is combined with SELECT > from the > other table, and this is the one which fails with HY104. > > Should you need any further information, please let me know. > > > Regards,

Hi, I've not tried your code yet but I will get around to it. I suspect you'll find that the MS SQL Server ODBC driver is attempting to rearrange your SQL in order to find out what the parameters are and failing to come up with the right SQL. You can probably see this if you enough about SQL Server and can monitor the SQL being executed or you can set DBI_TRACE=15=x.log in your environment and rerun your script then examine the x.log file to see if SQLDescribeParam is failing. Try splitting the failing do into a prepare, bind_param and execute and setting the TYPE of each bound parameter to SQL_VARCHAR. See the DBD::ODBC FAQ for examples (http://search.cpan.org/~mjevans/DBD-ODBC-1.25/FAQ#Why_do_I_get_errors_with_bound_parameters_and_MS_SQL_Server?). I will look at this in more detail but in the mean time you can help yourself and me by trying my suggestion and providing a log. Thanks. Martin -- Martin J. Evans Wetherby, UK

Sat Sep 25 17:11:08 2010 The RT System itself - Status changed from 'new' to 'open'

Sun Sep 26 02:59:03 2010 mertap [...] upcmail.cz - Correspondence added

Subject:	Re: [rt.cpan.org #61630] Invalid precision value (SQL-HY104) error on inserts
Date:	Sun, 26 Sep 2010 08:58:49 +0200
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Petr Merta <mertap [...] upcmail.cz>

On Saturday 25 September 2010 23:11:08 Martin J Evans via RT wrote: Show quoted text

> Hi, > > I've not tried your code yet but I will get around to it. > > I suspect you'll find that the MS SQL Server ODBC driver is attempting > to rearrange your SQL in order to find out what the parameters are and > failing to come up with the right SQL. You can probably see this if you > enough about SQL Server and can monitor the SQL being executed or you > can set DBI_TRACE=15=x.log in your environment and rerun your script > then examine the x.log file to see if SQLDescribeParam is failing. Try > splitting the failing do into a prepare, bind_param and execute and > setting the TYPE of each bound parameter to SQL_VARCHAR. See the > DBD::ODBC FAQ for examples > (http://search.cpan.org/~mjevans/DBD-ODBC-1.25/FAQ#Why_do_I_get_errors_with >_bound_parameters_and_MS_SQL_Server?). > > I will look at this in more detail but in the mean time you can help > yourself and me by trying my suggestion and providing a log.

Martin, first I have to apologize I was not more careful when looking for bug cause/solution; I've relied on google little too much for this one :-) Your suggestion with splitting the code to prep-bind-exec works like a charm. As for me, I'm quite happy with this solution, I suppose it won't be a problem to handle failing statements this way. So please consider the urgency of my report rather low. There are logs attached gathered with single do() call and another one gathered with splitted command. It really looks like SQLDescribeParam is not able to handle long varchars. Thanks for your time, I really appreciate your help. -- Petr Merta, mertap@upcmail.cz

Download log.zip
application/x-zip 10.6k

Message body not shown because it is not plain text.

Mon Sep 27 09:09:05 2010 bohica [...] ntlworld.com - Correspondence added

On Sun Sep 26 02:59:03 2010, mertap@upcmail.cz wrote: Show quoted text

> On Saturday 25 September 2010 23:11:08 Martin J Evans via RT wrote:

> > Hi, > > > > I've not tried your code yet but I will get around to it. > > > > I suspect you'll find that the MS SQL Server ODBC driver is

> attempting

> > to rearrange your SQL in order to find out what the parameters are

> and

> > failing to come up with the right SQL. You can probably see this if

> you

> > enough about SQL Server and can monitor the SQL being executed or

> you

> > can set DBI_TRACE=15=x.log in your environment and rerun your script > > then examine the x.log file to see if SQLDescribeParam is failing.

> Try

> > splitting the failing do into a prepare, bind_param and execute and > > setting the TYPE of each bound parameter to SQL_VARCHAR. See the > > DBD::ODBC FAQ for examples > > (http://search.cpan.org/~mjevans/DBD-ODBC-

> 1.25/FAQ#Why_do_I_get_errors_with

> >_bound_parameters_and_MS_SQL_Server?). > > > > I will look at this in more detail but in the mean time you can help > > yourself and me by trying my suggestion and providing a log.

> > Martin, > > first I have to apologize I was not more careful when looking for bug > cause/solution; I've relied on google little too much for this one :-)

Not a problem. Show quoted text

> Your suggestion with splitting the code to prep-bind-exec works like a > charm.

:-) Show quoted text

> As for me, I'm quite happy with this solution, I suppose it won't be a > problem to handle failing statements this way. So please consider the > urgency > of my report rather low.

I would but I don't like rts hanging around. Show quoted text

> There are logs attached gathered with single do() call and another one > gathered with splitted command. It really looks like SQLDescribeParam > is not > able to handle long varchars.

Your log shows: SQLDescribeParam failed reverting to default SQL bind type -9 07009 [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index 42000 [Microsoft][ODBC SQL Server Driver]Syntax error or access violation It is nothing to do with long varchars it is your SQL which is breaking the ODBC driver. SQLPrepare INSERT INTO EventLog (ComputerIDC, EventMessage) SELECT cs.ComputerIDC, ? FROM ComputerSlice AS cs WHERE cs.ComputerSliceID = ? In order for the SQL Server ODBC driver to work out what the 2 parameters are it attempts to rearrange your SQL into a select for the parameter columns from a table e.g., select a_col from mytable where b_col = ? SQL Server rearranges it into "select b_col from mytable" and hence finds out what column b_col is. In your SQL one of the parameters is a string not related to a column so SQLDescribeParam fails and DBD::ODBC has no choice to assume a default (it chose SQL_WCHAR). Then you tried to pass input parameter data of 4002 chrs which is too big for an SQL_WCHAR and hence the HY104 invalid precision error. So, although I see why this failed I am slightly surprised DBD::ODBC did not default the parameter type to SQL_WLONGVARCHAR when it saw the parameter was > 4000 bytes. I will look into this as it may mean you can go back to how you had it although the SQLDescribeParam will have to be called and fail - so slightly more work than specifying a bind type. Show quoted text

> Thanks for your time, I really appreciate your help.

No problem. I'll try and get back to you soon. Martin -- Martin J. Evans Wetherby, UK

Mon Sep 27 10:14:24 2010 bohica [...] ntlworld.com - Correspondence added

On Mon Sep 27 09:09:05 2010, MJEVANS wrote: Show quoted text

> So, although I see why this failed I am slightly surprised DBD::ODBC did > not default the parameter type to SQL_WLONGVARCHAR when it saw the > parameter was > 4000 bytes. I will look into this as it may mean you can > go back to how you had it although the SQLDescribeParam will have to be > called and fail - so slightly more work than specifying a bind type.

ok, I see why this happens now. When SQLDescribeParam fails DBD::ODBC picks either SQL_WCHAR or SQL_WLONGVARCHAR to fall back on. If the parameter is > 4000 bytes it uses the latter so in your case it used SQL_WCHAR. However, in your case the parameter is 2001 bytes but will be converted to wide characters hence doubling the length to 4002. A varchar should be able to hold 4000 characters not bytes so yet another issue in SQL Server. You kind of had it nailed yourself with this comment in your code: # THIS IS IT; "X" x 2000 works ok, x 2001 will fail; however, value goes to column VARCHAR(8000) The SQL Server ODBC driver appears broken as 2001 characters should fit into a VARCHAR(8000) especially when they are all 'X'. I could sort of understood it if it was a varchar(4000). I am however, going to change DBD::ODBC to switch to longvarchar at 2000 bytes instead of 4000. I hope you'll be happy with me writing this issue off now. Martin -- Martin J. Evans Wetherby, UK

Tue Sep 28 04:23:05 2010 mertap [...] upcmail.cz - Correspondence added

Subject:	Re: [rt.cpan.org #61630] Invalid precision value (SQL-HY104) error on inserts
Date:	Tue, 28 Sep 2010 10:22:50 +0200
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Petr Merta <mertap [...] upcmail.cz>

On Monday 27 September 2010 16:14:26 Martin J Evans via RT wrote: Show quoted text

> ok, I see why this happens now. When SQLDescribeParam fails DBD::ODBC > picks either SQL_WCHAR or SQL_WLONGVARCHAR to fall back on. If the > parameter is > 4000 bytes it uses the latter so in your case it used > SQL_WCHAR. However, in your case the parameter is 2001 bytes but will be > converted to wide characters hence doubling the length to 4002. A > varchar should be able to hold 4000 characters not bytes so yet another > issue in SQL Server. > > You kind of had it nailed yourself with this comment in your code: > > # THIS IS IT; "X" x 2000 works ok, x 2001 will fail; however, value goes > to column VARCHAR(8000) > > The SQL Server ODBC driver appears broken as 2001 characters should fit > into a VARCHAR(8000) especially when they are all 'X'. I could sort of > understood it if it was a varchar(4000).

well, you're right. I never consider possibility that this "2000" is almost completely unrelated to column size; now when I've checked it I could see it fails on the same boundary for varchar(7000) too. I simply thought there has to be some magic in the process causing strings grow four times bigger. Hm, I probably should have warned you: I'm merely a user, most of that things going under the hood is greek to me :-) Show quoted text

> I am however, going to change DBD::ODBC to switch to longvarchar at 2000 > bytes instead of 4000.

ok Show quoted text

> > I hope you'll be happy with me writing this issue off now.

of course. You did a nice piece of work here. Thanks again, and good luck with future development. Petr -- Petr Merta, mertap@upcmail.cz

Fri Nov 26 09:28:09 2010 bohica [...] ntlworld.com - Fixed in 1.26_1 added

Fri Nov 26 09:28:23 2010 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'