Dashboard > iBATIS DataMapper > Home > Environment Specific Information > Information > Page Comparison
Environment Specific Information
Version 14 by Guido
on Aug 31, 2006 04:59.


compared with
Current by John Beatty
on Oct 07, 2006 15:46.

(show comment)
 
Key
These lines were removed. This word was removed.
These lines were added. This word was added.

View page history


There are 3 changes. View first change.

 h1. Environment Specific Solutions
  
  
 h2. Oracle
  
  
 h3. BLOBs/CLOBs
  
 As of release 2.0.9, iBATIS includes default BLOB/CLOB handlers. If you are using an older version of Oracle (pre-10g), then you might have to write your own Custom Tag Handler to access the proprietary Oracle API to work with LOBs.
  
 h3. Some selectKey examples
  
 {noformat}
 <insert id="insert" parameterClass="customer">
  <selectKey resultClass="int" keyProperty="id">
  select someSequence.NEXTVAL as "id" from dual
  </selectKey>
  insert into Customer (id, name)
  values (#id#, #name#)
 </insert>
 {noformat}
 {noformat}
 An example of using selectKey with a parameterMap.
 The selectKey stanza is executed and the result is used to set the property 'pkey' of the parameter object,
 then the parameter map is invoked.
  
 <insert id="insert2" parameterClass="Entity" parameterMap="insert-paramMap">
  <selectKey resultClass="int" keyProperty="pkey"> SELECT seq.nextval FROM DUAL </selectKey>
  INSERT INTO EntityTable (PKEY, DATA) VALUES (?, ?)
 </insert>
 {noformat}
  
 h3. LONG
  
 I was having trouble using an Oracle LONG column with an iBatis sqlmap.&nbsp; I had defined the jdbcType as a LONGVARCHAR in the sqlmap, but iBatis was still attempting to call getCLOB on the oracle jdbc driver's LongAccessor class.&nbsp; The solution to this was to explicitly use the StringTypeHandler on the column, as follows:
  
 h3. Using the StringTypeHandler on an Oracle LONG column - attempt #1 (failed)
  
 {noformat}
 <result column="MY_VARCHAR_COLUMN" property="myStringProperty" jdbcType="VARCHAR" />
 <result column="MY_LONG_COLUMN" property="myStringProperty2" jdbcType="LONGVARCHAR" typeHandler="com.ibatis.sqlmap.engine.type.StringTypeHandler"/>
 {noformat}
 This correctly resulted in a call to LongAccessor.getString() in the oracle jdbc driver.&nbsp; However, it threw up a new problem, "Stream has already been closed".&nbsp;
  
 The solution was to change the ordering of the columns in the result map so that the LONG column was accessed first, as follows:
  
 h3. Using the StringTypeHandler on an Oracle LONG column - attempt #2 (succeeded)
  
 {noformat}
 <result column="MY_LONG_COLUMN" property="myStringProperty2" jdbcType="LONGVARCHAR" typeHandler="com.ibatis.sqlmap.engine.type.StringTypeHandler"/>
 <result column="MY_VARCHAR_COLUMN" property="myStringProperty" jdbcType="VARCHAR" />
 {noformat}
 So in summary, to use an Oracle LONG column, the following worked for me:
 # Declare the jdbcType attribute as "LONGVARCHAR" in the result map
 # Use a typeHandler attribute of "com.ibatis.sqlmap.engine.type.StringTypeHandler"
 # Ensure the LONG column is the first declared column in the result map.
  
 h3. Oracle Paging
  
 An example of how to select a subset of an ordered list. The keys added to the parameter map over and above the abator generated ones (used by the 'Where_Clause' sql) are FIRST (0-based, hence the "\+ 1") and COUNT (no of entries to return).
 {noformat}
 <select id="selectPagedIds" resultClass="int" parameterClass="java.util.Map">
  select * from (
  select a.*, ROWNUM rn from (
  select ID from Schema.MYTABLE
  <include refid="Where_Clause"/>
  order by $ABATOR_ORDER_BY_CLAUSE$) a
  WHERE ROWNUM <![CDATA[ <= ]]> (#FIRST# + #COUNT#))
  WHERE rn <![CDATA[ >= ]]> (#FIRST# + 1)
 </select>
 {noformat}
  
 h3. Timeout
  
 As of release 2.2.0, iBATIS includes a query timeout funcionality.
 You can specify both a global timeout
 {noformat}
 <settings defaultStatementTimeout="2" />
 {noformat}
 or a per query timeout
 {noformat}
 <statement ... timeout="2">
 {noformat}
 When the timeout expires, it throws a SQL Exception "ORA-01013 : user requested cancel of current operation".
  
 Tested with:
 * Oracle 9i release 2 drivers.
  
 h2. Sybase
  
  
 h3. Stored Proc/UNCHAINED mode
  
 By default, Sybase doesn't allow procs to be run in a transactional context. You can work around this problem a few different ways:
  
 1. Use your own Connection and .setAutoCommit(true). Pass this connection into the SqlMapClient.setUserConnection(Connection) method. You're responsible for closing the connection afterwards. If you like, you can get the Connection from the same DataSource by calling SqlMapClient.getDataSource(). Although this seems a bit "roundabout", it works.
  
 2. In Sybase, use the following command to change all stored procedures into proper transaction mode.
  
 sp_procxmode <stored procedure> , "anymode"
  
 3. Use the longer approach proposed by Scott Severtson: \["Scott's Sybase Proc Solution"\]
  
 h2. MySQL
  
  
 h3. Transactions
  
 Older versions of MySQL don't support transactions. Make sure you're using a modern MySQL release and the latest drivers. iBATIS won't work without Transaction support (an RDBMS without TX support is kind of silly anyway).
  
 h3. Some selectKey Examples
  
 {noformat}
 <insert id="insertFolder" parameterClass="folder">
  INSERT INTO folder (parent_id, owner, foldername)
  VALUES (#parentId#, #owner#, #foldername#)
  <selectKey resultClass="int" keyProperty="folderId">
  SELECT LAST_INSERT_ID() AS folderId
  </selectKey>
 </insert>
 {noformat}
  
 h2. WebSphere
  
  
 h3. Container Managed Transactions (CMT) Configuration
  
 This transaction configuration is appropriate for WebSphere when using EJBs and container managed transactions (CMT).
 {code:xml}
 <transactionManager type="EXTERNAL">
  <property name="SetAutoCommitAllowed" value="false"/>
  <dataSource type="JNDI">
  <property name="DataSource" value="java:comp/env/..."/>
  </dataSource>
  </transactionManager>
 {code}
  
 h3. Bean Managed Transactions or Servlets Without EJBs
  
 This transaction configuration is appropriate for WebSphere when your are using WebSphere connection pooling, but not using EJBs (servlets only). It is also appropriate when using WebSphere connection pooling, EJBs, and bean managed transactions.
 {code:xml}
 <transactionManager type="JTA" commitRequired="true">
  <property name="UserTransaction" value="java:comp/env/UserTransaction"/>
  <dataSource type="JNDI">
  <property name="DataSource" value="java:comp/env/..."/>
  </dataSource>
  </transactionManager>
 {code}
 For more information on using WAS with iBATIS read this article on the developerworks website:
 [http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0502cline/]
  
 h2. PostgreSQL
  
  
 h3. Some selectKey Examples
  
 {noformat}
 <insert id="insert">
  <selectKey keyProperty="accountId" resultClass="int">
  SELECT nextVal('account_accountid_seq')
  </selectKey>
  INSERT INTO Account (accountId, username, password)
  VALUES (#accountId#, #username#, #password#)
</insert>
  </insert>
 {noformat}
  
 If accountId is defined as a 'serial' column in your schema, you can allow the accountId to default to the next number in the sequence and use Postgres's currval function to retrieve the generated ID.
 {code:xml}
 <insert id="insert">
  INSERT INTO Account (accountId, username, password)
  VALUES (#accountId#, #username#, #password#)
  <selectKey keyProperty="accountId" resultClass="long">
  SELECT currval('account_accountid_seq')
  </selectKey>
 </insert>
 {code}
  
 h2. DB400 (AS400 / iSeries)
  
  
 h3. Invalid Usages of the Parameter Marker ?
  
 DB400 has some quirks about where the ? can go in the statement.&nbsp;&nbsp; For instance, the ? cannot be "an operand of a scalar function."&nbsp; This means that DB400 will complain about using functions like UPPER or TRIM. This is a quirk with using *PreparedStatement{*}s, and therefore, SqlMaps.&nbsp; Below is an example of how to get scalar functions to work, much like the example of using LIKE in the FAQ section.
  
 For more info, see&nbsp; [http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzala/rzalaml.htm]&nbsp; Look for SQL code *SQL0418.*
 {noformat}
 <select id="company" resultMap="getCompanyInfo" parameterClass="java.lang.String">
  <!--
  The 400 does not allow ? in scalar functions, like UPPER(?).
  This means we must use $value$ instead of #value#.
  We must rely on Java to escape the value before passing it in to SqlMaps
  by using StringEscapeUtils.escapeSql(value) to avoid injection attacks.
  -->
  SELECT COID, NAME
  FROM USRCOM
  WHERE UPPER(USER) = UPPER('$value$')
  ORDER BY NAME
 </select>
 {noformat}
Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators