Re: [Wonder-disc] Difference between date and dateTime prototypes in ERPrototypes

[Ricardo J. P. <rp...@ma...>]

On Dec 11, 2009, at 8:18 AM, Jon Nolan wrote:

> Lachlan Deck wrote:
>> On 11/12/2009, at 3:31 PM, Jon Nolan wrote:
>> 
>>  
>>> Ricardo J. Parada wrote:
>>>> 
>>>> http://issues.objectstyle.org/jira/browse/WONDER-387
>>>> http://issues.objectstyle.org/jira/browse/WONDER-421
>>>> 
>>>> Thanks,
>>>> Ricardo
>>> Ricardo,
>>> 
>>> The problem with DATE v. TIMESTAMP external types has to do with Oracle versions.  Here's a reference:
>> 
>> I'd be guessing that 'Date' means the 'Date without time' as opposed to DateTime.
> That would seem to make sense but as far as I can tell that's not true beyond Oracle 8.  In 9.2 and above DATE means what you and I would consider timestamp without fractional seconds.  From the link I posted earlier:
> 
> "Prior to 9.2, the Oracle JDBC drivers mapped the DATE SQL type to java.sql.Timestamp. This made a certain amount of sense because the Oracle DATE SQL type contains both date and time information as does java.sql.Timestamp. The more obvious mapping to java.sql.Date was somewhat problematic as java.sql.Date does not include time information. It was also the case that the RDBMS did not support the TIMESTAMP SQL type, so there was no problem with mapping DATE to Timestamp.
> 
> In 9.2 TIMESTAMP support was added to the RDBMS. The difference between DATE and TIMESTAMP is that TIMESTAMP includes nanoseconds and DATE does not. So, beginning in 9.2, DATE is mapped to Date and TIMESTAMP is mapped to Timestamp. Unfortunately if you were relying on DATE values to contain time information, there is a problem. "
> 
> The only way I could get (a) the model to work with fractional seconds and (b) avoid massive tweaking of my migration classes/scripts was to change the dateTime's external type to TIMESTAMP.  Oracle 8 is not a concern for me so I'm happy going off down this route as everything is working perfectly.  If the external type needs to remain as DATE for backward migration or some other reason then perhaps a patch isn't warranted and I'll just make my changes locally.

We're using ORACLE 10.2.0.4.0

Our columns in the database are already defined as DATE.  We don't need the fractional seconds for those columns.

My question/concern is what would happen if the dateTime prototype says the externalType is changed to TIMESTAMP and the column is really a DATE in the database.  Would it still work correctly?

> When you start adding in Oracle's support for fractional second precision it gets even trickier.  i.e.  TIMESTAMP(6), etc.

Didn't know you could do that.  :-)

> I thought about creating my own prototypes that somehow extend those in the Oracle.plist but I couldn't figure out how to do it or if it's even possible.

Not sure... I've been using only the ones from ERPrototypes but would be interested in adding my own as well.
I know we have an MPVPrototypes framework with an intJavaEnum prototype in there in an EOJDBCOraclePrototypes entity.  But I'm not sure if we're missing a step because when the app launches I see the following warnings:

Dec 11 11:37:27 MyApp[51090] WARN  er.extensions.eof.ERXModelGroup  - BusinessLogic/PayorEditExclusion/editType references a prototype attribute named intJavaEnum that does not exist in EOJDBCOraclePrototypes.
Dec 11 11:37:27 MyApp[51090] WARN  er.extensions.eof.ERXModelGroup  - BusinessLogic/PayorEditList/editType references a prototype attribute named intJavaEnum that does not exist in EOJDBCOraclePrototypes.

and the app already has both ERPrototypes and MPVPrototypes in the classpath.

Maybe I'll shoot this in a separate email to have it figured out.