Bug #81929 for DBD-ADO: DBI::SQL_TYPE_TIMESTAMP (adTimestamp) insertion erroneous

Wed Dec 12 10:46:29 2012 lotan_rm [...] gmx.de - Ticket created

Subject:	DBI::SQL_TYPE_TIMESTAMP (adTimestamp) insertion erroneous
Date:	Wed, 12 Dec 2012 16:46:12 +0100
To:	bug-DBD-ADO [...] rt.cpan.org
From:	"Günter Merz" <lotan_rm [...] gmx.de>

I have the following problem using DBI/DBD::ADO: I'm trying to insert a date into a column of type DBI::SQL_TYPE_TIMESTAMP using a string of the following format: "YYYY-MM-DD hh:mm:ss". When I do this, I've observed that there seems to be a logic somewhere that when the day is outside of the month range [1 .. 12] the insertion is correct. However, when the day is within the month range, month and day get swapped---the insertion is incorrect. To work around this problem, I'm currently using this code: if ($day > 12) { $field = "$year-$month-$day $hour:$minute:$second"; } else { $field = "$year-$day-$month $hour:$minute:$second"; } This is illogical and makes a move to da different DBD needlessly hard. I don't know if it is DBD::ADOs problem. When I looked at the code (ADO.pm, _assign_code, line 1153), I didn't see how it could be. On the other hand, I can't imagine that ADO timestamp handling is incorrect in general. I also wondered if this has to do with NLS. I tried using different locales but couldn't solve the problem this way. My last try was using a OLE Variant created from a named month (to avoid confusion) instead of a timestamp string: $field = Win32::OLE::Variant->new(Win32::OLE::Variant::VT_DATE, "August 5, 2012 12:01:00"); But that didn't help either.

Thu Dec 13 05:36:27 2012 sgoeldner [...] cpan.org - Correspondence added

Unfortunately, that's very DBMS specific. What's your DBMS? Here some general hints: A DBMS may support implicit casts, i.e. you give it a string like '2003-10-13' - directly or via bind variable - and the DBMS does understand that. There may be an issue with the correct format. Usually, you can configure that. If the DBMS doesn't support implicit casts, you can make it explicit in SQL-92, e.g.: TIMESTAMP '2003-10-13 13:14:15' CAST('2003-10-13 13:14:15' TO TIMESTAMP ) But vendors need some time to implement this ... The ODBC equivalents are: {ts '2003-10-13 13:14:15'} {fn convert('2003-10-13 13:14:15', SQL_TIMESTAMP )} Often, you find a non-standard, vendor specific equivalent, e.g.: to_date('2003-10-13 13:14:15','YYYY-MM-DD HH24:MI:SS') -- Oracle ctot('2003-10-13 13:14:15') -- FoxPro? datetime(2003,10,13,13,14,15) -- FoxPro? An ODBC driver (and, maybe, an ADO provider) should map the ODBC syntax to the vendor specific syntax. If (and how) this works together with placeholders is a different story (and poorly documented). Giving bind_param() an explicit TYPE parameter (other than the default SQL_VARCHAR) may help or not (because a native representation of the value may be required). I hope I've been of some help. Let us know, what works best for you!

Thu Dec 13 05:36:28 2012 The RT System itself - Status changed from 'new' to 'open'

Fri Dec 14 12:22:51 2012 lotan_rm [...] gmx.de - Correspondence added

Subject:	Re: [rt.cpan.org #81929] DBI::SQL_TYPE_TIMESTAMP (adTimestamp) insertion erroneous
Date:	Fri, 14 Dec 2012 18:22:39 +0100
To:	Steffen Goeldner via RT <bug-DBD-ADO [...] rt.cpan.org>
From:	Günter Merz <lotan_rm [...] gmx.de>

Quoting Steffen Goeldner via RT <bug-DBD-ADO@rt.cpan.org>: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=81929 > > > Unfortunately, that's very DBMS specific.

That's a shame. I thought the point of having an API is to abstract from the backend and its differences. Show quoted text

> What's your DBMS?

It's Jet 4.0 (also used by MS Access). Show quoted text

> Here some general hints: > > A DBMS may support implicit casts, i.e. you give it a string like > '2003-10-13' - directly or via bind variable - and the DBMS does > understand that. > There may be an issue with the correct format. Usually, you can > configure that. > If the DBMS doesn't support implicit casts, you can make it explicit > in SQL-92, e.g.: > > TIMESTAMP '2003-10-13 13:14:15' > CAST('2003-10-13 13:14:15' TO TIMESTAMP ) > > But vendors need some time to implement this ... > The ODBC equivalents are: > > {ts '2003-10-13 13:14:15'} > {fn convert('2003-10-13 13:14:15', SQL_TIMESTAMP )} > > Often, you find a non-standard, vendor specific equivalent, e.g.: > > to_date('2003-10-13 13:14:15','YYYY-MM-DD HH24:MI:SS') -- Oracle > ctot('2003-10-13 13:14:15') -- FoxPro? > datetime(2003,10,13,13,14,15) -- FoxPro? > > An ODBC driver (and, maybe, an ADO provider) should map the > ODBC syntax to the vendor specific syntax. > > If (and how) this works together with placeholders is a > different story (and poorly documented). > > Giving bind_param() an explicit TYPE parameter (other than > the default SQL_VARCHAR) may help or not (because a native > representation of the value may be required).

I had tried to use bind_param() using the TYPE parameter DBI::SQL_TYPE_TIMESTAMP before reporting this bug. And as described in my original post, I had tried to bind an OLE Variant of type VT_DATE instead of a simple string. Using your advice I've tried casting the type in the INSERT statement like this: INSERT INTO $table VALUES(CDate($field)) Apparently, this is supposed to be the way to cast into a type for MS Access. Unfortunately, that didn't help. So far it seems I'm stuck with my workaround. Thanks for your hints anyway, much appreciated.

Fri Dec 21 06:17:35 2012 sgoeldner [...] cpan.org - Correspondence added

Show quoted text

> That's a shame. I thought the point of having an API is to abstract > from the backend and its differences.

And the DBD moduls go a long way to implement the DBI API in a portable way. Certainly, DB specific driver achieve this better then generic one like DBD::ODBC or DBD::ADO. But DBI does *not* rewrite SQL statements! Indeed, it is language independent (in "pass-thru" mode). So we have to rely on standard compliance of the DBMS. Unfortunately, most DBMS stagnate at SQL-92 entry level, i.e. SQL-89. For your problem: I can reproduce it if I bind with TYPE => DBI::SQL_TYPE_TIMESTAMP and there are German regional settings. It works with ISO time format in Windows regional settings, and it works if I bind w/o the TYPE parameter, i.e. rely on implicit type casts. In general, it's seldom necessary to use the TYPE parameter.

Fri Dec 21 06:53:24 2012 lotan_rm [...] gmx.de - Correspondence added

Subject:	Re: [rt.cpan.org #81929] DBI::SQL_TYPE_TIMESTAMP (adTimestamp) insertion erroneous
Date:	Fri, 21 Dec 2012 12:53:09 +0100
To:	Steffen Goeldner via RT <bug-DBD-ADO [...] rt.cpan.org>
From:	Günter Merz <lotan_rm [...] gmx.de>

Sorry, that must have come accross the wrong way, I didn't mean to be condescending. When I talked about the API, I actually didn't refer to DBD::ADO or indeed DBI. I meant to complain about ADO and the whole damn business about NLS. Not only did I have to work around timestamps but also about floating point numbers (in German you have to use a comma). The timestamp business was just especially frustrating because it isn't consistent. Someone must have put some kind of plausibility control in it (months > 12 must be days). Thanks for testing some more (I didn't expect that). Unfortunately, it's not feasible to have all users change to ISO time setting (although I would like that). I just have to live with the workaround. If you want you can change the bug. Reason: Regional settings have to be changed to ISO. Quoting Steffen Goeldner via RT <bug-DBD-ADO@rt.cpan.org>: Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=81929 > >

>> That's a shame. I thought the point of having an API is to abstract >> from the backend and its differences.

> > And the DBD moduls go a long way to implement the DBI API in a > portable way. Certainly, DB specific driver achieve this better > then generic one like DBD::ODBC or DBD::ADO. > > But DBI does *not* rewrite SQL statements! Indeed, it is > language independent (in "pass-thru" mode). > So we have to rely on standard compliance of the DBMS. > Unfortunately, most DBMS stagnate at SQL-92 entry level, > i.e. SQL-89. > > For your problem: I can reproduce it if I bind with TYPE => > DBI::SQL_TYPE_TIMESTAMP and there are German regional settings. > It works with ISO time format in Windows regional settings, > and it works if I bind w/o the TYPE parameter, i.e. rely on > implicit type casts. > In general, it's seldom necessary to use the TYPE parameter.