ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Neil Lott" <neilmatthewl...@yahoo.com>
Subject ibatis and blobs
Date Wed, 26 Sep 2007 16:02:48 GMT
I'm getting the following error when I try to insert a BLOB larger than 4K:

 

nested exception: 

com.ibatis.common.jdbc.exception.NestedSQLException:   

--- The error occurred in Blob.xml.  

--- The error occurred while applying a parameter map.  

--- Check the insertBlob-InlineParameterMap.  

--- Check the parameter mapping for the 'payload' property.  

--- Cause: java.sql.SQLException: Data size bigger than max size for this
type: 6000

 

 

>From what I understand from my DBA:

 

The max BLOB/CLOB size is 4G. 4K is the max size for storing the BLOB 

inline (in the row itself), which is why you see dbvisualizer showing the
max length

of 4000. Blobs larger than 4K are stored outside the table, and only the
locator 

is stored in the table column. In this case, the BLOB is stored in the
tables 

tablespace in default configured LOB storage. When mapping the column, does

IBATIS have a storage clause that can be specified for BLOB columns?

 

Here's the background and code.  Thanks for your help.  Neil

 

----

 

I'm using iBATIS version 2.3.0.677

 

I'm using Oracle

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

 

I've tried using jdbc driver classes12.zip or ojdbc14.jar (both give me the
same error)

 

I've also read the FAQ entry surrounding this, and I'm not sure what I'm
doing wrong.  I've also tried using a parameter map with the "B[" java type
and that gives me an oracle invalid character error.

 

Here is my table create statement:

 

CREATE TABLE 

    TC50_FRESH.BLOB_TEST 

( 

    ID INTEGER NOT NULL CONSTRAINT PK_ID PRIMARY KEY, 

    PAYLOAD BLOB 

)

 

Here is my sql map:

 

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig

   PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"

   "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

 

<sqlMapConfig>

 

   <transactionManager type="JDBC">

      <dataSource type="DBCP">

         <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>

         <property name="url" value="jdbc:oracle:thin:@azurite:1521:ourDB"/>

         <property name="username" value="TC50_FRESH"/>

         <property name="password" value="TC50_FRESH"/>

         <property name="maxActive" value="50"/>

         <property name="maxIdle" value="10"/>

      </dataSource>

   </transactionManager>

 

   <sqlMap resource="com/mystrotv/db/ondemand/performance/Blob.xml"/>

 

</sqlMapConfig>

 

Here is my Blob.xml

 

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap

   PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"

   "http://ibatis.apache.org/dtd/sql-map-2.dtd">

 

<sqlMap namespace="Blob">

 

   <typeAlias alias="blobBean" type=" performance.ibatis.blob.BlobBean"/>

 

   <resultMap id="blobResult" class="blobBean">

        <result column="ID" property="ID"/>

        <result property="payload" column="PAYLOAD" jdbcType="BLOB"/>

   </resultMap>

 

   <select id="findBlob" parameterClass="int" resultMap="blobResult">

        SELECT

            *

        FROM

            BLOB_TEST

        WHERE

            ID = #value#

   </select>

 

   <insert id="insertBlob" parameterClass="blobBean">

        INSERT INTO

            BLOB_TEST (

                ID,

                PAYLOAD

                )

            values (

                #ID#,

                #payload#

            )

   </insert>

 

   <delete id="deleteBlobs">

      delete from BLOB_TEST

   </delete>

 

</sqlMap>

 

Here is my blob bean:

 

package performance.ibatis.blob;

 

public class BlobBean

{

 

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

   // /// Data Members

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

 

   private Integer ID;

 

   private byte[] payload;

 

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

   // /// Public

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

 

   public Integer getID()

   {

      return ID;

   }

 

   public void setID(Integer ID)

   {

      this.ID = ID;

   }

 

   public byte[] getPayload()

   {

      return payload;

   }

 

   public void setPayload(byte[] payload)

   {

      this.payload = payload;

   }

}

 

Here is my DAO:

 

package performance.ibatis.blob

 

import java.io.Reader;

import java.sql.SQLException;

 

import com.ibatis.common.resources.Resources;

import com.ibatis.sqlmap.client.SqlMapClient;

import com.ibatis.sqlmap.client.SqlMapClientBuilder;

 

public class BlobDAOImpl

{

 

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

   // /// Data Members

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

 

   private static SqlMapClient sqlMap;

 

   private static BlobDAOImpl instance;

 

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

   // /// Constructors

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

 

   public static synchronized BlobDAOImpl getInstance() throws Exception

   {

      if (instance == null)

      {

         Reader reader = Resources.getResourceAsReader(

            "performance/ibatis/blob-sqlMapConfig.xml");

         sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

 

         instance = new BlobDAOImpl();

      }

 

      return instance;

   }

 

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

   // /// Public

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

 

   public void createBlob(BlobBean blobBean) throws SQLException

   {

         sqlMap.insert("insertBlob", blobBean);

   }

 

   public BlobBean findBlob(int ID) throws SQLException

   {

         BlobBean result = (BlobBean) sqlMap.queryForObject("findBlob", ID);

         return result;

   }

 

   public int deleteBlobs() throws SQLException

   {

         return sqlMap.delete("deleteBlobs");

   }

}

 

Here is my test:

 

package performance.ibatis.blob;

 

import org.testng.Assert;

import org.testng.annotations.Test;

 

public class BlobDAOImplTest

{

 

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

   // /// Tests

   //
////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////

 

   public static byte[] thePayload;

 

   static

   {

      thePayload = new byte[6000];

      for (int i = 0; i < 6000; i++)

      {

         thePayload[i] = 0x0A;

      }

   }

 

   @Test (groups = {"blob-test"}, enabled = true)

   public void insertBlob() throws Exception

   {

      BlobDAOImpl.getInstance().deleteBlobs();

 

      BlobBean blobBean = new BlobBean();

      blobBean.setID(1);

      blobBean.setPayload(thePayload);

      BlobDAOImpl.getInstance().createBlob(blobBean);

 

      BlobBean result = BlobDAOImpl.getInstance().findBlob(1);

      for (int i = 0; i < 6000; i++)

      {

         Assert.assertEquals(result.getPayload()[i], 0x0A);

      }

   }

}


Mime
View raw message