Clob Issue

[Craig Swift]

Hello,

Has anyone seen this type of error before when trying to use Clobs in
IBatis?

javax.servlet.ServletException: Error executing update.  Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in recognition/dao/ibatis/mapping/Nomination.xml.
--- The error occurred while applying a parameter map.
--- Check the updateNomination-InlineParameterMap.
--- Check the parameter mapping for the 'writeUp' property.
--- Cause: java.sql.SQLException: Data size bigger than max size for this type: 4236

I'm using a String as the underlining object and specifying the JDBC type as a clob in the sql map. It works for inserts/updates/deletes until the String size becomes to large. I was under the impression that a CustomType Hnadler wasn't necessary. Any information would be appreciated, thanks!

Craig S

[Steve Biondi]

Is this with Oracle? If so, the string can only be up to 4000 characters long if you use setString even if the underlying column is a CLOB and you tell the driver that. With bigger strings, you need to use the CLOB-specific API stuff.

 

A nice workaround is to use the Oracle10g JDBC driver and set the following connection property:

 

SetBigStringTryClob=true;

 

Then, you can pass any size string to setString, and the JDBC driver will internally do the "clob" work. You can also define your maps using simply "string".

 

FYI - the Oracle10g driver works well with both Oracle9i and 10g databases. We support both DBs and make extensive use of clobs in our iBatis stuff.

 

Steve B.

---

From: Craig Swift [mailto:Craig.Swift@...]
Sent: Thu 2005-08-11 3:33 PM
To: user-java@...
Subject: Clob Issue

Hello,

Has anyone seen this type of error before when trying to use Clobs in
IBatis?

javax.servlet.ServletException: Error executing update.  Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in recognition/dao/ibatis/mapping/Nomination.xml.
--- The error occurred while applying a parameter map.
--- Check the updateNomination-InlineParameterMap.
--- Check the parameter mapping for the 'writeUp' property.
--- Cause: java.sql.SQLException: Data size bigger than max size for this type: 4236

I'm using a String as the underlining object and specifying the JDBC type as a clob in the sql map. It works for inserts/updates/deletes until the String size becomes to large. I was under the impression that a CustomType Hnadler wasn't necessary. Any information would be appreciated, thanks!

Craig S

[Nathan Maves]

Steve,

How would one go about setting this property when using the jakarta connection pool build into ibatis?

Nathan

On Aug 11, 2005, at 5:13 PM, Steve Biondi wrote:

Is this with Oracle? If so, the string can only be up to 4000 characters long if you use setString even if the underlying column is a CLOB and you tell the driver that. With bigger strings, you need to use the CLOB-specific API stuff.

 

A nice workaround is to use the Oracle10g JDBC driver and set the following connection property:

 

SetBigStringTryClob=true;

 

Then, you can pass any size string to setString, and the JDBC driver will internally do the "clob" work. You can also define your maps using simply "string".

 

FYI - the Oracle10g driver works well with both Oracle9i and 10g databases. We support both DBs and make extensive use of clobs in our iBatis stuff.

 

Steve B.

---

From: Craig Swift [Craig.Swift@...]
Sent: Thu 2005-08-11 3:33 PM
To: user-java@...
Subject: Clob Issue

Hello,

Has anyone seen this type of error before when trying to use Clobs in
IBatis?

javax.servlet.ServletException: Error executing update.  Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in recognition/dao/ibatis/mapping/Nomination.xml.
--- The error occurred while applying a parameter map.
--- Check the updateNomination-InlineParameterMap.
--- Check the parameter mapping for the 'writeUp' property.
--- Cause: java.sql.SQLException: Data size bigger than max size for this type: 4236

I'm using a String as the underlining object and specifying the JDBC type as a clob in the sql map. It works for inserts/updates/deletes until the String size becomes to large. I was under the impression that a CustomType Hnadler wasn't necessary. Any information would be appreciated, thanks!

Craig S

[Steve Biondi]

I've always used an external JDBC DataSource or a previously configured connection with iBatis, so I'm not exactly sure. From glancing at the SimpleDataSource code, you should be able to set the property as is in the JDBC section of the config file and it should get passed to the DataSource create method and thereby be set in the driver. I would try adding this element to the <dataSource> element:

<transactionManager type="JDBC">
  <dataSource type="SIMPLE">
    <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
    ...
    <property name="SetBigStringTryClob" value="true"/>
  </dataSource>
</transactionManager>

Someone can correct me if I'm wrong about that. The prop should pass through to the driver.

Steve

-----Original Message-----
From: Nathan Maves [mailto:Nathan.Maves@...]
Sent: Thu 2005-08-11 6:29 PM
To: user-java@...
Subject: Re: Clob Issue
 
Steve,

How would one go about setting this property when using the jakarta  
connection pool build into ibatis?

Nathan

On Aug 11, 2005, at 5:13 PM, Steve Biondi wrote:

> Is this with Oracle? If so, the string can only be up to 4000  
> characters long if you use setString even if the underlying column  
> is a CLOB and you tell the driver that. With bigger strings, you  
> need to use the CLOB-specific API stuff.
>
> A nice workaround is to use the Oracle10g JDBC driver and set the  
> following connection property:
>
> SetBigStringTryClob=true;
>
> Then, you can pass any size string to setString, and the JDBC  
> driver will internally do the "clob" work. You can also define your  
> maps using simply "string".
>
> FYI - the Oracle10g driver works well with both Oracle9i and 10g  
> databases. We support both DBs and make extensive use of clobs in  
> our iBatis stuff.
>
> Steve B.
>
> From: Craig Swift [mailto:Craig.Swift@...]
> Sent: Thu 2005-08-11 3:33 PM
> To: user-java@...
> Subject: Clob Issue
>
> Hello,
>
> Has anyone seen this type of error before when trying to use Clobs in
> IBatis?
>
> javax.servlet.ServletException: Error executing update.  Cause:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in recognition/dao/ibatis/mapping/
> Nomination.xml.
> --- The error occurred while applying a parameter map.
> --- Check the updateNomination-InlineParameterMap.
> --- Check the parameter mapping for the 'writeUp' property.
> --- Cause: java.sql.SQLException: Data size bigger than max size  
> for this type: 4236
>
> I'm using a String as the underlining object and specifying the  
> JDBC type as a clob in the sql map. It works for inserts/updates/
> deletes until the String size becomes to large. I was under the  
> impression that a CustomType Hnadler wasn't necessary. Any  
> information would be appreciated, thanks!
>
> Craig S
>
>
>
>

[Nathan Maves]

Steve,

You almost had it!

After checking the source code you just need to prepend "Driver." in  
front of your property name.

// Additional Driver Properties prefix
private static final String ADD_DRIVER_PROPS_PREFIX = "Driver.";


So .....

<transactionManager type="JDBC">
   <dataSource type="SIMPLE">
     <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
     ...
     <property name="Driver.SetBigStringTryClob" value="true"/>
   </dataSource>
</transactionManager>


Should do the trick.  I will verify that is works in the morning and  
if so I will append all that we have learned to the BLOB/CLOB wiki!

Nathan


On Aug 11, 2005, at 11:57 PM, Steve Biondi wrote:

> I've always used an external JDBC DataSource or a previously  
> configured connection with iBatis, so I'm not exactly sure. From  
> glancing at the SimpleDataSource code, you should be able to set  
> the property as is in the JDBC section of the config file and it  
> should get passed to the DataSource create method and thereby be  
> set in the driver. I would try adding this element to the  
> <dataSource> element:
>
> <transactionManager type="JDBC">
>   <dataSource type="SIMPLE">
>     <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
>     ...
>     <property name="SetBigStringTryClob" value="true"/>
>   </dataSource>
> </transactionManager>
>
> Someone can correct me if I'm wrong about that. The prop should  
> pass through to the driver.
>
> Steve
>
> -----Original Message-----
> From: Nathan Maves [mailto:Nathan.Maves@...]
> Sent: Thu 2005-08-11 6:29 PM
> To: user-java@...
> Subject: Re: Clob Issue
>
> Steve,
>
> How would one go about setting this property when using the jakarta
> connection pool build into ibatis?
>
> Nathan
>
> On Aug 11, 2005, at 5:13 PM, Steve Biondi wrote:
>
>
>> Is this with Oracle? If so, the string can only be up to 4000
>> characters long if you use setString even if the underlying column
>> is a CLOB and you tell the driver that. With bigger strings, you
>> need to use the CLOB-specific API stuff.
>>
>> A nice workaround is to use the Oracle10g JDBC driver and set the
>> following connection property:
>>
>> SetBigStringTryClob=true;
>>
>> Then, you can pass any size string to setString, and the JDBC
>> driver will internally do the "clob" work. You can also define your
>> maps using simply "string".
>>
>> FYI - the Oracle10g driver works well with both Oracle9i and 10g
>> databases. We support both DBs and make extensive use of clobs in
>> our iBatis stuff.
>>
>> Steve B.
>>
>> From: Craig Swift [mailto:Craig.Swift@...]
>> Sent: Thu 2005-08-11 3:33 PM
>> To: user-java@...
>> Subject: Clob Issue
>>
>> Hello,
>>
>> Has anyone seen this type of error before when trying to use Clobs in
>> IBatis?
>>
>> javax.servlet.ServletException: Error executing update.  Cause:
>> com.ibatis.common.jdbc.exception.NestedSQLException:
>> --- The error occurred in recognition/dao/ibatis/mapping/
>> Nomination.xml.
>> --- The error occurred while applying a parameter map.
>> --- Check the updateNomination-InlineParameterMap.
>> --- Check the parameter mapping for the 'writeUp' property.
>> --- Cause: java.sql.SQLException: Data size bigger than max size
>> for this type: 4236
>>
>> I'm using a String as the underlining object and specifying the
>> JDBC type as a clob in the sql map. It works for inserts/updates/
>> deletes until the String size becomes to large. I was under the
>> impression that a CustomType Hnadler wasn't necessary. Any
>> information would be appreciated, thanks!
>>
>> Craig S
>>
>>
>>
>>
>>
>
>
>
> <winmail.dat>
>

[Daniel Henrique Ferreira e Silva]

Hi Craig,

This error is quite common when dealing with Oracle databases. Is
Oracle your database engine?

If yes, try grabbing the latest JDBC driver version. I remember that
9i driver has a issue regarding CLOBs. Not sure what it is exactly.

Hope that helped.

Cheers,
Daniel Silva.


On 8/11/05, Craig Swift <Craig.Swift@...> wrote:

> Hello,
>
> Has anyone seen this type of error before when trying to use Clobs in
> IBatis?
>
> javax.servlet.ServletException: Error executing update.  Cause:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in recognition/dao/ibatis/mapping/Nomination.xml.
> --- The error occurred while applying a parameter map.
> --- Check the updateNomination-InlineParameterMap.
> --- Check the parameter mapping for the 'writeUp' property.
> --- Cause: java.sql.SQLException: Data size bigger than max size for this type: 4236
>
> I'm using a String as the underlining object and specifying the JDBC type as a clob in the sql map. It works for inserts/updates/deletes until the String size becomes to large. I was under the impression that a CustomType Hnadler wasn't necessary. Any information would be appreciated, thanks!
>
> Craig S
>
>
>
>

[Craig Swift]

Hey Everyone,

First off thanks for the assistance, appreciate it a lot. I tried both
suggestions. The first being setting the driver property
"SetBigStringTryClob" to true and the second getting the latest 10g
drivers from Oracle. It looks like the one that did the trick was the
updated drivers from Oracle. In fact I didn't need the property string
in there after I updated the driver. ;) Thanks again for the assistance!


Nathan Maves wrote:

>Steve,
>
>You almost had it!
>
>After checking the source code you just need to prepend "Driver." in  
>front of your property name.
>
>// Additional Driver Properties prefix
>private static final String ADD_DRIVER_PROPS_PREFIX = "Driver.";
>
>
>So .....
>
><transactionManager type="JDBC">
>   <dataSource type="SIMPLE">
>     <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
>     ...
>     <property name="Driver.SetBigStringTryClob" value="true"/>
>   </dataSource>
></transactionManager>
>
>
>Should do the trick.  I will verify that is works in the morning and  
>if so I will append all that we have learned to the BLOB/CLOB wiki!
>
>Nathan
>
>
>On Aug 11, 2005, at 11:57 PM, Steve Biondi wrote:
>
>  
>
>>I've always used an external JDBC DataSource or a previously  
>>configured connection with iBatis, so I'm not exactly sure. From  
>>glancing at the SimpleDataSource code, you should be able to set  
>>the property as is in the JDBC section of the config file and it  
>>should get passed to the DataSource create method and thereby be  
>>set in the driver. I would try adding this element to the  
>><dataSource> element:
>>
>><transactionManager type="JDBC">
>>  <dataSource type="SIMPLE">
>>    <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
>>    ...
>>    <property name="SetBigStringTryClob" value="true"/>
>>  </dataSource>
>></transactionManager>
>>
>>Someone can correct me if I'm wrong about that. The prop should  
>>pass through to the driver.
>>
>>Steve
>>
>>-----Original Message-----
>>From: Nathan Maves [mailto:Nathan.Maves@...]
>>Sent: Thu 2005-08-11 6:29 PM
>>To: user-java@...
>>Subject: Re: Clob Issue
>>
>>Steve,
>>
>>How would one go about setting this property when using the jakarta
>>connection pool build into ibatis?
>>
>>Nathan
>>
>>On Aug 11, 2005, at 5:13 PM, Steve Biondi wrote:
>>
>>
>>    
>>
>>>Is this with Oracle? If so, the string can only be up to 4000
>>>characters long if you use setString even if the underlying column
>>>is a CLOB and you tell the driver that. With bigger strings, you
>>>need to use the CLOB-specific API stuff.
>>>
>>>A nice workaround is to use the Oracle10g JDBC driver and set the
>>>following connection property:
>>>
>>>SetBigStringTryClob=true;
>>>
>>>Then, you can pass any size string to setString, and the JDBC
>>>driver will internally do the "clob" work. You can also define your
>>>maps using simply "string".
>>>
>>>FYI - the Oracle10g driver works well with both Oracle9i and 10g
>>>databases. We support both DBs and make extensive use of clobs in
>>>our iBatis stuff.
>>>
>>>Steve B.
>>>
>>>From: Craig Swift [mailto:Craig.Swift@...]
>>>Sent: Thu 2005-08-11 3:33 PM
>>>To: user-java@...
>>>Subject: Clob Issue
>>>
>>>Hello,
>>>
>>>Has anyone seen this type of error before when trying to use Clobs in
>>>IBatis?
>>>
>>>javax.servlet.ServletException: Error executing update.  Cause:
>>>com.ibatis.common.jdbc.exception.NestedSQLException:
>>>--- The error occurred in recognition/dao/ibatis/mapping/
>>>Nomination.xml.
>>>--- The error occurred while applying a parameter map.
>>>--- Check the updateNomination-InlineParameterMap.
>>>--- Check the parameter mapping for the 'writeUp' property.
>>>--- Cause: java.sql.SQLException: Data size bigger than max size
>>>for this type: 4236
>>>
>>>I'm using a String as the underlining object and specifying the
>>>JDBC type as a clob in the sql map. It works for inserts/updates/
>>>deletes until the String size becomes to large. I was under the
>>>impression that a CustomType Hnadler wasn't necessary. Any
>>>information would be appreciated, thanks!
>>>
>>>Craig S
>>>
>>>
>>>
>>>
>>>
>>>      
>>>
>>
>><winmail.dat>
>>
>>    
>>

[Nathan Maves]

I have updated the BLOB/CLOB page in the wiki to reflect this new info.

Nathan

On Aug 12, 2005, at 10:24 AM, Craig Swift wrote:

> Hey Everyone,
>
> First off thanks for the assistance, appreciate it a lot. I tried both
> suggestions. The first being setting the driver property
> "SetBigStringTryClob" to true and the second getting the latest 10g
> drivers from Oracle. It looks like the one that did the trick was the
> updated drivers from Oracle. In fact I didn't need the property string
> in there after I updated the driver. ;) Thanks again for the  
> assistance!
>
>
> Nathan Maves wrote:
>
>
>> Steve,
>>
>> You almost had it!
>>
>> After checking the source code you just need to prepend "Driver." in
>> front of your property name.
>>
>> // Additional Driver Properties prefix
>> private static final String ADD_DRIVER_PROPS_PREFIX = "Driver.";
>>
>>
>> So .....
>>
>> <transactionManager type="JDBC">
>>   <dataSource type="SIMPLE">
>>     <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
>>     ...
>>     <property name="Driver.SetBigStringTryClob" value="true"/>
>>   </dataSource>
>> </transactionManager>
>>
>>
>> Should do the trick.  I will verify that is works in the morning and
>> if so I will append all that we have learned to the BLOB/CLOB wiki!
>>
>> Nathan
>>
>>
>> On Aug 11, 2005, at 11:57 PM, Steve Biondi wrote:
>>
>>
>>
>>
>>> I've always used an external JDBC DataSource or a previously
>>> configured connection with iBatis, so I'm not exactly sure. From
>>> glancing at the SimpleDataSource code, you should be able to set
>>> the property as is in the JDBC section of the config file and it
>>> should get passed to the DataSource create method and thereby be
>>> set in the driver. I would try adding this element to the
>>> <dataSource> element:
>>>
>>> <transactionManager type="JDBC">
>>>  <dataSource type="SIMPLE">
>>>    <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
>>>    ...
>>>    <property name="SetBigStringTryClob" value="true"/>
>>>  </dataSource>
>>> </transactionManager>
>>>
>>> Someone can correct me if I'm wrong about that. The prop should
>>> pass through to the driver.
>>>
>>> Steve
>>>
>>> -----Original Message-----
>>> From: Nathan Maves [mailto:Nathan.Maves@...]
>>> Sent: Thu 2005-08-11 6:29 PM
>>> To: user-java@...
>>> Subject: Re: Clob Issue
>>>
>>> Steve,
>>>
>>> How would one go about setting this property when using the jakarta
>>> connection pool build into ibatis?
>>>
>>> Nathan
>>>
>>> On Aug 11, 2005, at 5:13 PM, Steve Biondi wrote:
>>>
>>>
>>>
>>>
>>>
>>>> Is this with Oracle? If so, the string can only be up to 4000
>>>> characters long if you use setString even if the underlying column
>>>> is a CLOB and you tell the driver that. With bigger strings, you
>>>> need to use the CLOB-specific API stuff.
>>>>
>>>> A nice workaround is to use the Oracle10g JDBC driver and set the
>>>> following connection property:
>>>>
>>>> SetBigStringTryClob=true;
>>>>
>>>> Then, you can pass any size string to setString, and the JDBC
>>>> driver will internally do the "clob" work. You can also define your
>>>> maps using simply "string".
>>>>
>>>> FYI - the Oracle10g driver works well with both Oracle9i and 10g
>>>> databases. We support both DBs and make extensive use of clobs in
>>>> our iBatis stuff.
>>>>
>>>> Steve B.
>>>>
>>>> From: Craig Swift [mailto:Craig.Swift@...]
>>>> Sent: Thu 2005-08-11 3:33 PM
>>>> To: user-java@...
>>>> Subject: Clob Issue
>>>>
>>>> Hello,
>>>>
>>>> Has anyone seen this type of error before when trying to use  
>>>> Clobs in
>>>> IBatis?
>>>>
>>>> javax.servlet.ServletException: Error executing update.  Cause:
>>>> com.ibatis.common.jdbc.exception.NestedSQLException:
>>>> --- The error occurred in recognition/dao/ibatis/mapping/
>>>> Nomination.xml.
>>>> --- The error occurred while applying a parameter map.
>>>> --- Check the updateNomination-InlineParameterMap.
>>>> --- Check the parameter mapping for the 'writeUp' property.
>>>> --- Cause: java.sql.SQLException: Data size bigger than max size
>>>> for this type: 4236
>>>>
>>>> I'm using a String as the underlining object and specifying the
>>>> JDBC type as a clob in the sql map. It works for inserts/updates/
>>>> deletes until the String size becomes to large. I was under the
>>>> impression that a CustomType Hnadler wasn't necessary. Any
>>>> information would be appreciated, thanks!
>>>>
>>>> Craig S
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> <winmail.dat>
>>>
>>>
>>>
>>>
>
>

[Steve Biondi]

Just to let you know, the upgrade only partially solves the problem. The updated driver still has a hard limit of 32766 characters for a string. Setting the "SetBigStringTryClob=true" solves this too. Try inserting a 60000 char string into a CLOB column.
 
I wrote a quick example to generate the exception text for this case:
 
java.sql.SQLException: setString can only process strings of less than 32766 chararacters
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:305)
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5220)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5191)
at TestBigClob.main(TestBigClob.java:28)
 
Here's where I configure the connection:
 
   String url = "jdbc:oracle:thin:uname/pword@server:1521:sid";
   Class.forName("oracle.jdbc.OracleDriver");
   Connection conn = null;
   PreparedStatement pstmt = null;
   Properties props = new Properties();
//   props.put("SetBigStringTryClob", "true");
   conn = DriverManager.getConnection(url, props);

     ...
If I uncomment the props.put line with SetBigStringTryClob, no exception.
 
For the iBatis basic datasource, the following might work:
 
<property name = "Driver.connectionProperties" values = "SetBigStringTryClob=true;"/>
 
We do something like this when using the Tomcat Simple JDBC Datasource. The Apache DBCP component uses bean getters/setters to configure the underlying driver, and "connectionProperties" is a collection property. DBCP apparently is smart enough to do this right, as it works in our Tomcat instances.
 
Here's an example of this DBCP config in Tomcat server.xml:
 
<Resource name="jdbc/OracleDs"    
     type="javax.sql.DataSource"
     auth="Container"/>
     
 <ResourceParams name="jdbc/OracleDs">
  <parameter>
   <name>url</name>
   <value>jdbc:oracle:thin:@server:1521:sid</value>
  </parameter>
  <parameter>
   <name>driverClassName</name>
   <value>oracle.jdbc.OracleDriver</value>
  </parameter>
  <parameter>
   <name>username</name>
   <value>user</value>
  </parameter>
  <parameter>
   <name>password</name>
   <value>password</value>
  </parameter>
  <parameter>
   <name>connectionProperties</name>
   <value>SetBigStringTryClob=true;</value>
  </parameter>
 </ResourceParams>

 
Regards,
 
Steve B.
 
________________________________

From: Nathan Maves [mailto:Nathan.Maves@...]
Sent: Fri 2005-08-12 9:52 AM
To: user-java@...
Subject: Re: Clob Issue



I have updated the BLOB/CLOB page in the wiki to reflect this new info.

Nathan

On Aug 12, 2005, at 10:24 AM, Craig Swift wrote:

> Hey Everyone,
>
> First off thanks for the assistance, appreciate it a lot. I tried both
> suggestions. The first being setting the driver property
> "SetBigStringTryClob" to true and the second getting the latest 10g
> drivers from Oracle. It looks like the one that did the trick was the
> updated drivers from Oracle. In fact I didn't need the property string
> in there after I updated the driver. ;) Thanks again for the
> assistance!
>
>
> Nathan Maves wrote:
>
>
>> Steve,
>>
>> You almost had it!
>>
>> After checking the source code you just need to prepend "Driver." in
>> front of your property name.
>>
>> // Additional Driver Properties prefix
>> private static final String ADD_DRIVER_PROPS_PREFIX = "Driver.";
>>
>>
>> So .....
>>
>> <transactionManager type="JDBC">
>>   <dataSource type="SIMPLE">
>>     <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
>>     ...
>>     <property name="Driver.SetBigStringTryClob" value="true"/>
>>   </dataSource>
>> </transactionManager>
>>
>>
>> Should do the trick.  I will verify that is works in the morning and
>> if so I will append all that we have learned to the BLOB/CLOB wiki!
>>
>> Nathan
>>
>>
>> On Aug 11, 2005, at 11:57 PM, Steve Biondi wrote:
>>
>>
>>
>>
>>> I've always used an external JDBC DataSource or a previously
>>> configured connection with iBatis, so I'm not exactly sure. From
>>> glancing at the SimpleDataSource code, you should be able to set
>>> the property as is in the JDBC section of the config file and it
>>> should get passed to the DataSource create method and thereby be
>>> set in the driver. I would try adding this element to the
>>> <dataSource> element:
>>>
>>> <transactionManager type="JDBC">
>>>  <dataSource type="SIMPLE">
>>>    <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
>>>    ...
>>>    <property name="SetBigStringTryClob" value="true"/>
>>>  </dataSource>
>>> </transactionManager>
>>>
>>> Someone can correct me if I'm wrong about that. The prop should
>>> pass through to the driver.
>>>
>>> Steve
>>>
>>> -----Original Message-----
>>> From: Nathan Maves [mailto:Nathan.Maves@...]
>>> Sent: Thu 2005-08-11 6:29 PM
>>> To: user-java@...
>>> Subject: Re: Clob Issue
>>>
>>> Steve,
>>>
>>> How would one go about setting this property when using the jakarta
>>> connection pool build into ibatis?
>>>
>>> Nathan
>>>
>>> On Aug 11, 2005, at 5:13 PM, Steve Biondi wrote:
>>>
>>>
>>>
>>>
>>>
>>>> Is this with Oracle? If so, the string can only be up to 4000
>>>> characters long if you use setString even if the underlying column
>>>> is a CLOB and you tell the driver that. With bigger strings, you
>>>> need to use the CLOB-specific API stuff.
>>>>
>>>> A nice workaround is to use the Oracle10g JDBC driver and set the
>>>> following connection property:
>>>>
>>>> SetBigStringTryClob=true;
>>>>
>>>> Then, you can pass any size string to setString, and the JDBC
>>>> driver will internally do the "clob" work. You can also define your
>>>> maps using simply "string".
>>>>
>>>> FYI - the Oracle10g driver works well with both Oracle9i and 10g
>>>> databases. We support both DBs and make extensive use of clobs in
>>>> our iBatis stuff.
>>>>
>>>> Steve B.
>>>>
>>>> From: Craig Swift [mailto:Craig.Swift@...]
>>>> Sent: Thu 2005-08-11 3:33 PM
>>>> To: user-java@...
>>>> Subject: Clob Issue
>>>>
>>>> Hello,
>>>>
>>>> Has anyone seen this type of error before when trying to use
>>>> Clobs in
>>>> IBatis?
>>>>
>>>> javax.servlet.ServletException: Error executing update.  Cause:
>>>> com.ibatis.common.jdbc.exception.NestedSQLException:
>>>> --- The error occurred in recognition/dao/ibatis/mapping/
>>>> Nomination.xml.
>>>> --- The error occurred while applying a parameter map.
>>>> --- Check the updateNomination-InlineParameterMap.
>>>> --- Check the parameter mapping for the 'writeUp' property.
>>>> --- Cause: java.sql.SQLException: Data size bigger than max size
>>>> for this type: 4236
>>>>
>>>> I'm using a String as the underlining object and specifying the
>>>> JDBC type as a clob in the sql map. It works for inserts/updates/
>>>> deletes until the String size becomes to large. I was under the
>>>> impression that a CustomType Hnadler wasn't necessary. Any
>>>> information would be appreciated, thanks!
>>>>
>>>> Craig S
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> <winmail.dat>
>>>
>>>
>>>
>>>
>
>

[Steve Biondi]

FYI - I figured out how to configure connection properties using DBCP within iBatis (latest 2.1.5):
<transactionManager type="JDBC">
<dataSource type="DBCP">
<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@server:1521:sid"/>
<property name="username" value="uname"/>
<property name="password" value="pword"/>
<property name="Driver.SetBigStringTryClob" value="true"/>
</dataSource>
</transactionManager>

Note that the "JDBC." prefix has been left off the first 4 properties.
 
The iBatis DBCP configurator has two modes: "legacy" and "new".
 
If it sees the property "JDBC.Driver", it goes into "legacy" mode and manually sets values using if/then/else. But it looks like one can't set connection properties in this mode.
 
Otherwise, iBatis uses "new" mode using bean setters to config the DBCP object. Additionally, it adds any property name with the prefix "Driver." as a name- value tuple to the connection properties collection where the name is everything after "Driver.".
 
The other four property names listed above match those defined by the DBCP object itself per their configuration document (driverClassName, url, username, password).  
 
I've tested this with big (length() > 32k) strings using Oracle9i backend and the Oracle10g driver.
 
Steve

________________________________

From: Steve Biondi [mailto:SteveB@...]
Sent: Fri 2005-08-12 2:03 PM
To: user-java@...
Subject: RE: Clob Issue


Just to let you know, the upgrade only partially solves the problem. The updated driver still has a hard limit of 32766 characters for a string. Setting the "SetBigStringTryClob=true" solves this too. Try inserting a 60000 char string into a CLOB column.
 
I wrote a quick example to generate the exception text for this case:
 
java.sql.SQLException: setString can only process strings of less than 32766 chararacters
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:305)
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5220)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5191)
at TestBigClob.main(TestBigClob.java:28)
 
Here's where I configure the connection:
 
   String url = "jdbc:oracle:thin:uname/pword@server:1521:sid";
   Class.forName("oracle.jdbc.OracleDriver");
   Connection conn = null;
   PreparedStatement pstmt = null;
   Properties props = new Properties();
//   props.put("SetBigStringTryClob", "true");
   conn = DriverManager.getConnection(url, props);

     ...
If I uncomment the props.put line with SetBigStringTryClob, no exception.
 
For the iBatis basic datasource, the following might work:
 
<property name = "Driver.connectionProperties" values = "SetBigStringTryClob=true;"/>
 
We do something like this when using the Tomcat Simple JDBC Datasource. The Apache DBCP component uses bean getters/setters to configure the underlying driver, and "connectionProperties" is a collection property. DBCP apparently is smart enough to do this right, as it works in our Tomcat instances.
 
Here's an example of this DBCP config in Tomcat server.xml:
 
<Resource name="jdbc/OracleDs"    
     type="javax.sql.DataSource"
     auth="Container"/>
     
 <ResourceParams name="jdbc/OracleDs">
  <parameter>
   <name>url</name>
   <value>jdbc:oracle:thin:@server:1521:sid</value>
  </parameter>
  <parameter>
   <name>driverClassName</name>
   <value>oracle.jdbc.OracleDriver</value>
  </parameter>
  <parameter>
   <name>username</name>
   <value>user</value>
  </parameter>
  <parameter>
   <name>password</name>
   <value>password</value>
  </parameter>
  <parameter>
   <name>connectionProperties</name>
   <value>SetBigStringTryClob=true;</value>
  </parameter>
 </ResourceParams>

 
Regards,
 
Steve B.
 
________________________________

From: Nathan Maves [mailto:Nathan.Maves@...]
Sent: Fri 2005-08-12 9:52 AM
To: user-java@...
Subject: Re: Clob Issue



I have updated the BLOB/CLOB page in the wiki to reflect this new info.

Nathan

On Aug 12, 2005, at 10:24 AM, Craig Swift wrote:

> Hey Everyone,
>
> First off thanks for the assistance, appreciate it a lot. I tried both
> suggestions. The first being setting the driver property
> "SetBigStringTryClob" to true and the second getting the latest 10g
> drivers from Oracle. It looks like the one that did the trick was the
> updated drivers from Oracle. In fact I didn't need the property string
> in there after I updated the driver. ;) Thanks again for the
> assistance!
>
>
> Nathan Maves wrote:
>
>
>> Steve,
>>
>> You almost had it!
>>
>> After checking the source code you just need to prepend "Driver." in
>> front of your property name.
>>
>> // Additional Driver Properties prefix
>> private static final String ADD_DRIVER_PROPS_PREFIX = "Driver.";
>>
>>
>> So .....
>>
>> <transactionManager type="JDBC">
>>   <dataSource type="SIMPLE">
>>     <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
>>     ...
>>     <property name="Driver.SetBigStringTryClob" value="true"/>
>>   </dataSource>
>> </transactionManager>
>>
>>
>> Should do the trick.  I will verify that is works in the morning and
>> if so I will append all that we have learned to the BLOB/CLOB wiki!
>>
>> Nathan
>>
>>
>> On Aug 11, 2005, at 11:57 PM, Steve Biondi wrote:
>>
>>
>>
>>
>>> I've always used an external JDBC DataSource or a previously
>>> configured connection with iBatis, so I'm not exactly sure. From
>>> glancing at the SimpleDataSource code, you should be able to set
>>> the property as is in the JDBC section of the config file and it
>>> should get passed to the DataSource create method and thereby be
>>> set in the driver. I would try adding this element to the
>>> <dataSource> element:
>>>
>>> <transactionManager type="JDBC">
>>>  <dataSource type="SIMPLE">
>>>    <property name="JDBC.Driver" value="jdbc.oracle.OracleDriver"/>
>>>    ...
>>>    <property name="SetBigStringTryClob" value="true"/>
>>>  </dataSource>
>>> </transactionManager>
>>>
>>> Someone can correct me if I'm wrong about that. The prop should
>>> pass through to the driver.
>>>
>>> Steve
>>>
>>> -----Original Message-----
>>> From: Nathan Maves [mailto:Nathan.Maves@...]
>>> Sent: Thu 2005-08-11 6:29 PM
>>> To: user-java@...
>>> Subject: Re: Clob Issue
>>>
>>> Steve,
>>>
>>> How would one go about setting this property when using the jakarta
>>> connection pool build into ibatis?
>>>
>>> Nathan
>>>
>>> On Aug 11, 2005, at 5:13 PM, Steve Biondi wrote:
>>>
>>>
>>>
>>>
>>>
>>>> Is this with Oracle? If so, the string can only be up to 4000
>>>> characters long if you use setString even if the underlying column
>>>> is a CLOB and you tell the driver that. With bigger strings, you
>>>> need to use the CLOB-specific API stuff.
>>>>
>>>> A nice workaround is to use the Oracle10g JDBC driver and set the
>>>> following connection property:
>>>>
>>>> SetBigStringTryClob=true;
>>>>
>>>> Then, you can pass any size string to setString, and the JDBC
>>>> driver will internally do the "clob" work. You can also define your
>>>> maps using simply "string".
>>>>
>>>> FYI - the Oracle10g driver works well with both Oracle9i and 10g
>>>> databases. We support both DBs and make extensive use of clobs in
>>>> our iBatis stuff.
>>>>
>>>> Steve B.
>>>>
>>>> From: Craig Swift [mailto:Craig.Swift@...]
>>>> Sent: Thu 2005-08-11 3:33 PM
>>>> To: user-java@...
>>>> Subject: Clob Issue
>>>>
>>>> Hello,
>>>>
>>>> Has anyone seen this type of error before when trying to use
>>>> Clobs in
>>>> IBatis?
>>>>
>>>> javax.servlet.ServletException: Error executing update.  Cause:
>>>> com.ibatis.common.jdbc.exception.NestedSQLException:
>>>> --- The error occurred in recognition/dao/ibatis/mapping/
>>>> Nomination.xml.
>>>> --- The error occurred while applying a parameter map.
>>>> --- Check the updateNomination-InlineParameterMap.
>>>> --- Check the parameter mapping for the 'writeUp' property.
>>>> --- Cause: java.sql.SQLException: Data size bigger than max size
>>>> for this type: 4236
>>>>
>>>> I'm using a String as the underlining object and specifying the
>>>> JDBC type as a clob in the sql map. It works for inserts/updates/
>>>> deletes until the String size becomes to large. I was under the
>>>> impression that a CustomType Hnadler wasn't necessary. Any
>>>> information would be appreciated, thanks!
>>>>
>>>> Craig S
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> <winmail.dat>
>>>
>>>
>>>
>>>
>
>