ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: Oracle function result sets with iBatis
Date Sun, 16 Jan 2005 04:24:32 GMT
Could you explain more clearly what you changed?  

Clinton


On Sat, 15 Jan 2005 13:01:19 -0600, Ken Katsma <kenkatsma@gmail.com> wrote:
> I think iBatis is a great product, my group was looking to build
> something that extracted our sql into xml files anyway, and when I found
> iBatis, I was pretty happy to see all that work has already been done.
> 
> One thing it can't seem to do right now though is support Oracle result
> sets from functions.  If there is a way to do so without modifying the
> code let me know.  I went in and modified SQLExecutor to support the
> functionality I need.  This is a really bad hack, and it would be great
> if we could specify our own SQLExecutor in the xml file to implement
> database specific code like this in a more elegant fashion.  But this
> works, and all you have to do is modify the SQLExecutor like this:
> 
>   public void executeQueryProcedure(RequestScope request, Connection
> conn, String sql, Object[] parameters,
>                                     int skipResults, int maxResults,
> RowHandlerCallback callback)
>       throws SQLException {
>     ErrorContext errorContext = request.getErrorContext();
>     errorContext.setActivity("executing query procedure");
>     errorContext.setObjectId(sql);
> 
>     CallableStatement cs = null;
>     ResultSet rs = null;
> 
>     try {
>       errorContext.setMoreInfo("Check the SQL Statement (preparation
> failed).");
>       cs = conn.prepareCall(sql);
> 
>       ParameterMap parameterMap = request.getParameterMap();
> 
>       ParameterMapping[] mappings = parameterMap.getParameterMappings();
> 
>       errorContext.setMoreInfo("Check the output parameters (register
> output parameters failed).");
>       registerOutputParameters(cs, mappings);
> 
>       errorContext.setMoreInfo("Check the parameters (set parameters
> failed).");
>       parameterMap.setParameters(request, cs, parameters);
> 
>       errorContext.setMoreInfo("Check the statement (update procedure
> failed).");
> 
>       if
> (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
>       {
>           boolean b = cs.execute();
> 
>           errorContext.setMoreInfo("In Oracle query mode.");
>           errorContext.setMoreInfo("Check the output parameters
> (retrieval of output parameters failed).");
>           retrieveOutputParameters(cs, mappings, parameters);
> 
>           for (int i=0;i<parameters.length;i++)
>           {
>               if (parameters[i] instanceof ResultSet)
>               {
>                   rs = (ResultSet) parameters[i];
>                   break;
>               }
>           }
> 
>           errorContext.setMoreInfo("Check the results (failed to
> retrieve results).");
>           handleResults(request, rs, skipResults, maxResults, callback);
>       }
>       else
>       {
>           errorContext.setMoreInfo("In non-Oracle mode.");
>           rs = cs.executeQuery();
> 
>         errorContext.setMoreInfo("Check the results (failed to retrieve
> results).");
>         handleResults(request, rs, skipResults, maxResults, callback);
> 
>         errorContext.setMoreInfo("Check the output parameters (retrieval
> of output parameters failed).");
>         retrieveOutputParameters(cs, mappings, parameters);
>       }
>       } finally {
>       try {
>         closeResultSet(rs);
>       } finally {
>         closeStatement(cs);
>       }
>     }
> 
> If you stick this above the base iBatis libraries, you can now get the
> results out.
> No big deal in the xml file:
> 
> <typeAlias alias="Client" type="com.test.vo.ClientVO" />
> 
>     <resultMap id="clientResult" class="Client">
>          <result property="clntSeq" columnIndex="1" />
>          <result property="clntName" columnIndex="2" />
>         <result property="clntSubType" columnIndex="3" />
>         <result property="clntCode" columnIndex="4"/>
>         <result property="clntMailName" columnIndex="5" />
>          <result property="clntLOBCode" columnIndex="6" />
>          <result property="clntCompanyID" columnIndex="7" />
>          <result property="clntCostCenter" columnIndex="8" />
>     </resultMap>
> 
>     <parameterMap id="clientParameters" class="map" >
>         <parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
>         <parameter property="maxRows" jdbcType="VARCHAR"
> javaType="java.lang.String" mode="IN"/>
>     </parameterMap>
> 
>     <procedure id="getClientListProc" resultMap="clientResult"
> parameterMap="clientParameters">
>         {?= call abc.CLIENT_VIEW_PKG.client_result_list_f(?)}
>     </procedure>
> 
> Hope nobody get's offended by the ugliness of this hack, but we really
> need the functionality as all queries are sp's in my project (no choice).
> 
> Thanks,
> 
> Ken
> 
> 
>

Mime
View raw message