Executing Stored Procedures with MyBatis

[Jeff Butler]

Hello Everyone,

There have been a lot of questions recently about how to call stored
procedures with MyBatis. This is an area where the documentation is,
admittedly, very weak. Until we can improve the documentation, I've
committed several tests that demonstrate the many different ways to
call stored procedures with MyBatis. You can use this as example code
for the different situations you might face. The complete test
package is here:

http://mybatis.googlecode.com/svn/trunk/src/test/java/org/apache/ibatis/submitted/sptests/

Please note that this code will only fully run with the HSQLDB 2.0.1
release candidates. In the code you will see that many of the tests
have the @Ignore annotation so we don't break the MyBatis build. Once
HSQLDB 2.0.1 is released, I'll remove the @Ignore annotation for many
of these tests.

If you are having trouble with stored procedures in MyBatis, please
review this sample code and then feel free to ask questions.

In the last couple of days we have talked about a potential problem
with MyBatis and stored procedures. Here are the circumstances under
which you will see this problem:

1. The procedure has one or more output parameters AND returns a result set.
2. You call the procedure more than once in the same session, with the
exact same input parameters.

In this case, the output parameters will not be returned properly on
subsequent calls, unless you clear the session cache manually before
each subsequent call.

But this is a VERY RARE situation indeed. So the message is that the
vast majority of stored procedures will work just fine with MyBatis as
it stands.

Enjoy!
Jeff Butler

[Clinton Begin]

This is fantastic Jeff, thanks so much!

Clinton

[naveen SR]

Hi Jeff,

Thanks a lot for this example. I have been able to finally resolve my issues calling stored proc. I will mention the root cause of my issue on the separate on going mail chain for completeness.

I would like to clarify your second point in the trail mail on MyBatis having issue calling the same Stored Proc more than once within same session. If I have a Java Arraycollection which requires to be sent to the Database for storing. So using the ForEach tag in the Mapper.xml would not work? If yes, is there a way to send a List to a stored proc?

Thanks,
Naveen

[scarlettxu]

Hi Jeff, Quite appreciate that you noticed our stored procedure problems and give out an example. As I have met one problem and researched for nearly 2 weeks still without solution, and I have post my issue here:http://mybatis-user.963551.n3.nabble.com/MyBatis-call-stored-procedure-return-null-result-td1846154.html#a1846154 but nobody reply me yet. So I copy your example and run it, but I still face the same problem, Maybe you are the right person to help on this. As you use the HSQLDB, and I use Sybase, so did not completely copy the sql for create table and sp, I modify a little to make it simpler. I simplify your SPTest.java to only have one main method: public class SPTest { private static SqlSessionFactory createSqlMapper() throws IOException { String resource = "resources/sqlMapConfig.xml"; Reader reader = Resources.getResourceAsReader(resource); return new SqlSessionFactoryBuilder().build(reader,"development"); } public static void main(String[] args) { SqlSession sqlSession = null; try { sqlSession = createSqlMapper().openSession(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { SPMapper spMapper = sqlSession.getMapper(SPMapper.class); Map parms = new HashMap(); parms.put("first_name", "Barney"); List names = spMapper.getNames(parms); System.out.println(names); } finally { sqlSession.close(); } } } and I create the table without column ID: create table names(first_name varchar(20),last_name varchar(20)) go insert into names (first_name, last_name) values('Fred', 'Flintstone') insert into names (first_name, last_name) values('Wilma', 'Flintstone') insert into names (first_name, last_name) values('Barney', 'Rubble') insert into names (first_name, last_name) values('Betty', 'Rubble') go I create the getNames stored procedure as below: create proc getNames @in varchar(10) as declare @cur varchar(20) select @cur=first_name from names select first_name,last_name from names where first_name >= @in go the SPMapper.xml: {call SET CHAINED OFF exec tempdb..getNames #{in,jdbcType=VARCHAR,mode=IN}} I did not change other files, when run SPTest, result is null: DEBUG PooledDataSource - Created connection 12423818. DEBUG Connection - ooo Connection Opened DEBUG PreparedStatement - ==> Executing: {call SET CHAINED OFF exec tempdb..getNames ?} DEBUG PreparedStatement - ==> Parameters: null Result names - [] DEBUG Connection - xxx Connection Closed DEBUG PooledDataSource - Returned connection 12423818 to pool. I'm feeling very tough about this problem, Hope you can have a look of my problem and help me on this problem, as MyBatis is so lightweight, I do not want to drop it in our new program. Besides, I conclude the problem as: If there are more than one selection in the stored procedure, when call the sp in MyBatis, only the first selection will be executed.

---

View this message in context: Re: Executing Stored Procedures with MyBatis
Sent from the mybatis-user mailing list archive at Nabble.com.

[Jeff Butler]

I noticed two things immediately...

1. In your HashMap, the parameter is called "first_name", in your XML
that parameter is called "in". So you need to be consistent there.
2. In the XML, the call looks wrong. You've not added the required
parenthesis, and there's a lot of extra stuff in the call. Stored
proc calls MUST follow the standard JDBC escape sequence.

So, change your XML to this and see if it improves:

{call tempdb..getNames (#{first_name,jdbcType=VARCHAR,mode=IN})}

Jeff Butler

> from names where first_name >= @in go the SPMapper.xml: I did not change

[scarlett xu]

Hi Jeff,

I change the SPMapper.xml file according to your below advice, but it has not solved my problem.

And the cause seems evident. it is the stored procedure that matters. you may have not clear what my problem is, I show you three testing case, you will be clear after that.

1/
when there is only one selection(see the yellow background content), the sp will return the selection result of that selection.
1> use tempdb
2> go
1> drop proc getNames
2> go
1> create proc getNames
2> @in varchar(10)
3> as
4> declare @cur varchar(20)
5> select *  from names where first_name >= @in
6> go
1> grant exec on getNames to public
2> go

DEBUG Connection - ooo Connection Opened

DEBUG PreparedStatement - ==>  Executing: {call tempdb..getNames (?)}
DEBUG PreparedStatement - ==> Parameters: Barney(String)
DEBUG ResultSet - <==    Columns: first_name, last_name
DEBUG ResultSet - <==        Row: Fred, Flintstone
DEBUG ResultSet - <==        Row: Wilma, Flintstone
DEBUG ResultSet - <==        Row: Barney, Rubble
DEBUG ResultSet - <==        Row: Betty, Rubble
Result names - [com.icil.esolution.cargoStatus.AS.model.Name@1506dc4, com.icil.esolution.cargoStatus.AS.model.Name@15663a2, com.icil.esolution.cargoStatus.AS.model.Name@a761fe, com.icil.esolution.cargoStatus.AS.model.Name@1126b07]

DEBUG Connection - xxx Connection Closed

2/
when I add another selection(see blue background part) before that selection, only the new added selection will exec, and that selection has null result, so the sp returns null.
1> drop proc getNames
2> go
1> create proc getNames
2> @in varchar(10)
3> as
4> declare @cur varchar(20)
5> select @cur=first_name from names where first_name >= @in
6> select *  from names where first_name >= @in
7> go
1> grant exec on getNames to public
2> go

DEBUG Connection - ooo Connection Opened

DEBUG PreparedStatement - ==>  Executing: {call tempdb..getNames (?)}
DEBUG PreparedStatement - ==> Parameters: Barney(String)

Result names - []
DEBUG Connection - xxx Connection Closed

3/
Then I do another testing, change the sequence of the two selection, this time, the sp returns the result of the first selection.
1> drop proc getNames
2> go
1> create proc getNames
2> @in varchar(10)
3> as
4> declare @cur varchar(20)
5> select *  from names where first_name >= @in
6> select @cur=first_name from names where first_name >= @in
7> go
1> grant exec on getNames to public
2> go

DEBUG Connection - ooo Connection Opened

DEBUG PreparedStatement - ==>  Executing: {call tempdb..getNames (?)}
DEBUG PreparedStatement - ==> Parameters: Barney(String)
DEBUG ResultSet - <==    Columns: first_name, last_name
DEBUG ResultSet - <==        Row: Fred, Flintstone
DEBUG ResultSet - <==        Row: Wilma, Flintstone
DEBUG ResultSet - <==        Row: Barney, Rubble
DEBUG ResultSet - <==        Row: Betty, Rubble
Result names - [com.icil.esolution.cargoStatus.AS.model.Name@1506dc4, com.icil.esolution.cargoStatus.AS.model.Name@15663a2, com.icil.esolution.cargoStatus.AS.model.Name@a761fe, com.icil.esolution.cargoStatus.AS.model.Name@1126b07]

DEBUG Connection - xxx Connection Closed

So now, you must be clear about my problem.
when there are more than one selection in the stored procedure, only the first selection will be execed, just that cause the problem.

It will be quite thankful if you can help me~~

[Jeff Butler]

Well, you have the MyBatis stuff working correctly. The problem is on
the sybase side. I'm no sybase expert, but I believe that you should
add this line to the start or your stored procedures:

SET NOCOUNT ON

This will tell sybase not to show the intermediate rowcounts. There
have been some posts in the past about this issue.

Jeff Butler

[scarlett xu]

Hi Jeff,

Thanks for your advice.

But if I directly exec the SP on sybase, there is no problem! Also my other colleagues who use other OR mapping frmework such as Hibernate also has no such problem.

only when I call SP through MyBatis, there will be such problem.

Isn't there some setting on MyBatis side to avoid this problem?

I have searched past posts, some other guys also report this kind of problem, but no one answered.

[Jeff Butler]

Did you try it?

Jeff

[scarlett xu]

Yes, I tried to exec
1> SET NOCOUNT ON
2> go
on the DB, then use MyBatis to call the sp.
this does not help/

[scarlett xu]

Hi Jeff,

thank you so much! your idea works~~
i add the set nocount on in the sp, it works.

[scarlett xu]

Hi Jeff,

I searched one posts on the internet.

>> > 1> create proc getNames
>> > 2> @in varchar(10)
>> > 3> as
>> > 4> declare @cur varchar(20)
>> > 5> select *  from names where first_name >= @in
>> > 6> go

someone says if the application call the sp through ado or .net, the first line is null result, the second line is the result which we actually want, and can use recordset.nextrecordset or datareader.nextresult to get the second line.

I wonder if I do not set the 'set nocount on' for the sp, will the second statement actually be executed or not?

if the second statement also be executed, is there an API in MyBatis that can used to get the next result just like recordset.nextrecordset ?

[Jeff Butler]

"set nocount on" does not stop execution of statements, it stops
Sybase from reporting intermediate results back to the client. This
is what was causing your problems - MyBatis was not setup to deal with
the intermediate results reported back from Sybase.

If you actually want to process more than one result set, then MyBatis
can do it - simply specify more than one resultMap like this:

resultMap="map1,map2,map3"

Jeff Butler

[scarlett xu]

Hi Jeff,

I can get more than one result set by specifying resultMap="map1,map2,map3".

But why no result set if Sybase reporting intermediate results back to the client? Is it the Sybase problem?