Bug #75479 for DBD-ODBC: [unixODBC][Driver Manager]Function sequence error (SQL-HY010)

Fri Mar 02 10:56:46 2012 Wayne.Bower [...] lsi.com - Ticket created

Subject:	[unixODBC][Driver Manager]Function sequence error (SQL-HY010)
Date:	Fri, 2 Mar 2012 10:56:19 -0500
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	"Bower, Wayne (Wayne)" <Wayne.Bower [...] lsi.com>

Found what looks like a bug and I did my best to reduce the code that reproduces to a small test case. The code I reduced it to has no practical use other than to reveal what appears to be the bug and how I am able to work around it. uname -a Linux palltc04 2.6.9-89.0.20.EL.bz501565.11largesmp #1 SMP Tue Mar 23 04:20:50 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux This is perl 5, version 14, subversion 1 (v5.14.1) built for x86_64-linux-thread-multi-ld DBI 1.616 DBD::ODBC 1.33 The 1st attachment (debugio14.pl) is the script that demonstrates the problem and how I'm attempting to work around it. The 2nd attachment (debugio14.log) is from DBI_TRACE=15=debugio14_dbi.log ./debugio14 >debugio14.log 2>&1 And the 3rd attachment (debugio14_dbi.log) is the DBI_TRACE output. Essentially the issue appears to be related to using 'SET NOCOUNT ON' in a stored procedure. The script creates a procedure and executes it. The first procedure and execution fails with 'Unable to fetch information about the error' while the remaining ones that fail return '[unixODBC][Driver Manager]Function sequence error (SQL-HY010)'. To avoid the failure I have to either remove 'SET NOCOUNT ON' or move later in the procedure. Keep in mind that this is just a test case to reveal the issue. In our actual code we always start each procedure with 'SET NOCOUNT ON' and do much more than shown in this test case. We don't see this problem using older perl 5.8.7, DBI 1.616, DBD::ODBC 1.16, tds 0.64, iodbc Please let me know if more information is needed. Thank you, Wayne Bower

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

Download debugio14.log
application/octet-stream 3.9k

Message body not shown because it is not plain text.

Download debugio14_dbi.log
application/octet-stream 76.3k

Message body not shown because it is not plain text.

Fri Mar 02 13:08:28 2012 bohica [...] ntlworld.com - Correspondence added

On Fri Mar 02 10:56:46 2012, Wayne.Bower@lsi.com wrote: Show quoted text

> Found what looks like a bug and I did my best to reduce the code that > reproduces to a small test case. > The code I reduced it to has no practical use other than to reveal > what appears to be the bug and how I am able to work around it. > > uname -a > Linux palltc04 2.6.9-89.0.20.EL.bz501565.11largesmp #1 SMP Tue Mar 23 > 04:20:50 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux > > This is perl 5, version 14, subversion 1 (v5.14.1) built for x86_64- > linux-thread-multi-ld > > DBI 1.616 > DBD::ODBC 1.33 > > The 1st attachment (debugio14.pl) is the script that demonstrates the > problem and how I'm attempting to work around it. > > The 2nd attachment (debugio14.log) is from > DBI_TRACE=15=debugio14_dbi.log ./debugio14 >debugio14.log 2>&1 > > And the 3rd attachment (debugio14_dbi.log) is the DBI_TRACE output. > > > Essentially the issue appears to be related to using 'SET NOCOUNT ON' > in a stored procedure. The script creates a procedure and executes > it. The first procedure and execution fails with 'Unable to fetch > information about the error' while the remaining ones that fail > return '[unixODBC][Driver Manager]Function sequence error (SQL- > HY010)'. To avoid the failure I have to either remove 'SET NOCOUNT > ON' or move later in the procedure. Keep in mind that this is just > a test case to reveal the issue. In our actual code we always > start each procedure with 'SET NOCOUNT ON' and do much more than > shown in this test case. > > We don't see this problem using older perl 5.8.7, DBI 1.616, DBD::ODBC > 1.16, tds 0.64, iodbc > > Please let me know if more information is needed. > > Thank you, > Wayne Bower >

Thanks for the report. What version of freeTDS is showing the error as 0.64 is very old and so is DBD::ODBC 1.16. I've seen freeTDS return an error status but no error before and that is definitely a bug in freeTDS. In fact I added a special case to output an error saying an error occurred but not error text was available just for that reason. I run through your test case tonight and see what I find. Martin -- Martin J. Evans Wetherby, UK

Fri Mar 02 13:08:30 2012 The RT System itself - Status changed from 'new' to 'open'

Fri Mar 02 13:18:49 2012 bohica [...] ntlworld.com - Correspondence added

The output of your script using the MS SQL Server ODBC Driver is: C:\Users\martin\Downloads>perl debugio14.pl Create TMP_SP_Test_ODBC with input and no output exec_sp_io TMP_SP_Test_ODBC Error: exec_sp_io TMP_SP_Test_ODBC failed ERROR_CODE=1 ERROR_STRING=[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error (SQL-07002) TMP_SP_Test_ODBC returned: rv=<> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input and output exec_sp_io TMP_SP_Test_ODBC TMP_SP_Test_ODBC returned: rv=<9> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input and output without SET NOCOUNT ON exec_sp_io TMP_SP_Test_ODBC TMP_SP_Test_ODBC returned: rv=<9> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input, output, select, init SET NOCOUNT exec_sp_io TMP_SP_Test_ODBC Selected values: I 10 Output values: rv=<9> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input, output, select disabled, init SET NOCOUNT exec_sp_io TMP_SP_Test_ODBC Output values: rv=<5> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input, output, select disabled, later SET NOCOUNT exec_sp_io TMP_SP_Test_ODBC Output values: rv=<5> o=<55> Drop TMP_SP_Test_ODBC I see no function sequence error although I see a bind issue. This would suggest a) there is an issue with the test code (count field incorrect) and b) freeTDS (I now see you are using 0.91) is probably broken. Martin -- Martin J. Evans Wetherby, UK

Fri Mar 02 14:44:29 2012 Wayne.Bower [...] lsi.com - Correspondence added

Subject:	RE: [rt.cpan.org #75479] [unixODBC][Driver Manager]Function sequence error (SQL-HY010)
Date:	Fri, 2 Mar 2012 14:44:05 -0500
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	"Bower, Wayne (Wayne)" <Wayne.Bower [...] lsi.com>

<URL: https://rt.cpan.org/Ticket/Display.html?id=75479 > I see I made a mistake in the first procedure call 'Create TMP_SP_Test_ODBC with input and no output' in my test script. I incorrectly passed 2 arguments (:I, :O) to the procedure and it was expecting 1. After fixing this the first case no longer results in the error 'Unable to fetch information about the error', but there is no change to the remaining cases when run with DBD::ODBC 1.33, freeTDS 0.91, and unixODBC. However the same script works fine using our old install of perl 5.8.7 with DBD::ODBC 1.16 and freeTDS 0.64 with iodbc. Based on your response below, I guess this means that freeTDS 0.91 is causing the 'Function sequence error' problem and not DBD::ODBC 1.33. Since our scripts need to run on Linux and Solaris we have been using freeTDS and I assume you don't work with this. Please confirm if this means that we need to either report the problem to those who maintain freeTDS or find an alternative. Thank you, Wayne Bower Show quoted text

-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Friday, March 02, 2012 1:19 PM To: Bower, Wayne (Wayne) Subject: [rt.cpan.org #75479] [unixODBC][Driver Manager]Function sequence error (SQL-HY010) <URL: https://rt.cpan.org/Ticket/Display.html?id=75479 > The output of your script using the MS SQL Server ODBC Driver is: C:\Users\martin\Downloads>perl debugio14.pl Create TMP_SP_Test_ODBC with input and no output exec_sp_io TMP_SP_Test_ODBC Error: exec_sp_io TMP_SP_Test_ODBC failed ERROR_CODE=1 ERROR_STRING=[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error (SQL-07002) TMP_SP_Test_ODBC returned: rv=<> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input and output exec_sp_io TMP_SP_Test_ODBC TMP_SP_Test_ODBC returned: rv=<9> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input and output without SET NOCOUNT ON exec_sp_io TMP_SP_Test_ODBC TMP_SP_Test_ODBC returned: rv=<9> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input, output, select, init SET NOCOUNT exec_sp_io TMP_SP_Test_ODBC Selected values: I 10 Output values: rv=<9> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input, output, select disabled, init SET NOCOUNT exec_sp_io TMP_SP_Test_ODBC Output values: rv=<5> o=<55> Drop TMP_SP_Test_ODBC Create TMP_SP_Test_ODBC with input, output, select disabled, later SET NOCOUNT exec_sp_io TMP_SP_Test_ODBC Output values: rv=<5> o=<55> Drop TMP_SP_Test_ODBC I see no function sequence error although I see a bind issue. This would suggest a) there is an issue with the test code (count field incorrect) and b) freeTDS (I now see you are using 0.91) is probably broken. Martin -- Martin J. Evans Wetherby, UK

Fri Mar 02 15:43:59 2012 bohica [...] ntlworld.com - Correspondence added

On Fri Mar 02 14:44:29 2012, Wayne.Bower@lsi.com wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=75479 > > > I see I > made a mistake in the first procedure call 'Create TMP_SP_Test_ODBC > with input and no output' in my test script. I incorrectly passed > 2 arguments (:I, :O) to the procedure and it was expecting 1.

Yeah, I saw that. Show quoted text

> After fixing this the first case no longer results in the error > 'Unable to fetch information about the error', but there is no > change to the remaining cases when run with DBD::ODBC 1.33, freeTDS > 0.91, and unixODBC. However the same script works fine using our > old install of perl 5.8.7 with DBD::ODBC 1.16 and freeTDS 0.64 with > iodbc. > > Based on your response below, I guess this means that > freeTDS 0.91 is causing the 'Function sequence error' problem and > not DBD::ODBC 1.33. Since our scripts need to run on Linux and > Solaris we have been using freeTDS and I assume you don't work with > this. Please confirm if this means that we need to either report > the problem to those who maintain freeTDS or find an alternative. > Thank you, > Wayne Bower

All I can say is that I've tried 2 cases: Easysoft SQL Server ODBC Driver and unixODBC driver manager - no errors. MS ODBC Driver manager and MS SQL Server ODBC Driver - no errors. I cannot really comment on iODBC since I don't use it - unixODBC has been the dominant ODBC driver manager for non-Windows (except OSX) for years now. Your function sequence error is likely issued by the driver manager but I cannot tell without seeing the error message itself. The problem, in my opinion, lies in unixODBC or freeTDS. I'm ruling out the former as it works for me with another driver. There is a freeTDS mailing list which is usually good at addressing problems. Please keep me informed and don't hesitate to come back here with any progress or further information. Martin -- Martin J. Evans Wetherby, UK

Sun Mar 11 10:09:09 2012 Wayne.Bower [...] lsi.com - Correspondence added

Subject:	RE: [rt.cpan.org #75479] [unixODBC][Driver Manager]Function sequence error (SQL-HY010)
Date:	Sun, 11 Mar 2012 10:08:57 -0400
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	"Bower, Wayne (Wayne)" <Wayne.Bower [...] lsi.com>

<URL: https://rt.cpan.org/Ticket/Display.html?id=75479 > On Fri Mar 02 15:44:02 2012, Martin J Evans via RT [bug-DBD-ODBC@rt.cpan.org] wrote: Show quoted text

> The problem, in my opinion, lies in unixODBC or freeTDS. I'm ruling out > the former as it works for me with another driver. There is a freeTDS > mailing list which is usually good at addressing problems. > > Please keep me informed and don't hesitate to come back here with any > progress or further information.

I did notify the FreeTDS mailing list of the issue and the problem was quickly identified, solved, and a patch was made to FreeTDS 0.91 to resolve. Thank you for your help. Wayne Bower

Mon Mar 12 04:48:09 2012 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'