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.
