ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Zeltner Martin <martin.zelt...@elca.ch>
Subject FW: 32k limitations for clob/blob?
Date Mon, 13 Jun 2005 06:42:13 GMT
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
{
    /**
     * {@inheritDoc}
     */
    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);
        }
    }

    /**
     * {@inheritDoc}
     */
    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;
    }

    /**
     * {@inheritDoc}
     */
    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:zhlu@umich.edu]
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 <zhlu@umich.edu> 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.executeUpd
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.insertT
gs_info_trainee(Tgs_info_traineeDaoSql.java:67)
>>>>>>  at
>>>>>>unit.edu.umich.med.umms.tgsubmission.dao.Tgs_info_traineeDaoSqlTestCas
e.doTestInsert(Tgs_info_traineeDaoSqlTestCase.java:100)
>>>>>>  at
>>>>>>unit.edu.umich.med.umms.tgsubmission.dao.Tgs_info_traineeDaoSqlTestCas
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.setParameter(Clo
bTypeHandlerCallback.java:45)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.type.CustomTypeHandler.setParameter(CustomTyp
eHandler.java:46)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParame
ter(BasicParameterMap.java:165)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParame
ters(BasicParameterMap.java:125)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecut
or.java:80)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecute
Update(GeneralStatement.java:196)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpd
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.setParameter(Clo
bTypeHandlerCallback.java:45)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.type.CustomTypeHandler.setParameter(CustomTyp
eHandler.java:46)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParame
ter(BasicParameterMap.java:165)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParame
ters(BasicParameterMap.java:125)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecut
or.java:80)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecute
Update(GeneralStatement.java:196)
>>>>>>  at
>>>>>>com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpd
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.insertT
gs_info_trainee(Tgs_info_traineeDaoSql.java:67)
>>>>>>  at
>>>>>>unit.edu.umich.med.umms.tgsubmission.dao.Tgs_info_traineeDaoSqlTestCas
e.doTestInsert(Tgs_info_traineeDaoSqlTestCase.java:100)
>>>>>>  at
>>>>>>unit.edu.umich.med.umms.tgsubmission.dao.Tgs_info_traineeDaoSqlTestCas
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
>>>>>>
>>>>>>  
>>>>>>
>>>>>>       
>>>>>>
>>>>>>            
>>>>>>
>>>>>     
>>>>>
>>>>>          
>>>>>
>>>
>>> 
>>>
>>>      
>>>
>
>
>
>  
>

Mime
View raw message