iBATIS DataMapper
Added by Jeff Butler, last edited by Jeff Butler on Sep 20, 2006  (view change)
Labels: 
(None)


OBSOLETE

This page is obsolete, as of iBATIS 2.2.0 we now support multiple result sets returned from queries and stored procedures so the need for this new function has reduced sharply.

The Problem

The traffic on the user's list regarding stored procedures has increased dramatically recently. This is partially because using procedures in iBATIS is not as intuitive as it could be. There are also at least 5 JIRA issues related to stored procedures (28, 96, 173, 176, 178). Some of these issues may be bugs, but some are related to support that cannot be offered given the current setup.

The Proposed Solution

New DTD Syntax

Alter the DTD to allow syntax for declaring procedures something like this:

Option 1

<storedProcedureParameterMap id="myParameterMap" class="map">
<storedProcedureReturnCode property="rc" javaType="java.math.BigDecimal" jdbcType="DECIMAL" numericScale="2"/>
<storedProcedureParameter property="num1" parameterName="@num1" javaType="java.math.BigDecimal" jdbcType="DECIMAL" mode="IN" />
<storedProcedureParameter property="num2" parameterName="@num2" javaType=" java.math.BigDecimal" jdbcType="DECIMAL" mode="IN" />
<storedProcedureParameter property="total" parameterName="@total" javaType="java.math.BigDecimal" jdbcType="DECIMAL" mode="OUT" numericScale="2" />
</storedProcedureParameterMap>

<storedProcedureResultSets id="myResultSets">
<storedProcedureResultSet resultClass="myresults.Result1" />
<storedProcedureResultSet resultClass="myresults.Result2" />
</storedProcedureResultSets>

<storedProcedure id="myproc" procedureName="db2admin.MyProc" parameterMap="myParameterMap" resultSets="myResultSets" />

This syntax allows unlimited paramters, allows use of parameter names (optional), allows multiple result sets, allows definition of a stored procedure return code.

Option 2

<storedProcedure id="myproc2" procedureName="db2admin.MyProc" parameterClass="map" >
<storedProcedureReturnCode property="rc" javaType="java.math.BigDecimal" jdbcType="DECIMAL" numericScale="2"/>
<storedProcedureParameter property="num1" javaType="java.math.BigDecimal" jdbcType="DECIMAL" mode="IN" />
<storedProcedureParameter property="num2" javaType=" java.math.BigDecimal" jdbcType="DECIMAL" mode="IN" />
<storedProcedureParameter property="total" javaType="java.math.BigDecimal" jdbcType="DECIMAL" mode="OUT" numericScale="2" />
<storedProcedureResultSet resultClass="myresults.Result1" />
<storedProcedureResultSet resultClass="myresults.Result2" />
</storedProcedure>

This syntax is similar to the above, but more compact. Both options could be supported.

New Method for Stored Procedures

Add a method "executeProcedure" to the existing SQLExecuter and SqlMapClient classes. This method would:

  1. Compose the JDBC call statement from the procedure name and the specified parameters
  2. Deal with parameters (set input parameters, register output parameters)
  3. Execute the procedure
  4. Deal with results - multiple ResultSets or UpdateCounts are allowed, we will consume everything the procedure spits back at us.
  5. Get the output parameters, and possibly the return code
    The executeProcedure method would always return on object of type ProcedureResults (or similar).

The ProcedureResults object looks like this:

public class ProcedureResults {
    private ArrayList results;
    private Object returnCode;

    //getters, setters, yada, yada...
}

The results list contains Lists (in the case of ResultSets), or Integers(in the case of UpdateCounts). It would be up to the user to determine if the result was a List or an Integer - either dynamically with instanceof, or just by knowing what the procedure would return and in what order. The returnCode is the procedure return code (if expected), or null.

Hi

I have sybase procedure to call. the procedure returns loads of records. so i used a resultMap (mapping only required fields from the resultset), but when i run the application it is giving me NullPointerException.

Posted by Keyur Thadeshwar at Jan 12, 2006 03:02

Hiu

Even in this thread spread some weeks before

How do I call a stored procedure, Sep 06, 2005

http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+call+a+stored+procedure?focusedCommentId=5821

I've just come here searching the internet while trying to understand
which method should be called on a SqlMapClient object to run a remote procedure
(specifically a method, query or function, of an Oracle package)

Section "Executing Statements via the SqlMapClient API", p. 43, of the iBATIS-SqlMaps-2.pdf guide does NOT mention any method to call procedures.

Neither do the identifiers and the signatures of the methods available via the inherited interfaces.

Do I miss anything?

Thanks in advance for any help.
Cesare

Posted by Cesare.Zecca at Sep 20, 2006 08:56
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