Hi all,

before and after having post my question, I dig a lot in the forums but did not find any real solution that match my situation.

About the problem of CLOB value retrieval, I found that some people had the same problem and solve it specifying some special type-mapping in the configuration.
OK, but I try but failed to set the correct settings playing with the XDoclet tags and the "standardjbosscmp-jdbc.xml".

Can someone give me hint about this ?


The second point is that I read very often that the CLOB can only work with the OCI drivers to Oracle. I must admit that I am very disappointed for this argument since we work only with the thin driver and no problem dealing (in BMP) with clobs.
Here you can find some code that we use (this code is slightly modified to be understood in a mail):

public void insert( LogData logData )
 throws PrimaryKeyCreateException, SQLException, LogDataInsertFKLogException {
 if( logData.getId( ) == null ) {
 logData.setId( ... );
 }

 StringBuffer sql = new StringBuffer( "INSERT INTO " ).append( TableLogData.TABLE_NAME ).append( " ( " );
 sql.append( TableLogData.ID ).append( ", " );
 sql.append( TableLogData.DATA ).append( ", " );
 sql.append( TableLogData.TYPE );
 sql.append( " ) VALUES ( ?, ?, ?)" );

 PreparedStatement psStmt = this.getConnection( ).prepareStatement( sql.toString( ) );
 try {
 psStmt.setLong( 1, logData.getId( ).longValue( ) );
 psStmt.setClob( 2, oracle.sql.CLOB.empty_lob( ) );
 psStmt.setString( 3, logData.getType( ) );
 if( psStmt.executeUpdate( ) <= 0 ) {
 throw new SQLException( "Company could not be inserted!" );
 }
 try {
 Statement sqlStatement = getConnection( ).createStatement( );
 try {
 StringBuffer query = new StringBuffer("SELECT ");
 query.append( TableLogData.DATA );
 query.append( " FROM " ).append( TableLogData.TABLE_NAME );
 query.append( " WHERE " );
 query.append( TableLogData.ID ).append( "=" ).append( logData.getId( ).toString( ) );
 ResultSet result = sqlStatement.executeQuery( query.toString( ) );

 try {
 if( result.next( ) ) {
 CLOB clob = ( CLOB ) result.getClob( 1 );
 try {
 if( clob != null ) {
 Writer writer = clob.getCharacterOutputStream( );
 if( logData.getData( ) == null ) {
 writer.write( "" );
 } else {
 writer.write( logData.getData( ) );
 }
 writer.close( );
 }
 } catch( IOException ioe ) {
 ...
 }
 }
 } finally {
 result.close( );
 }
 } finally {
 sqlStatement.close( );
 }
 } catch( SQLException sqle ) {
 ...
 }
 } catch( SQLException sqle ) {
 ...
 } finally {
 psStmt.close( );
 }
 }

So, if the "OCI solution" is the only one, is this a bug or a possible improvement of JBoss?
I work with the 3.0.8 version, is there corrections of this situation in the 3.2.x versions or in the Jboss 4 future version?

Thanks by advance for any feedback,
Alban.

Hi All and Hi Nicholas,

I made a lot of tests with Oracle and drivers.

The first thing I done is correct the mapping of my content field to a CLOB:
XDoclet produced the following xml fragment from this code:

 /**
 * @ejb.persistence
 * column-name = "LOGDATA_CONTENT"
 * jdbc-type = "CLOB"
 */
 public abstract String getContent();
 public abstract void setContent(String content);

produce:
 <cmp-field>
 <field-name>content</field-name>
 <column-name>LOGDATA_CONTENT</column-name>
 </cmp-field>

XDoclet forgive to specify the type mapping for Oracle.
You need to specify the sql-type to obtain XDoclet defining the mapping
So with this code I obtain the good xml fragment:

 /**
 * @ejb.persistence
 * column-name = "LOGDATA_CONTENT"
 * jdbc-type = "CLOB"
 * sql-type = "CLOB"
 *
 */
 public abstract String getContent();
 public abstract void setContent(String content);

produce:
 <cmp-field>
 <field-name>content</field-name>
 <column-name>LOGDATA_CONTENT</column-name>
 <jdbc-type>CLOB</jdbc-type>
 <sql-type>CLOB</sql-type>
 </cmp-field>

I think it's a XDoclet problem but we can pass through it.

Now with this code, I get the following error with the Oracle Thin driver:

java.sql.SQLException: Io exception: Connection reset by peer: socket write error
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
 at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2061)
 at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
 at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
 at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:308)
 at org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.insertEntity(JDBCCreateEntityCommand.java:196)
 at org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.execute(JDBCCreateEntityCommand.java:131)
 at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:527)
 at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:253)
 at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.createEntity(CachedConnectionInterceptor.java:270)
...

This is very different from the original error.
Now looking into the Oracle site for more information about the jdbc drivers, I found the following information:
select:
- AsciiStream: Thin->OK, OCI->OK
- java.sql.Clob: Thin->OK, OCI->OK
- oracle.sql.CLOB: Thin->OK, OCI->OK
Insert/Update:
- AsciiStream: Thin->BUG, OCI->OK
- java.sql.Clob: Thin->NOT POSSIBLE, OCI->NOT POSSIBLE
- oracle.sql.CLOB: Thin->OK, OCI->OK

Conclusion: If we want to use the Thin driver, we must use the oracle.sql.CLOB type!!!

From this, I test other Oracle JDBC drivers and found that the one from DataDirect works perfectly!!! It's solution but an little expensive still it's a commercial driver.

Now, I will try your solution Nicholas, but how do you specify the mapping type for your StringClob field?

:) keep thinking... :-P

OK, I have try the StringClob idea but it cannot work as this:
the problem is in the JDBCUtil.setParameter(...) method in JBoss.

...
 // Set the prepared statement parameter based upon the jdbc type
 switch (jdbcType)
 {
 //
 // Large character types
 //
 case Types.CLOB:
 case Types.LONGVARCHAR:
 {
 String string = value.toString();
 ps.setCharacterStream(index, new StringReader(string), string.length());
 // Can't close the reader because some drivers don't use it until
 // the statement is executed. This would appear to be a safe thing
 // to do with a StringReader as it only releases its reference.
 }
 break;

 //
 // All binary types
 //
 /*
 case Types.JAVA_OBJECT: // scoy: I'm not convinced that these should be here
 case Types.OTHER: // ie. mixed in with the binary types.
 case Types.STRUCT:
 */
 //
 // Small binary types
 //
 case Types.BINARY:
 case Types.VARBINARY:
 {
 byte[] bytes = convertObjectToByteArray(value);
 ps.setBytes(index, bytes);
 }
 break;

 //
 // Large binary types
 //
 case Types.BLOB:
 case Types.LONGVARBINARY:
 {
 byte[] bytes = convertObjectToByteArray(value);
 ps.setBinaryStream(index, new ByteArrayInputStream(bytes), bytes.length);
 // Can't close the stream because some drivers don't use it until
 // the statement is executed. This would appear to be a safe thing
 // to do with a ByteArrayInputStream as it only releases its reference.
 }
 break;


 //
 // Let the JDBC driver handle these if it can.
 // If it can't, then don't use them!
 // Map to a binary type instead and let JBoss marshall/unmarshall the data.
 //
 case Types.JAVA_OBJECT:
 case Types.OTHER:
 case Types.STRUCT:

 //
 // Standard SQL type
 //
 default:
 ps.setObject(index, value, jdbcType);
 break;

 }
...

in this piece of code extract from the JDBCUtil class, we can see that if whatever the data type you have, if you are mapping a CLOB object, JBoss will call the toString() on your value and use CharacterStream.
And this cannot work on the Oracle thin driver.

So we have to use the last case to let the driver deal with the object, but here again the call is made using the

ps.setObject(index, value, jdbcType);

instead of

ps.setObject(index, value);

.
This cause the problem that the data type mapped for your field will be transmit to the driver. Here we should use oracle.sql.CLOB type but with this type the call will be done using the CLOB case and not the Object one.


Nicholas:
the Binary types like BLOBs are fully functional with the oracle thin driver and the JDBCUtil code should not cause any problem.

The only solution I have found 'til now is to wrap the Oracle thin driver into a dummy one that just transfer all the calls to the real one, but the setObject(...) method on PreparedStatement sould have a little more intelligence and should deal with a special datatype that we define in the mapping.

Bye all,
Alban (aka Corduroy).