Re: 32k limitations for clob/blob?

[Nathan Maves]

Henry,

I just thought that I would let you know that this is also the way  
the default CLOB CTH bundled with ibatis works as well.

public class ClobTypeHandlerCallback implements TypeHandlerCallback {

  public Object getResult(ResultGetter getter)
      throws SQLException {
    String value = "";
    Clob clob = getter.getClob();
    if (clob != null) {
      int size = (int) clob.length();
      value = clob.getSubString(1, size);
    }
    return value;
  }

  public void setParameter(ParameterSetter setter, Object parameter)
      throws SQLException {
    String s = (String) parameter;
    if (s != null) {
      StringReader reader = new StringReader(s);
      setter.setCharacterStream(reader, s.length());
    } else {
      setter.setString(null);
    }
    setter.setString((String) parameter);
  }

  public Object valueOf(String s) {
    return s;
  }

}

Not sure why the spring framework would have this class as well  
unless it is from before the BLOB and CLOB CTH were introduced into  
the ibatis builds.

Nathan



On Jun 13, 2005, at 12:42 AM, Zeltner Martin wrote:

Hello Henry,

I had the same problem. You must use a TypeHandler which handles  
the string
on java to a clob on database site. In spring  
(www.springframework.org) you
can find custom type handler in package
"org.springframework.orm.ibatis.support". I've written following
TypeHandler:

public class ClobToStringTypeHandlerCallback implements  
TypeHandlerCallback
{
    /**
     * [EMAIL PROTECTED]
     */
    public void setParameter(ParameterSetter setter, Object parameter)
        throws SQLException {
        String s = (String) parameter;
        if (s != null) {
            StringReader reader = new StringReader(s);
            setter.setCharacterStream(reader, s.length());
        } else {
            setter.setString(null);
        }
    }

    /**
     * [EMAIL PROTECTED]
     */
    public Object getResult(ResultGetter getter) throws SQLException {
        String value = "";
        Clob clob = getter.getClob();
        if (clob != null) {
            int size = (int) clob.length();
            value = clob.getSubString(1, size);
        }
        return value;
    }

    /**
     * [EMAIL PROTECTED]
     */
    public Object valueOf(String s) {
        return s;
    }
}

See the iBatis doc how to use TypeHandlers in sql map configuration  
files.

If something is not clear to you don't hesitate to ask.

Cheers,
Martin


-----Original Message-----
From: Henry Lu [mailto:[EMAIL PROTECTED]
Sent: Donnerstag, 9. Juni 2005 20:39
To: ibatis-user-java@incubator.apache.org
Subject: Re: 32k limitations for clob/blob?


Is there anyone who have a working-code for insert CLOB and LONG data
type in Oracle?

-Henry

P.S.

Check the statement (update failed). - is a missleading error  
message from
iBatis. I did use INSERT command.

-Henry


Sven Boden wrote:


In your trace it states:
Check the statement (update failed).

I would have to check the iBatis code what's actually happening (and
probably would need a few more pieces of information from your setup)
but LOBs/CLOBs can't be updated, only deleted/inserted.

From the Oracle documentation:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/ 
a85397/fun

ction.htm#79466


Regards,
Sven Boden

On Thu, 09 Jun 2005 13:33:45 -0400, you wrote:




Thanks for your info. I change my sql to be:

<insert id="insert_clob">
 insert into x_clob
        (name,c)
  values
        (#name:VARCHAR#,to_lob(#c:VARCHAR#))
</insert>

and got the following errors:

   [java] size=1819572, 1819572
   [java] org.springframework.jdbc.BadSqlGrammarException: Bad SQL
grammar [(m
apped statement)] in task 'SqlMapClientTemplate'; nested  
exception is
com.ibatis
.common.jdbc.exception.NestedSQLException:
   [java] --- The error occurred in ibatis_map/AccessDB.xml.
   [java] --- The error occurred while applying a parameter map.
   [java] --- Check the insert_clob-InlineParameterMap.
   [java] --- Check the statement (update failed).
   [java] --- Cause: java.sql.SQLException: ORA-00932: inconsistent
datatypes:
expected - got BINARY

-Henry

Sven Boden wrote:




Item 1 is String limitation.

Item 2 I've never hit when using iBatis. I did get it however a few
times in PL-SQL... the problem was then solved by using the Oracle
function to_lob() while inserting the lob.

Regards,
Sven Boden

On Thu, 09 Jun 2005 13:05:50 -0400, you wrote:






No, I didn't. However, I got more details on the errors.
1. If I insert into CLOB or LONG as jdbc type with String in  
java, I got



the 32K limitation error.

  [java] size=1551138, 1551138
  [java] org.springframework.jdbc.UncategorizedSQLException:
(SqlMapClientTem
plate): encountered SQLException [
  [java] --- The error occurred in ibatis_map/AccessDB.xml.
  [java] --- The error occurred while applying a parameter map.
  [java] --- Check the insert_long-InlineParameterMap.
  [java] --- Check the parameter mapping for the 'l' property.
  [java] --- Cause: java.sql.SQLException: setString can only  
process
strings
of less than 32766 chararacters]; nested exception is
com.ibatis.common.jdbc.ex
ception.NestedSQLException:
  [java] --- The error occurred in ibatis_map/AccessDB.xml.
  [java] --- The error occurred while applying a parameter map.
  [java] --- Check the insert_long-InlineParameterMap.
  [java] --- Check the parameter mapping for the 'l' property.
  [java] --- Cause: java.sql.SQLException: setString can only  
process
strings
of less than 32766 chararacters

2. If I insert into CLOB or LONG as jdbc type with byte[] in  
java, I got



the following errors:

  [java] size=1461140, 1461140
  [java] org.springframework.jdbc.UncategorizedSQLException:
(SqlMapClientTem
plate): encountered SQLException [
  [java] --- The error occurred in ibatis_map/AccessDB.xml.
  [java] --- The error occurred while applying a parameter map.
  [java] --- Check the insert_long-InlineParameterMap.
  [java] --- Check the statement (update failed).
  [java] --- Cause: java.sql.SQLException: ORA-01461: can bind  
a LONG
value o
nly for insert into a LONG column
  [java] ]; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLExc
eption:
  [java] --- The error occurred in ibatis_map/AccessDB.xml.
  [java] --- The error occurred while applying a parameter map.
  [java] --- Check the insert_long-InlineParameterMap.
  [java] --- Check the statement (update failed).
  [java] --- Cause: java.sql.SQLException: ORA-01461: can bind  
a LONG
value o
nly for insert into a LONG column

Any idea?

-Henry


Brandon Goodin wrote:






I'm not aware of this limitation. We have run into similar things
before with the oracle driver. But, upgrading has always fixed  
it.
Have you attempted a test using straight JDBC?

Brandon

On 6/9/05, Henry Lu <[EMAIL PROTECTED]> wrote:







I got the following errors when I insert a large text into a  
CLOB in
oracle by using iBatis. How do I resulve it? I am using  
Oracle 10G

jdbc

deiver.


org.springframework.jdbc.UncategorizedSQLException:
(SqlMapClientTemplate): encountered SQLException [
--- The error occurred in
edu/umich/med/umms/tgsubmission/dao/ibatis_map/ 
tgs_info_trainee.xml.
--- The error occurred while applying a parameter map.
--- Check the Tgs_info_trainee.insert_Tgs_info_trainee_map.
--- Check the parameter mapping for the 'INFO_TEXT' property.
--- Cause: java.sql.SQLException: setString can only process  
strings

of

less than 32766 chararacters]; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
edu/umich/med/umms/tgsubmission/dao/ibatis_map/ 
tgs_info_trainee.xml.
--- The error occurred while applying a parameter map.
--- Check the Tgs_info_trainee.insert_Tgs_info_trainee_map.
--- Check the parameter mapping for the 'INFO_TEXT' property.
--- Cause: java.sql.SQLException: setString can only process  
strings

of

less than 32766 chararacters
org.springframework.jdbc.UncategorizedSQLException:
(SqlMapClientTemplate): encountered SQLException [
--- The error occurred in
edu/umich/med/umms/tgsubmission/dao/ibatis_map/ 
tgs_info_trainee.xml.
--- The error occurred while applying a parameter map.
--- Check the Tgs_info_trainee.insert_Tgs_info_trainee_map.
--- Check the parameter mapping for the 'INFO_TEXT' property.
--- Cause: java.sql.SQLException: setString can only process  
strings

of

less than 32766 chararacters]; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
edu/umich/med/umms/tgsubmission/dao/ibatis_map/ 
tgs_info_trainee.xml.
--- The error occurred while applying a parameter map.
--- Check the Tgs_info_trainee.insert_Tgs_info_trainee_map.
--- Check the parameter mapping for the 'INFO_TEXT' property.
--- Cause: java.sql.SQLException: setString can only process  
strings

of

less than 32766 chararacters
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
edu/umich/med/umms/tgsubmission/dao/ibatis_map/ 
tgs_info_trainee.xml.
--- The error occurred while applying a parameter map.
--- Check the Tgs_info_trainee.insert_Tgs_info_trainee_map.
--- Check the parameter mapping for the 'INFO_TEXT' property.
--- Cause: java.sql.SQLException: setString can only process  
strings

of

less than 32766 chararacters
Caused by: java.sql.SQLException: setString can only process  
strings

of

less than 32766 chararacters
 at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.exec 
uteUpd

ate(GeneralStatement.java:87)

 at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update 
(SqlMapExec

utorDelegate.java:500)

 at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update 
(SqlMapSessionIm

pl.java:89)

 at
org.springframework.orm.ibatis.SqlMapClientTemplate 
$11.doInSqlMapClien

t(SqlMapClientTemplate.java:282)

 at
org.springframework.orm.ibatis.SqlMapClientTemplate.execute 
(SqlMapClie

ntTemplate.java:140)

 at
org.springframework.orm.ibatis.SqlMapClientTemplate.update 
(SqlMapClien

tTemplate.java:280)

 at
edu.umich.med.umms.tgsubmission.dao.sql.Tgs_info_traineeDaoSql.i 
nsertT

gs_info_trainee(Tgs_info_traineeDaoSql.java:67)

 at
unit.edu.umich.med.umms.tgsubmission.dao.Tgs_info_traineeDaoSqlT 
estCas

e.doTestInsert(Tgs_info_traineeDaoSqlTestCase.java:100)

 at
unit.edu.umich.med.umms.tgsubmission.dao.Tgs_info_traineeDaoSqlT 
estCas

e.testAll(Tgs_info_traineeDaoSqlTestCase.java:64)

 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown  
Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 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
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests( 
Remote

TestRunner.java:474)

 at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run 
(RemoteTestR

unner.java:342)

 at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main 
(RemoteTest

Runner.java:194)

Caused by: java.sql.SQLException: setString can only process  
strings

of

less than 32766 chararacters
 at
oracle.jdbc.driver.DatabaseError.throwSqlException 
(DatabaseError.java:

125)

 at
oracle.jdbc.driver.DatabaseError.throwSqlException 
(DatabaseError.java:

162)

 at
oracle.jdbc.driver.DatabaseError.throwSqlException 
(DatabaseError.java:

227)

 at
oracle.jdbc.driver.OraclePreparedStatement.setStringInternal 
(OraclePre

paredStatement.java:4588)

 at
oracle.jdbc.driver.OraclePreparedStatement.setString 
(OraclePreparedSta

tement.java:4559)

 at
com.ibatis.sqlmap.engine.type.ParameterSetterImpl.setString 
(ParameterS

etterImpl.java:139)

 at
com.ibatis.sqlmap.engine.type.ClobTypeHandlerCallback.setParamet 
er(Clo

bTypeHandlerCallback.java:45)

 at
com.ibatis.sqlmap.engine.type.CustomTypeHandler.setParameter 
(CustomTyp

eHandler.java:46)

 at
com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.set 
Parame

ter(BasicParameterMap.java:165)

 at
com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.set 
Parame

ters(BasicParameterMap.java:125)

 at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate 
(SqlExecut

or.java:80)

 at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlE 
xecute

Update(GeneralStatement.java:196)

 at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.exec 
uteUpd

ate(GeneralStatement.java:74)

 ... 23 more

Caused by:
java.sql.SQLException: setString can only process strings of  
less than
32766 chararacters
 at
oracle.jdbc.driver.DatabaseError.throwSqlException 
(DatabaseError.java:

125)

 at
oracle.jdbc.driver.DatabaseError.throwSqlException 
(DatabaseError.java:

162)

 at
oracle.jdbc.driver.DatabaseError.throwSqlException 
(DatabaseError.java:

227)

 at
oracle.jdbc.driver.OraclePreparedStatement.setStringInternal 
(OraclePre

paredStatement.java:4588)

 at
oracle.jdbc.driver.OraclePreparedStatement.setString 
(OraclePreparedSta

tement.java:4559)

 at
com.ibatis.sqlmap.engine.type.ParameterSetterImpl.setString 
(ParameterS

etterImpl.java:139)

 at
com.ibatis.sqlmap.engine.type.ClobTypeHandlerCallback.setParamet 
er(Clo

bTypeHandlerCallback.java:45)

 at
com.ibatis.sqlmap.engine.type.CustomTypeHandler.setParameter 
(CustomTyp

eHandler.java:46)

 at
com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.set 
Parame

ter(BasicParameterMap.java:165)

 at
com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.set 
Parame

ters(BasicParameterMap.java:125)

 at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate 
(SqlExecut

or.java:80)

 at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlE 
xecute

Update(GeneralStatement.java:196)

 at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.exec 
uteUpd

ate(GeneralStatement.java:74)

 at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update 
(SqlMapExec

utorDelegate.java:500)

 at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update 
(SqlMapSessionIm

pl.java:89)

 at
org.springframework.orm.ibatis.SqlMapClientTemplate 
$11.doInSqlMapClien

t(SqlMapClientTemplate.java:282)

 at
org.springframework.orm.ibatis.SqlMapClientTemplate.execute 
(SqlMapClie

ntTemplate.java:140)

 at
org.springframework.orm.ibatis.SqlMapClientTemplate.update 
(SqlMapClien

tTemplate.java:280)

 at
edu.umich.med.umms.tgsubmission.dao.sql.Tgs_info_traineeDaoSql.i 
nsertT

gs_info_trainee(Tgs_info_traineeDaoSql.java:67)

 at
unit.edu.umich.med.umms.tgsubmission.dao.Tgs_info_traineeDaoSqlT 
estCas

e.doTestInsert(Tgs_info_traineeDaoSqlTestCase.java:100)

 at
unit.edu.umich.med.umms.tgsubmission.dao.Tgs_info_traineeDaoSqlT 
estCas

e.testAll(Tgs_info_traineeDaoSqlTestCase.java:64)

 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown  
Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 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
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests( 
Remote

TestRunner.java:474)

 at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run 
(RemoteTestR

unner.java:342)

 at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main 
(RemoteTest

Runner.java:194)


-Henry