Bug #50852 for DBD-ODBC: [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime

Mon Oct 26 19:27:24 2009 mockodin [...] gmail.com - Ticket created

Subject:	[SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Mon, 26 Oct 2009 16:27:02 -0700
To:	bug-dbd-odbc <bug-dbd-odbc [...] rt.cpan.org>
From:	Michael Thomas <mockodin [...] gmail.com>

[error] DBD::ODBC::db do failed: [Microsoft] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) [for Statement "UPDATE bugs SET cf_window_end = ? WHERE bug_id = ?"] at D:/WebRoot/Bugzilla/Bugzilla/DB/Mssql.pm line 570 Bugzilla::DB::Mssql::do(undef, 'UPDATE bugs SET cf_window_end = ? WHERE bug_id = ?', undef, '2009-10-28 0:03', 132572) The following link might be useful: http://technet.microsoft.com/en-us/library/bb677170.aspx --My Build--: Windows: 2008 Std ActivePerl: 5.10.1006 32Bit Apache: 2.22.14 DBD-ODBC: 1.23 Driver: SQL Server Native Client 10.0

Tue Oct 27 12:46:06 2009 bohica [...] ntlworld.com - Correspondence added

On Mon Oct 26 19:27:24 2009, mockodin wrote: Show quoted text

> [error] DBD::ODBC::db do failed: [Microsoft] > [SQL Server Native Client 10.0]Invalid character value for cast > specification (SQL-22018) [for Statement "UPDATE bugs SET

cf_window_end = ? Show quoted text

> WHERE bug_id = ?"] > at D:/WebRoot/Bugzilla/Bugzilla/DB/Mssql.pm line 570 > Bugzilla::DB::Mssql::do(undef, 'UPDATE bugs SET

cf_window_end Show quoted text

> = ? WHERE bug_id = ?', undef, '2009-10-28 0:03', 132572) > > The following link might be useful: > http://technet.microsoft.com/en-us/library/bb677170.aspx > > --My Build--: > Windows: 2008 Std > ActivePerl: 5.10.1006 32Bit > Apache: 2.22.14 > DBD-ODBC: 1.23 > Driver: SQL Server Native Client 10.0

Was this working in a previous DBD::ODBC with the same SQL Server native client? Martin -- Martin J. Evans Wetherby, UK

Tue Oct 27 12:46:07 2009 The RT System itself - Status changed from 'new' to 'open'

Tue Oct 27 15:13:48 2009 bohica [...] ntlworld.com - Correspondence added

On Mon Oct 26 19:27:24 2009, mockodin wrote: Show quoted text

> [error] DBD::ODBC::db do failed: [Microsoft] > [SQL Server Native Client 10.0]Invalid character value for cast > specification (SQL-22018) [for Statement "UPDATE bugs SET

cf_window_end = ? Show quoted text

> WHERE bug_id = ?"] > at D:/WebRoot/Bugzilla/Bugzilla/DB/Mssql.pm line 570 > Bugzilla::DB::Mssql::do(undef, 'UPDATE bugs SET

cf_window_end Show quoted text

> = ? WHERE bug_id = ?', undef, '2009-10-28 0:03', 132572) >

Could you tell me what the column type is for cf_window_end. Obviously this is bugzilla - why is this code not using ODBC syntax for timestamps or datetimes? e.g., {d '1980-01-01'} or {ts '2009-10-29 00:03:00'} and why does this appear to not be a valid timestamp (missing leading 0 on hours for instance and also missing seconds) - although this would not be a problem with a datetime column. I think a) the column needs to be a "datetime" column and not a timestamp (assuming it is a timestamp) and also the datetime needs to be in a valid format 'yyyy-mm-dd hh:mm:ss[.fff]' - see http://msdn.microsoft.com/en-us/library/ms190234(SQL.90).aspx. You could omit the ODBC syntax {ts'x'} but it needs to be a valid datetime format then. e.g., Show quoted text

SQL> create table mje (a timestamp);

SQLRowCount returns -1 Show quoted text

SQL> insert into mje values ('1998-05-02 01:23:00');

[37000][unixODBC][Easysoft][SQL Server Driver][SQL Server]Statement(s) could not be prepared. [23000][unixODBC][Easysoft][SQL Server Driver][SQL Server]Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. [ISQL]ERROR: Could not SQLExecute Show quoted text

SQL> drop table mje;

SQLRowCount returns -1 Show quoted text

SQL> create table mje (a datetime);

SQLRowCount returns -1 Show quoted text

SQL> insert into mje values ('2009-10-28 0:03');

SQLRowCount returns 1 I'll happily look at this further if you can supply the column type for the cf_window_end. Martin -- Martin J. Evans Wetherby, UK

Tue Oct 27 15:22:07 2009 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Tue, 27 Oct 2009 12:21:37 -0700
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

datetime or smalldatetime for the column type tried both (mssql's timestamp is a non insertable type, used for incrementing , not a true timestamp) MSSQL should accept 2009-10-28 12:03 formatting perfectly fine, seconds being optional as is AM/PM Further info not previously included, should have been though (sorry): Fails: do(undef, 'UPDATE bugs SET cf_window_end = ? WHERE bug_id = ?', undef, '2009-10-28 12:03', 132572) Succeeds: do(undef, 'UPDATE bugs SET cf_window_end = ? WHERE bug_id = ?', undef, '2009-10-28', 132572) However, executing the same action in query screen (Microsoft SQL Ser but without placeholders works: UPDATE bugs SET cf_window_end = '2009-10-28 12:01' WHERE bug_id = 132572 It would appear to the untrained eye that implicit conversion from a string or (n)varchar is working but when ? is used it fails, perhaps expects a more rigorously formatted string?

Tue Oct 27 16:00:38 2009 bohica [...] ntlworld.com - Correspondence added

On Tue Oct 27 15:22:07 2009, mockodin wrote: Show quoted text

> datetime or smalldatetime for the column type tried both

that's good. Show quoted text

> (mssql's timestamp is a non insertable type, used for incrementing , not a > true timestamp)

Yes, I know - that is why I highlighted it. Show quoted text

> MSSQL should accept 2009-10-28 12:03 formatting perfectly fine, seconds > being optional as is AM/PM

as I think my example showed but as I didn't have access to the native client odbc driver at the moment I could not prove it - I am unix based. Show quoted text

> > Further info not previously included, should have been though (sorry): > > Fails: > do(undef, 'UPDATE bugs SET cf_window_end = ? WHERE bug_id = ?', undef, > '2009-10-28 12:03', 132572) > > Succeeds: > do(undef, 'UPDATE bugs SET cf_window_end = ? WHERE bug_id = ?', undef, > '2009-10-28', 132572) > > However, executing the same action in query screen (Microsoft SQL Ser but > without placeholders works: > > UPDATE bugs SET cf_window_end = '2009-10-28 12:01' WHERE bug_id = 132572 > > It would appear to the untrained eye that implicit conversion from a

string Show quoted text

> or (n)varchar is working but when ? is used it fails, perhaps expects

a more Show quoted text

> rigorously formatted string?

There are workarounds in DBD::ODBC for datetimes. In the past we have had them working but you needed to specify the seconds: case SQL_TYPE_TIMESTAMP: d_digits = 0; /* tbd: millisecondS?) */ if (SvOK(phs->sv)) { char *cp; if (phs->sv_buf && *phs->sv_buf) { cp = strchr(phs->sv_buf, '.'); if (cp) { ++cp; while (*cp != '\0' && isdigit(*cp)) { cp++; d_digits++; } } } else { /* hard code for SQL Server when passing Undef to bound parameters */ column_size = 23; } and the problem (if there is any) is probably in this. I'll try and look in to it more tomorrow. In the meantime if you can come up with a single perl script (rather than the bugzilla example) which illustrates the problem as succinctly as possible it would expedite a solution. Martin -- Martin J. Evans Wetherby, UK

Tue Oct 27 16:43:12 2009 bohica [...] ntlworld.com - Correspondence added

I think it will work with the current DBD::ODBC if you do: create table mje (a datetime); use DBI; use strict; use warnings; my $h = DBI->connect('dbi:ODBC:xxx', 'xxx', 'xxx', {RaiseError => 1, PrintError => 0}); my $s = $h->prepare(q{insert into mje values(?)}); eval { $s->execute(q/{ts '2009-10-28 00:03:00'}/); }; print "Died $@\n" if $@; outputs nothing in error. i.e., put leading 0 in hours and add seconds. The problem is that DateTime fields are reported as SQL_TYPE_TIMESTAMP columns so it ends up in the code I provided before. As soon as you miss the leading hours 0 or seconds it fails. Actually, even if I omit, the ODBC syntax with: use DBI; use strict; use warnings; my $h = DBI->connect('dbi:ODBC:baugi', 'sa', 'easysoft', {RaiseError => 1, PrintError => 0}); my $s = $h->prepare(q{insert into mje values(?)}); eval { $s->execute(q/2009-10-28 00:03:00/); }; print "Died $@\n" if $@; it works fine. The document you refer to is not for ODBC and you have to bare in mind that the ODBC driver sits between sql server and the database. I suspect you will need to include the leading 0 hours and seconds. This is only confirmed from unix via a SQL Server driver for unix and not via native client driver. Martin -- Martin J. Evans Wetherby, UK

Tue Oct 27 17:27:39 2009 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Tue, 27 Oct 2009 14:27:18 -0700
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

Connecting to [sqldev].[bugs] as bugs ...Connected 㸀㸀㸀㸀㸀圀椀琀栀漀甀琀倀氀愀挀攀栀漀氀搀攀爀猀㰀㰀㰀㰀㰀ഀ਀吀攀猀琀㄀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　✀ഀ਀吀攀猀琀㈀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　㨀　　✀ഀ਀吀攀猀琀㌀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　䄀䴀✀ഀ਀吀攀猀琀㐀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　㨀　　䄀䴀✀ഀ਀吀攀猀琀㔀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　✀ഀ਀吀攀猀琀㘀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　㨀　　✀ഀ਀吀攀猀琀㜀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　䄀䴀✀ഀ਀吀攀猀琀㠀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　㨀　　䄀䴀✀ഀ਀㸀㸀㸀㸀㸀圀䤀吀䠀倀氀愀挀攀栀漀氀搀攀爀猀㰀㰀㰀㰀㰀ഀ਀吀攀猀琀㤀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　✀ഀ਀䘀愀椀氀攀搀椀渀猀攀爀琀漀昀漀昀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　✀ഀ਀ 嬀䴀椀挀爀漀猀漀昀琀崀嬀匀儀䰀匀攀爀瘀攀爀一愀琀椀瘀攀䌀氀椀攀渀琀㄀　⸀　崀䤀渀瘀愀氀椀搀挀栀愀爀愀挀琀攀爀瘀愀氀甀攀昀漀爀挀愀猀琀猀瀀攀挀椀昀椀挀愀琀椀漀渀 ⠀匀儀䰀ⴀ㈀㈀　㄀㠀⤀ഀ਀ഀ਀吀攀猀琀㄀　㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　㨀　　✀ഀ਀䘀愀椀氀攀搀椀渀猀攀爀琀漀昀漀昀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　㨀　　✀ഀ਀ 嬀䴀椀挀爀漀猀漀昀琀崀嬀匀儀䰀匀攀爀瘀攀爀一愀琀椀瘀攀䌀氀椀攀渀琀㄀　⸀　崀䤀渀瘀愀氀椀搀挀栀愀爀愀挀琀攀爀瘀愀氀甀攀昀漀爀挀愀猀琀猀瀀攀挀椀昀椀挀愀琀椀漀渀 ⠀匀儀䰀ⴀ㈀㈀　㄀㠀⤀ഀ਀ഀ਀吀攀猀琀㄀㄀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　䄀䴀✀ഀ਀䘀愀椀氀攀搀椀渀猀攀爀琀漀昀漀昀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　䄀䴀✀ഀ਀ 嬀䴀椀挀爀漀猀漀昀琀崀嬀匀儀䰀匀攀爀瘀攀爀一愀琀椀瘀攀䌀氀椀攀渀琀㄀　⸀　崀䤀渀瘀愀氀椀搀挀栀愀爀愀挀琀攀爀瘀愀氀甀攀昀漀爀挀愀猀琀猀瀀攀挀椀昀椀挀愀琀椀漀渀 ⠀匀儀䰀ⴀ㈀㈀　㄀㠀⤀ഀ਀ഀ਀吀攀猀琀㄀㈀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　㨀　　䄀䴀✀ഀ਀䘀愀椀氀攀搀椀渀猀攀爀琀漀昀漀昀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　㨀　　㨀　　䄀䴀✀ഀ਀ 嬀䴀椀挀爀漀猀漀昀琀崀嬀匀儀䰀匀攀爀瘀攀爀一愀琀椀瘀攀䌀氀椀攀渀琀㄀　⸀　崀䤀渀瘀愀氀椀搀挀栀愀爀愀挀琀攀爀瘀愀氀甀攀昀漀爀挀愀猀琀猀瀀攀挀椀昀椀挀愀琀椀漀渀 ⠀匀儀䰀ⴀ㈀㈀　㄀㠀⤀ഀ਀ഀ਀吀攀猀琀㄀㌀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　✀ഀ਀䘀愀椀氀攀搀椀渀猀攀爀琀漀昀漀昀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　✀ഀ਀ 嬀䴀椀挀爀漀猀漀昀琀崀嬀匀儀䰀匀攀爀瘀攀爀一愀琀椀瘀攀䌀氀椀攀渀琀㄀　⸀　崀䤀渀瘀愀氀椀搀挀栀愀爀愀挀琀攀爀瘀愀氀甀攀昀漀爀挀愀猀琀猀瀀攀挀椀昀椀挀愀琀椀漀渀 ⠀匀儀䰀ⴀ㈀㈀　㄀㠀⤀ഀ਀ഀ਀吀攀猀琀㄀㐀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　㨀　　✀ഀ਀吀攀猀琀㄀㔀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　䄀䴀✀ഀ਀䘀愀椀氀攀搀椀渀猀攀爀琀漀昀漀昀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　䄀䴀✀ഀ਀ 嬀䴀椀挀爀漀猀漀昀琀崀嬀匀儀䰀匀攀爀瘀攀爀一愀琀椀瘀攀䌀氀椀攀渀琀㄀　⸀　崀䤀渀瘀愀氀椀搀挀栀愀爀愀挀琀攀爀瘀愀氀甀攀昀漀爀挀愀猀琀猀瀀攀挀椀昀椀挀愀琀椀漀渀 ⠀匀儀䰀ⴀ㈀㈀　㄀㠀⤀ഀ਀ഀ਀吀攀猀琀㄀㘀㨀䄀琀琀攀洀瀀琀椀渀最琀漀椀渀猀攀爀琀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　㨀　　䄀䴀✀ഀ਀䘀愀椀氀攀搀椀渀猀攀爀琀漀昀漀昀 ✀㈀　　㤀ⴀ　㄀ⴀ　㄀　　㨀　　㨀　　䄀䴀✀ഀ਀ 嬀䴀椀挀爀漀猀漀昀琀崀嬀匀儀䰀匀攀爀瘀攀爀一愀琀椀瘀攀䌀氀椀攀渀琀㄀　⸀　崀䤀渀瘀愀氀椀搀挀栀愀爀愀挀琀攀爀瘀愀氀甀攀昀漀爀挀愀猀琀猀瀀攀挀椀昀椀挀愀琀椀漀渀 ⠀匀儀䰀ⴀ㈀㈀　㄀㠀⤀ഀ਀ഀ਀

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

See Attached Files (included is the test script) The test scripts makes 16 passes 8 without Placeholders -- All Passed 8 with Placeholders -- 1/8 passed The only test that passed for placeholders confirmed your statement that '2009-01-01 00:00:00' works, test output is attached as result.txt

Tue Oct 27 17:40:57 2009 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Tue, 27 Oct 2009 14:40:40 -0700
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

Look like the result.txt file was mangled (displaying in Asian characters to me): Connecting to [sqldev].[bugs] as bugs ...Connected Show quoted text

>>>>> Without Placeholders <<<<<

Test 1: Attempting to insert '2009-01-01 0:00' Test 2: Attempting to insert '2009-01-01 0:00:00' Test 3: Attempting to insert '2009-01-01 0:00 AM' Test 4: Attempting to insert '2009-01-01 0:00:00 AM' Test 5: Attempting to insert '2009-01-01 00:00' Test 6: Attempting to insert '2009-01-01 00:00:00' Test 7: Attempting to insert '2009-01-01 00:00 AM' Test 8: Attempting to insert '2009-01-01 00:00:00 AM' Show quoted text

>>>>> WITH Placeholders <<<<<

Test 9: Attempting to insert '2009-01-01 0:00' Failed insert of of '2009-01-01 0:00' [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) Test 10: Attempting to insert '2009-01-01 0:00:00' Failed insert of of '2009-01-01 0:00:00' [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) Test 11: Attempting to insert '2009-01-01 0:00 AM' Failed insert of of '2009-01-01 0:00 AM' [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) Test 12: Attempting to insert '2009-01-01 0:00:00 AM' Failed insert of of '2009-01-01 0:00:00 AM' [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) Test 13: Attempting to insert '2009-01-01 00:00' Failed insert of of '2009-01-01 00:00' [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) Test 14: Attempting to insert '2009-01-01 00:00:00' Test 15: Attempting to insert '2009-01-01 00:00 AM' Failed insert of of '2009-01-01 00:00 AM' [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) Test 16: Attempting to insert '2009-01-01 00:00:00 AM' Failed insert of of '2009-01-01 00:00:00 AM' [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018)

Wed Oct 28 14:37:50 2009 bohica [...] ntlworld.com - Correspondence added

On Tue Oct 27 17:40:57 2009, mockodin wrote: Show quoted text

> Look like the result.txt file was mangled (displaying in Asian

characters to Show quoted text

> me): > > Connecting to [sqldev].[bugs] as bugs ...Connected

> >>>>> Without Placeholders <<<<<

<snipped> I've taken another look at this today and I cannot make it work unless the datetimes have the documented ODBC syntax for a datetime (with or without the ts{}). I imagine this is deliberate by microsoft. I realise it works without the ODBC driver, direct to sql server from the enterprise manager or via SQL and ODBC but in both cases the sql is passed through untouched whereas in the case of parameters they take a different route through the ODBC driver. I'm open to any suggestion but I don't currently see a way around this - it appears to be this way and you will need to format your datetimes before passing them to SQL Server. I would have thought this is something bugzilla could easily do. Martin -- Martin J. Evans Wetherby, UK

Wed Oct 28 17:14:51 2009 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Wed, 28 Oct 2009 14:14:35 -0700
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

As with most things yes the adjustment can be made in bugzilla. But seems somewhat unfortunate that the change should be made there. Not that I blame DBD::ODBC, if MS is indeed handling it in two distinct manners then it is a fault on their side. It should be consistent imo. I'm ok with closing this ticket as invalid

Thu Oct 29 05:56:36 2009 bohica [...] ntlworld.com - Correspondence added

On Wed Oct 28 17:14:51 2009, mockodin wrote: Show quoted text

> As with most things yes the adjustment can be made in bugzilla. But seems > somewhat unfortunate that the change should be made there. Not that I

blame Show quoted text

> DBD::ODBC, if MS is indeed handling it in two distinct manners then it

is a Show quoted text

> fault on their side. It should be consistent imo. > > I'm ok with closing this ticket as invalid

I sympathize and mostly agree with you. I spoke to one of my colleagues and he thinks it has been this was forever. I'll not close this right now as a) I might think some more about it and b) I want to faq it anyway. What I am still not sure about is how this has not been spotted before - surely someone has used bugzilla with ms sql server. Was this a change in bugzilla and DBD::ODBC has worked with it before or was it a change in DBD::ODBC (which I doubt). I'd really like to know this for certain. I do not know what your association with bugzilla is but if anyone on the bugzilla team wants to talk to me about it I'd be happy with that. Martin -- Martin J. Evans Wetherby, UK

Thu Oct 29 11:53:21 2009 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Thu, 29 Oct 2009 08:53:00 -0700
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

Actually I'm working on the first official port of Bugzilla onto MSSQL. So its unlikely anyone has ran across it previously. There was a ancient attempt to port to mssql I recall, but I don't know what if any issues they ran into. Bugzilla was much less mature at that time. I'd actually wager that others may have run across this issue but either found the same thing we did and made the application level adjustment or wrote it off as perl incompatibility with mssql and went another route. Its also possible that the older mssql drivers did the conversion that the newer drivers do not. Parameters and mssql under perl have been long stand issues according to some FreeTDS articles. Though that was more geared toward interaction from non-Microsoft environments.

Tue Dec 01 16:50:21 2009 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Tue, 1 Dec 2009 13:49:54 -0800
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

Martin I have not tested this fully yet, but I did run across an oddity earlier today. I was receiving "Invalid character value for cast specification" dealing with INT values when using parameters. Since this seemed odd since it worked sometimes but not others, from different locations in the code, I went searching my perl install on a hunch. Here is what I found. My perl installation referenced DBD::ODBC 1.22 and DBD::ODBC 1.23 \usr\lib\DBD\ODBC.pm <- 1.22 \usr\site\lib\DBD\ODBC.pm <- 1.23 I overwrote the '\usr\lib\DBD\ODBC.pm' file with the one from '\usr\site\lib\DBD\ODBC.pm' and the errors went away. Is this something in the base CPAN module or brokenness on Active Perl's side? Or something expected somehow? Active Perl 5.10.1 Build 1006 32bit

Tue Dec 01 17:03:05 2009 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Tue, 1 Dec 2009 14:02:47 -0800
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

Hmm I take it back, not corrected, but still interesting that both file versions exist?

Thu Dec 17 15:25:34 2009 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] AutoReply: [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Thu, 17 Dec 2009 12:25:11 -0800
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

Further Issues same error: [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) SELECT DISTINCT top 1001 userid FROM profiles WHERE (CHARINDEX(?, login_name) > 0 OR CHARINDEX(?, realname) > 0) AND disabledtext = '' the value of both parameters is 'superman' login_name and realname are nvarchar same issue occured in some instance of int. Seems related to being processed by a function of any type, eg SELECT DATEADD(minute,?,GETDATE()) parameter value '1' throws the same error.

Fri Dec 18 03:53:26 2009 bohica [...] ntlworld.com - Correspondence added

On Thu Dec 17 15:25:34 2009, mockodin wrote: Show quoted text

> Further Issues same error: > > [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast > specification (SQL-22018) > > SELECT DISTINCT top 1001 userid FROM profiles WHERE (CHARINDEX(?, > login_name) > 0 OR CHARINDEX(?, realname) > 0) AND disabledtext = '' > > the value of both parameters is 'superman' > > login_name and realname are nvarchar > > same issue occured in some instance of int. Seems related to being

processed Show quoted text

> by a function of any type, eg > > SELECT DATEADD(minute,?,GETDATE()) > > parameter value '1' throws the same error.

Can you provide a small example of these failures - something which creates the schema and demonstrates the problem? Martin -- Martin J. Evans Wetherby, UK

Fri Dec 18 12:06:35 2009 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Fri, 18 Dec 2009 09:06:13 -0800
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

#!/usr/bin/perl -w # vim:ts=4:sw=4:ht=4:expandtab use strict; use DBI; use Data::Dumper; my $dbname = "bugs"; my $user = "bugs"; my $pass = "bugs"; my $host = ""; _run_test("dbi:ODBC:Driver={SQL Server Native Client 10.0};Server=$host;UID=$user;PWD=$pass;Database=$dbname;APP=Bugzilla;",'bugs','bugs'); sub _run_test { my ($DSN, $user, $pass) = @_; print "Connecting..."; my $dbh = DBI->connect("dbi:ODBC:Bugzilla",$user,$pass); print "Connected\n" if $dbh; exit unless $dbh; print "\n\ncreating table 'test_me_i_am_broken'..."; $dbh->do("CREATE TABLE test_me_i_am_broken ( userid int identity(1,1), login_name NVARCHAR(255), realname NVARCHAR(255), disabledtext NVARCHAR(MAX) )"); print "done\n"; print "creating values..."; $dbh->do("INSERT INTO test_me_i_am_broken (login_name,realname, disabledtext) VALUES ('mockodin\@gmail.com', 'Michael Thomas', '')");\ print "done\n"; print "------------------------\n\n\n"; print "testing...\n\n"; my $findme = "m.t"; print Dumper $dbh->selectall_arrayref("SELECT TOP 1001 userid FROM test_me_i_am_broken WHERE (CHARINDEX(?, login_name) > 0 OR CHARINDEX(?, realname) > 0) AND disabledtext = ''",undef,$findme, $findme); print "\n\n------------------------\n\n\n"; print "dropping test table 'test_me_i_am_broken'..."; $dbh->do("DROP TABLE test_me_i_am_broken"); print "done\n"; $dbh->disconnect if ($dbh); }

Fri Dec 18 12:11:05 2009 bohica [...] ntlworld.com - Correspondence added

On Fri Dec 18 12:06:35 2009, mockodin wrote: Show quoted text

> #!/usr/bin/perl -w > # vim:ts=4:sw=4:ht=4:expandtab > use strict; > use DBI; > use Data::Dumper; > > my $dbname = "bugs"; > my $user = "bugs"; > my $pass = "bugs"; > my $host = ""; > > _run_test("dbi:ODBC:Driver={SQL Server Native Client >

10.0};Server=$host;UID=$user;PWD=$pass;Database=$dbname;APP=Bugzilla;",'bugs','bugs'); Show quoted text

> > sub _run_test { > my ($DSN, $user, $pass) = @_; > print "Connecting..."; > > my $dbh = DBI->connect("dbi:ODBC:Bugzilla",$user,$pass); > print "Connected\n" if $dbh; > exit unless $dbh; > > > print "\n\ncreating table 'test_me_i_am_broken'..."; > $dbh->do("CREATE TABLE test_me_i_am_broken ( userid int > identity(1,1), > login_name NVARCHAR(255), realname NVARCHAR(255), disabledtext > NVARCHAR(MAX) > )"); > print "done\n"; > print "creating values..."; > $dbh->do("INSERT INTO test_me_i_am_broken (login_name,realname, > disabledtext) VALUES ('mockodin\@gmail.com', 'Michael Thomas', '')");\ > print "done\n"; > > print "------------------------\n\n\n"; > print "testing...\n\n"; > > my $findme = "m.t"; > print Dumper $dbh->selectall_arrayref("SELECT TOP 1001 userid FROM > test_me_i_am_broken WHERE (CHARINDEX(?, login_name) > 0 OR > CHARINDEX(?, > realname) > 0) AND disabledtext = ''",undef,$findme, $findme); > print "\n\n------------------------\n\n\n"; > print "dropping test table 'test_me_i_am_broken'..."; > $dbh->do("DROP TABLE test_me_i_am_broken"); > print "done\n"; > $dbh->disconnect if ($dbh); > > }

Thanks. I am on holiday now but I'll try and find time to look into this. Martin -- Martin J. Evans Wetherby, UK

Thu Jan 07 12:50:28 2010 bohica [...] ntlworld.com - Correspondence added

On Fri Dec 18 12:11:05 2009, MJEVANS wrote:
Show quoted text

> On Fri Dec 18 12:06:35 2009, mockodin wrote:

> > #!/usr/bin/perl -w

> > # vim:ts=4:sw=4:ht=4:expandtab

> > use strict;
> > use DBI;
> > use Data::Dumper;
> >
> > my $dbname = "bugs";
> > my $user = "bugs";
> > my $pass = "bugs";
> > my $host = "";
> >
> > _run_test("dbi:ODBC:Driver={SQL Server Native Client
> >

> 10.0};Server=$host;UID=$user;PWD=$pass;Database=$dbname;APP=Bugzilla;",'bugs','bugs');

> >
> > sub _run_test {
> > my ($DSN, $user, $pass) = @_;
> > print "Connecting...";
> >
> > my $dbh = DBI->connect("dbi:ODBC:Bugzilla",$user,$pass);
> > print "Connected\n" if $dbh;
> > exit unless $dbh;
> >
> >
> > print "\n\ncreating table 'test_me_i_am_broken'...";
> > $dbh->do("CREATE TABLE test_me_i_am_broken ( userid int
> > identity(1,1),
> > login_name NVARCHAR(255), realname NVARCHAR(255), disabledtext
> > NVARCHAR(MAX)
> > )");
> > print "done\n";
> > print "creating values...";
> > $dbh->do("INSERT INTO test_me_i_am_broken (login_name,realname,
> > disabledtext) VALUES ('mockodin\@gmail.com', 'Michael Thomas',

> '')");\

> > print "done\n";
> >
> > print "------------------------\n\n\n";
> > print "testing...\n\n";
> >
> > my $findme = "m.t";
> > print Dumper $dbh->selectall_arrayref("SELECT TOP 1001 userid

> FROM

> > test_me_i_am_broken WHERE (CHARINDEX(?, login_name) > 0 OR
> > CHARINDEX(?,
> > realname) > 0) AND disabledtext = ''",undef,$findme, $findme);
> > print "\n\n------------------------\n\n\n";
> > print "dropping test table 'test_me_i_am_broken'...";
> > $dbh->do("DROP TABLE test_me_i_am_broken");
> > print "done\n";
> > $dbh->disconnect if ($dbh);
> >
> > }

>
> Thanks. I am on holiday now but I'll try and find time to look into
> this.
>
> Martin

Sorry, I am back from holiday and forgot about this. It is now top of my list for non work issues so please feel free to nag me if you do not hear back in a few days.

Martin
--
Martin J. Evans
Wetherby, UK

Thu Jan 07 16:26:15 2010 bohica [...] ntlworld.com - Correspondence added

Really annoyinging, your example works fine with our SQL Server driver which confirms what I thought which is that this is a driver issue and not a DBD::ODBC issue. Looks like I am going to have to resort to finding what is broken in the MS native client driver which means Windows which means it is going to take a bit longer.

Martin
--
Martin J. Evans
Wetherby, UK

Thu Jan 07 18:07:09 2010 bohica [...] ntlworld.com - Correspondence added

Hi. The problem with this latest issue you reported (which confused me earlier because I looked at the start of the rt report and reminded myself it was a datetime issue but then it changed to an error 22018 error nothing to do with datetimes) is that the ODBC driver you are using reports the two parameters (via the ODBC API SQLDescribeParam) as SQL_INTEGERs but they are in fact strings:

      SQLDescribeParam 1: SqlType=INTEGER(4) param_size=10 Scale=0 Nullable=0
      SQLDescribeParam 2: SqlType=INTEGER(4) param_size=10 Scale=0 Nullable=0

DBD::ODBC cannot know they are strings when the driver says they are integers so it believes what the driver tells it. This does not happen with some other SQL Server ODBC Drivers (e.g., ours i.e., Easysoft's) as they describe the parameters as SQL_NVARCHARs. In this case you need to force the parameter types to be SQL_VARCHAR like this:

if (0) {
    # your original code
    print Dumper $dbh->selectall_arrayref("SELECT TOP 1001 userid FROM test_me_i_am_broken WHERE (CHARINDEX(?, login_name) > 0 OR CHARINDEX(?, realname) > 0) AND disabledtext = ''",undef,$findme, $findme);
} else {
    # replacement
    my $s = $dbh->prepare("SELECT TOP 1001 userid FROM test_me_i_am_broken WHERE (CHARINDEX(?, login_name) > 0 OR CHARINDEX(?, realname) > 0) AND disabledtext = ''");
    $s->bind_param(1, $findme, {TYPE => SQL_VARCHAR});
    $s->bind_param(2, $findme, {TYPE => SQL_VARCHAR});
    $s->execute;
    print Dumper $s->fetchall_arrayref;
}

NOTE, you need to add:

use DBI qw(:sql_types);

to get the SQL_VARCHAR definition. Also note, that DBI does not define SQL_NVARCHAR, I am not sure whether you are using the unicode build of DBD::ODBC and I don't know whether any of the strings you pass are likely to be unicode so this may make a difference. Also note that your original example did not work for me until I changed $findme to "m" (and not "m.t") but I didn't look into that since your strings do not contain "m.t" and I didn't know if CHARINDEX supports wildcards.

So it is basically another bug in the ODBC driver - your second - hmmm. There may be a way to reorganise your SQL to persuade the driver to return the correct SQL types for the parameter but I don't know what that is (although I'll ask a colleague here tomorrow). You are not the only one to hit this problem although in most cases the SQL required to confuse the sql server driver is a little more complex (unless it is something daft like "select func(?) where 1 = 1". Basically, the sql server driver attempts to reoganise your SQL statement to end up with a "select parameter_1, parameter_2 from your_table" so it can then find the column types - in your SQL it is failing to come up with the correct select.

As this has caused a few people problems I am considering making a change to DBD::ODBC to allow you to say just bind everything as strings and don't bother asking the driver what the column is. It is a pain and I've resisted doing this in the vain hope MS would fix their drivers but it seems that is not going to happen. In the mean time your only option is to force SQL_VARCHAR as I describe above.

Martin
--
Martin J. Evans
Wetherby, UK

Thu Jan 07 18:42:46 2010 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Thu, 7 Jan 2010 15:42:30 -0800
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

That "bind_param" indeed is the workaround I'm using for this project, though is obviously less than spectacular to have to do. Show quoted text

>> unless it is something daft like "select func(?) where 1 = 1"

Ah well since you mentioned it I decided to check: This is what is detected when I run profiler and then execute the test script I sent you. set fmtonly on select 0,0 from test_me_i_am_broken where 1=2 set fmtonly off I'm guessing it is basing the field type on the function instead of the params.

Tue Jan 26 01:36:16 2010 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Mon, 25 Jan 2010 22:36:01 -0800
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

Any updates?

Tue Jan 26 04:41:15 2010 bohica [...] ntlworld.com - Correspondence added

On Tue Jan 26 01:36:16 2010, mockodin wrote: Show quoted text

> Any updates?

Yes, I coded odbc_force_bind_type but then got distracted. Give me an hour or two to test it and package it up then if you send me (mjevans at cpan dot org) your email address I'll send a distribution to try out. Martin -- Martin J. Evans Wetherby, UK

Tue Jan 26 09:03:47 2010 bohica [...] ntlworld.com - Correspondence added

On Tue Jan 26 04:41:15 2010, MJEVANS wrote: Show quoted text

> On Tue Jan 26 01:36:16 2010, mockodin wrote:

> > Any updates?

> > Yes, I coded odbc_force_bind_type but then got distracted. Give me an > hour or two to test it and package it up then if you send me (mjevans at > cpan dot org) your email address I'll send a distribution to try out. > > Martin

1.23_2 has been uploaded to CPAN. It adds odbc_force_bind_type - see POD and t/rt_50852.t. I'm still a little uncertain about the benefit of this over simple adding {TYPE => SQL_VARCHAR} etc to the end of the bind_param call as the underlying problem is a buggy MS SQL Server ODBC Driver and hence this is a development release. Martin -- Martin J. Evans Wetherby, UK

Tue Jan 26 12:06:48 2010 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Tue, 26 Jan 2010 09:06:19 -0800
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

Show quoted text

> I'm still a little uncertain about the benefit of this over simple > adding {TYPE => SQL_VARCHAR}

the benefit being that you can use a single line of code to accomplish that otherwise takes three. $dbh->do("select * from table1 WHERE fielda = ?", undef, $blah); vs $dbh->prepare( $dbh->bind_param($blah, SQL_VARCHAR); $dbh->execute Not to say defaulting a particular datatype is a great way to go, but if it works the option being open is good. Optimally mssql gets their act together.

Tue Jan 26 12:12:27 2010 bohica [...] ntlworld.com - Correspondence added

On Tue Jan 26 12:06:48 2010, mockodin wrote: Show quoted text

> > I'm still a little uncertain about the benefit of this over simple > > adding {TYPE => SQL_VARCHAR}

> > the benefit being that you can use a single line of code to accomplish > that otherwise takes three. > > $dbh->do("select * from table1 WHERE fielda = ?", undef, $blah); > > vs > > $dbh->prepare( > $dbh->bind_param($blah, SQL_VARCHAR); > $dbh->execute > > Not to say defaulting a particular datatype is a great way to go, but > if it works the option being open is good. Optimally mssql gets their > act together.

The problem is you will lose track of the SQL which exhibits the bug whereas if you put TYPE=>SQL_VARCHAR you know this is a dodgy one. DBI and DBDs try very hard to do the right thing and make it so that code written for one db works with another. As soon as you have to start adding special attributes this falls down. No way out of it at present but TYPE=>SQL_VARCHAR is more portable than odbc_force_bind_type. Anyway, it is a small change and I don't think it hurts. Let me know how you get on. Martin -- Martin J. Evans Wetherby, UK

Fri Feb 05 14:48:02 2010 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Fri, 5 Feb 2010 11:47:05 -0800
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

I'm thinking this introduces more uncertainty than benefit the follow worked fine under 1.23 but threw the Specification error for 1.23_2 "UPDATE whine_schedules SET run_next = ? WHERE id = ?" Opened a Bug with Microsoft: https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater-datatype-lookup-returns-incorrectly I noted they have already marked to "Closed as Fixed" but have provided no detail on a hotfix or forth coming release version that fix the issue.

Wed Apr 14 14:02:54 2010 bohica [...] ntlworld.com - Correspondence added

Hi again, I am about to do a new development release of DBD::ODBC 1.23_4 which includes changes which speed up prepared statements considerably in certain circumstances. I'm afraid it does nothing more than the last release I sent you to fix the problem you reported as the problem is in MS's ODBC driver. However, would you be interested in checking it out before I do a final release of 1.24? If so get back to me and I'll let you know when it is uploaded to CPAN. I'm struggling to find people to test development releases before final releases and then when the final release is done I get a bunch of rt reports so I'd appreciate it. If on the other hand you are no longer using DBD::ODBC, that would be useful to know too. I currently have no way to further your rt beyond the suggestions I originally made and the addition of the new attribute to force binding type globally. I'd like to close this rt with reference to the issue in the DBD::ODBC FAQ as there is no way for me to progress it - I'd be interested in your opinion on this. Martin On Fri Feb 05 14:48:02 2010, mockodin wrote: Show quoted text

> I'm thinking this introduces more uncertainty than benefit the follow > worked fine under 1.23 but threw the Specification error for 1.23_2 > "UPDATE whine_schedules SET run_next = ? WHERE id = ?" > > Opened a Bug with Microsoft: > https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater- > datatype-lookup-returns-incorrectly > > I noted they have already marked to "Closed as Fixed" but have > provided no detail on a hotfix or forth coming release version that > fix the issue.

-- Martin J. Evans Wetherby, UK

Wed Apr 14 23:30:23 2010 mockodin [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #50852] [SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) for datetime/smalldatetime
Date:	Wed, 14 Apr 2010 20:30:07 -0700
To:	bug-DBD-ODBC [...] rt.cpan.org
From:	Michael Thomas <mockodin [...] gmail.com>

Sure I can test the patch. At present I have a app using DBD::ODBC with FreeTDS and the bugzilla project, which will likely end up supporting Windows and Linux with mssql. Code review just started on the bugzilla project, on bugzilla.org, so it should end up getting a fairly good work out in the coming months. As far as this ticket, since it appears to be a bug on Microsoft's part, admitted by them essentially here https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater-datatype-lookup-returns-incorrectly , we can probably close and note it. On Wed, Apr 14, 2010 at 11:02 AM, Martin J Evans via RT <bug-DBD-ODBC@rt.cpan.org> wrote: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=50852 > > > Hi again, > > I am about to do a new development release of DBD::ODBC 1.23_4 which > includes changes which speed up prepared statements considerably in > certain circumstances. I'm afraid it does nothing more than the last > release I sent you to fix the problem you reported as the problem is in > MS's ODBC driver. However, would you be interested in checking it out > before I do a final release of 1.24? If so get back to me and I'll let > you know when it is uploaded to CPAN. I'm struggling to find people to > test development releases before final releases and then when the final > release is done I get a bunch of rt reports so I'd appreciate it. > > If on the other hand you are no longer using DBD::ODBC, that would be > useful to know too. I currently have no way to further your rt beyond > the suggestions I originally made and the addition of the new attribute > to force binding type globally. I'd like to close this rt with reference > to the issue in the DBD::ODBC FAQ as there is no way for me to progress > it - I'd be interested in your opinion on this. > > Martin > > On Fri Feb 05 14:48:02 2010, mockodin wrote:

>> I'm thinking this introduces more uncertainty than benefit the follow >> worked fine under 1.23 but threw the Specification error for 1.23_2 >> "UPDATE whine_schedules SET run_next = ? WHERE id = ?" >> >> Opened a Bug with Microsoft: >> https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater- >> datatype-lookup-returns-incorrectly >> >> I noted they have already marked to "Closed as Fixed" but have >> provided no detail on a hotfix or forth coming release version that >> fix the issue.

> > > -- > Martin J. Evans > Wetherby, UK >

Thu Apr 15 03:26:38 2010 bohica [...] ntlworld.com - Correspondence added

On Wed Apr 14 23:30:23 2010, mockodin wrote: Show quoted text

> Sure I can test the patch. At present I have a app using DBD::ODBC > with FreeTDS and the bugzilla project, which will likely end up > supporting Windows and Linux with mssql. > > Code review just started on the bugzilla project, on bugzilla.org, so > it should end up getting a fairly good work out in the coming months. > As far as this ticket, since it appears to be a bug on Microsoft's > part, admitted by them essentially here > https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater- > datatype-lookup-returns-incorrectly > , we can probably close and note it.

Thanks for the response. DBD::ODBC 1.23_4 is on CPAN. I will add the URL and some notes to the DBD::ODBC FAQ and close this rt. Martin -- Martin J. Evans Wetherby, UK Show quoted text

> On Wed, Apr 14, 2010 at 11:02 AM, Martin J Evans via RT > <bug-DBD-ODBC@rt.cpan.org> wrote:

> > <URL: https://rt.cpan.org/Ticket/Display.html?id=50852 > > > > > Hi again, > > > > I am about to do a new development release of DBD::ODBC 1.23_4 which > > includes changes which speed up prepared statements considerably in > > certain circumstances. I'm afraid it does nothing more than the last > > release I sent you to fix the problem you reported as the problem is

> in

> > MS's ODBC driver. However, would you be interested in checking it

> out

> > before I do a final release of 1.24? If so get back to me and I'll

> let

> > you know when it is uploaded to CPAN. I'm struggling to find people

> to

> > test development releases before final releases and then when the

> final

> > release is done I get a bunch of rt reports so I'd appreciate it. > > > > If on the other hand you are no longer using DBD::ODBC, that would

> be

> > useful to know too. I currently have no way to further your rt

> beyond

> > the suggestions I originally made and the addition of the new

> attribute

> > to force binding type globally. I'd like to close this rt with

> reference

> > to the issue in the DBD::ODBC FAQ as there is no way for me to

> progress

> > it - I'd be interested in your opinion on this. > > > > Martin > > > > On Fri Feb 05 14:48:02 2010, mockodin wrote:

> >> I'm thinking this introduces more uncertainty than benefit the

> follow

> >> worked fine under 1.23 but threw the Specification error for 1.23_2 > >> "UPDATE whine_schedules SET run_next = ? WHERE id = ?" > >> > >> Opened a Bug with Microsoft: > >>

> https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater-

> >> datatype-lookup-returns-incorrectly > >> > >> I noted they have already marked to "Closed as Fixed" but have > >> provided no detail on a hotfix or forth coming release version that > >> fix the issue.

> > > > > > -- > > Martin J. Evans > > Wetherby, UK > >

Thu Apr 15 03:53:52 2010 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'