Bug #49760 for DBD-ODBC: Invalid precision value (SQL-HY104) with DBD-ODBC 1.23

Wed Sep 16 12:31:34 2009 Bruce.Tanner [...] Cerritos.edu - Ticket created

Subject:	Invalid precision value (SQL-HY104) with DBD-ODBC 1.23
Date:	Wed, 16 Sep 2009 09:31:21 -0700
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	"Tanner, Bruce" <Bruce.Tanner [...] Cerritos.edu>

Download cerritos.zip
application/x-zip-compressed 3.3k

Message body not shown because it is not plain text.

Hello Martin, The enclosed program generates: Execute DBI = 1.609, DBD::ODBC = 1.23 DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid precision value (SQL-HY104) at Z:\Perl\odbc\test_odbc.pl line 31. This with ActiveState Perl 5.10.1.1006 on Windows 7 Enterprise (Build 7600) 32bit, SQL Server 2005 (9.0.4035). I've also enclosed the SQL to build the tables involved. The program runs correctly on version 1.22. -Bruce -- Bruce Tanner (562) 653-7861 Bruce.Tanner@Cerritos.edu Cerritos College Norwalk, CA

Wed Sep 16 12:49:12 2009 bohica [...] ntlworld.com - Correspondence added

On Wed Sep 16 12:31:34 2009, Bruce.Tanner@Cerritos.edu wrote: Show quoted text

> Hello Martin, > > The enclosed program generates: > > Execute DBI = 1.609, DBD::ODBC = 1.23 > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server > Driver]Invalid precision value (SQL-HY104) at > Z:\Perl\odbc\test_odbc.pl line 31. > > This with ActiveState Perl 5.10.1.1006 on Windows 7 Enterprise (Build > 7600) 32bit, SQL Server 2005 (9.0.4035). > > I've also enclosed the SQL to build the tables involved. The program > runs correctly on version 1.22. > > > -Bruce > -- > Bruce Tanner (562) 653-7861 Bruce.Tanner@Cerritos.edu > Cerritos College Norwalk, CA > > >

Bruce, Thanks for the report. I am very sorry to have caused this but truely, it is becoming a real PITA working around bugs in the dozens of SQL Server drivers and an even bigger PITA trying to make sure a change has not broken one of the 100s of different ODBC Drivers. I will try and find some time to work through your example. BTW, what I really need is some way of encouraging good people like yourself to download and test development releases of DBD::ODBC so I can get more coverage of ODBC drivers and confidence before releases. If you have any ideas on this I'd welcome them. Martin -- Martin J. Evans Wetherby, UK

Wed Sep 16 12:49:12 2009 The RT System itself - Status changed from 'new' to 'open'

Wed Sep 30 15:04:20 2009 Martin J. Evans - Correspondence added

I have been really busy and not got around to this - Sorry. In order to help me focus I've publicly :-) committed to get this sorted out in a week - see http://www.martin-evans.me.uk/node/39.

Tue Oct 06 04:39:58 2009 bohica [...] ntlworld.com - Correspondence added

On Wed Sep 16 12:31:34 2009, Bruce.Tanner@Cerritos.edu wrote: Show quoted text

> Hello Martin, > > The enclosed program generates: > > Execute DBI = 1.609, DBD::ODBC = 1.23 > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server > Driver]Invalid precision value (SQL-HY104) at > Z:\Perl\odbc\test_odbc.pl line 31. > > This with ActiveState Perl 5.10.1.1006 on Windows 7 Enterprise (Build > 7600) 32bit, SQL Server 2005 (9.0.4035). >

I've now reproduced this but only using MS SQL Server Native Client Driver version 2005.90.1399.00. With that version I get: DBD::ODBC::st execute failed: [Microsoft][SQL Native Client]Invalid precision value (SQL-HY104) at mine.pl line 37. but with the SQL Server driver 2000.85.1132.00 (somewhat older) I get: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]The data types varchar and ntext are incompatible in the equal to operator. (SQL-42000) [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000) at mine.pl line 31. Can you tell me whether you are using the SQL Server Driver or the native client driver and what version you have. I assumed the 9.0.4035 above was your SQL Server database engine version not the driver version. Thanks Martin -- Martin J. Evans Wetherby, UK

Tue Oct 06 04:56:27 2009 bohica [...] ntlworld.com - Correspondence added

Neither can I make this work with 0.22 (either with the sql server driver or the native client driver) where I get: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]The data types varchar and ntext are incompatible in the equal to operator. (SQL-42000) [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000) at c:\rt49760\mine.pl line 43. Martin -- Martin J. Evans Wetherby, UK

Tue Oct 06 05:18:40 2009 bohica [...] ntlworld.com - Correspondence added

Any chance you could add: use DBD::ODBC; DBI->trace(DBD::ODBC- Show quoted text

>parse_trace_flags('odbcconnection|odbcunicode|15'));

to the start of the script and send me the output? Even when I ignore DBD::ODBC and simply prepare your SQL and then call SQLDescribeParam on the first parameter I get either: SQLDescribeParam: In: StatementHandle = 0x008918E0, ParameterNumber = 1, DataTypePtr = 0x0008CF90, ParameterSizePtr = 0x000A4530, DecimalDigits = 0x00090D90, NullablePtr = 0x000AD2E8 Return: SQL_ERROR=-1 Out: *DataTypePtr = <unmodified>, *ParameterSizePtr = <unmodified>, *DecimalDigits = <unmodified>, *NullablePtr = <unmodified> Errors All: szSqlState = "07009", *pfNativeError = 0, *pcbErrorMsg = 59 szErrorMsg = "[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index" szSqlState = "42000", *pfNativeError = 0, *pcbErrorMsg = 67 szErrorMsg = "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" or SQLDescribeParam: In: StatementHandle = 0x008918E0, ParameterNumber = 1, DataTypePtr = 0x00085890, ParameterSizePtr = 0x0008CF90, DecimalDigits = 0x000A4530, NullablePtr = 0x000AD2E8 Return: SQL_ERROR=-1 Out: *DataTypePtr = <unmodified>, *ParameterSizePtr = <unmodified>, *DecimalDigits = <unmodified>, *NullablePtr = <unmodified> Errors All: szSqlState = "07009", *pfNativeError = 0, *pcbErrorMsg = 54 szErrorMsg = "[Microsoft][SQL Native Client]Invalid Descriptor Index" szSqlState = "42000", *pfNativeError = 0, *pcbErrorMsg = 92 szErrorMsg = "[Microsoft][SQL Native Client]Syntax error, permission violation, or other nonspecific error" depending on which driver I use so it would appear I cannot call SQLDescribeParam on this SQL! Martin -- Martin J. Evans Wetherby, UK

Tue Oct 06 11:08:35 2009 Bruce.Tanner [...] Cerritos.edu - Correspondence added

Subject:	RE: [rt.cpan.org #49760] Invalid precision value (SQL-HY104) with DBD-ODBC 1.23
Date:	Tue, 6 Oct 2009 08:07:34 -0700
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	"Tanner, Bruce" <Bruce.Tanner [...] Cerritos.edu>

Hello Martin, I've enclosed the output with the trace flags turned on. Show quoted text

>Can you tell me whether you are using the SQL Server Driver or the >native client driver and what version you have. I assumed the 9.0.4035 >above was your SQL Server database engine version not the driver version.

I would if I knew how to find out. :-) -Bruce

Download test_odbc.log
application/octet-stream 15.1k

Message body not shown because it is not plain text.

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

Tue Oct 06 11:20:04 2009 bohica [...] ntlworld.com - Correspondence added

Subject:	Re: [rt.cpan.org #49760] Invalid precision value (SQL-HY104) with DBD-ODBC 1.23
Date:	Tue, 06 Oct 2009 16:19:26 +0100
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Martin Evans <martin.evans [...] easysoft.com>

Tanner, Bruce via RT wrote: Show quoted text

> Queue: DBD-ODBC > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=49760 > > > Hello Martin, > > I've enclosed the output with the trace flags turned on.

thanks Show quoted text

>> Can you tell me whether you are using the SQL Server Driver or the >> native client driver and what version you have. I assumed the 9.0.4035 >> above was your SQL Server database engine version not the driver version.

> > I would if I knew how to find out. :-)

Well you would do what is shown below but the log you sent tells me that. Go to control panel, administrative tools, then click on odbc administrator, then go to drivers tab and pull out the name and version of your sql server driver(s). DRIVER_ODBC_VER = 03.52 DRIVER_NAME = SQLSRV32.DLL DRIVER_VERSION = 06.01.7600 Thanks for the log. Your log shows I have duplicated the problem. Now for one (hopefully) more favour - can I have the same log for DBD::ODBC 0.22 (the one which works)? Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com

Tue Oct 06 11:42:51 2009 Bruce.Tanner [...] Cerritos.edu - Correspondence added

Subject:	RE: [rt.cpan.org #49760] Invalid precision value (SQL-HY104) with DBD-ODBC 1.23
Date:	Tue, 6 Oct 2009 08:41:57 -0700
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	"Tanner, Bruce" <Bruce.Tanner [...] Cerritos.edu>

Here's the log from DBD::ODBC 1.22. -Bruce

Download test_odbc_good.log
application/octet-stream 14.8k

Message body not shown because it is not plain text.

Tue Oct 06 11:53:12 2009 bohica [...] ntlworld.com - Correspondence added

Subject:	Re: [rt.cpan.org #49760] Invalid precision value (SQL-HY104) with DBD-ODBC 1.23
Date:	Tue, 06 Oct 2009 16:50:43 +0100
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Martin Evans <martin.evans [...] easysoft.com>

Tanner, Bruce via RT wrote: Show quoted text

> Queue: DBD-ODBC > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=49760 > > > Here's the log from DBD::ODBC 1.22. > > -Bruce > >

Thanks. This a tricky one as SQLDescribeParam is actually failing in the SQL Server driver. The SQL Server driver appears to support SQLDescribeParam by attempting to reassembly your SQL into a select statement then look at the columns. Sometimes the driver fails to parse your SQL and come up with a valid SQL select statement - and that is what is happening here. 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 get_param_type: modified value type to SQL_C_WCHAR However, a change between 0.22 and 0.23 now means that although both default the now "unknown" parameter to SQL_C_WCHAR, in the former case it is bound with a length of 8 bytes (unicode) and in the latter a length of 0. The 0 comes from the fact that DBD::ODBC just does not really know much about the parameter because SQLDescribeParam failed. Will look into it further - not a lost cause yet. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com

Tue Oct 06 14:04:45 2009 bohica [...] ntlworld.com - Correspondence added

Hi again Bruce, It appears I have found 2 workarounds but I'm not sure you'll like one of them. The problem stems from the facts that there are a) various bugs in SQL Server ODBC drivers which DBD::ODBC attempts to workaround and b) the introduction of varXXX(max) types threw a bit of a spanner in the works since the column_size on a SQLBindParameter call needs to be set at 0. As a result, if you have the native client driver (which you do not) or you are connected to "Microsoft SQL Server" (which you are) and the parameter type is SQL_WVARCHAR and the parameter type has not been overridden in the bind_param method, DBD::ODBC sets the column_size to 0. The problem here is that you have come up with SQL which the driver cannot use to provide the SQLDescribeParam results. When you call SQLDescribeParam to find out about a parameter, the sql server driver attempts to parse the SQL and form new SQL which does a select on the columns where you have parameter markers. It then does the equivalent of SQLDescribeCol on those columns and uses the returned values to describe the parameters. Your SQL cannot be successfully rearranged into a select statement so SQLDescribeParam fails. DBD::ODBC spots SQLDescribeParam failed and falls back on the default parameter bind type of SQL_VARCHAR (non-unicode build) or SQL_WVARCHAR (unicode build) but the other information it would have used from SQLDescribeParam is not available and it falls into the workaround to handle VARxxx(max) columns. My choices to resolve this are difficult. (a) I could say if the driver has SQLDescribeParam and it fails then DBD::ODBC will fail and you will have to override the bind type - I quite like this BUT it could break existing code that uses DBD::ODBC (like yours). (b) I could try and find a more accurate way of detecting VARxxx(MAX) types but I've been there before and failed. (c) I could extend the check for the VARxxx(MAX) types to include a test for whether SQLDescribeParam failed - in addition to the existing one which checks to see if you've overridden the bind type. As it happens (c) will work for you and causes the least disturbance right now. However, if I make this change anyone who has SQL with bound parameters which also cannot be rearranged into a select for SQLDescribeParam and the bound parameters are on VARxxx(MAX) columns will now fail. Dilemma :-( An alternative workaround that does not involve any DBD::ODBC code changes is to specify the bind type: use DBI qw(:sql_types); . . $request->bind_param(1, 1099, { TYPE => SQL_VARCHAR}); $request->execute; This is the one I guess you are not going to like. The (c) code change is in dbdimp.c and is: if (phs->param_size == 0) { if ((imp_dbh->driver_type == DT_SQL_SERVER_NATIVE_CLIENT) || ((strcmp(imp_dbh->odbc_dbms_name, "Microsoft SQL Server") == 0) && (phs->sql_type == SQL_WVARCHAR) && (phs->requested_type == 0) /*&& (phs->describe_param_status == SQL_SUCCESS)*/)) { column_size = 0; } } by uncommenting the commented out code above - removing the "/*" and "*/". What I do now partly depends on you. Did you see DBD::ODBC 1.23 released and decide I better try that out even though I don't need any of the changes right now or did you really want to upgrade? If it is the former then I might delay a while and consult with Nick Gorham to see if I can find a better way of detecting VARxxx(MAX) columns. If it is the latter you can either change your code to specify the bind type or change DBD::ODBC dbdimp.c as above or I can release a new version with this change to see how it is accepted (and to allow you to use it). Look forward to hearing from you (and once again, apologies for taking longer than normal to look into this - I have been really busy with other stuff but I do very much appreciate your bug reports and cooperation in tracking them down). Martin -- Martin J. Evans Wetherby, UK

Tue Oct 06 14:46:50 2009 Bruce.Tanner [...] Cerritos.edu - Correspondence added

Subject:	RE: [rt.cpan.org #49760] Invalid precision value (SQL-HY104) with DBD-ODBC 1.23
Date:	Tue, 6 Oct 2009 11:45:51 -0700
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	"Tanner, Bruce" <Bruce.Tanner [...] Cerritos.edu>

Show quoted text

>An alternative workaround that does not involve any DBD::ODBC code >changes is to specify the bind type:

Show quoted text

>use DBI qw(:sql_types); >. >. >$request->bind_param(1, 1099, { TYPE => SQL_VARCHAR}); >$request->execute; >This is the one I guess you are not going to like.

It is a little bit of typing, but not too bad. Show quoted text

>What I do now partly depends on you. Did you see DBD::ODBC 1.23 released >and decide I better try that out even though I don't need any of the >changes right now or did you really want to upgrade? If it is the former >then I might delay a while and consult with Nick Gorham to see if I can >find a better way of detecting VARxxx(MAX) columns. If it is the latter >you can either change your code to specify the bind type or change >DBD::ODBC dbdimp.c as above or I can release a new version with this >change to see how it is accepted (and to allow you to use it).

I had just gotten a new desktop system and decided to install the latest of everything. After I sent the bug report, I renamed the /perl/site/lib/DBD/ODBC directory and fell back to the /perl/lib version which happened to be 1.22. I'm perfectly happy with that. Show quoted text

>Look forward to hearing from you (and once again, apologies for taking >longer than normal to look into this - I have been really busy with >other stuff but I do very much appreciate your bug reports and >cooperation in tracking them down).

Thanks for all of your volunteer support, -Bruce

Tue Oct 06 15:12:25 2009 bohica [...] ntlworld.com - Correspondence added

Ok, it sounds like you are not desperate to upgrade. I'll leave this issue open, investigate other alternatives and post back here when I have something definite. Martin -- Martin J. Evans Wetherby, UK

Thu Dec 10 21:56:30 2009 starrychloe [...] oliveyou.net - Correspondence added

From:

starrychloe [...] oliveyou.net

Thanks for looking into this. I also experienced this bug when trying to insert. I uploaded the log file, but I cut a lot of sensitive information out and xxx'd the rest. I'm using ODBC 10, SQL Server 2008, and Windows 2003 x64. Normally it gives this error [Microsoft][SQL Server Native Client 10.0]Invalid precision value (SQL-HY104) When I run it with the tracing, it gives a different error message [Microsoft][SQL Server Native Client 10.0]Connection is busy with results for another command (SQL-HY000)

Download odbc.log
application/octet-stream 18.9k

Message body not shown because it is not plain text.

Wed Apr 14 14:16:45 2010 bohica [...] ntlworld.com - Correspondence added

Bruce, I know it has been a while since you reported this rt but because a few other people are hitting this issue with the MS SQL Server ODBC Driver I've decided to change to err in the favour of non VARXXX(max) columns. I've also made changes that speed up prepared statements in certain circumstances. I will be uploading a development release 1.23_4 to CPAN soon and wanted to a) give you advance warning and b) wondered if you might be in a position to try it out. Let me know and I can tell you when it is uploaded to CPAN. Thanks. Martin On Thu Dec 10 21:56:30 2009, starrychloe wrote: Show quoted text

> Thanks for looking into this. I also experienced this bug when trying to > insert. I uploaded the log file, but I cut a lot of sensitive > information out and xxx'd the rest. I'm using ODBC 10, SQL Server 2008, > and Windows 2003 x64. > > Normally it gives this error > [Microsoft][SQL Server Native Client 10.0]Invalid precision value > (SQL-HY104) > > When I run it with the tracing, it gives a different error message > [Microsoft][SQL Server Native Client 10.0]Connection is busy with > results for another command (SQL-HY000)

-- Martin J. Evans Wetherby, UK

Wed Apr 14 14:23:59 2010 Bruce.Tanner [...] Cerritos.edu - Correspondence added

Subject:	RE: [rt.cpan.org #49760] Invalid precision value (SQL-HY104) with DBD-ODBC 1.23
Date:	Wed, 14 Apr 2010 11:23:21 -0700
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	"Tanner, Bruce" <Bruce.Tanner [...] Cerritos.edu>

It'd be happy to test it out. I have another development system running strawberry Perl that has MinGW, so I have a good chance of running it straight out of CPAN. -Bruce

Wed Apr 14 15:25:01 2010 bohica [...] ntlworld.com - Correspondence added

On Wed Apr 14 14:23:59 2010, Bruce.Tanner@Cerritos.edu wrote: Show quoted text

> It'd be happy to test it out. I have another development system > running strawberry Perl that has MinGW, so I have a good chance of > running it straight out of CPAN. > > -Bruce

1.23_4 on its way to pause now - might be a while before it makes it way around the mirrors. Will announce in the dbi-dev list and if I can find your email address on my home machine will bcc you. Thanks. Martin -- Martin J. Evans Wetherby, UK

Thu Jun 06 13:58:14 2013 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'