Done! I'll clean up my mocked code and post it sometime soon.
Jeff Butler
On 8/9/05, Larry Meadors <[EMAIL PROTECTED]> wrote:
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 <[EMAIL PROTECTED]> 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
>
