You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Michael Imhof <mi...@nowhow.ch> on 2007/05/15 07:42:17 UTC
Problems with Oracle Database
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#a10616881
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: Problems with Oracle Database
Posted by Michael Imhof <mi...@nowhow.ch>.
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
jacques.le.roux wrote:
>
> 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" <mi...@nowhow.ch>
> À : <us...@ofbiz.apache.org>
> 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.
>
>
>
--
View this message in context: http://www.nabble.com/Problems-with-Oracle-Database-tf3756432.html#a10713771
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: Problems with Oracle Database
Posted by Jacques Le Roux <ja...@les7arts.com>.
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" <mi...@nowhow.ch>
À : <us...@ofbiz.apache.org>
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.
Re: Problems with Oracle Database
Posted by Michael Imhof <mi...@nowhow.ch>.
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.
Re: Problems with Oracle Database
Posted by Jacopo Cappellato <ti...@sastau.it>.
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
Posted by Michael Imhof <mi...@nowhow.ch>.
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??
sashash77 wrote:
>
>
> 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: jacques.le.roux@les7arts.com>
> Subject: Re: Problems with Oracle Database> To: user@ofbiz.apache.org> >
> 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" <mi...@nowhow.ch>> À :
> <us...@ofbiz.apache.org>> 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
>
--
View this message in context: http://www.nabble.com/Problems-with-Oracle-Database-tf3756432.html#a10713872
Sent from the OFBiz - User mailing list archive at Nabble.com.