You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by Jeff Butler <je...@gmail.com> on 2005/08/09 16:53:14 UTC
A New Look at Stored Procedures
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
Re: A New Look at Stored Procedures
Posted by Jeff Butler <je...@gmail.com>.
Done! I'll clean up my mocked code and post it sometime soon.
Jeff Butler
On 8/9/05, Larry Meadors <la...@gmail.com> 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 <je...@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
> >
>
Re: A New Look at Stored Procedures
Posted by Larry Meadors <la...@gmail.com>.
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 <je...@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
>