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 Brad Matlack <bm...@workzen.userworld.com> on 2004/02/19 03:13:35 UTC
Oracle date tip
Hi,
After struggling and searching for a solution to Oracle date comparisons,
I'ld like to share what I came up with.
Using the standard TIMESTAMP descriptor where "date" is a java.util.Date:
<field-descriptor
name="date"
column="audit_date"
jdbc-type="TIMESTAMP"
conversion="org.apache.ojb.broker.accesslayer.conversions.JavaDate2SqlTimest
ampFieldConversion"
/>
and the criteria:
criteria.addGreaterThan("date", fromJavaUtilDate);
or:
criteria.addGreaterThan("date", fromJavaSqlDate);
p6spy always built this where clause which failed:
A0.audit_date > '2003-01-01 00:00:00.0'
By setting the NSL_DATE_FORMAT in the init.ora to this:
nls_date_format="YYYY-MM-DD HH24:MI:SS"
we have success!
Hope that helps future Oracle developers.
Cheers,
Brad
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Out of memory error... Help please
Posted by Glauber Andrade <gl...@maximasti.com.br>.
I am getting this error, what should I do?
Thanks,
Glauber
[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] ERROR: SQLException
during the execution of the query (for a com.maximas.sinret.data.Prestador):
Memory allocation failure, message from server: "Out of memory. Restart
daemon and try again (needed 65528 bytes)"
Memory allocation failure, message from server: "Out of memory. Restart
daemon and try again (needed 65528 bytes)"
java.sql.SQLException: Memory allocation failure, message from server: "Out of memory. Restart daemon and try again (needed 65528 bytes)"
(OT?) Re: Oracle date tip
Posted by "John L. Webber" <Jo...@jentro.com>.
At the risk of wandering off-topic here, we discovered that when we
store a java.util.Date to an Oracle database using an Oracle "date", we
lost the millisecond precision of our Java data; switching the column to
an Oracle "timestamp" solved the problem. We noticed this because
various unit tests that made assumptions about a retrieved date being
before or after a reference date were failing mysteriously. BTW, we're
pretty much Oracle novices here...
John
Joe Germuska wrote:
> At 6:37 PM -0700 2/19/04, Brad Matlack wrote:
>
>> > > Oracle doesn't have a TIMESTAMP type; its "DATE" encompasses SQL
>> TIME,
>>
>>> > TIMESTAMP, and DATE.
>>>
>>>
>>> No I think you misunderstood the meaning of TIMESTAMP with Oracle in
>>> this case. With MySQL for example Timestamp column is just that a
>>> timestamp from the DB, nothing you can insert into, like an auto date
>>> column. In Oracle timestamp contains the date AND time in the field,
>>> where as the date field contains just the date.
>>
>
> I may well have... I always figured that since you can't make a
> column in an Oracle table of type "timestamp" that it was just an
> Oracle quirk. Or, to avoid sticking my neck out too far again,
> whenever I tried using "timestamp" as a column type in my Oracle
> databases, I've gotten an error.
>
> In any case, it seems like everyone who is used to writing Oracle SQL
> is used to using the "to_date" function to make sure that their String
> representations use correct date formats, and it seems like one
> shouldn't have to change a database-level setting to make OJB work.
> There should be an application-layer place to make it work.
>
> Joe
>
--
---------------------------------------------------------
Jentro Technologies GmbH
John L. Webber, Software Development
---------------------------------------------------------
Rosenheimer Str. 145e 81671 München
Tel. +49 89 189 169 80 mailto:John.Webber@jentro.com
Fax +49 89 189 169 99 http://www.jentro.com
---------------------------------------------------------
NOTICE: The information contained in this e-mail is confidential or may otherwise be legally privileged. It is intended for the named recipient only. If you have received it in error, please notify us immediately by reply or by calling the telephone number above and delete this message and all its attachments without any use or further distribution of its contents. Please note that any unauthorised review, copying, disclosing or otherwise making use of the information is strictly prohibited. Thank you.
---------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle date tip
Posted by "Robert S. Sfeir" <ro...@codepuccino.com>.
> I may well have... I always figured that since you can't make a
> column in an Oracle table of type "timestamp" that it was just an
> Oracle quirk. Or, to avoid sticking my neck out too far again,
> whenever I tried using "timestamp" as a column type in my Oracle
> databases, I've gotten an error.
>
> In any case, it seems like everyone who is used to writing Oracle SQL
> is used to using the "to_date" function to make sure that their String
> representations use correct date formats, and it seems like one
> shouldn't have to change a database-level setting to make OJB work.
> There should be an application-layer place to make it work.
Actually, at the risk of getting pelted with tomatoes... Since my work
is pretty much always in Java, and Java and dates/sql date are a pain to
deal with/convert sometimes, I just convert all my dates to Long(s)
(time in millis) and store them in the DB as such. For Oracle I use
NUMBER, for Postgres Integer etc... So when I get the data back, I
don't have to do anything DB special, I just convert the date from Long
to a Date String and pass it to the view, the conversion requires less
steps, and is more consistent.. This also makes it very cool to
calculate date diffs etc, and I find the app to be much more portable
between DBs.
It might be different, and perhaps unconventional, but it's worked like
a charm.
R
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle date tip
Posted by Joe Germuska <Jo...@Germuska.com>.
At 6:37 PM -0700 2/19/04, Brad Matlack wrote:
> > > Oracle doesn't have a TIMESTAMP type; its "DATE" encompasses SQL TIME,
>> > TIMESTAMP, and DATE.
>>
>>
>> No I think you misunderstood the meaning of TIMESTAMP with Oracle in
>> this case. With MySQL for example Timestamp column is just that a
>> timestamp from the DB, nothing you can insert into, like an auto date
>> column. In Oracle timestamp contains the date AND time in the field,
>> where as the date field contains just the date.
I may well have... I always figured that since you can't make a
column in an Oracle table of type "timestamp" that it was just an
Oracle quirk. Or, to avoid sticking my neck out too far again,
whenever I tried using "timestamp" as a column type in my Oracle
databases, I've gotten an error.
In any case, it seems like everyone who is used to writing Oracle SQL
is used to using the "to_date" function to make sure that their
String representations use correct date formats, and it seems like
one shouldn't have to change a database-level setting to make OJB
work. There should be an application-layer place to make it work.
Joe
--
Joe Germuska
Joe@Germuska.com
http://blog.germuska.com
"Imagine if every Thursday your shoes exploded if you tied them
the usual way. This happens to us all the time with computers, and
nobody thinks of complaining."
-- Jef Raskin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle date tip
Posted by Brad Matlack <bm...@workzen.userworld.com>.
Display Oracle NLS settings with "select * from nls_database_parameters;"
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P15
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET WE8ISO8859P15
NLS_RDBMS_VERSION 8.1.7.0.0
----- Original Message -----
From: "Robert S. Sfeir" <ro...@codepuccino.com>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Thursday, February 19, 2004 17:23
Subject: Re: Oracle date tip
> Joe Germuska wrote:
>
> > At 6:02 PM -0500 2/19/04, Andy Malakov wrote:
> >
> >> Brad,
> >>
> >> I do not think it is OJB/JDBC problem.
> >>
> >> What is strange about your mail is the fact that NLS_DATE_FORMAT
> >> fixed the problem - instead of NLS_TIMESTAMP_FORMAT. Since you
> >> applied JavaDate2SqlTimes converter - all your dates at JDBC level
> >> should have been converted to java.sql.Timestamp.
> >>
> >> Please do not be confused by the way P6SPY displays generated JDBC
> >> query (it may be approximate).
> >
> >
> > Oracle doesn't have a TIMESTAMP type; its "DATE" encompasses SQL TIME,
> > TIMESTAMP, and DATE.
>
>
> No I think you misunderstood the meaning of TIMESTAMP with Oracle in
> this case. With MySQL for example Timestamp column is just that a
> timestamp from the DB, nothing you can insert into, like an auto date
> column. In Oracle timestamp contains the date AND time in the field,
> where as the date field contains just the date.
>
> R
>
> ---------------------------------------------------------------------
> 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 date tip
Posted by "Robert S. Sfeir" <ro...@codepuccino.com>.
Joe Germuska wrote:
> At 6:02 PM -0500 2/19/04, Andy Malakov wrote:
>
>> Brad,
>>
>> I do not think it is OJB/JDBC problem.
>>
>> What is strange about your mail is the fact that NLS_DATE_FORMAT
>> fixed the problem - instead of NLS_TIMESTAMP_FORMAT. Since you
>> applied JavaDate2SqlTimes converter - all your dates at JDBC level
>> should have been converted to java.sql.Timestamp.
>>
>> Please do not be confused by the way P6SPY displays generated JDBC
>> query (it may be approximate).
>
>
> Oracle doesn't have a TIMESTAMP type; its "DATE" encompasses SQL TIME,
> TIMESTAMP, and DATE.
No I think you misunderstood the meaning of TIMESTAMP with Oracle in
this case. With MySQL for example Timestamp column is just that a
timestamp from the DB, nothing you can insert into, like an auto date
column. In Oracle timestamp contains the date AND time in the field,
where as the date field contains just the date.
R
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle date tip
Posted by Brendan Boesen <bb...@tpg.com.au>.
Ok, maybe I HAVE posted this to the list.
(Mental note: don't send emails after lunch at the pub...)
Brendan
----- Original Message -----
From: "Joe Germuska" <Jo...@Germuska.com>
To: "OJB Users List" <oj...@db.apache.org>
Cc: "Brad Matlack" <bm...@workzen.userworld.com>
Sent: Friday, February 20, 2004 10:41 AM
Subject: Re: Oracle date tip
> At 6:02 PM -0500 2/19/04, Andy Malakov wrote:
> >Brad,
> >
> >I do not think it is OJB/JDBC problem.
> >
> >What is strange about your mail is the fact that NLS_DATE_FORMAT
> >fixed the problem - instead of NLS_TIMESTAMP_FORMAT. Since you
> >applied JavaDate2SqlTimes converter - all your dates at JDBC level
> >should have been converted to java.sql.Timestamp.
> >
> >Please do not be confused by the way P6SPY displays generated JDBC
> >query (it may be approximate).
>
> Oracle doesn't have a TIMESTAMP type; its "DATE" encompasses SQL
> TIME, TIMESTAMP, and DATE.
>
> Joe
> --
> Joe Germuska
> Joe@Germuska.com
> http://blog.germuska.com
> "Imagine if every Thursday your shoes exploded if you tied them
> the usual way. This happens to us all the time with computers, and
> nobody thinks of complaining."
> -- Jef Raskin
>
> ---------------------------------------------------------------------
> 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 date tip
Posted by Joe Germuska <Jo...@Germuska.com>.
At 2:37 PM +1100 2/20/04, Brendan Boesen wrote:
>Joe,
>
>I haven't posted this to the list because I'm not sure it's entirely
>relevant to your specific need. What I found I had to do with Oracle
>TIMESTAMPs to get them to correctly deal with min, hours, secs, etc. was to:
>A. write my own converter and
>B. extend the PlatformOracle9iImpl class to make sure I was calling
>setTIMESTAMP on a OraclePreparedStatement (in setObjectForStatement).
>
>All this was with the vanilla rc5 release.
>
>If you think this would help, I can post you the code. I had a post on the
>OJB-DEV list about this and Armin (I think) suggested I post tests and a
>fix. Unfortunately I haven't had time to do this.
>
>Hoping this may help,
For what it's worth, Brad M is the one who started this thread; for
now, I haven't been victim to the problem -- I was just saying that
there should be an OJB way to fix it without changing the database
configs (and potentially disrupting other applications which liked
the database config the way it was.) I just haven't learned the OJB
internals well enough to know where that goes.
It sounds like you've found that method. Maybe you can post a
bug/enhancement request that at least contains your code, so that
anyone who needs it might find it, and if you don't have time to
flesh it out, another OJB contributor would have something to start
from.
We still use Oracle 8; is your fix 9i specific, or could it go in the
base Oracle platform class? Why did you have to write a custom
converter?
Thanks for the info.
joe
--
Joe Germuska
Joe@Germuska.com
http://blog.germuska.com
"Imagine if every Thursday your shoes exploded if you tied them
the usual way. This happens to us all the time with computers, and
nobody thinks of complaining."
-- Jef Raskin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle date tip
Posted by Brendan Boesen <bb...@tpg.com.au>.
Joe,
I haven't posted this to the list because I'm not sure it's entirely
relevant to your specific need. What I found I had to do with Oracle
TIMESTAMPs to get them to correctly deal with min, hours, secs, etc. was to:
A. write my own converter and
B. extend the PlatformOracle9iImpl class to make sure I was calling
setTIMESTAMP on a OraclePreparedStatement (in setObjectForStatement).
All this was with the vanilla rc5 release.
If you think this would help, I can post you the code. I had a post on the
OJB-DEV list about this and Armin (I think) suggested I post tests and a
fix. Unfortunately I haven't had time to do this.
Hoping this may help,
Brendan
----- Original Message -----
From: "Joe Germuska" <Jo...@Germuska.com>
To: "OJB Users List" <oj...@db.apache.org>
Cc: "Brad Matlack" <bm...@workzen.userworld.com>
Sent: Friday, February 20, 2004 10:41 AM
Subject: Re: Oracle date tip
> At 6:02 PM -0500 2/19/04, Andy Malakov wrote:
> >Brad,
> >
> >I do not think it is OJB/JDBC problem.
> >
> >What is strange about your mail is the fact that NLS_DATE_FORMAT
> >fixed the problem - instead of NLS_TIMESTAMP_FORMAT. Since you
> >applied JavaDate2SqlTimes converter - all your dates at JDBC level
> >should have been converted to java.sql.Timestamp.
> >
> >Please do not be confused by the way P6SPY displays generated JDBC
> >query (it may be approximate).
>
> Oracle doesn't have a TIMESTAMP type; its "DATE" encompasses SQL
> TIME, TIMESTAMP, and DATE.
>
> Joe
> --
> Joe Germuska
> Joe@Germuska.com
> http://blog.germuska.com
> "Imagine if every Thursday your shoes exploded if you tied them
> the usual way. This happens to us all the time with computers, and
> nobody thinks of complaining."
> -- Jef Raskin
>
> ---------------------------------------------------------------------
> 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 date tip
Posted by Andy Malakov <an...@transdecisions.com>.
> Oracle doesn't have a TIMESTAMP type; its "DATE" encompasses SQL
> TIME, TIMESTAMP, and DATE.
What do you mean?
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#54201
http://www.databasejournal.com/features/oracle/article.php/2234501
----- Original Message -----
From: "Joe Germuska" <Jo...@Germuska.com>
To: "OJB Users List" <oj...@db.apache.org>
Cc: "Brad Matlack" <bm...@workzen.userworld.com>
Sent: Thursday, February 19, 2004 6:41 PM
Subject: Re: Oracle date tip
> At 6:02 PM -0500 2/19/04, Andy Malakov wrote:
> >Brad,
> >
> >I do not think it is OJB/JDBC problem.
> >
> >What is strange about your mail is the fact that NLS_DATE_FORMAT
> >fixed the problem - instead of NLS_TIMESTAMP_FORMAT. Since you
> >applied JavaDate2SqlTimes converter - all your dates at JDBC level
> >should have been converted to java.sql.Timestamp.
> >
> >Please do not be confused by the way P6SPY displays generated JDBC
> >query (it may be approximate).
>
> Oracle doesn't have a TIMESTAMP type; its "DATE" encompasses SQL
> TIME, TIMESTAMP, and DATE.
>
> Joe
> --
> Joe Germuska
> Joe@Germuska.com
> http://blog.germuska.com
> "Imagine if every Thursday your shoes exploded if you tied them
> the usual way. This happens to us all the time with computers, and
> nobody thinks of complaining."
> -- Jef Raskin
>
> ---------------------------------------------------------------------
> 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 date tip
Posted by Joe Germuska <Jo...@Germuska.com>.
At 6:02 PM -0500 2/19/04, Andy Malakov wrote:
>Brad,
>
>I do not think it is OJB/JDBC problem.
>
>What is strange about your mail is the fact that NLS_DATE_FORMAT
>fixed the problem - instead of NLS_TIMESTAMP_FORMAT. Since you
>applied JavaDate2SqlTimes converter - all your dates at JDBC level
>should have been converted to java.sql.Timestamp.
>
>Please do not be confused by the way P6SPY displays generated JDBC
>query (it may be approximate).
Oracle doesn't have a TIMESTAMP type; its "DATE" encompasses SQL
TIME, TIMESTAMP, and DATE.
Joe
--
Joe Germuska
Joe@Germuska.com
http://blog.germuska.com
"Imagine if every Thursday your shoes exploded if you tied them
the usual way. This happens to us all the time with computers, and
nobody thinks of complaining."
-- Jef Raskin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle date tip
Posted by Andy Malakov <an...@transdecisions.com>.
Brad,
I do not think it is OJB/JDBC problem.
What is strange about your mail is the fact that NLS_DATE_FORMAT fixed the problem - instead of NLS_TIMESTAMP_FORMAT. Since you
applied JavaDate2SqlTimes converter - all your dates at JDBC level should have been converted to java.sql.Timestamp.
Please do not be confused by the way P6SPY displays generated JDBC query (it may be approximate).
----- Original Message -----
From: "Joe Germuska" <Jo...@Germuska.com>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Thursday, February 19, 2004 10:34 AM
Subject: Re: Oracle date tip
> At 8:16 AM -0700 2/19/04, Brad Matlack wrote:
> >
> >Normally, a "manual" oracle date query would look something like this:
> >A0.audit_date > TO_DATE('01-JAN-2001 00:00:00','DD-MON-YYYY HH24:MI:SS')
> >But this is not a portable query.
>
> Right -- that's what I was thinking too. I'm hoping some of the OJB
> gurus can explain to use where you'd put that in, because from
> browsing the API, it seems as if there is support for platform
> specific SQL. I just don't have time to unravel the complete path!
>
> Joe
>
> --
> Joe Germuska
> Joe@Germuska.com
> http://blog.germuska.com
> "Imagine if every Thursday your shoes exploded if you tied them
> the usual way. This happens to us all the time with computers, and
> nobody thinks of complaining."
> -- Jef Raskin
>
> ---------------------------------------------------------------------
> 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 date tip
Posted by Joe Germuska <Jo...@Germuska.com>.
At 8:16 AM -0700 2/19/04, Brad Matlack wrote:
>
>Normally, a "manual" oracle date query would look something like this:
>A0.audit_date > TO_DATE('01-JAN-2001 00:00:00','DD-MON-YYYY HH24:MI:SS')
>But this is not a portable query.
Right -- that's what I was thinking too. I'm hoping some of the OJB
gurus can explain to use where you'd put that in, because from
browsing the API, it seems as if there is support for platform
specific SQL. I just don't have time to unravel the complete path!
Joe
--
Joe Germuska
Joe@Germuska.com
http://blog.germuska.com
"Imagine if every Thursday your shoes exploded if you tied them
the usual way. This happens to us all the time with computers, and
nobody thinks of complaining."
-- Jef Raskin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Oracle date tip
Posted by Brad Matlack <bm...@workzen.userworld.com>.
Clarification:
> Also, isn't p6spy just logging? I don't think it's correct to say
> "p6spy always built...".
Yes, I meant to say that p6spy is "reporting" the query.
Also, a criteria that uses java.sql.Date results in a query of:
A0.audit_date > '2003-01-01'
while java.sql.Timestamp and java.util.Date results in a query of:
A0.audit_date > '2003-01-01 00:00:00.0'
Normally, a "manual" oracle date query would look something like this:
A0.audit_date > TO_DATE('01-JAN-2001 00:00:00','DD-MON-YYYY HH24:MI:SS')
But this is not a portable query.
Also, I'm using Oracle 8.1.6 on AIX 4.3.
The original "default" nsl_date_format was: DD-MON-RR
----- Original Message -----
From: "Joe Germuska" <Jo...@Germuska.com>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Wednesday, February 18, 2004 19:55
Subject: Re: Oracle date tip
> Thanks for the info; however, it doesn't seem right that you should
> have to set the NLS_DATE_FORMAT to cooperate with OJB!
>
> Is this a property of the JDBC driver you are using? I don't think
> I've encountered this before; I'm not an OJB wizard, but I'm pretty
> sure I've got some date-based queries.
>
> It seems like, in all the abstraction of OJB, there ought to be a
> place where you could configure this to behave correctly without
> being forced to change your database settings.
>
> Also, isn't p6spy just logging? I don't think it's correct to say
> "p6spy always built...".
>
> Just some thoughts from another OJB/Oracle user.
>
> Joe
>
>
> At 7:13 PM -0700 2/18/04, Brad Matlack wrote:
> >Hi,
> >
> >After struggling and searching for a solution to Oracle date comparisons,
> >I'ld like to share what I came up with.
> >
> >Using the standard TIMESTAMP descriptor where "date" is a java.util.Date:
> ><field-descriptor
> >name="date"
> >column="audit_date"
> >jdbc-type="TIMESTAMP"
>
>conversion="org.apache.ojb.broker.accesslayer.conversions.JavaDate2SqlTimes
t
> >ampFieldConversion"
> >/>
> >
> >and the criteria:
> >criteria.addGreaterThan("date", fromJavaUtilDate);
> >or:
> >criteria.addGreaterThan("date", fromJavaSqlDate);
> >
> >p6spy always built this where clause which failed:
> >
> >A0.audit_date > '2003-01-01 00:00:00.0'
> >
> >By setting the NSL_DATE_FORMAT in the init.ora to this:
> >
> >nls_date_format="YYYY-MM-DD HH24:MI:SS"
> >
> >we have success!
> >
> >Hope that helps future Oracle developers.
> >Cheers,
> >Brad
> >
> >
> >
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
> --
> Joe Germuska
> Joe@Germuska.com
> http://blog.germuska.com
> "Imagine if every Thursday your shoes exploded if you tied them
> the usual way. This happens to us all the time with computers, and
> nobody thinks of complaining."
> -- Jef Raskin
>
> ---------------------------------------------------------------------
> 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 date tip
Posted by Joe Germuska <Jo...@Germuska.com>.
Thanks for the info; however, it doesn't seem right that you should
have to set the NLS_DATE_FORMAT to cooperate with OJB!
Is this a property of the JDBC driver you are using? I don't think
I've encountered this before; I'm not an OJB wizard, but I'm pretty
sure I've got some date-based queries.
It seems like, in all the abstraction of OJB, there ought to be a
place where you could configure this to behave correctly without
being forced to change your database settings.
Also, isn't p6spy just logging? I don't think it's correct to say
"p6spy always built...".
Just some thoughts from another OJB/Oracle user.
Joe
At 7:13 PM -0700 2/18/04, Brad Matlack wrote:
>Hi,
>
>After struggling and searching for a solution to Oracle date comparisons,
>I'ld like to share what I came up with.
>
>Using the standard TIMESTAMP descriptor where "date" is a java.util.Date:
><field-descriptor
>name="date"
>column="audit_date"
>jdbc-type="TIMESTAMP"
>conversion="org.apache.ojb.broker.accesslayer.conversions.JavaDate2SqlTimest
>ampFieldConversion"
>/>
>
>and the criteria:
>criteria.addGreaterThan("date", fromJavaUtilDate);
>or:
>criteria.addGreaterThan("date", fromJavaSqlDate);
>
>p6spy always built this where clause which failed:
>
>A0.audit_date > '2003-01-01 00:00:00.0'
>
>By setting the NSL_DATE_FORMAT in the init.ora to this:
>
>nls_date_format="YYYY-MM-DD HH24:MI:SS"
>
>we have success!
>
>Hope that helps future Oracle developers.
>Cheers,
>Brad
>
>
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
--
Joe Germuska
Joe@Germuska.com
http://blog.germuska.com
"Imagine if every Thursday your shoes exploded if you tied them
the usual way. This happens to us all the time with computers, and
nobody thinks of complaining."
-- Jef Raskin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org