-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 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 - -- Martin J. Evans Easysoft Limited http://www.easysoft.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFKJReihd1KVpsamNgRAodyAJwMMzGuQ6dDZbAHZ39H+ORDerxIWgCgqQ0Q PTknJeh0nmJqJVkhIC3HgRs= =a05/ -----END PGP SIGNATURE-----
