Re: Blob/Clob insertion test fails in Oracle 8.1.7

Rajeev Kaul Tue, 29 Oct 2002 15:37:34 -0800

I decided to roll up my sleeves and dig into the code to figure out the problem. I came to the conclusion that CLOBS are not working properly in OJB. To fix the problem, I made the following changes:

1. Modified ojbtest_schema.xml BLOB_TEST table to (the changes are shown in Red).

2. Modified the setObjectForStatement method of PlatformOracleImpl class to write CLOB data as shown below:

public void setObjectForStatement(PreparedStatement ps, int index, Object value, int sqlType) throws SQLException

{

if (((sqlType == Types.VARBINARY) ||

(sqlType == Types.LONGVARBINARY) ||

(sqlType == Types.BLOB)) &&

(value instanceof byte[]))

{

byte buf[] = (byte[])value;

ByteArrayInputStream inputStream = new ByteArrayInputStream(buf);

changePreparedStatementResultSetType(ps);

ps.setBinaryStream(index, inputStream, buf.length);

}

else if (sqlType == Types.CLOB)

{

CharArrayReader inputStream = new CharArrayReader((char[]) value);

ps.setCharacterStream(index, inputStream, ((char[])value).length);

}

else if (value instanceof Double)

{

// workaround for the bug in Oracle thin driver

ps.setDouble(index, ((Double) value).doubleValue());

}

else

{

super.setObjectForStatement(ps, index, value, sqlType);

}

3. In JdbcAccess class modified the getObjectFromColumn method for CLOB to:

case Types.CLOB :

{

java.sql.Clob aClob = rs.getClob(columnId);

result = aClob.getSubString(1L, (int) aClob.length()).toCharArray();

break;

}

The CLOB should return an object of type char[] to be consistent with BLOBs returning objects of type byte[]. However, it is not mandatory to do so. We could leave this as is, and modify the CLOB fields in corresponding classes to be of type String. That is, for example, the ObjectWithBlob class will have to be modified to have the clob property defined as a String. But if we make the change shown above, you do not have to modify the ObjectWithBlob class - the clob property can remain as character array.

I have attached the modified files, in case, somebody finds them useful to fix the current CLOB problem in OJB.

----- Original Message -----

From: "Rajeev Kaul" <[EMAIL PROTECTED]>

To: "OJB Users List" <[EMAIL PROTECTED]>

Sent: Monday, October 28, 2002 6:05 PM

Subject: Blob/Clob insertion test fails in Oracle 8.1.7

The BlobTest class in the OJB test suite fails when storing the ObjectWithBlob object in Oracle 8.1.7 database. I get the stack trace shown below.

Has anyone got it working successfully with Oracle? I tried to search the OJB mailing list archive but the search function on the site is not working at the moment.

[DEFAULT] ERROR: java.lang.ClassCastException: [C

at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2016)

at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2052)

at org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectForStatement(PlatformDefaultImpl.java:212)

at org.apache.ojb.broker.platforms.PlatformOracleImpl.setObjectForStatement(PlatformOracleImpl.java:130)

at org.apache.ojb.broker.accesslayer.StatementManager.bindInsert(StatementManager.java:465)

at org.apache.ojb.broker.accesslayer.JdbcAccess.executeInsert(JdbcAccess.java:208)[C

at org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBrokerImpl.java:1675)

at org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBrokerImpl.java:635)

at org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBrokerImpl.java:591)

at org.apache.ojb.broker.BlobTest.testBlobInsertion(BlobTest.java:77)

at java.lang.reflect.Method.invoke(Native Method)

at junit.framework.TestCase.runTest(TestCase.java:154)

at junit.framework.TestCase.runBare(TestCase.java:127)

at junit.framework.TestResult$1.protect(TestResult.java:106)

at junit.framework.TestResult.runProtected(TestResult.java:124)

at junit.framework.TestResult.run(TestResult.java:109)

at junit.framework.TestCase.run(TestCase.java:118)

at junit.framework.TestSuite.runTest(TestSuite.java:208)

at junit.framework.TestSuite.run(TestSuite.java:203)

at junit.textui.TestRunner.doRun(TestRunner.java:116)

at junit.textui.TestRunner.start(TestRunner.java:172)

at junit.textui.TestRunner.main(TestRunner.java:138)

at org.apache.ojb.broker.BlobTest.main(BlobTest.java:15)

<?xml version="1.0" encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE database SYSTEM "http://jakarta.apache.org/turbine/dtd/database.dtd";>



<database name="@DATABASE_DEFAULT@" defaultIdMethod="none"
  baseClass="">

  <!-- =================================================== -->
  <!-- B O O K  T A B L E                                  -->
  <!-- =================================================== -->

  <!--
  <table name="">
    <column name="" required="true" primaryKey="true" type="INTEGER"/>
    <column name="" required="true" type=""/>
    <foreign-key foreignTable="">
      <reference local="" foreign=""/>
    </foreign-key>
  </table>
  -->

  <table name="Artikel"
         javaName="Article">
    <column name="Artikel_Nr" required="true" primaryKey="true" type="INTEGER"
            javaName="articleId"/>
    <column name="Artikelname" type="VARCHAR" size="60"
            javaName="articleName"/>
    <column name="Lieferanten_Nr" type="INTEGER"
            javaName="supplierId"/>
    <column name="Kategorie_Nr" type="INTEGER"
            javaName="productGroupId"/>
    <column name="Liefereinheit" type="VARCHAR" size="30"
            javaName="unit"/>
    <column name="Einzelpreis" type="FLOAT"
            javaName="price"/>
    <column name="Lagerbestand" type="INTEGER"
            javaName="stock"/>
    <column name="BestellteEinheiten" type="INTEGER"
            javaName="orderedUnits"/>
    <column name="MindestBestand" type="INTEGER"
            javaName="minimumStock"/>
    <column name="Auslaufartikel" type="INTEGER"
            javaName="inSelloutArticle"/>
  </table>

  <table name="BOOKS">
    <column name="Artikel_Nr" required="true" primaryKey="true" type="INTEGER"/>
    <column name="Artikelname" type="VARCHAR" size="60"/>
    <column name="Lieferanten_Nr" type="INTEGER"/>
    <column name="Kategorie_Nr" type="INTEGER"/>
    <column name="Liefereinheit" type="VARCHAR" size="30"/>
    <column name="Einzelpreis" type="FLOAT"/>
    <column name="Lagerbestand" type="INTEGER"/>
    <column name="BestellteEinheiten" type="INTEGER"/>
    <column name="MindestBestand" type="INTEGER"/>
    <column name="Auslaufartikel" type="INTEGER"/>
    <column name="ISBN" type="CHAR" size="10"/>
    <column name="AUTHOR" type="VARCHAR" size="50"/>
  </table>

  <table name="CDS">
    <column name="Artikel_Nr" required="true" primaryKey="true" type="INTEGER"/>
    <column name="Artikelname" type="VARCHAR" size="60"/>
    <column name="Lieferanten_Nr" type="INTEGER"/>
    <column name="Kategorie_Nr" type="INTEGER"/>
    <column name="Liefereinheit" type="VARCHAR" size="30"/>
    <column name="Einzelpreis" type="FLOAT"/>
    <column name="Lagerbestand" type="INTEGER"/>
    <column name="BestellteEinheiten" type="INTEGER"/>
    <column name="MindestBestand" type="INTEGER"/>
    <column name="Auslaufartikel" type="INTEGER"/>
    <column name="LABEL" type="VARCHAR" size="50"/>
    <column name="MUSICIANS" type="VARCHAR" size="250"/>
  </table>

  <table name="Kategorien"
         javaName="ProductGroup">
    <column name="Kategorie_Nr" required="true" primaryKey="true" type="INTEGER"
            javaName="groupId"/>
    <column name="KategorieName" type="VARCHAR" size="20"
            javaName="groupName"/>
    <column name="Beschreibung" type="VARCHAR" size="60"
            javaName="description"/>
  </table>

  <table name="ORDER_POSITION">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="ORDER_ID" type="INTEGER"/>
    <column name="ARTICLE_ID" type="INTEGER"/>
  </table>

  <table name="TREE">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="DATA" type="VARCHAR" size="50"/>
    <column name="PARENT_ID" type="INTEGER"/>
  </table>

  <table name="TREEGROUP">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="DATA" type="VARCHAR" size="50"/>
    <column name="PARENT_ID" type="INTEGER"/>
    <column name="GROUP_ID" type="INTEGER"/>
  </table>

  <table name="AB_TABLE">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="CLASS_NAME" type="VARCHAR" size="60"/>
    <column name="VALUE_" type="INTEGER"/>
  </table>

  <!-- Product Table for Tutorial 1, 2 & 4-->
  <table name="PRODUCT">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"
            javaName="_id"/>
    <column name="NAME" type="VARCHAR" size="100"/>
    <column name="PRICE" type="FLOAT"/>
    <column name="STOCK" type="INTEGER"/>
  </table>

  <table name="PERSON">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="FIRSTNAME" type="VARCHAR" size="50"/>
    <column name="LASTNAME" type="VARCHAR" size="50"/>
  </table>

  <table name="PROJECT">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="TITLE" type="VARCHAR" size="50"/>
    <column name="DESCRIPTION" type="VARCHAR" size="250"/>
  </table>

  <table name="PERSON_PROJECT">
    <column name="PERSON_ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="PROJECT_ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="ROLENAME" type="VARCHAR" size="20"/>
  </table>

  <table name="TASK">
    <column name="TASK_ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="PERSON_ID" required="true" type="INTEGER"/>
    <column name="PROJECT_ID" required="true" type="INTEGER"/>
    <column name="TASKNAME" type="VARCHAR" size="20"/>
    <foreign-key foreignTable="PERSON_PROJECT">
      <reference local="PERSON_ID" foreign="PERSON_ID"/>
      <reference local="PROJECT_ID" foreign="PROJECT_ID"/>
    </foreign-key>
  </table>

  <table name="FAMILY_MEMBER">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="FIRSTNAME" type="VARCHAR" size="50"/>
    <column name="LASTNAME" type="VARCHAR" size="50"/>
    <column name="MOTHER_ID" type="INTEGER"/>
    <column name="FATHER_ID" type="INTEGER"/>
  </table>

  <table name="LOCKED_BY_VERSION">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="VALUE_" type="VARCHAR" size="60"/>
    <column name="VERSION_" type="INTEGER"/>
  </table>

  <table name="LOCKED_BY_TIMESTAMP">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="VALUE_" type="VARCHAR" size="60"/>
    <column name="TIMESTAMP_" type="TIMESTAMP"/>
  </table>

  <table name="BLOB_TEST">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="BLOB_VALUE_" type="BLOB"/>
    <column name="CLOB_VALUE_" type="CLOB"/>
  </table>

  <table name="MDTEST_MASTER">
    <column name="MASTERID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="MASTER_TEXT" type="VARCHAR" size="255"/>
  </table>

  <table name="MDTEST_DETAIL_FKINPK">
    <column name="MASTERID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="DETAILID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="DETAIL_TEXT" type="VARCHAR" size="255"/>
    <foreign-key foreignTable="MDTEST_MASTER">
      <reference local="MASTERID" foreign="MASTERID"/>
    </foreign-key>
  </table>

  <table name="MDTEST_DETAIL_FKNOPK">
    <column name="DETAILID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="MASTERID" required="true" type="INTEGER"/>
    <column name="DETAIL_TEXT" type="VARCHAR" size="255"/>
    <foreign-key foreignTable="MDTEST_MASTER">
      <reference local="MASTERID" foreign="MASTERID"/>
    </foreign-key>
  </table>

  <table name="POINT_TABLE">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="X" required="true" type="INTEGER"/>
    <column name="Y" required="true" type="INTEGER"/>
  </table>

  <table name="GRAPH_NODE">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="NAME" required="true" type="VARCHAR" size="50"/>
    <column name="LOCATION" type="INTEGER"/>
  </table>

  <table name="GRAPH_EDGE">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="SOURCE" required="true" type="INTEGER"/>
    <column name="SINK" required="true" type="INTEGER"/>
  </table>

  <table name="TestClassA">
    <column name="id" required="true" primaryKey="true" type="VARCHAR" size="48"/>
    <column name="value1" required="false" type="VARCHAR" size="64"/>
    <column name="value2" required="false" type="VARCHAR" size="64"/>
    <column name="value3" required="true" type="INTEGER" />
    <column name="bOid" required="false" type="VARCHAR" size="48"/>
  </table>

  <table name="TestClassB">
    <column name="id" required="true" primaryKey="true" type="VARCHAR" size="48"/>
    <column name="value1" required="false" type="VARCHAR" size="64"/>
    <column name="aOid" required="false" type="VARCHAR" size="48"/>
  </table>
    <table name="RelatedToContract">
        <column name="pk" required="true" primaryKey="true" type="VARCHAR" size="48"/>
        <column name="relatedValue1" required="false" type="VARCHAR" size="64"/>
        <column name="relatedValue2" required="false" type="INTEGER"/>
        <column name="relatedValue3" required="false" type="TIMESTAMP"/>
    </table>

    <table name="Contract">
        <column name="pk" required="true" primaryKey="true" type="VARCHAR" size="48"/>
        <column name="fk_to_related" required="false" type="VARCHAR" size="48"/>
        <column name="contract_value1" required="false" type="VARCHAR" size="64"/>
        <column name="contract_value2" required="false" type="INTEGER"/>
        <column name="contract_value3" required="false" type="VARCHAR" size="64"/>
        <column name="contract_value4" required="false" type="TIMESTAMP"/>
        <foreign-key foreignTable="RelatedToContract">
          <reference local="fk_to_related" foreign="pk"/>
        </foreign-key>
    </table>
    <table name="Version">
        <column name="pk" required="true" primaryKey="true" type="VARCHAR" size="48"/>
        <column name="fk_to_contract" required="true" type="VARCHAR" size="48"/>
        <column name="version_value1" required="false" type="VARCHAR" size="64"/>
        <column name="version_value2" required="false" type="INTEGER"/>
        <column name="version_value3" required="false" type="TIMESTAMP"/>
        <foreign-key foreignTable="Contract">
          <reference local="fk_to_contract" foreign="pk"/>
        </foreign-key>
    </table>
    <table name="Effectiveness">
        <column name="pk" required="true" primaryKey="true" type="VARCHAR" size="48"/>
        <column name="fk_to_version" required="true" type="VARCHAR" size="48"/>
        <column name="eff_value1" required="false" type="VARCHAR" size="64"/>
        <column name="eff_value2" required="false" type="INTEGER"/>
        <column name="eff_value3" required="false" type="TIMESTAMP"/>
        <foreign-key foreignTable="Version">
          <reference local="fk_to_version" foreign="pk"/>
        </foreign-key>
    </table>

    <table name="MultiMappedTable">
        <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
        <column name="value1" required="false" type="VARCHAR" size="64"/>
        <column name="value2" required="false" type="INTEGER"/>
        <column name="value3" required="false" type="TIMESTAMP"/>
        <column name="value4" required="false" type="VARCHAR" size="64"/>
        <column name="value5" required="false" type="INTEGER"/>
        <column name="value6" required="false" type="TIMESTAMP"/>
        <column name="value7" required="false" type="VARCHAR" size="64"/>
        <column name="ojbConcreteClass" required="true" type="VARCHAR" size="64"/>
    </table>

     <table name="TABLE_1">
        <column name="pk" required="true" primaryKey="true" type="INTEGER"/>
        <column name="fk_to_related" required="false" type="INTEGER"/>
        <foreign-key foreignTable="TABLE_2">
          <reference local="fk_to_related" foreign="pk"/>
        </foreign-key>
    </table>

    <table name="TABLE_2">
        <column name="pk" required="true" primaryKey="true" type="INTEGER"/>
    </table>

    <table name="BIDIR_A">
       <column name="pk" required="true" primaryKey="true" type="VARCHAR" size="48"/>
       <column name="fk_to_B" required="false" type="VARCHAR" size="48"/>
       <foreign-key foreignTable="BIDIR_B">
         <reference local="fk_to_B" foreign="pk"/>
       </foreign-key>
   </table>

   <table name="BIDIR_B">
       <column name="pk" required="true" primaryKey="true" type="VARCHAR" size="48"/>
       <column name="fk_to_A" required="false" type="VARCHAR" size="48"/>
       <foreign-key foreignTable="BIDIR_A">
         <reference local="fk_to_A" foreign="pk"/>
       </foreign-key>
   </table>

  <table name="MAMMAL">
    <column name="animalId" required="true" primaryKey="true" type="INTEGER"/>
    <column name="name" type="VARCHAR" size="60"/>
    <column name="age" type="INTEGER"/>
    <column name="numLegs" type="INTEGER"/>
    <column name="zooId" type="INTEGER"/>
  </table>

  <table name="REPTILE">
    <column name="animalId" required="true" primaryKey="true" type="INTEGER"/>
    <column name="name" type="VARCHAR" size="60"/>
    <column name="age" type="INTEGER"/>
    <column name="color" type="VARCHAR" size="60"/>
    <column name="zooId" type="INTEGER"/>
  </table>

  <table name="ZOO">
    <column name="zooId" required="true" primaryKey="true" type="INTEGER"/>
    <column name="name" type="VARCHAR" size="60"/>
  </table>

  <table name="FISH">
    <column name="foodId" required="true" primaryKey="true" type="INTEGER"/>
    <column name="name" type="VARCHAR" size="60"/>
    <column name="calories" type="INTEGER"/>
    <column name="typeOfWater" type="VARCHAR" size="60"/>
  </table>

  <table name="SALAD">
    <column name="foodId" required="true" primaryKey="true" type="INTEGER"/>
    <column name="name" type="VARCHAR" size="60"/>
    <column name="calories" type="INTEGER"/>
    <column name="color" type="VARCHAR" size="60"/>
  </table>

  <table name="GOURMET">
    <column name="gourmetId" required="true" primaryKey="true" type="INTEGER"/>
    <column name="name" type="VARCHAR" size="60"/>
  </table>

  <table name="GOURMET_FOOD">
    <column name="GOURMET_ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="FOOD_ID" required="true" primaryKey="true" type="INTEGER"/>
  </table>

  <table name="GUIDTEST">
    <column name="GUID" required="true" primaryKey="true" type="VARCHAR" size="60"/>
    <column name="GUIDVALUE" type="VARCHAR" size="60"/>
  </table>
  <table name="CONV_REFERER" javaName="ConversionReferrer">
    <column name="PK1" required="true" primaryKey="true" type="INTEGER"/>
    <column name="REF1" type="INTEGER"/>
  </table>

  <table name="CONV_REFERED" javaName="ConversionReferred">
    <column name="PK1" required="true" primaryKey="true" type="INTEGER"/>
  </table>


  <table name="SITE">
    <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="NAME" required="true" type="VARCHAR" size="100"/>
    <column name="SITEYEAR" type="INTEGER"/>
    <column name="SEMESTER" type="INTEGER"/>
    <unique name="NAME_UNIQUE">
        <unique-column name="NAME"/>
    </unique>

  </table>

</database>

JdbcAccess.java
Description: Binary data

PlatformOracleImpl.java
Description: Binary data

--
To unsubscribe, e-mail:   <mailto:ojb-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:ojb-user-help@;jakarta.apache.org>

Re: Blob/Clob insertion test fails in Oracle 8.1.7

Reply via email to