Martin Evans wrote:
> Martin Evans wrote:
>> Dhanashri Bhate wrote:
>>>> -----Original Message-----
>>>> From: Martin Evans [mailto:martin.ev...@easysoft.com]
>>>> Sent: Tuesday, June 02, 2009 5:44 PM
>>>> To: dbi-users@perl.org
>>>> Subject: Re: Prepare, SQL query with to_date call
>>>>
>>> Dhanashri Bhate wrote:
>>>>>> Thanks,
>>>>>> Well when I tried with properly formatted string for a date field I
>>>>>> still got Error -
>>> Datetime field overflow.
>>>>>> And the $dbh->do was just to show that it works, but $sth->prepare and
>>>>>> $sth-
>>>>>> execute doesn't.
>>>>>>
>>>>>> One more thing is, I probably shouldn't use $dbh->do in a loop since
>>>>>> I'm doing
>>> bulk insert here.
>>>>>> Any insight into why prepare is not working for date values ( with /
>>>>>> without
>>> to_date ) will be helpful,
>>>>>> Thanks!
>>>>>>
>>>>>>
>>>>>>> -----Original Message-----
>>>>>>> From: John Scoles [mailto:sco...@pythian.com]
>>>>>>> Sent: Tuesday, June 02, 2009 4:30 PM
>>>>>>> To: Dhanashri Bhate
>>>>>>> Cc: dbi-users@perl.org
>>>>>>> Subject: Re: Prepare, SQL query with to_date call
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Dhanashri Bhate wrote:
>>>>>>>> Forgot to mention, $dbh->do works, but $sth->execute doesn't.
>>>>>>>>
>>>>>>>> In the while loop, if I have the following -
>>>>>>>>
>>>>>>>> $insert_query = "INSERT INTO EMP VALUES ( $num, $name, $job, $mgr,
>>>>>>> TO_DATE ( $doj), $sal, $comm, $dept)";
>>>>>>> That query above would not be a good idea as it would open your system
>>>>>>> up to sql injection (http://en.wikipedia.org/wiki/SQL_injection)
>>>>>>> attacks. Far better to add a few extra lines of code and make it a
>>>>>>> parametrized query.
>>>>>>>
>>>>>>> By the way you may not need to use to_date on the insert you could just
>>>>>>> try a properly formated string I thin ODBC is smart enough to do the
>>>>>>> parse for you.
>>>>>>>> $dbh->do ( $insert_query );
>>>>>>>>
>>>>>>>> This works well too.
>>>>>>>>
>>>>>>>>
>>>>>>>> _____________________________________________
>>>>>>>> From: Dhanashri Bhate
>>>>>>>> Sent: Tuesday, June 02, 2009 3:28 PM
>>>>>>>> To: dbi-users@perl.org
>>>>>>>> Subject: Prepare, SQL query with to_date call
>>>>>>>>
>>>>>>>>
>>>>>>>> Hi DBI users,
>>>>>>>>
>>>>>>>> I've recently started working on Perl DBI.I'm using ActivePerl on
>>>>>>>> Windows XP,
>>>>>>> and using Oracle ODBC dsn.
>>>>>>>> My database connection, and simple select queries work fine.
>>>>>>>> The queries with $sth->prepare and with placeholders ( e.g. where
>>>>>>>> SAL>?)
>>> work
>>>>>>> well too.
>>>>>>>> I am having a problem when trying to insert date values and having a
>>>>>>>> call to
>>>>>>> to_date function in the SQL in $sth->prepare statement.
>>>>>>>> Can I do something like this? - have the to_date call as I have in the
>>>>>>>> code
>>>>>>> below? ( Without the to_date call , I get "[Oracle][ODBC]Datetime field
>>>>>>> overflow"
>>>>>>> error.)
>>>>>>>> The SQL insert statement as below , all ? s replaced with real values
>>>>>>>> works
>>> fine
>>>>>>> with SQL-Plus.
>>>>>>>> I have copied my code snippet below.
>>>>>>>>
>>>>>>>> #------------------------------------------------------------------------------------
>>>>>>>> # Read from a '~' delimited file and insert records in EMP table
>>>>>>>>
>>>>>>>> $sth = $dbh->prepare("INSERT INTO EMP VALUES (?,?,?,?,to_date(?,'DD-
>>>>>>> MON-YY'),?,?,?)"); #does this work?
>>>>>>>> open ( INFILE, "./employees.txt" ) or die ( "Cannot open the input
>>>>>>>> file\n") ;
>>>>>>>>
>>>>>>>> while(<INFILE>) {
>>>>>>>> chomp;
>>>>>>>> my ($num, $name, $job, $mgr, $doj, $sal, $comm, $dept ) =
>>>>>>>> split /~/;
>>>>>>> #split line in fields
>>>>>>>> print "Inserting values $num, $name, $job, $mgr, $doj, $sal,
>>>>>>>> $comm,
>>> $dept
>>>>>>> \n"; # this shows the values as expected
>>>>>>>> $sth->execute($num, $name, $job, $mgr, $doj, $sal, $comm,
>>>>>>>> $dept );
>>>>>>>> }
>>>>>>>> #-----------------------------------------------------------------------
>>>>>>>>
>>>>>>>> The input file
>>>>>>>> #--------------------------------
>>>>>>>> 7200~'BALAJI'~'TUTOR'~7839~'30-DEC-78'~2500~0~20
>>>>>>>> #--------------------------------
>>>>>>>>
>>>>>>>> The error
>>>>>>>> #
>>>>>>>> Inserting values 7200, 'BALAJI', 'TUTOR', 7839, '30-DEC-78', 2500, 0,
>>>>>>>> 20
>>>>>>>> DBD::ODBC::st execute failed: [Oracle][ODBC]Datetime field overflow.
>>>>>>>> (SQL-
>>>>>>> 22008) [state was 22008 now HY000]
>>>>>>>> [Oracle][ODBC]General error. (SQL-HY000) at D:\perl-work\simple_dml.pl
>>>>>>>> line
>>>>>>> 138, <INFILE> line 1.
>>>>>>>> All help/comments welcome,
>>>>>>>> Thanks,
>>>>>>>> DYB
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> DISCLAIMER
>>>>>>>> ==========
>>>>>>>> This e-mail may contain privileged and confidential information which
>>>>>>>> is the
>>>>>>> property of Persistent Systems Ltd. It is intended only for the use of
>>>>>>> the
>>> individual
>>>>>>> or entity to which it is addressed. If you are not the intended
>>>>>>> recipient, you are
>>> not
>>>>>>> authorized to read, retain, copy, print, distribute or use this
>>>>>>> message. If you
>>> have
>>>>>>> received this communication in error, please notify the sender and
>>>>>>> delete all
>>>>>>> copies of this message. Persistent Systems Ltd. does not accept any
>>>>>>> liability for
>>>>>>> virus infected mails.
>>>>>> DISCLAIMER
>>>>>> ==========
>>>>>> This e-mail may contain privileged and confidential information which is
>>>>>> the
>>> property of Persistent Systems Ltd. It is intended only for the use of the
>>> individual
>>> or entity to which it is addressed. If you are not the intended recipient,
>>> you are not
>>> authorized to read, retain, copy, print, distribute or use this message. If
>>> you have
>>> received this communication in error, please notify the sender and delete
>>> all
>>> copies of this message. Persistent Systems Ltd. does not accept any
>>> liability for
>>> virus infected mails.
>>> The Oracle ODBC driver has a problem with date parameters although I'm
>>> not that familiar with it. There is an entry in the DBD::ODBC FAQ that
>>> looks like this:
>>>
>>> =head2 Why do I get "Datetime field overflow" when attempting to insert a
>>> date into Oracle?
>>>
>>> If you are using the Oracle or Microsoft ODBC drivers then you may get
>>> the following error when inserting dates into an Oracle database:
>>>
>>> [Oracle][ODBC]Datetime field overflow. (SQL-22008)
>>>
>>> If you do then check v$nls_parameters and v$parameter to see if you are
>>> using a date format containing the RR format. e.g.,
>>>
>>> select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT'
>>> select * from v$parameter where name = 'nls_date_format'
>>>
>>> If you see a date format like 'DD-MON-RR' (e.g., contains an RR) then
>>> all I can suggest is you change the date format for your session as I
>>> have never been able to bind a date using this format. You can do this
>>> with:
>>>
>>> alter session set nls_date_format='YYYY/MM/DD'
>>>
>>> and use any format you like but keep away from 'RR'.
>>>
>>> You can find some test code in the file examples/rtcpan_28821.pl which
>>> demonstrates this problem. This was originally a rt.cpan issue which
>>> can be found at L<http://rt.cpan.org/Ticket/Display.html?id=28821>.
>>>
>>> As an aside, if anyone is reading this and can shed some light on the
>>> problem
>>> I'd love to hear from you. The technical details are:
>>>
>>> create table rtcpan28821 (a date)
>>> insert into rtcpan28821 values('23-MAR-62') fails
>>>
>>> Looking at the ODBC trace, SQLDescribeParam returns:
>>>
>>> data type: 93, SQL_TYPE_TIMESTAMP
>>> size: 19
>>> decimal digits: 0
>>> nullable: 1
>>>
>>> and DBD::ODBC calls SQLBindParameter with:
>>>
>>> ValueType: SQL_C_CHAR
>>> ParameterType: SQL_TYPE_TIMESTAMP
>>> ColumnSize: 9
>>> DecimalDigits: 0
>>> Data: 23-MAR-62
>>> BufferLength: 9
>>>
>>> =cut
>>>
>>> I might be able to find time to try this again but it would be useful if
>>> you can confirm if this FAQ applies to you.
>>>
>>> Also you might like to try the proper ODBC syntax for dates which uses
>>> { d '1990-10-02' } - see
>>> http://msdn.microsoft.com/en-us/library/ms190234(SQL.90).aspx.
>>>
>>> Martin
>>> [Dhanashri>]
>>> Thanks for the response,
>>> It is interesting to know about the 'NLS_DATE_FORMAT, it indeed is set to
>>> "DD-MON-RR".
>>> In my Perl script I tried changing this by - ALTER SESSION SET
>>> NLS_DATE_FORMAT = 'DD-MON-YY'
>>> But it still didn't work , But then again, DBI doesn't have any concept of
>>> sessions does it? I mean does this new date format apply for the next SQL
>>> executed?
>> What happens if you select the changed NLS_DATE_FORMAT back after you
>> changed it?
>>
>> DBI might not have the idea of a session but Oracle (and OCI) does so
>> I'd have expected changing NLS_DATE_FORMAT to work - are you using
>> Oracle's or Microsoft's ODBC driver or another third party driver?
>>
>>> Anyway, what I'm seeing is exactly - I cannot have dates in placeholders.
>>> But I doubt it has anything to do with the NLS_DATE_FORMAT or the actual
>>> data. This is because my $dbh->do statements with the same data are
>>> successful. Prepare and execute fail.
>>> So it must be something in the way these two have been in implemented in
>>> DBD::ODBC.
>> I will need to make time to try this myself - which is unlikely to
>> happen today as I'm already busy with another DBD::ODBC issue.
>>
>> Of course there are significant differences between do and bind/execute.
>> A do requires DBD::ODBC to do nothing other than pass your SQL across
>> in an SQLExecDirect call and with bind/execute there is loads to do.
>> DBD::ODBC has to bind your parameter with SQLBindParameter and the ODBC
>> driver has to understand and do the right thing with it. At execute
>> time, the parameter has to be sent to the database with possible type
>> conversion.
>>
>> In the example I have here the do statement works fine with a
>> NLS_DATE_FORMAT containing RR but the bound parameter does not so I
>> still think the issue is related to the NLS_DATE_FORMAT setting - we
>> will have to agree to differ until I can find time to look into this more.
>>
>> Martin
>
> Here is an example of what works currently and what doesn't + a
> workaround for you.
>
> create table martin (a datetime);
>
> use DBI;
> use strict;
>
> my $h = DBI->connect;
>
> $h->do(q{alter session set nls_date_format='DD-MON-YY'});
>
> my $s = $h->prepare(q{select * from v$nls_parameters where parameter =
> 'NLS_DATE_FORMAT'});
> $s->execute;
> print DBI::dump_results($s);
>
> my $date = '30-DEC-99';
>
> # the following works ok - resulting in 2099-12-30 being inserted
> $h->do(qq{insert into martin values ('$date')});
>
> # the following works resulting in 1999-12-30 being inserted
> $h->do(qq{insert into martin values ({d '1999-12-30'})});
>
> # fails
> eval {
> my $s = $h->prepare(q{insert into martin values(?)});
> $s->bind_param(1, $date);
> # fails
> # Numeric value out of range: invalid character in date/time string
> (SQL-22003)
> $s->execute;
> };
>
> # works
> eval {
> my $s = $h->prepare(q{insert into martin values(?)});
> $s->bind_param(1, $date, DBI::SQL_VARCHAR);
> $s->execute;
> };
>
> So you can make it work by specifically binding your dates as VARCHARs.
> It appears to be that Oracle does not like SQL_TYPE_DATE even though
> that is the type it reports the parameter to be.
>
> Martin
A further example and an explanation. I've talked with one of the
authors of Oracle ODBC drivers here and he says you should bind the date
in ODBC date syntax because that is the only date syntax the ODBC driver
has to know.
The ODBC driver will describe the parameter as a SQL_DATE or
SQL_TYPE_DATE and DBD::ODBC will therefore bind it as a SQL_TYPE_DATE.
If you use '30-DEC-99' then that means the C type is SQL_CHAR and the
SQL type is SQL_TYPE_DATE so the driver is forced to parse the date
before sending it to Oracle (that would mean knowing what your
NLS_DATE_FORMAT is and it would also mean knowing all the magic special
chrs Oracle can use to define date formats). If you override the bind
type to SQL_VARCHAR then the driver sees SQL_CHAR => SQL_VARCHAR,
nothing to do and hence Oracle itself does the translation - that is why
the SQL_VARCHAR workaround I mentioned in the last reply works.
I have not go the Oracle ODBC driver to hand right now but I'd like to
know if you change to ODBC syntax for dates if it works. Here is my
latest example:
use DBI;
use strict;
# table is "create table martin (a date, b int)"
my $h = DBI->connect;
$h->do(q{alter session set nls_date_format='DD-MON-YY'});
my $s = $h->prepare(q{select * from v$nls_parameters where parameter =
'NLS_DATE_FORMAT'});
$s->execute;
print DBI::dump_results($s);
my $date = '30-DEC-99';
my $dateodbc = qq/{ d '1999-12-30'}/;
# the following works ok - resulting in 2099-12-30 being inserted
$h->do(qq{insert into martin values ('$date', 1)});
# the following works resulting in 1999-12-30 being inserted
$h->do(qq{insert into martin values ($dateodbc, 2)});
# fails
eval {
my $s = $h->prepare(q{insert into martin values(?,3)});
$s->bind_param(1, $date);
# fails
# Numeric value out of range: invalid character in date/time string
(SQL-22003)
$s->execute;
};
# works resulting in 2099-12-30 being inserted
eval {
my $s = $h->prepare(q{insert into martin values(?,4)});
$s->bind_param(1, $date, DBI::SQL_VARCHAR);
$s->execute;
};
# works resulting in 1999-12-30 being inserted
eval {
my $s = $h->prepare(q{insert into martin values(?,5)});
$s->bind_param(1, $dateodbc);
$s->execute;
};
I am now convinced this is NOT a bug in DBD::ODBC.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
