Bug #75699 for DBD-ODBC: DBI::SQL_DATETIME - Invalid data type (SQL-HY004)

Sun Mar 11 17:30:19 2012 Wayne.Bower [...] lsi.com - Ticket created

Subject:	DBI::SQL_DATETIME - Invalid data type (SQL-HY004)
Date:	Sun, 11 Mar 2012 17:30:07 -0400
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	"Bower, Wayne (Wayne)" <Wayne.Bower [...] lsi.com>

While testing our code with DBD::ODBC 1.33 I found a case that is failing. The 1st attachment (test_sql_types.pl) is the script that demonstrates the problem. And the 2nd attachment (test_sql_types.log) is from DBI_TRACE=15=test_sql_types_dbi.log ./test_sql_types.pl >test_sql_types.log 2>&1 And the 3rd attachment (test_sql_types_dbi.log) is the DBI_TRACE output. This is using perl 5.14.1, DBI 1.616, DBD::ODBC 1.33 with freeTDS 0.91 and unixODBC on Linux. The script creates a procedure with input parameters of different types and executes it resulting in the following error: DBD::ODBC::st bind_param failed: [FreeTDS][SQL Server]Invalid data type (SQL-HY004) If I specify DBI::SQL_DATETIME as the type it fails with this error, but if I change the type to DBI::SQL_TIMESTAMP it works. It also works if I do not specify the type. In fact we have changed to not specifying the type because this seems to work well for any parameter type. I'm not sure if you would consider this a bug or if I overlooked something in the documentation about the SQL_DATETIME data type. Thank you, Wayne Bower

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

Download test_sql_types.log
application/octet-stream 3.1k

Message body not shown because it is not plain text.

Download test_sql_types_dbi.log
application/octet-stream 33.6k

Message body not shown because it is not plain text.

Mon Mar 12 15:22:02 2012 bohica [...] ntlworld.com - Correspondence added

On Sun Mar 11 17:30:19 2012, Wayne.Bower@lsi.com wrote: Show quoted text

> While testing our code with DBD::ODBC 1.33 I found a case that is > failing. > > The 1st attachment (test_sql_types.pl) is the script that demonstrates > the problem. > > And the 2nd attachment (test_sql_types.log) is from > DBI_TRACE=15=test_sql_types_dbi.log ./test_sql_types.pl

> >test_sql_types.log 2>&1

> > And the 3rd attachment (test_sql_types_dbi.log) is the DBI_TRACE > output. > > This is using perl 5.14.1, DBI 1.616, DBD::ODBC 1.33 with freeTDS 0.91 > and unixODBC on Linux. > > The script creates a procedure with input parameters of different > types and executes it resulting in the following error: > DBD::ODBC::st bind_param failed: [FreeTDS][SQL Server]Invalid data > type (SQL-HY004) > > If I specify DBI::SQL_DATETIME as the type it fails with this error, > but if I change the type to DBI::SQL_TIMESTAMP it works. It also > works if I do not specify the type. In fact we have changed to not > specifying the type because this seems to work well for any > parameter type. > > I'm not sure if you would consider this a bug or if I overlooked > something in the documentation about the SQL_DATETIME data type. > > Thank you, > Wayne Bower

freeTDS is throwing this out as: DBD::ODBC::st bind_param failed: [unixODBC][FreeTDS][SQL Server]Invalid data type (SQL-HY004) at rt_75699.pl line 91. I'm not definitely saying it is wrong but it fails with other SQL Server ODBC drivers too. MS SQL Server is funny with datetime types and it does not surprise me a timestamp works. When I ran this against the MS SQL Server driver on Windows I got: exec TMP_SP_Test_ODBC using SQL_DATETIME causes Invalid data type (SQL-HY004) DBD::ODBC::st bind_param failed: [Microsoft][ODBC SQL Server Driver]Optional fe ture not implemented (SQL-HYC00) at rt_75699.pl line 91. Issuing rollback() due to DESTROY without explicit disconnect() of DBD::ODBC::d handle asus2 at rt_75699.pl line 91. I don't see any error in DBD::ODBC here. It is a straight forward call to SQLBindParameter which is failing. Martin -- Martin J. Evans Wetherby, UK

Mon Mar 12 15:22:04 2012 The RT System itself - Status changed from 'new' to 'open'

Wed Mar 14 11:33:19 2012 Wayne.Bower [...] lsi.com - Correspondence added

Subject:	RE: [rt.cpan.org #75699] DBI::SQL_DATETIME - Invalid data type (SQL-HY004)
Date:	Wed, 14 Mar 2012 11:33:02 -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=75699 > On Mon Mar 12 15:22:05 2012, Martin J Evans via RT bug-DBD-ODBC@rt.cpan.org wrote: Show quoted text

> freeTDS is throwing this out as: > > DBD::ODBC::st bind_param failed: [unixODBC][FreeTDS][SQL Server]Invalid > data type (SQL-HY004) at rt_75699.pl line 91. > > I'm not definitely saying it is wrong but it fails with other SQL Server > ODBC drivers too. > > MS SQL Server is funny with datetime types and it does not surprise me a > timestamp works. > > When I ran this against the MS SQL Server driver on Windows I got: > > exec TMP_SP_Test_ODBC using SQL_DATETIME causes Invalid data type > (SQL-HY004) > DBD::ODBC::st bind_param failed: [Microsoft][ODBC SQL Server > Driver]Optional fe > ture not implemented (SQL-HYC00) at rt_75699.pl line 91. > Issuing rollback() due to DESTROY without explicit disconnect() of > DBD::ODBC::d > handle asus2 at rt_75699.pl line 91. > > I don't see any error in DBD::ODBC here. It is a straight forward call > to SQLBindParameter which is failing.

Interesting that the MS SQL Server driver on Windows doesn't handle properly either, so not sure if I'll bother posting to FreeTDS list. I will work around this by not specifying DBI::SQL_TIMESTAMP or not specifying the type, which seems to work well in all of the cases I've tried thus far. Thank you, Wayne Bower

Wed Mar 14 11:49:10 2012 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'