ibatis-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Larry Meadors <larry.mead...@gmail.com>
Subject Re: A New Look at Stored Procedures
Date Tue, 09 Aug 2005 14:56:14 GMT
I agree, stored proc support seems to be incredibly problematic,
because there seem to be as many ways to get data back as there are
ways to..um, skin a cat or something.

Put this on the wiki so we have a whiteboard instead of emails. I have
been thinking along the same lines, so maybe we can all come up with
something workable.

Larry


On 8/9/05, Jeff Butler <jeffgbutler@gmail.com> wrote:
> 
> 
> Let me say this first...ARGH!!! 
> 
> I think we need to take another look at stored procedure support in iBATIS. 
> 
> The traffic on the user's list regarding stored procedures has increased
> dramatically recently.  I think 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. 
> So I have a proposal. 
>   
> New DTD Syntax 
> 
> We could 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 
> 
> Then I propose that we 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).  That object looks like this: 
> public class ProcedureResults {
>     ArrayList results;
>     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. 
> 
> I've done some initial mocking of my proposed executeProcedure method so I
> think this is possible.  I'm sure there are issues I haven't thought of, but
> this is a start. 
> 
> What do you think? 
> 
> Jeff Butler
>

Mime
View raw message