Dhanashri Bhate wrote: >> -----Original Message----- >> From: Martin Evans [mailto:martin.ev...@easysoft.com] >> Sent: Tuesday, June 02, 2009 5:44 PM >> To: dbi-users@perl.org >> 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: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
> [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 -- Martin J. Evans Easysoft Limited http://www.easysoft.com
