• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Writing Oracle Blobs under WO 5.4
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Writing Oracle Blobs under WO 5.4


  • Subject: Re: Writing Oracle Blobs under WO 5.4
  • From: Dov Rosenberg <email@hidden>
  • Date: Mon, 25 Feb 2008 06:14:44 -0500
  • Thread-topic: Writing Oracle Blobs under WO 5.4

Title: Re: Writing Oracle Blobs under WO 5.4
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 ="">       
      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@hidden> 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@hidden)
Help/Unsubscribe/Update your Subscription:

This email sent to email@hidden
 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:

This email sent to email@hidden

  • Follow-Ups:
    • Re: Writing Oracle Blobs under WO 5.4 - WO fails
      • From: Michael Scott <email@hidden>
References: 
 >Writing Oracle Blobs under WO 5.4 (From: Michael Scott <email@hidden>)

  • Prev by Date: Re: Wosid suppression broken in 5.4?
  • Next by Date: JavaEOGenerator, Eclipse, WOLips, WO 5.4, Mac OS X 10.5.2
  • Previous by thread: Writing Oracle Blobs under WO 5.4
  • Next by thread: Re: Writing Oracle Blobs under WO 5.4 - WO fails
  • Index(es):
    • Date
    • Thread