Problems with Oracle Database
Locked
 
|
We developed our OfBiz application on a mysql database and all works fine.
Deploying the application on the client server with Oracle database I got exceptions with the sandbox: "ORA-01861: literal does not match format string" This is a problem with the date conversion. On MySql, the following SQL Statement is used SELECT .... WHERE RUN_TIME <= '2007-05-15 07:45:10.875' AND .... Using an Oracle database, this statement is wrong and generates the ORA-01861 error. The statement should look like this: SELECT .... WHERE RUN_TIME <= TO_DATE('2007-05-15 07:45:10', 'YYYY-MM-DD HH:MI:SS') AND .... It works fine if the query is generated with the PreparedStatement of the driver. But why the hell is this not always the case??? The call of delegator.storeByCondition("JobSandbox", updateFields, mainCondition); is creating a where statement BY HAND!!! (And of course this statement is not generic and does not work with a oracle database). Michael PS: Probably I will fix this in my ofbiz version, but that will be a big redesign (I don't think I can create a patch out of this. It's a redesign!). |
Re: Problems with Oracle Database
|
|
Michael,
have a look at these notes: http://docs.ofbiz.org/x/gAI let me know if they help you and if you have additional suggestions for OFBiz&Oracle Jacopo Michael Imhof wrote: > We developed our OfBiz application on a mysql database and all works fine. > Deploying the application on the client server with Oracle database I got > exceptions with > the sandbox: > "ORA-01861: literal does not match format string" > > This is a problem with the date conversion. > On MySql, the following SQL Statement is used > SELECT .... WHERE RUN_TIME <= '2007-05-15 07:45:10.875' AND .... > Using an Oracle database, this statement is wrong and generates the > ORA-01861 error. > The statement should look like this: > SELECT .... WHERE RUN_TIME <= TO_DATE('2007-05-15 07:45:10', > 'YYYY-MM-DD HH:MI:SS') AND .... > > It works fine if the query is generated with the PreparedStatement of the > driver. > But why the hell is this not always the case??? > > The call of > delegator.storeByCondition("JobSandbox", updateFields, mainCondition); > is creating a where statement BY HAND!!! (And of course this statement is > not generic and does not > work with a oracle database). > > Michael > > PS: Probably I will fix this in my ofbiz version, but that will be a big > redesign (I don't think I can create a patch > out of this. It's a redesign!). |
Re: Problems with Oracle Database
|
|
Hi Jacopo,
I saw this note before, but I don't want to install a trigger on the client database. I would like to fix the cause and not the problem!! I implemented a solution based on the design hibernate is using. In hibernate you have to define in a xml file wich dialect you're using. We don't have this definition, but I'm using the drivername declared in the entityengine.xml to find the dialect to use. In GenericDAO I read the corresponding dialect instance and every value is passed through this dialect instance (every dialect implements a method "toSqlString(Object value)"). The where string values are build in the SqlJdbcUtil class, so that's the point where I finally use my dialect class. If you look at SqlJdbcUtil.addValueSingle(..): buffer.append('\'').append(value).append('\''); is now: Object sqlValue = value; if (dialect != null) { sqlValue = dialect.toSqlString(value); } buffer.append(sqlValue); The instance dialect is passed as a parameter to the method. At the moment I only implemented a OracleDialect class. All the other drivers are using the default dialect wich is returning: return '\'' +value '\''; Seems to work fine... Michael
|
Re: Problems with Oracle Database
Administrator
|
Michael,
Would you be interested by contributing a patch for this ? It does not mean that it will be commited but at least the idea will be there, opened to everybody... Jacques ----- Message d'origine ----- De : "Michael Imhof" <[hidden email]> À : <[hidden email]> Envoyé : mardi 15 mai 2007 10:05 Objet : Re: Problems with Oracle Database > > Hi Jacopo, > I saw this note before, but I don't want to install a trigger on the client > database. I would like > to fix the cause and not the problem!! > > I implemented a solution based on the design hibernate is using. In > hibernate you have to define > in a xml file wich dialect you're using. We don't have this definition, but > I'm using the drivername > declared in the entityengine.xml to find the dialect to use. > > In GenericDAO I read the corresponding dialect instance and every value is > passed through this dialect > instance (every dialect implements a method "toSqlString(Object value)"). > The where string values are build in the SqlJdbcUtil class, so that's the > point where I finally use my > dialect class. > If you look at SqlJdbcUtil.addValueSingle(..): > buffer.append('\'').append(value).append('\''); > is now: > Object sqlValue = value; > if (dialect != null) { > sqlValue = dialect.toSqlString(value); > } > buffer.append(sqlValue); > The instance dialect is passed as a parameter to the method. At the > only implemented a > OracleDialect class. All the other drivers are using the default dialect > wich is returning: > return '\'' +value '\''; > > Seems to work fine... > Michael > > > Jacopo Cappellato wrote: > > > > Michael, > > > > have a look at these notes: > > > > http://docs.ofbiz.org/x/gAI > > > > let me know if they help you and if you have additional suggestions > > OFBiz&Oracle > > > > Jacopo > > > > Michael Imhof wrote: > >> We developed our OfBiz application on a mysql database and all works > >> fine. > >> Deploying the application on the client server with Oracle database I got > >> exceptions with > >> the sandbox: > >> "ORA-01861: literal does not match format string" > >> > >> This is a problem with the date conversion. > >> On MySql, the following SQL Statement is used > >> SELECT .... WHERE RUN_TIME <= '2007-05-15 07:45:10.875' AND .... > >> Using an Oracle database, this statement is wrong and generates the > >> ORA-01861 error. > >> The statement should look like this: > >> SELECT .... WHERE RUN_TIME <= TO_DATE('2007-05-15 07:45:10', > >> 'YYYY-MM-DD HH:MI:SS') AND .... > >> > >> It works fine if the query is generated with the PreparedStatement of the > >> driver. > >> But why the hell is this not always the case??? > >> > >> The call of > >> delegator.storeByCondition("JobSandbox", updateFields, > >> mainCondition); > >> is creating a where statement BY HAND!!! (And of course this statement is > >> not generic and does not > >> work with a oracle database). > >> > >> Michael > >> > >> PS: Probably I will fix this in my ofbiz version, but that will be a big > >> redesign (I don't think I can create a patch > >> out of this. It's a redesign!). > > > > > > > > > > -- > View this message in context: http://www.nabble.com/Problems-with-Oracle-Database-tf3756432.html#a10618443 > Sent from the OFBiz - User mailing list archive at Nabble.com. |
|
|
In reply to this post by Michael Imhof
If you run below statement no need for to_date wrap:ALTER SYSTEM set NLS_TIMESTAMP_FORMAT='RRRR-MM-DD HH24:MI:SS.FF' SCOPE = SPFILEAlex> Date: Fri, 18 May 2007 00:24:03 +0200> From: [hidden email]> Subject: Re: Problems with Oracle Database> To: [hidden email]> > Michael,> > Would you be interested by contributing a patch for this ? It does not> mean that it will be commited but at least the idea will be there,> opened to everybody...> > Jacques> > ----- Message d'origine ----- > De : "Michael Imhof" <[hidden email]>> À : <[hidden email]>> Envoyé : mardi 15 mai 2007 10:05> Objet : Re: Problems with Oracle Database> > > >> > Hi Jacopo,> > I saw this note before, but I don't want to install a trigger on the> client> > database. I would like> > to fix the cause and not the problem!!> >> > I implemented a solution based on the design hibernate is using. In> > hibernate you have to define> > in a xml file wich dialect you're using. We don't have this> definition, but> > I'm using the drivername> > declared in the entityengine.xml to find the dialect to use.> >> > In GenericDAO I read the corresponding dialect instance and every> value is> > passed through this dialect> > instance (every dialect implements a method "toSqlString(Object> value)").> > The where string values are build in the SqlJdbcUtil class, so that's> the> > point where I finally use my> > dialect class.> > If you look at SqlJdbcUtil.addValueSingle(..):> > buffer.append('\'').append(value).append('\'');> > is now:> > Object sqlValue = value;> > if (dialect != null) {> > sqlValue = dialect.toSqlString(value);> > }> > buffer.append(sqlValue);> > The instance dialect is passed as a parameter to the method. At the> moment I> > only implemented a> > OracleDialect class. All the other drivers are using the default> dialect> > wich is returning:> > return '\'' +value '\'';> >> > Seems to work fine...> > Michael> >> >> > Jacopo Cappellato wrote:> > >> > > Michael,> > >> > > have a look at these notes:> > >> > > http://docs.ofbiz.org/x/gAI> > >> > > let me know if they help you and if you have additional suggestions> for> > > OFBiz&Oracle> > >> > > Jacopo> > >> > > Michael Imhof wrote:> > >> We developed our OfBiz application on a mysql database and all> works> > >> fine.> > >> Deploying the application on the client server with Oracle database> I got> > >> exceptions with> > >> the sandbox:> > >> "ORA-01861: literal does not match format string"> > >>> > >> This is a problem with the date conversion.> > >> On MySql, the following SQL Statement is used> > >> SELECT .... WHERE RUN_TIME <= '2007-05-15 07:45:10.875'> AND ....> > >> Using an Oracle database, this statement is wrong and generates the> > >> ORA-01861 error.> > >> The statement should look like this:> > >> SELECT .... WHERE RUN_TIME <= TO_DATE('2007-05-15> 07:45:10',> > >> 'YYYY-MM-DD HH:MI:SS') AND ....> > >>> > >> It works fine if the query is generated with the PreparedStatement> of the> > >> driver.> > >> But why the hell is this not always the case???> > >>> > >> The call of> > >> delegator.storeByCondition("JobSandbox", updateFields,> > >> mainCondition);> > >> is creating a where statement BY HAND!!! (And of course this> statement is> > >> not generic and does not> > >> work with a oracle database).> > >>> > >> Michael> > >>> > >> PS: Probably I will fix this in my ofbiz version, but that will be> a big> > >> redesign (I don't think I can create a patch> > >> out of this. It's a redesign!).> > >> > >> > >> > >> >> > -- > > View this message in context:> http://www.nabble.com/Problems-with-Oracle-Database-tf3756432.html#a10618443> > Sent from the OFBiz - User mailing list archive at Nabble.com.> _________________________________________________________________ Change is good. See what’s different about Windows Live Hotmail. www.windowslive-hotmail.com/learnmore/default.html?locale=en-us&ocid=TXT_TAGLM_HMWL_reten_changegood_0507 |
Re: Problems with Oracle Database
|
|
In reply to this post by Jacques Le Roux
I contributed a patch (with state wish) to the JIRA:
https://issues.apache.org/jira/browse/OFBIZ-1001 I also added the other solutions mentioned in this thread... Michael
|
RE: Problems with Oracle Database
|
|
In reply to this post by sashash77
Thanks,
I like this solution better than the trigger solution mentioned before. But still it's not really what I want. OfBiz should adopt to the database and not vice-versa. If you look at persistent frameworks like hibernate, toplink, etc or driver classes (PreparedStatement's) they all working with every database without changing database settings! Michael PS: What's wrong with you're message format. Looks like you have no line wrapping??
|
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |