RE: Prepare, SQL query with to_date call



-----Original Message-----
From: Martin Evans [mailto:martin.evans@xxxxxxxxxxxx]
Sent: Tuesday, June 02, 2009 8:59 PM
To: dbi-users@xxxxxxxx
Subject: Re: Prepare, SQL query with to_date call

Martin Evans wrote:
Martin Evans wrote:
Dhanashri Bhate wrote:
-----Original Message-----
From: Martin Evans [mailto:martin.evans@xxxxxxxxxxxx]
Sent: Tuesday, June 02, 2009 5:44 PM
To: dbi-users@xxxxxxxx
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:scoles@xxxxxxxxxxx]
Sent: Tuesday, June 02, 2009 4:30 PM
To: Dhanashri Bhate
Cc: dbi-users@xxxxxxxx
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@xxxxxxxx
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;
};
[Dhanashri>] Thanks Martin,
This helps a lot. And using ODBC date format makes sense too.



I am now convinced this is NOT a bug in DBD::ODBC.



Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

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.
.

Relevant Pages

  • Re: Prepare, SQL query with to_date call
    ... SQL query with to_date call ... The Oracle ODBC driver has a problem with date parameters although I'm ... using a date format containing the RR format. ...
    (perl.dbi.users)
  • Re: Prepare, SQL query with to_date call
    ... SQL query with to_date call ... and using Oracle ODBC dsn. ... The Oracle ODBC driver has a problem with date parameters although I'm ... using a date format containing the RR format. ...
    (perl.dbi.users)
  • Re: Date Formats
    ... tables of SQL Server. ... I had problem in exporting these fields into Oracle. ... > I presume that the problem is that Oracle doesn't interpret the datetime values from SQL server ... > a format, hence trying to enforce a format for that datatype is meaningless. ...
    (microsoft.public.sqlserver.programming)
  • Re: No one could logon to productio database for a while
    ... Puget Sound Oracle Users Group ... code for script is quite simple, it does a v$sql to find all sql ... The script ask for the hash value of the SQL statement. ... col "SLAVE SQL" format A95 WORD_WRAP ...
    (comp.databases.oracle.server)
  • Re: Prepare, SQL query with to_date call
    ... SQL query with to_date call ... Well when I tried with properly formatted string for a date field I still got Error - ... and using Oracle ODBC dsn. ... using a date format containing the RR format. ...
    (perl.dbi.users)