Skip Menu |
 

This queue is for tickets about the DBD-ADO CPAN distribution.

Report information
The Basics
Id: 81929
Status: open
Priority: 0/
Queue: DBD-ADO

People
Owner: Nobody in particular
Requestors: lotan_rm [...] gmx.de
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



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>
Download (untitled) / with headers
text/plain 1.2k
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.
Download (untitled) / with headers
text/plain 1.2k
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!
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>
Download (untitled) / with headers
text/plain 2.1k
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.
Download (untitled) / with headers
text/plain 839b
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.
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>
Download (untitled) / with headers
text/plain 1.8k
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.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.