ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Steve Biondi" <Ste...@schemalogic.com>
Subject RE: 32k limitations for clob/blob?
Date Mon, 13 Jun 2005 08:43:24 GMT
Hello all, just joined this list.
 
Maybe you've already done this, but just in case you hadn't I thought I would mention it:
with the Oracle10g driver, try setting the connection property "SetBigStringTryClob=true"
to directly set and get Strings longer than 32k in PreparedStatements and ResultSets. We've
successfully eradicated CLOB handling completely in my current project with this setting.
Apparently, this "fix" must be set explicitly to override the driver's default handling and
conversion logic. Makes life MUCH easier if streaming logic isn't explicitly required for
the app.
 
Regards,
 
Steve

________________________________

From: Zeltner Martin [mailto:martin.zeltner@elca.ch]
Sent: Sun 2005-06-12 11:42 PM
To: user-java@ibatis.apache.org
Subject: FW: 32k limitations for clob/blob?



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