ADO request mistery
in [Oracle]
|
From: Wilfrid on 20 Jun 2008 06:32 Hello, I am running an application on a client machine (server 2003 sp2) that uses the oledb driver to execute ado command (driver ODAC1020221.exe). It connects to an oracle server 10.2.0.1.0 installed on a windows server 2003R2 SP2. The oracle has nls_language AMERICAN and nls_territory. The application is regularly executing an update command. Some of the values are dates formatted as DD/MM/YYYY. The application then writes a variant of date type. Sometimes, oracle returns the error message: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 When i check what data the application has sent i can see: 732531;0;20/06/2008 08:46:58;20/06/2008 08:47:00;title;type;19/06/2008 00:00:00;69;349;349;0;0;5;;2;1;20/06/2008 08:46:13; One of the date is apparently taken as 0 event though the date we send has a normal format. In oracle 10, the view v$sql does not show the data inserted (only the variable of the update command. Now, this error started to appear few month ago (after installation of the system), on the same day we might have 3 or 4 errors (some requests worked some others not). We changed the regional settings of the 2 windows server (to US). It stopped for a little while and then re appeared again. What I do not understand is why it is working fine for weeks and then the errors appears from time to time with no apparent patterns. If anyone has an idea, please do not hesitate to share them with me! thanks in advance
From: Charles Hooper on 20 Jun 2008 08:03 On Jun 20, 6:32 am, "Wilfrid" <grill...(a)yahoo.com> wrote: > Hello, > > I am running an application on a client machine (server 2003 sp2) that uses > the oledb driver to execute ado command (driver ODAC1020221.exe). > It connects to an oracle server 10.2.0.1.0 installed on a windows server > 2003R2 SP2. The oracle has nls_language AMERICAN and nls_territory. > > The application is regularly executing an update command. Some of the values > are dates formatted as DD/MM/YYYY. The application then writes a variant of > date type. > Sometimes, oracle returns the error message: ORA-01841: (full) year must be > between -4713 and +9999, and not be 0 > When i check what data the application has sent i can see: > > 732531;0;20/06/2008 08:46:58;20/06/2008 08:47:00;title;type;19/06/2008 > 00:00:00;69;349;349;0;0;5;;2;1;20/06/2008 08:46:13; > > One of the date is apparently taken as 0 event though the date we send has a > normal format. > In oracle 10, the view v$sql does not show the data inserted (only the > variable of the update command. > > Now, this error started to appear few month ago (after installation of the > system), on the same day we might have 3 or 4 errors (some requests worked > some others not). We changed the regional settings of the 2 windows server > (to US). It stopped for a little while and then re appeared again. > > What I do not understand is why it is working fine for weeks and then the > errors appears from time to time with no apparent patterns. > If anyone has an idea, please do not hesitate to share them with me! > > thanks in advance I see a similar problem with one of the computers in our IT group when literal values with implicit type conversion are used rather than bind variables. I have not spent much time tracking down the problem - it may be a result of using an ODBC connection with ADO, or NOT using ODBC when using ADO directly against the database. Such a problem is an excellent excuse to implement the proper method using bind variables. If, for some reason, you cannot use bind variables, modify the SQL statement to perform an explicit data type conversion: INSERT INTO ... TO_DATE('20/06/2008','DD/MM/YYYY') ... Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: Wilfrid on 20 Jun 2008 10:40 We do not send a sql request, we are using an ado record set in which we define for which column of the table we will put which data. "Charles Hooper" <hooperc2000(a)yahoo.com> wrote in message news:8a769e8d-b98f-4111-8a68-d6167cfc6b81(a)d1g2000hsg.googlegroups.com... On Jun 20, 6:32 am, "Wilfrid" <grill...(a)yahoo.com> wrote: > Hello, > > I am running an application on a client machine (server 2003 sp2) that > uses > the oledb driver to execute ado command (driver ODAC1020221.exe). > It connects to an oracle server 10.2.0.1.0 installed on a windows server > 2003R2 SP2. The oracle has nls_language AMERICAN and nls_territory. > > The application is regularly executing an update command. Some of the > values > are dates formatted as DD/MM/YYYY. The application then writes a variant > of > date type. > Sometimes, oracle returns the error message: ORA-01841: (full) year must > be > between -4713 and +9999, and not be 0 > When i check what data the application has sent i can see: > > 732531;0;20/06/2008 08:46:58;20/06/2008 08:47:00;title;type;19/06/2008 > 00:00:00;69;349;349;0;0;5;;2;1;20/06/2008 08:46:13; > > One of the date is apparently taken as 0 event though the date we send has > a > normal format. > In oracle 10, the view v$sql does not show the data inserted (only the > variable of the update command. > > Now, this error started to appear few month ago (after installation of the > system), on the same day we might have 3 or 4 errors (some requests worked > some others not). We changed the regional settings of the 2 windows server > (to US). It stopped for a little while and then re appeared again. > > What I do not understand is why it is working fine for weeks and then the > errors appears from time to time with no apparent patterns. > If anyone has an idea, please do not hesitate to share them with me! > > thanks in advance I see a similar problem with one of the computers in our IT group when literal values with implicit type conversion are used rather than bind variables. I have not spent much time tracking down the problem - it may be a result of using an ODBC connection with ADO, or NOT using ODBC when using ADO directly against the database. Such a problem is an excellent excuse to implement the proper method using bind variables. If, for some reason, you cannot use bind variables, modify the SQL statement to perform an explicit data type conversion: INSERT INTO ... TO_DATE('20/06/2008','DD/MM/YYYY') ... Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: Frank van Bortel on 25 Jun 2008 03:46 Wilfrid wrote: > Sometimes, oracle returns the error message: ORA-01841: (full) year must be > between -4713 and +9999, and not be 0 > When i check what data the application has sent i can see: > > 732531;0;20/06/2008 08:46:58;20/06/2008 08:47:00;title;type;19/06/2008 > 00:00:00;69;349;349;0;0;5;;2;1;20/06/2008 08:46:13; Where's the year, then? > > One of the date is apparently taken as 0 event though the date we send has a > normal format. There's no such thing as "normal"; US based systems -as yours- think a date format of MM/DD/YYYY is normal. Europeans know the standard is DD-MM-YYYY. You have a crappy system, that replies in implicit date conversions. Always use explicit conversions: to_date(variable,'Date Format') -- Regards, Frank van Bortel
|
Pages: 1 Prev: Role/Security Question Next: Listing successfull RMAN backups from RC |