can I change the SQL Datatype for Oracle Date?

[Former Member]

xMII 11.5 maps Oracle's Date type to its SQL type code 91 (Date). When used in a display template, this xMII type displays only the date component (for example, "5/5/2007"), but in Oracle it holds the date and time. In Java, the java.sql.Date type also holds the time component. To get xMII to display a date/time (for example, "5/5/2007 11:23:59") you must have a SQL type code of 93 (Timestamp).

Is there any way to get my xMII widgets to show the date and time components of an Oracle Date? Is there a mapping of some kind that tells xMII which database SQL types map to which xMII SQL type codes?

-tim

[Former Member]

Rick and Jeremy found <a href="https://service.sap.com/sap/support/notes/943507">SAP Note 943507</a> which describes replacing the faulting Oracle 9.x JDBC driver with the older 8.x driver.

After doing that and returning to my original query/display templates, the time component of the Oracle Date field began showing real data and not "00:00:00".

Thanks all,

-tim

[Former Member]

Maybe you have not selected the correct 'Date Format' for your query (In your query template for the Date Range Selection).

[Former Member]

In my query template, I'm not using my Date field as the xMII Date Range - I'm not using Date Range at all. To test your theory, I assigned my Date field as the "Date Column" in the "SQL Query Details" tab, and set valid start/end dates on the "Date Range" tab and I left the date format as the default "MM/dd/yyyy HH:mm:ss".

That column is now printed with date and time components, but the time component is always "00:00:00" although in Oracle the time component is always something other than "00:00:00".

I believe xMII reads the Oracle Date field into an xMII Date "object type" which only stores the date component and has no time component. It doesn't matter how it's printed, the time information is lost. I need to convince xMII to store the Oracle Date into an xMII Timestamp object.

-tim

[Former Member]

Ok,

Just for clarity - does that mean, for example for a datetime column DATEANDTIME in a table xyz, if you write the following SQL stmt "select DATEANDTIME from xyz" in

1. An Oracle Query Editor - You get the desired result as in with the time stamp.

2. In an xMII Query template - You get only the date (if you do not use the Date Range).

[Just curious to know the results!]

[Former Member]

In Aqua Data Studio:

SELECT CREATED_DATE_TIME, MESSAGE FROM COLLABORATION_LOG
where CREATED_DATE_TIME > to_date('01/01/07','mm/dd/yy') and site='DEM1'
order by CREATED_DATE_TIME DESC
GO

results:

CREATED_DATE_TIME   MESSAGE                                                                         
 --------------------  -----------------------------------------
 5/3/2007 8:30:25 PM   (403)Access Forbidden                                                           
 5/3/2007 8:30:25 PM   (403)Access Forbidden                                                           
 5/3/2007 8:30:25 PM   (403)Access Forbidden    

If I put the exact same SQL (without the "GO" at the end which is an Aqua Data Studio keyword) into a Fixed SQL Query in xMII and click "Test" and choose html format I get:

CREATED_DATE_TIME MESSAGE 
05/03/2007 00:00:00 (403)Access Forbidden 
05/03/2007 00:00:00 (403)Access Forbidden 
05/03/2007 00:00:00 (403)Access Forbidden 

The time component is reported as "00:00:00" in xMII which is not the desired result. I want xMII to see that column as a Timestamp which should read and sore both the date and time components in xMII.

-tim

[Former Member]

If for a "Test" you return an xml instead of html then does it show that the date time is SQLDataType="91" instead of the mentioned "93"?

Would a

select to_char(Current_date_time,"MM/DD/YYYY HH:MM:SS") from COLLABORATION_LOG

make any difference?

Note : I do not have a Oracle DB to test out these , so pestering you with all these questions , sorry about that.

[Former Member]

Udayan, in the query editor, when I click "Test" and choose XML, it does return a SQL data type of 91 (Date).

I gave your query a shot (slightly edited):

SELECT to_char(CREATED_DATE_TIME,'YYYY-MM-DD HH24:MI:SS') as CREATED_TS, MESSAGE FROM COLLABORATION_LOG
where CREATED_DATE_TIME > to_date('01/01/07','mm/dd/yy') and site='DEM1'
order by CREATED_DATE_TIME DESC

and this returns:

CREATED_TS MESSAGE 
2007-05-03 20:30:25 (403)Access Forbidden 
2007-05-03 20:30:25 (403)Access Forbidden 

which works in a way. When I return XML, the SQL Data Type for CREATED_TS is 12 (Variable Character), not 93 (Timestamp), but this is good enough for now.

I'd still like to see a way to control the mapping of database SQL data types to xMII data types, but this is a good work-around.

Thanks much,

-tim

[Former Member]

Tim:

This is actually a setting in the Oracle DB. There are better workarounds (you definitely don't want to send back date/times as character - you'll lose a lot of functionality if you do).

I've copied Jeremy Good "off list" and you and he can discuss workarounds and post them back up here for the "good of the community".

- Rick