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.
