Skip Menu |
 

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

Report information
The Basics
Id: 28821
Status: resolved
Worked: 1 hour (60 min)
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: dland [...] cpan.org
Cc:
AdminCc:

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



Subject: Cannot use dates in placeholders
Download (untitled) / with headers
text/plain 1.5k
DBD::ODBC doesn't deal with dates in placeholders correctly. I have searched the web and found a few inconclusive reports of such behaviour. I have tried binding the datatype to the column but no joy. I have tried to find 1.14 in ppm format, but no luck on AS's repo nor uwinnipeg. There is a 1.14 available at trouchelle.com, but ppm doesn't know what to do with it. Just for reference the following program may be called with two dates on the command line. Only the second date will be inserted into the table. (The db() routine just something that returns a DBD::ODBC connection). (Note that the dates are formatted in the same way. In my case, this is DD/MM/YYYY. The fact that the do() works means that the underlying database can parse the format correctly). The error returned is: DBD::ODBC::st execute failed: [Oracle][ODBC]Datetime field overflow. (SQL-22008) [Oracle][ODBC]General error. (SQL-HY000)(DBD:st_execute/SQLExecute err=-1) at date-insert.pl line 22. The code: use strict; use warnings; use DBI ':sql_types'; use MyDV 'db'; my $db = db(); # a valid DBI handle END { $db->commit } $db->do(<<END_SQL); create table dl ( d date ) END_SQL my $put = $db->prepare(<<END_SQL) or die "prepare put\n", $db->errstr; insert into dl (d) values (?) END_SQL $put->bind_param(1, undef, SQL_DATE); my $r = $put->execute($ARGV[0]); $db->do("insert into dl (d) values ('$ARGV[1]')") or die $db->errstr; __END__ Hopefully this is fixed in 1.14. If not, if you need any help in tracking this down, just holler. Regards, David Landgren
From: martin.evans [...] easysoft.com
Download (untitled) / with headers
text/plain 350b
Would I be right in thinking you are using Oracle's ODBC driver on Windows? What version have you got installed? I am currently looking after DBD::ODBC and the code contains a number of comments about Oracle's ODBC driver having problems with dates but I'll look in to it. What have you got set in your environment? re NLS_LANG, NLS_DATE_FORMAT etc?
Download (untitled) / with headers
text/plain 159b
Should have also said it would be quicker if you could supply the schema for the table you are inserting into and an example run with a command line parameter.
From: martin.evans [...] easysoft.com
Download (untitled) / with headers
text/plain 1.3k
This is the result of my investigation so far. I don't have Oracle on Windows at the moment and I cannot successfully build DBD::ODBC 1.14 for ActiveState so I've experimented on Linux. schema ====== create table fred (a date); perl code ========= #!/usr/bin/perl -w use strict; use warnings; use DBI; use Data::Dumper; my $h = DBI->connect('dbi:ODBC:oracle', 'xxx', 'yyy'); eval { $h->do(q/delete from fred/); }; my $df = $h->selectall_arrayref(q/select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT'/); print Dumper($df); my $s = $h->prepare(q/insert into fred values(?)/); $s->execute('23-mar-1962'); my $r = $h->selectall_arrayref(q/select * from fred/); print Dumper($r); When run using the Easysoft ODBC driver for Oracle the code runs fine with the output: $VAR1 = [ [ 'NLS_DATE_FORMAT', 'DD-MON-RR' ] ]; $VAR1 = [ [ '1962-03-23 00:00:00' ] ]; When run with Oracle's ODBC driver (v 10.2.0.1) I get: $VAR1 = [ [ 'NLS_DATE_FORMAT', 'DD-MON-RR' ] ]; DBD::ODBC::st execute failed: [unixODBC][Oracle][ODBC]General error. (SQL-HY000) [unixODBC][Oracle][ODBC]Datetime field overflow. (SQL-22008)(DBD: st_execute/SQLExecute err=-1) at ./oracle_date.pl line 15. $VAR1 = []; So I've reproduced it but it looks like a problem with Oracle's ODBC Driver.
From: dland [...] cpan.org
Download (untitled) / with headers
text/plain 436b
On Tue Aug 14 06:14:39 2007, MJEVANS wrote: Show quoted text
> This is the result of my investigation so far. I don't have Oracle on > Windows at the moment and I cannot successfully build DBD::ODBC 1.14 for > ActiveState so I've experimented on Linux.
Thanks very much for your efforts so far. I have been on holidays and now am at YAPC Europe. I should be able to give you the information you ask for when I get back to work next week. Thanks, David
From: dland [...] cpan.org
Download (untitled) / with headers
text/plain 782b
On Tue Aug 14 06:14:39 2007, MJEVANS wrote: [...] Show quoted text
> When run with Oracle's ODBC driver (v 10.2.0.1) I get: > > $VAR1 = [ > [ > 'NLS_DATE_FORMAT', > 'DD-MON-RR' > ] > ]; > DBD::ODBC::st execute failed: [unixODBC][Oracle][ODBC]General error. > (SQL-HY000) > [unixODBC][Oracle][ODBC]Datetime field overflow. (SQL-22008)(DBD: > st_execute/SQLExecute err=-1) at ./oracle_date.pl line 15. > $VAR1 = []; > > So I've reproduced it but it looks like a problem with Oracle's ODBC
Driver. Right. It's exactly this problem. I am Oracle's ODBC driver (using the 10.02.00.02 version of "Oracle in OraDb10g_home1"). I have a couple of other ODBC drivers for Oracle on the system, so I'll see if I have any more luck with them. Thanks, David
Download (untitled) / with headers
text/plain 1.3k
On Tue Aug 14 06:14:39 2007, MJEVANS wrote: Show quoted text
> This is the result of my investigation so far. I don't have Oracle on > Windows at the moment and I cannot successfully build DBD::ODBC 1.14 for > ActiveState so I've experimented on Linux.
This is very strange. I tried using the Microsoft ODBC driver for Oracle, and had exactly the same problem: DBD::ODBC::st execute failed: [Microsoft][ODBC driver for Oracle]Error in parameter 1: Datetime field overflow (SQL-22007)(DBD: st_execute/SQLExecute err=-1) at date.pl line 18. This would occur even with dates like 02/06/2007, which may be construed as valid dates both in MM/DD/YYYY and DD/MM/YYYY, even if they do not mean the same thing. I then tried nls_date_format='YYYY/MM/DD' and to my utter surprise... IT WORKS! better still, 2007/02/06 is indeed interpreted as the 6th of February. I don't pretend to understand what is going on, but you may wish to add a "Notes on dates in Oracle" section, and mention that if people are running into this problem, setting the nls_date_format this way may be a viable workaround. This may also entail having to explicitly format date output in select statements, e.g., select to_char(my_date, 'DD/MM/YYYY' ... but, it's a small price to pay if you're doing several thousand inserts which would otherwise trash the library cache. Thanks, David
From: martin.evans [...] easysoft.com
Download (untitled) / with headers
text/plain 482b
One last question. Can you tell me the output of the following SQL on the Oracle you were using before you make any changes to NLS settings: select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT' select name,type,value,isdefault from v$parameter where name = 'nls_date_format' I'm thinking it only fails with date formats containing RR in them. If you can let me know the output of above I will either find a solution or document. Thanks for your patience. Martin
From: dland [...] cpan.org
Download (untitled) / with headers
text/plain 728b
On Mon Oct 08 11:12:30 2007, MJEVANS wrote: Show quoted text
> One last question. > > Can you tell me the output of the following SQL on the Oracle you were > using before you make any changes to NLS settings: > > select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT'
NLS_DATE_FORMAT DD/MM/RR Show quoted text
> select name,type,value,isdefault from v$parameter where name = > 'nls_date_format'
nls_date_format 2 TRUE (I had to run this latter command as SYSTEM). Show quoted text
> I'm thinking it only fails with date formats containing RR in them. If > you can let me know the output of above I will either find a solution or > document.
Well there's certainly an RR in there... Show quoted text
> Thanks for your patience.
No sir, thank-you for yours :) Show quoted text
> Martin
Download (untitled) / with headers
text/plain 307b
Thanks for the latest information David. I have just committed some test code and a FAQ entry to subversion which explains this issue and it will be included in the next release. I know we have not actually got date formats including RR to work but we have found a solution so I will be closing this issue.


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.