Clob and Oracle

[asoupper]

Hi all,
these days I'm trying to change a BMP entity in a CMP.
I had strictly no problem with my CMP but I want to explore the BMP possibilities and performances.

But It seems I have a problem with the CLOB field of my bean:
- No data is retrieve when loading loading the BMP
- An exception is thrown upon insertion of long string in the CLOB field:

javax.ejb.EJBException: Internal error setting parameters for field content; CausedByException is:
 Data size bigger than max size for this type: 7115570
 at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.setArgumentParameters(JDBCAbstractCMPFieldBridge.java:297)
 at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.setInstanceParameters(JDBCAbstractCMPFieldBridge.java:270)
 at org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.insertEntity(JDBCCreateEntityCommand.java:192)
 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)

Environment:
- JBoss 3.0.8
- Oracle 8.1.7

My Bean:

/**
 * @ejb.bean
 * name="LogItem"
 * view-type="both"
 * type="CMP"
 * primkey-field="id"
 * cmp-version = "2.x"
 * jndi-name="theLogItem"
 *
 * @ejb:transaction
 * type="Required"
 *
 * @ejb.persistence
 * table-name="LOGDATA"
 *
 * @jboss.persistence
 * create-table = "false"
 * remove-table = "false"
 *
 */
public abstract class SessionLogItemEJBBean implements EntityBean {

 //==========================================
 // Business Methods
 //==========================================

 ...

 //==========================================
 // CMP fields
 //==========================================

 /**
 * @ejb.pk-field
 *
 * @ejb.persistence
 * column-name = "LOGDATA_ID"
 * jdbc-type = "INTEGER"
 *
 */
 public abstract Long getId();
 public abstract void setId(Long id);

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

 /**
 * @ejb.persistence
 * column-name = "LOGDATA_TYP"
 * jdbc-type = "VARCHAR"
 * sql-type = "varchar(300)"
 */
 public abstract String getType();
 public abstract void setType(String type);

 //==========================================
 // CMR fields
 //==========================================

 //==========================================
 // EJB Callbacks
 //==========================================
 /**
 * @ejb.create-method
 * view-type = "both"
 */
 public Long ejbCreate( ... )throws CreateException{
 ...
 return null;
 }
 public void ejbPostCreate( ... )throws CreateException{
 }
}

Can someone tell what I am missing or if there is a problem with this kind of stuff (and it's workaround).

Best regards,
Alban.

[asoupper]

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.

[nickman]

The BMP process you are following to both make the Clob work and to use the thin driver are the rubs. Here is what I have found:

1. The OCI driver allows you to bypass all the Oracle proprietary calls to empty_lob etc. etc. unless the size of the LOB is less than ~1024 bytes (not surgically sure that number is correct, but it is small....). This seems to me to be a limitation of Oracle's Type 4 driver, but I suppose it's possible that you might be able to fiddle with it and make it work. However, the thin driver does not really have much in the way of fine tunable parameters.

2. I have been able to get BLOBS to work seamlessly. However, instead of using a byte array or some such data type, I simply define it as a java.sql.Blob. e.g. code from my bean:

/**
 * getNotes()
 * XDoclet Field Tags for Notes
 *
 * @return the Notes
 * @ejb.persistence
 * column-name="NOTES"
 * @jboss.column-name NOTES
 * @jboss.method-attributes
 * read-only=true
 * @jboss.load-group
 * name="lazy"
 */
 public abstract java.sql.Blob getNotes();

/**
 * setNotes(java.sql.Clob notes)
 * @param notes
 */
 public abstract void setNotes(java.sql.Blob notes);

The actual implementation of the java.sql.Blob is org.jboss.ejb.plugins.cmp.jdbc.ByteArrayBlob which works quite nicely. The only limitation, which does not affect me is that the blob payload is immuatble.

This worked really well, and I assumed it would be trivial to convert the Blob handling over to Clobs based on the ByteArrayBlob. You simply need to implement java.sql.Clob, which is basically a matter of wraping a StringBuffer and implementing the methods in the interface.

Or so I though. Actually, I am still wrestling with it and I could not get it to work yet. Perhaps I missed something silly, but the Blobs do the job admirably, so I have not really pursued the Clob that aggresively.

Let me know what you find. Perhaps we can get the Clob stuff working.

//Nicholas

[nickman]

I went back to look at this, and immediately saw the silly mistake.

Here is the sanitized Clob implementation:

/**
 * <p>Title: StringClob</p>
 * <p>Description: A simple String buffer based implementation of a CLOB</p>
 * <p>Much code derived from mockrunner-0.2.7</p>
 * @author Whitehead
 * @version $Revision: 1.3 $
 */

public class StringClob
 implements Clob, Cloneable, Serializable, Externalizable {

 static final long serialVersionUID = 6518280165134659952L;
 private StringBuffer clobData;

public StringClob() {
 clobData = new StringBuffer();
}

public StringClob(String data)
{
 clobData = new StringBuffer(data);
}

public boolean setClobData(String s) {
 boolean changed = false;
 if(s!=null && clobData.toString().equalsIgnoreCase(s)) {
 } else {
 if(s==null) {
 if(clobData.length()!=0) changed=true;
 } else {
 clobData.delete(0, clobData.length());
 clobData.append(s);
 changed=true;
 }
 }
 return changed;
}

public boolean appendClobData(String s) {
 boolean changed = false;
 if(s!=null) {
 clobData.append(s);
 changed=true;
 }
 return changed;
}

public long length() throws SQLException
{
 return clobData.length();
}

public void truncate(long len) throws SQLException
{
 clobData.setLength((int)len);
}

public InputStream getAsciiStream() throws SQLException
{
 return new ByteArrayInputStream(clobData.toString().getBytes());
}

public OutputStream setAsciiStream(long pos) throws SQLException
{
 throw new java.lang.UnsupportedOperationException("setAsciiStream");
}

public Reader getCharacterStream() throws SQLException
{
 return new StringReader(clobData.toString());
}

public Writer setCharacterStream(long pos) throws SQLException
{
 throw new java.lang.UnsupportedOperationException("setCharacterStream");
}

public String getSubString(long pos, int length) throws SQLException
{
 return clobData.substring((int)(pos - 1), (int)(pos - 1) + length);
}

public int setString(long pos, String str) throws SQLException
{
 return setString(pos, str, 0, str.length());
}

public int setString(long pos, String str, int offset, int len) throws SQLException
{
 str = str.substring(offset, offset + len);
 clobData.replace((int)(pos - 1), (int)(pos - 1)+ str.length(), str);
 return len;
}

public long position(String searchstr, long start) throws SQLException
{
 int index = clobData.toString().indexOf(searchstr, (int)(start - 1));
 if(-1 != index) index += 1;
 return index;
}

public long position(Clob searchClob, long start) throws SQLException
{
 return position(searchClob.getSubString(1, (int)searchClob.length()), start);
}



private class ClobOutputStream extends OutputStream
{
 private int index;

 public ClobOutputStream(int index)
 {
 this.index = index;
 }

 public void write(int byteValue) throws IOException
 {
 byte[] bytes = new byte[] {(byte)byteValue};
 try
 {
 setString(index + 1, new String(bytes));
 }
 catch(SQLException exc)
 {
 throw new IOException(exc.getMessage());
 }
 index++;
 }
}

public String toString()
{
 return clobData.toString();
}

public Object clone()
{
 try
 {
 StringClob clone = (StringClob)super.clone();
 clone.clobData = new StringBuffer(clobData.toString());
 return clone;
 }
 catch(CloneNotSupportedException exc)
 {
 exc.printStackTrace();
 }
 return null;
}


 public void writeExternal(ObjectOutput out) throws IOException {
 out.writeUTF(clobData.toString());
 }

 public void readExternal(ObjectInput in) throws IOException,
 ClassNotFoundException {
 clobData = new StringBuffer(in.readUTF());
 }

}

So with the above code as the implementation, and the CMP code in the previous message, and the use of the OCI driver, I think you should be able to get it to work.

Let me know....

//Nicholas

[asoupper]

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?

[asoupper]

:) 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).

[nickman]

I recently found out (at Hibernate training, thanks Lance) that the new Oracle 10g JDBC driver has addressed many of the LOB issues and is backward compatible to Oracle 8i. Based on my work with Blobs so far, I can attest to this.

//Nicholas