We had that same error. I don¹t think it is your BLOB that is causing the
problem but how Oracle changed its default behavior on the driver and DB
regarding the Timestamp and Date data types. The ojdbc14.jar drivers have a
compatibility mode you can use to get around the problem, pass in the
following as a JVM parameter.
-Doracle.jdbc.V8compatible=true
Here is the article from MetaLink that talks about the problem (not sure of
the number):
PURPOSE
-------
Clarify the use of oracle.jdbc.V8Compatible property flag
SCOPE & APPLICATION
-------------------
JDBC 920x Date/TimeStamp mapping is different from JDBC 8i, 9iR1.
<TITLE FOR MAIN ARTICLE TEXT>
-----------------------------
Summary of features afftected by oracle.jdbc.V8Compatible.
As of 9.2.0.1.0 Oracle realigned its DATE type with the java.sql.Types.DATE
type.
Prior to this
java.sql.DATE and java.sql.TIMESTAMP were mapped to
java.sql.Types.TIMESTAMP.
This mapping change applies to JDBC default mapping (i.e when getObject() is
used for Date column.
example:
select sysdate from dual;
...
while (rset.next ()) {
System.out.println("getObject for sysdate : " +
rset.getObject(1).getClass().getName());
System.out.println("getDate for sysdate :" +
rset.getDate(1).getClass().getName());
System.out.println("getTimetamp for sysdate :" +
rset.getTimestamp(1).getClass().getName());
}
Prior to 9201, this will return
getObject for sysdate : java.sql.Timestamp <<<<
getDate for sysdate :java.sql.Date
getTimetamp for sysdate :java.sql.Timestamp
As of 9201 onward the following will be returned
getObject for sysdate : java.sql.Date <<<<<
getDate for sysdate :java.sql.Date >> no change
getTimetamp for sysdate :java.sql.Timestamp >> no change
Note: java.sql.Date has no time portion whereas java.sql.Timestamp does.
With this change in Datatype mapping, some application will fail and/or
generate
incorrect results when JDBC driver is upgraded from 8i/ 9iR1 to 920x JBDC
driver.
To maintain compatibility and keep applications working after upgrade, a
compatibility flag was
Provided. Developers now have some options:
1>
Use oracle.jdbc.V8Compatible flag.
JDBC Driver does not detect database version by default.
To change the compatibility flag for handling TIMESTAMP datatypes,
connection property 'oracle.jdbc.V8Compatible' can be set to
'true' and the driver behaves as it behaved in 8i, 901x, 9200
(with respect to TIMESTAMPs).
By default the flag is set to 'false'. In OracleConnection constructor
the driver obtains the server version and set the compatibility flag
Appropriately.
java.util.Properties prop = new java.util.Properties ();
prop.put ("oracle.jdbc.V8Compatible", "true");
prop.put ("user", "scott");
prop.put ("password", "tiger");
String url ="jdbc:oracle:thin:@host:port:sid";
Connection conn = DriverManager.getConnection (url,prop);
With JDBC 10.1.0.x, in instead of the connection property, the following
system
property can be useed
java -Doracle.jdbc.V8Compatible=true .....
Note: This flag is a client only flag that governs the Timestamp and Date
mapping.
It does not affect any Database feature.
2> use set/getDate and set/getTimestamp when dealing with Date and
TimeStamp column datatype accordingly.
9i server supports both Date and Timestamp column types
DATE is mapped to java.sql.Date and TIMESTAMP is mapped to
java.sql.Timestamp
I> using setTimestamp
PreparedStatement pstmt = conn.prepareStatement(
"SELECT count(*) from tstable where tscol between ? and ?");
// tscol of type Timetamp (or it can be Date)
String s = new String("2003-01-14 10:00:00.000000000");
Timestamp ts1 = Timestamp.valueOf(s);
pstmt.setTimestamp(1, ts1); // Timestamp
String s2 = new String("2003-01-16 10:00:00.000000000");
Timestamp ts2 = Timestamp.valueOf(s2);
pstmt.setTimestamp(2, ts2); // Timestamp
...
II>using setDate
PreparedStatement pstmt = conn.prepareStatement(
"SELECT count(*) from tstable where datecol between ? and ?");
// datecole of type Date
/*
pstmt.setDate(1,new java.sql.Date(System.currentTimeMillis()));
pstmt.setDate(2,new java.sql.Date(System.currentTimeMillis()));
*/
SimpleDateFormat start_dt_in1 = new SimpleDateFormat("2002-09-18
00:00:00");
SimpleDateFormat start_dt_in2 = new SimpleDateFormat("2003-09-18
00:00:00");
pstmt.setDate(1,start_dt_in1);
pstmt.setDate(2,start_dt_in2);
Summary of features afftected by oracle.jdbc.V8Compatible.
Feature Is backward compatible (with oracle.jdbc.V8Compatible)?
9.2.0.2.0 9.2.0.3.0 9.2.0.4 10.0.0.0
bug fix
---------------- ---------- ---------- -------- --------
setTimestamp NO YES YES YES 2517719
getTimestamp YES YES YES YES
CTS Fixes
TS{}* NO NO YES YES
2640192
getColumnType NO NO YES YES
2640192
getColumnTypeName NO NO YES YES 2640192
* Examples:
..
The following will fail when using JDBC 9iR1, 9iR2 connecting 817 server
since the
817 did not support Timestamp
Connection conn =
DriverManager.getConnection(url, "scott", "tiger");
// Prepare a statement to cleanup the emp table
Statement stmt = conn.createStatement();
try {
stmt.execute("delete from EMP where EMPNO = 1");
} catch (SQLException e) {
}
try {
stmt.execute("INSERT INTO EMP (EMPNO, ENAME, HIREDATE) VALUES
(1,
'ALI', {ts '2003-04-14 14:19:24.94'})");
} catch (SQLException e) {
e.printStackTrace();
}
Error : Exception in thread "main" java.sql.SQLException: ORA-00904: invalid
column name
Solution you need
1> fix for Bug 2640192 (included in 9204)
2> oracle.jdbc.V8Compatible", "true"
In earlier versions of JDBC drivers SQL FUNCTION "TS" was mapped to
"to_date" . So, the query
select {ts '2002-10-18 18:02:00'} from dual;
was translated by JDBC to,
select TO_DATE ('2002-10-18 18:02:00', 'YYYY-MM-DD HH24:MI:SS')
from dual;
With 9i Timestamp is supported in the database and also by 9203 JDBC
Drivers.
So the query
select {ts '2002-10-18 18:02:00'} from dual;
is now translated by JDBC to
select TO_TIMESTAMP('2002-10-18 18:02:00', 'YYYY-MM-DD HH24:MI:.SS.FF')
from dual;
Known issues: There is some performances issue when set/getTimestamp
Bug 3037615
Bug 2770935
These bugs are very likely duplicate.
The following code will no longer work with 9203+ unless V8 flag is set to
true
Timestamp start_dt_in = Timestamp.valueOf("2002-09-18 00:00:00");
Timestamp now_period_start_dt ;
PreparedStatement stmt = null;
ResultSet rs = null;
System.out.println("start_dt_in="+ start_dt_in );
try {
stmt = conn.prepareStatement( "SELECT TRUNC(?) FROM DUAL" );
stmt.setTimestamp( 1, start_dt_in );
rs = (OracleResultSet) stmt.executeQuery();
if ( rs.next() ) {
now_period_start_dt = rs.getTimestamp( 1 );
System.out.println("Curr Period Start="+ now_period_start_dt );
}
will generate
Exception in thread "main" java.sql.SQLException:
ORA-932: inconsistent datatypes
Reason : trunc ( ) supports Date columns and does not support Timestamp
(this is an RDBMS issue).
So, you need to set the V8 flag to true
Another bug that changed the Date/Timetamp mapping is 2428427 to comly with
J2EE 1.3 CTS. This was fixed in 9014 and it specific to classesdmx*.zip/jar
(the *dms* jar filed mainly used by iAS/OC4J). These *dms* jar files, by
the
default value for oracle.jdbc.J2EE13Compliant is true. in classes111.zip
classes12.jar and ojdbc14.jar/zip the default is false.
One can toggel this flag true/false by
java -Doracle.jdbc.J2EE13Compliant=true|false
example of of sample runs:
query used :"select sysdate from dual"
classes12dms.jar used.
Driver Version Object Type
============== ===========
9.0.1.3.0 java.sql.Timestamp >> fix for 2428427 NOT included
9.0.1.4.0 java.sql.Date >> fix for 2428427 INCLUDED
9.0.1.5.0 java.sql.Date >> fix for 2428427 INCLUDE
In JDBC 9014+ ,to keep older (9013) behavior simply run the application
with
$java -Doracle.jdbc.J2EE13Compliant=false .....
However please note that J2EE 1.3 CTS require that Date to mapped to
java.sql.Date.
On 2/25/08 12:24 AM, "Michael Scott" <[EMAIL PROTECTED]> wrote:
> Hi All
>
> Has anyone had any success writing blobs to an Oracle DB under WO 5.4?
>
> I have a large project that makes extensive use of blobs and works fine under
> WO 5.3 but throws the following error when writing a blob under WO 5.4:
>
> com.webobjects.eoaccess.EOGeneralAdaptorException: Unsupported feature
> (See full dump below.)
>
> The code can read existing blobs from an Oracle DB without any problems. It¹s
> just writing that causes exceptions.
>
> To eliminate other factors, I created a test project with a simple version of
> the offending code. It uses a table BlobTable with just a primary key and
> a blob field. The code attempting the write is below. It gets text data from
> a form via a key called notes¹. (The BlobTable already contains one record.)
>
> NSArray blobs =
> EOUtilities.objectsForEntityNamed(session().defaultEditingContext(),
> "BlobTable");
> if ((blobs != null) && (blobs.count() > 0)) {
> BlobTable blob = (BlobTable)blobs.objectAtIndex(0);
> NSData blobData = new NSData(notes(), "UTF8");
> blob.setBlobData(blobData);
> session().defaultEditingContext().saveChanges();
> }
>
> The problem is occurring on an OS X Leopard box in either Xcode or Eclipse
> (not that that should matter). The JDBC driver is for Oracle 9i 9.2.0.8
> it¹s a file called ojdbc14.jar.
>
> If anyone has Oracle blobs working under 10.4, that would be great news I¹d
> would then know to dig deeper rather than file a bug report.
>
> Better still how did you do it?
>
> Thanks
>
> Michael Scott
> Murdoch University
>
>
>
> Application: Cafe
> com.webobjects.eoaccess.EOGeneralAdaptorException: Unsupported feature
>
> File Line# Method Package
> EODatabaseContext.java 4500
> _exceptionWithDatabaseContextInformationAdded com.webobjects.eoaccess
> EODatabaseContext.java 6212 performChanges
> com.webobjects.eoaccess
> EOObjectStoreCoordinator.java 376 saveChangesInEditingContext
> com.webobjects.eocontrol
> EOEditingContext.java 3176 saveChanges
> com.webobjects.eocontrol
> Main.java 50 saveBlob Default Package
> NativeMethodAccessorImpl.java NA invoke0 sun.reflect
> NativeMethodAccessorImpl.java 39 invoke sun.reflect
> DelegatingMethodAccessorImpl.java 25 invoke sun.reflect
> Method.java 585 invoke java.lang.reflect
> KeyValueCodingProtectedAccessor.java 60 methodValue Default Package
> NSKeyValueCoding.java 1134 valueInObject
> com.webobjects.foundation
> NSKeyValueCoding.java 1293 valueForKey
> com.webobjects.foundation
> WOComponent.java 1686 valueForKey
> com.webobjects.appserver
> NSKeyValueCoding.java 447 valueForKey
> com.webobjects.foundation
> NSKeyValueCodingAdditions.java 212 valueForKeyPath
> com.webobjects.foundation
> WOComponent.java 1754 valueForKeyPath
> com.webobjects.appserver
> WOKeyValueAssociation.java 50 valueInComponent
> com.webobjects.appserver._private
> WOSubmitButton.java 81 invokeAction
> com.webobjects.appserver._private
> WODynamicGroup.java 105 invokeChildrenAction
> com.webobjects.appserver._private
> WODynamicGroup.java 115 invokeAction
> com.webobjects.appserver._private
> WOForm.java 141 invokeAction
> com.webobjects.appserver._private
> WODynamicGroup.java 105 invokeChildrenAction
> com.webobjects.appserver._private
> WODynamicGroup.java 115 invokeAction
> com.webobjects.appserver._private
> WOComponent.java 1078 invokeAction
> com.webobjects.appserver
> WOSession.java 1357 invokeAction
> com.webobjects.appserver
> WOApplication.java 1736 invokeAction
> com.webobjects.appserver
> WOComponentRequestHandler.java 206 _dispatchWithPreparedPage
> com.webobjects.appserver._private
> WOComponentRequestHandler.java 298 _dispatchWithPreparedSession
> com.webobjects.appserver._private
> WOComponentRequestHandler.java 332 _dispatchWithPreparedApplication
> com.webobjects.appserver._private
> WOComponentRequestHandler.java 369 _handleRequest
> com.webobjects.appserver._private
> WOComponentRequestHandler.java 445 handleRequest
> com.webobjects.appserver._private
> WOApplication.java 1678 dispatchRequest
> com.webobjects.appserver
> WOWorkerThread.java 144 runOnce
> com.webobjects.appserver._private
> WOWorkerThread.java 226 run com.webobjects.appserv
>
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list ([email protected])
> Help/Unsubscribe/Update your Subscription:
> http://lists.apple.com/mailman/options/webobjects-dev/drosenberg%40inquira.com
>
> This email sent to [EMAIL PROTECTED]
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [EMAIL PROTECTED]
