6

We have a ColdFusion 8 (Linux) application that uses an Oracle timestamp. We just converted to Oracle 11g from 10g and we're now using Oracle's thin client on the data sources. We're getting an error in the application where a timestamp column is selected.

It seems as though an object of class oracle.sql.TIMESTAMP is being returned. I verified this by dumping the contents of the column. Sure enough, it gives me a break down of the object's methods and their return types. But, I can't seem to be able to interface with this object directly:

<cfquery name="getstuff" ...>
    SELECT timestampfld ...
    FROM myTable
</cfquery>

getstuff.timestampfld contains an object. But, doing this:

<cfoutput query="getstuff">
    #timestampfld.stringValue()#
    #timestampfld.dateValue()#
</cfoutput>

produces the error that says those methods can't be found. How can I get at the data held in that object?

Update from comments:

When I take column value and apply the DateFormat( timestampfld, "dd.mm.yyyy" ) function to it. The CF error is

"The value class oracle.sql.timestamp cannot be converted to a date".

When I perform <cfoutput>, I get the class definition.

In the retrieved column, I seem to be getting an object instead of a string. When I cfdump the column, I get OBJECT OF oracle.sql.TIMESTAMP. The dump lays out the methods and fields available. When I cfoutput that same variable, a string is displayed. When I try to perform a DataFormat() on the variable, it complains that its not a date.

Bernhard Döbler
  • 1,960
  • 2
  • 25
  • 39
HugeBob
  • 467
  • 1
  • 5
  • 14
  • 2
    Can you post the *exact* error (ie: don't just describe it). – Adam Cameron Dec 31 '13 at 10:31
  • And what if you just `#timestampfld#` ? – Adam Cameron Dec 31 '13 at 10:31
  • The CF error is "The value class oracle.sql.timestamp cannot be converted to a date". I was taking the column value and applying the DateFormat() function to it. When I perform I get the class definition. – HugeBob Dec 31 '13 at 13:34
  • Correction. When I output the timestamp column value I get a string containing the time/date contained. – HugeBob Dec 31 '13 at 13:41
  • What is the format of the string? It is likely not one that the `DateFormat()` function can parse. Please add the output that is returned to your question so that we can help you further - [edit] – Miguel-F Dec 31 '13 at 13:58
  • 5
    _" When I output the timestamp column value I get a string containing the time/date contained"_ So... what's the problem? – duncan Dec 31 '13 at 14:48
  • @Duncan Precisely what I was thinking. – Adam Cameron Dec 31 '13 at 14:51
  • user10310142 (wanna give yerself a NAME??! ;-) when asked to post an error message, pls copy and paste the whole error - including the line of code it highlights and the top of the stack trace - and INCLUDE IT IN YOUR QUESTION (not as a comment). Same with values: don't describe the value (eg "When I output the timestamp column value I get a string containing the time/date contained."), just tell us what the value is! Contextualise it afterwards if need be. Basically don't second-guess what we're asking for. – Adam Cameron Dec 31 '13 at 14:53
  • In the retrieved column, I seem to be getting an object instead of a string. When I cfdump the column, I get OBJECT OF ORACLE.SQL.TIMESTAMP. The dump lays out the methods and fields available. When I cfoutput that same variable, a string is displayed. When I try to perform a DataFormat() on the variable, it complains that its not a date. – HugeBob Dec 31 '13 at 16:04
  • As a workaround, can you use a to_char() function in your query? – Dan Bracuk Dec 31 '13 at 16:23
  • OK, that is perhaps significant information to - once again - ADD TO THE QUESTION. What happens if you try `yourCol.toString()`, then parse it as a date? Or perhaps look for settings against the driver to not return objects, but return just values? – Adam Cameron Dec 31 '13 at 18:55
  • Checking the driver API is the best bet. However failing that, have you tried converting it to a `date` in your query? I do not use Oracle, but their docs says it is [an extension of date](http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006050), so their must be some conversion function available, like `to_date`. As a last resort, the java API suggests [`theTimeStampCol.dateValue()`](http://docs.oracle.com/cd/E16338_01/appdev.112/e13995/oracle/sql/TIMESTAMP.html#dateValue__) might work. – Leigh Dec 31 '13 at 19:50
  • (Edit) Hm... I just noticed you said you tried `dateValue()` already and said that did not work. IF that is the case, we are back to checking the driver API to see what methods *are* available. Speaking of which, when you dump the column ... what are the methods available? Because the API suggests `dateValue()` is one of them. – Leigh Dec 31 '13 at 20:59

1 Answers1

1

I just happened to stumble over this error during my development. I had it in the past and long forgotten since. I know of two ways to mitigate: The first is according to an answer of a question regarding the same error message in a different context. One would add the following to the jvm.config file

-Doracle.jdbc.J2EE13Compliant=true

The second is not to return an Oracle TIMESTAMP column but CAST it do DATE, first, like

<cfquery name="getstuff" ...>
  SELECT CAST( timestampfld as DATE ) timestampfld
  FROM myTable
</cfquery>

I'm not satisfied with either, to be honest. When the JVM argument is forgotten, the software crashes. The CAST, on the other hand, may influence how the SQL works.

Bernhard Döbler
  • 1,960
  • 2
  • 25
  • 39