You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by mike jackson <mj...@cdi-hq.com> on 2004/02/03 17:36:43 UTC
oracle and dates
Datebase: Oracle 8.1.7.4 or Oracle 9.2.0.4
OJB Version: 1.0rc5
I'm having problems trying to do a search something like this:
select * from foobar where date1 >= sysdate and date1 <= sysdate +1
I've tried using the greaterThan and lessThan methods on criteria but I get
an "invalid column error" from Oracle. I thought that perhaps the
parameters weren't getting bound to the PreparedStatement properly, but I
think I've been able to rule that out. It appears that it's failing in the
setObject method of the Oracle driver. I've tried to modify the
setObjectForStatement but for some reason it's still using the setObject.
I then looked at the list archives and found something about using addSql,
so I tried that. That gave me StringOutOfBounds(-2) exceptions, with the
criteria display showing that the I added looked to be added twice with a
"null" at the end of the string (looks like this "<SQL><SQL>null"). So I've
been looking at the source for the addSql method which seems to create a
SqlCriteria, all of which looks ok to me. My guess is that the SQL
generator is getting confused or something.
I then tried setting up the criteria for the date ranges as separate
criteria. I added the second criteria to the first as an addAndCriteria,
but instead of having just one of the "<SQL><SQL>null"'s I ended up with two
of them.
Anyway what I'm looking for is someone who can give me a clue of another way
to make this work. Or suggest somewhere in the code that I haven't looked
at yet to fix. I'll happily upload the repository.xml and the code that
calls OJB if need be.
--mikej
-=------
mike jackson
mjackson@cdi-hq.com
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: oracle and dates
Posted by Andy Malakov <an...@transdecisions.com>.
Hello Mike,
I do a TIMESTAMP comparisons - works fine for me.
Check that value types that you use in query Criteria match with JDBC types of your columns
(http://db.apache.org/ojb/jdbc-types.html). And what is exact exception that you are getting?
All the Best,
Andy
----- Original Message -----
From: "mike jackson" <mj...@cdi-hq.com>
To: "'OJB Users List'" <oj...@db.apache.org>
Sent: Tuesday, February 03, 2004 11:36 AM
Subject: oracle and dates
Datebase: Oracle 8.1.7.4 or Oracle 9.2.0.4
OJB Version: 1.0rc5
I'm having problems trying to do a search something like this:
select * from foobar where date1 >= sysdate and date1 <= sysdate +1
I've tried using the greaterThan and lessThan methods on criteria but I get
an "invalid column error" from Oracle. I thought that perhaps the
parameters weren't getting bound to the PreparedStatement properly, but I
think I've been able to rule that out. It appears that it's failing in the
setObject method of the Oracle driver. I've tried to modify the
setObjectForStatement but for some reason it's still using the setObject.
I then looked at the list archives and found something about using addSql,
so I tried that. That gave me StringOutOfBounds(-2) exceptions, with the
criteria display showing that the I added looked to be added twice with a
"null" at the end of the string (looks like this "<SQL><SQL>null"). So I've
been looking at the source for the addSql method which seems to create a
SqlCriteria, all of which looks ok to me. My guess is that the SQL
generator is getting confused or something.
I then tried setting up the criteria for the date ranges as separate
criteria. I added the second criteria to the first as an addAndCriteria,
but instead of having just one of the "<SQL><SQL>null"'s I ended up with two
of them.
Anyway what I'm looking for is someone who can give me a clue of another way
to make this work. Or suggest somewhere in the code that I haven't looked
at yet to fix. I'll happily upload the repository.xml and the code that
calls OJB if need be.
--mikej
-=------
mike jackson
mjackson@cdi-hq.com
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: oracle and dates
Posted by mike jackson <mj...@cdi-hq.com>.
I thought it did that automagically. I guess I was wrong. I'll go look at
that later today.
--mikej
-=------
mike jackson
mjackson@cdi-hq.com
> -----Original Message-----
> From: Armin Waibel [mailto:arminw@apache.org]
> Sent: Tuesday, February 03, 2004 10:25 AM
> To: OJB Users List
> Subject: Re: oracle and dates
>
> Hi Mike,
>
> mike jackson wrote:
>
> > I found my problem. In line 287 of StatementManager there's a
> setObject(
> > Statement, value ) call. It was getting a java.util.Date that hadn't
> been
> > converted to a java.sql.Date. I added a check to see if the value was
> an
> > instanceof java.util.Date and to convert it to a java.sql.Date if it is.
> >
> this sounds like a field conversion problem, did you see
>
> http://db.apache.org/ojb/jdbc-types.html
>
> Describe how to use OJB 'FieldConversion' feature. There is a
> JavaDate2SqlDateFieldConversion class. Assume this will solve your
problem.
>
> regards,
> Armin
>
>
> > --mikej
> > -=------
> > mike jackson
> > mjackson@cdi-hq.com
> >
> >
> >
> >
> >>-----Original Message-----
> >>From: mike jackson [mailto:mjackson@cdi-hq.com]
> >>Sent: Tuesday, February 03, 2004 8:37 AM
> >>To: 'OJB Users List'
> >>Subject: oracle and dates
> >>
> >>Datebase: Oracle 8.1.7.4 or Oracle 9.2.0.4
> >>OJB Version: 1.0rc5
> >>
> >>I'm having problems trying to do a search something like this:
> >>
> >> select * from foobar where date1 >= sysdate and date1 <= sysdate +1
> >>
> >>I've tried using the greaterThan and lessThan methods on criteria but I
> >>get
> >>an "invalid column error" from Oracle. I thought that perhaps the
> >>parameters weren't getting bound to the PreparedStatement properly, but
> I
> >>think I've been able to rule that out. It appears that it's failing in
> >>the
> >>setObject method of the Oracle driver. I've tried to modify the
> >>setObjectForStatement but for some reason it's still using the
setObject.
> >>
> >>I then looked at the list archives and found something about using
> addSql,
> >>so I tried that. That gave me StringOutOfBounds(-2) exceptions, with
> the
> >>criteria display showing that the I added looked to be added twice with
> a
> >>"null" at the end of the string (looks like this "<SQL><SQL>null"). So
> >>I've
> >>been looking at the source for the addSql method which seems to create a
> >>SqlCriteria, all of which looks ok to me. My guess is that the SQL
> >>generator is getting confused or something.
> >>
> >>I then tried setting up the criteria for the date ranges as separate
> >>criteria. I added the second criteria to the first as an
addAndCriteria,
> >>but instead of having just one of the "<SQL><SQL>null"'s I ended up with
> >>two
> >>of them.
> >>
> >>Anyway what I'm looking for is someone who can give me a clue of another
> >>way
> >>to make this work. Or suggest somewhere in the code that I haven't
> looked
> >>at yet to fix. I'll happily upload the repository.xml and the code that
> >>calls OJB if need be.
> >>
> >>--mikej
> >>-=------
> >>mike jackson
> >>mjackson@cdi-hq.com
> >>
> >>
> >>---------------------------------------------------------------------
> >>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >>For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: oracle and dates
Posted by Armin Waibel <ar...@apache.org>.
Hi Mike,
mike jackson wrote:
> I found my problem. In line 287 of StatementManager there's a setObject(
> Statement, value ) call. It was getting a java.util.Date that hadn't been
> converted to a java.sql.Date. I added a check to see if the value was an
> instanceof java.util.Date and to convert it to a java.sql.Date if it is.
>
this sounds like a field conversion problem, did you see
http://db.apache.org/ojb/jdbc-types.html
Describe how to use OJB 'FieldConversion' feature. There is a
JavaDate2SqlDateFieldConversion class. Assume this will solve your problem.
regards,
Armin
> --mikej
> -=------
> mike jackson
> mjackson@cdi-hq.com
>
>
>
>
>>-----Original Message-----
>>From: mike jackson [mailto:mjackson@cdi-hq.com]
>>Sent: Tuesday, February 03, 2004 8:37 AM
>>To: 'OJB Users List'
>>Subject: oracle and dates
>>
>>Datebase: Oracle 8.1.7.4 or Oracle 9.2.0.4
>>OJB Version: 1.0rc5
>>
>>I'm having problems trying to do a search something like this:
>>
>> select * from foobar where date1 >= sysdate and date1 <= sysdate +1
>>
>>I've tried using the greaterThan and lessThan methods on criteria but I
>>get
>>an "invalid column error" from Oracle. I thought that perhaps the
>>parameters weren't getting bound to the PreparedStatement properly, but I
>>think I've been able to rule that out. It appears that it's failing in
>>the
>>setObject method of the Oracle driver. I've tried to modify the
>>setObjectForStatement but for some reason it's still using the setObject.
>>
>>I then looked at the list archives and found something about using addSql,
>>so I tried that. That gave me StringOutOfBounds(-2) exceptions, with the
>>criteria display showing that the I added looked to be added twice with a
>>"null" at the end of the string (looks like this "<SQL><SQL>null"). So
>>I've
>>been looking at the source for the addSql method which seems to create a
>>SqlCriteria, all of which looks ok to me. My guess is that the SQL
>>generator is getting confused or something.
>>
>>I then tried setting up the criteria for the date ranges as separate
>>criteria. I added the second criteria to the first as an addAndCriteria,
>>but instead of having just one of the "<SQL><SQL>null"'s I ended up with
>>two
>>of them.
>>
>>Anyway what I'm looking for is someone who can give me a clue of another
>>way
>>to make this work. Or suggest somewhere in the code that I haven't looked
>>at yet to fix. I'll happily upload the repository.xml and the code that
>>calls OJB if need be.
>>
>>--mikej
>>-=------
>>mike jackson
>>mjackson@cdi-hq.com
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: oracle and dates
Posted by mike jackson <mj...@cdi-hq.com>.
I found my problem. In line 287 of StatementManager there's a setObject(
Statement, value ) call. It was getting a java.util.Date that hadn't been
converted to a java.sql.Date. I added a check to see if the value was an
instanceof java.util.Date and to convert it to a java.sql.Date if it is.
--mikej
-=------
mike jackson
mjackson@cdi-hq.com
> -----Original Message-----
> From: mike jackson [mailto:mjackson@cdi-hq.com]
> Sent: Tuesday, February 03, 2004 8:37 AM
> To: 'OJB Users List'
> Subject: oracle and dates
>
> Datebase: Oracle 8.1.7.4 or Oracle 9.2.0.4
> OJB Version: 1.0rc5
>
> I'm having problems trying to do a search something like this:
>
> select * from foobar where date1 >= sysdate and date1 <= sysdate +1
>
> I've tried using the greaterThan and lessThan methods on criteria but I
> get
> an "invalid column error" from Oracle. I thought that perhaps the
> parameters weren't getting bound to the PreparedStatement properly, but I
> think I've been able to rule that out. It appears that it's failing in
> the
> setObject method of the Oracle driver. I've tried to modify the
> setObjectForStatement but for some reason it's still using the setObject.
>
> I then looked at the list archives and found something about using addSql,
> so I tried that. That gave me StringOutOfBounds(-2) exceptions, with the
> criteria display showing that the I added looked to be added twice with a
> "null" at the end of the string (looks like this "<SQL><SQL>null"). So
> I've
> been looking at the source for the addSql method which seems to create a
> SqlCriteria, all of which looks ok to me. My guess is that the SQL
> generator is getting confused or something.
>
> I then tried setting up the criteria for the date ranges as separate
> criteria. I added the second criteria to the first as an addAndCriteria,
> but instead of having just one of the "<SQL><SQL>null"'s I ended up with
> two
> of them.
>
> Anyway what I'm looking for is someone who can give me a clue of another
> way
> to make this work. Or suggest somewhere in the code that I haven't looked
> at yet to fix. I'll happily upload the repository.xml and the code that
> calls OJB if need be.
>
> --mikej
> -=------
> mike jackson
> mjackson@cdi-hq.com
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org