Problems with stored procedures

[Eric Williams <er...@sandiego.com>]

Hi,

We're in the process of investigating iBATIS for use in our application. I'm
really excited about it since we use stored procedures for database access.
I'm having a few problems getting up and running, though:

When I call one of our stored procedures to return a result set, that stored
procedure will itself call another procedure to log the operation in a
logging table. When running the procedure by hand or via JDBC code the log
entry gets written out. When using iBATIS, it does not. It's as though the
transaction is being rolled back. I tried controlling the transaction with
startTransaction, commitTransaction and endTransaction, but had no success.
But even though the log entry isn't written, the data is still returned, and
iBATIS creates my objects.

My second problem involves using stored procedures to perform inserts. These
stored procedures do not return a result set, and I'm able to deal with that
in JDBC code by using executeUpdate instead of executeQuery. With iBATIS I
get the error: java.sql.SQLException: The executeQuery method must return a
result set. How should I deal with this?

Thanks very much for any help you can provide!

Regards,
	Eric

Re: Problems with stored procedures

[Clinton Begin <cl...@gmail.com>]

Ahhh....I'm guessing you're calling queryForList or queryForObject and
expecting an update to be done as well.  That's fair.  However, iBATIS
is "smart" in that it only truly commits when you call
insert/update/delete (and is therefore sometimes "dumb").  The
solution in your case would be to use 2.0.8 and set
commitRequired="true" on the <transactionManager> element.  For
example:

<transactionManager type="JDBC|JTA" commitRequired="true"> 

Cheers,
Clinton


On Tue, 7 Dec 2004 16:26:52 -0800, Eric Williams <er...@sandiego.com> wrote:
> Hi,
> 
> We're in the process of investigating iBATIS for use in our application. I'm
> really excited about it since we use stored procedures for database access.
> I'm having a few problems getting up and running, though:
> 
> When I call one of our stored procedures to return a result set, that stored
> procedure will itself call another procedure to log the operation in a
> logging table. When running the procedure by hand or via JDBC code the log
> entry gets written out. When using iBATIS, it does not. It's as though the
> transaction is being rolled back. I tried controlling the transaction with
> startTransaction, commitTransaction and endTransaction, but had no success.
> But even though the log entry isn't written, the data is still returned, and
> iBATIS creates my objects.
> 
> My second problem involves using stored procedures to perform inserts. These
> stored procedures do not return a result set, and I'm able to deal with that
> in JDBC code by using executeUpdate instead of executeQuery. With iBATIS I
> get the error: java.sql.SQLException: The executeQuery method must return a
> result set. How should I deal with this?
> 
> Thanks very much for any help you can provide!
> 
> Regards,
>         Eric
> 
>