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:[EMAIL PROTECTED]
Sent: Sun 2005-06-12 11:42 PM
To: [email protected]
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
{
/**
* [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: [email protected]
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.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
