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.
