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: Clob Issue
Date Fri, 12 Aug 2005 21:03:03 GMT
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@Sun.COM]
Sent: Fri 2005-08-12 9:52 AM
To: user-java@ibatis.apache.org
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@Sun.COM]
>>> Sent: Thu 2005-08-11 6:29 PM
>>> To: user-java@ibatis.apache.org
>>> 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@Sun.COM]
>>>> Sent: Thu 2005-08-11 3:33 PM
>>>> To: user-java@ibatis.apache.org
>>>> 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>
>>>
>>>
>>>
>>>
>
>




Mime
View raw message