Quantcast

Executing Stored Procedures with MyBatis

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

Clinton Begin
Administrator
This is fantastic Jeff, thanks so much!

Clinton

On Fri, Nov 5, 2010 at 7:00 PM, Jeff Butler <[hidden email]> wrote:
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

nav
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

nav
In reply to this post by Jeff Butler
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

On Sat, Nov 6, 2010 at 6:30 AM, Jeff Butler <[hidden email]> wrote:
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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

scarlettxu
In reply to this post by Jeff Butler
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:
<code>
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<String, Object> parms = new HashMap<String, Object>();
            parms.put("first_name", "Barney");
            List<Name> names = spMapper.getNames(parms);
            System.out.println(names);
        } finally {
            sqlSession.close();
        }
    }  
}
</code>

and I create the table without column ID:
<sql>
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
</sql>

I create the getNames stored procedure as below:
<sql>
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
</sql>

the SPMapper.xml:
<code>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.icil.esolution.cargoStatus.AS.model.SPMapper">
  <resultMap type="com.icil.esolution.cargoStatus.AS.model.Name" id="nameResult">
    <result column="ID" property="id"/>
    <result column="first_name" property="firstName"/>
    <result column="last_name" property="lastName"/>
  </resultMap>

  <select id="getNames" parameterType="java.util.Map" statementType="CALLABLE"
    resultMap="nameResult">
    {call SET CHAINED OFF exec tempdb..getNames #{in,jdbcType=VARCHAR,mode=IN}}
  </select>
</mapper>
</code>

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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

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


On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]> wrote:

> 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: 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.
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

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~~


On Mon, Nov 8, 2010 at 11:13 PM, Jeff Butler <[hidden email]> wrote:
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


On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]> wrote:
> 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: 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.
>


SPMapper.xml (1K) Download Attachment
SPTest.java (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

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


On Mon, Nov 8, 2010 at 9:42 PM, scarlett xu <[hidden email]> wrote:

> 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~~
>
>
> On Mon, Nov 8, 2010 at 11:13 PM, Jeff Butler <[hidden email]> wrote:
>>
>> 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
>>
>>
>> On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]> wrote:
>> > 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: 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.
>> >
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

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.



On Tue, Nov 9, 2010 at 11:56 AM, Jeff Butler <[hidden email]> wrote:
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


On Mon, Nov 8, 2010 at 9:42 PM, scarlett xu <[hidden email]> wrote:
> 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~~
>
>
> On Mon, Nov 8, 2010 at 11:13 PM, Jeff Butler <[hidden email]> wrote:
>>
>> 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
>>
>>
>> On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]> wrote:
>> > 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: 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.
>> >
>
>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

Jeff Butler
Did you try it?

Jeff

On Mon, Nov 8, 2010 at 10:15 PM, scarlett xu <[hidden email]> wrote:

> 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.
>
>
>
> On Tue, Nov 9, 2010 at 11:56 AM, Jeff Butler <[hidden email]> wrote:
>>
>> 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
>>
>>
>> On Mon, Nov 8, 2010 at 9:42 PM, scarlett xu <[hidden email]>
>> wrote:
>> > 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~~
>> >
>> >
>> > On Mon, Nov 8, 2010 at 11:13 PM, Jeff Butler <[hidden email]>
>> > wrote:
>> >>
>> >> 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
>> >>
>> >>
>> >> On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]> wrote:
>> >> > 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: 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.
>> >> >
>> >
>> >
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

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/

On Tue, Nov 9, 2010 at 12:28 PM, Jeff Butler <[hidden email]> wrote:
Did you try it?

Jeff

On Mon, Nov 8, 2010 at 10:15 PM, scarlett xu <[hidden email]> wrote:
> 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.
>
>
>
> On Tue, Nov 9, 2010 at 11:56 AM, Jeff Butler <[hidden email]> wrote:
>>
>> 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
>>
>>
>> On Mon, Nov 8, 2010 at 9:42 PM, scarlett xu <[hidden email]>
>> wrote:
>> > 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~~
>> >
>> >
>> > On Mon, Nov 8, 2010 at 11:13 PM, Jeff Butler <[hidden email]>
>> > wrote:
>> >>
>> >> 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
>> >>
>> >>
>> >> On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]> wrote:
>> >> > 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: 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.
>> >> >
>> >
>> >
>
>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

scarlett xu
Hi Jeff,

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

On Tue, Nov 9, 2010 at 1:11 PM, scarlett xu <[hidden email]> wrote:
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/


On Tue, Nov 9, 2010 at 12:28 PM, Jeff Butler <[hidden email]> wrote:
Did you try it?

Jeff

On Mon, Nov 8, 2010 at 10:15 PM, scarlett xu <[hidden email]> wrote:
> 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.
>
>
>
> On Tue, Nov 9, 2010 at 11:56 AM, Jeff Butler <[hidden email]> wrote:
>>
>> 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
>>
>>
>> On Mon, Nov 8, 2010 at 9:42 PM, scarlett xu <[hidden email]>
>> wrote:
>> > 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~~
>> >
>> >
>> > On Mon, Nov 8, 2010 at 11:13 PM, Jeff Butler <[hidden email]>
>> > wrote:
>> >>
>> >> 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
>> >>
>> >>
>> >> On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]> wrote:
>> >> > 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: 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.
>> >> >
>> >
>> >
>
>


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

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 ?



On Tue, Nov 9, 2010 at 3:28 PM, scarlett xu <[hidden email]> wrote:
Hi Jeff,

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


On Tue, Nov 9, 2010 at 1:11 PM, scarlett xu <[hidden email]> wrote:
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/


On Tue, Nov 9, 2010 at 12:28 PM, Jeff Butler <[hidden email]> wrote:
Did you try it?

Jeff

On Mon, Nov 8, 2010 at 10:15 PM, scarlett xu <[hidden email]> wrote:
> 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.
>
>
>
> On Tue, Nov 9, 2010 at 11:56 AM, Jeff Butler <[hidden email]> wrote:
>>
>> 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
>>
>>
>> On Mon, Nov 8, 2010 at 9:42 PM, scarlett xu <[hidden email]>
>> wrote:
>> > 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~~
>> >
>> >
>> > On Mon, Nov 8, 2010 at 11:13 PM, Jeff Butler <[hidden email]>
>> > wrote:
>> >>
>> >> 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
>> >>
>> >>
>> >> On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]> wrote:
>> >> > 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: 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.
>> >> >
>> >
>> >
>
>



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

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


On Tue, Nov 9, 2010 at 2:33 AM, scarlett xu <[hidden email]> wrote:

> 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 ?
>
>
>
> On Tue, Nov 9, 2010 at 3:28 PM, scarlett xu <[hidden email]> wrote:
>>
>> Hi Jeff,
>>
>> thank you so much! your idea works~~
>> i add the set nocount on in the sp, it works.
>>
>> On Tue, Nov 9, 2010 at 1:11 PM, scarlett xu <[hidden email]>
>> wrote:
>>>
>>> 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/
>>>
>>> On Tue, Nov 9, 2010 at 12:28 PM, Jeff Butler <[hidden email]>
>>> wrote:
>>>>
>>>> Did you try it?
>>>>
>>>> Jeff
>>>>
>>>> On Mon, Nov 8, 2010 at 10:15 PM, scarlett xu <[hidden email]>
>>>> wrote:
>>>> > 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.
>>>> >
>>>> >
>>>> >
>>>> > On Tue, Nov 9, 2010 at 11:56 AM, Jeff Butler <[hidden email]>
>>>> > wrote:
>>>> >>
>>>> >> 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
>>>> >>
>>>> >>
>>>> >> On Mon, Nov 8, 2010 at 9:42 PM, scarlett xu <[hidden email]>
>>>> >> wrote:
>>>> >> > 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~~
>>>> >> >
>>>> >> >
>>>> >> > On Mon, Nov 8, 2010 at 11:13 PM, Jeff Butler
>>>> >> > <[hidden email]>
>>>> >> > wrote:
>>>> >> >>
>>>> >> >> 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
>>>> >> >>
>>>> >> >>
>>>> >> >> On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]>
>>>> >> >> wrote:
>>>> >> >> > 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: 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.
>>>> >> >> >
>>>> >> >
>>>> >> >
>>>> >
>>>> >
>>>
>>
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Executing Stored Procedures with MyBatis

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?

On Wed, Nov 10, 2010 at 3:42 AM, Jeff Butler <[hidden email]> wrote:
"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


On Tue, Nov 9, 2010 at 2:33 AM, scarlett xu <[hidden email]> wrote:
> 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 ?
>
>
>
> On Tue, Nov 9, 2010 at 3:28 PM, scarlett xu <[hidden email]> wrote:
>>
>> Hi Jeff,
>>
>> thank you so much! your idea works~~
>> i add the set nocount on in the sp, it works.
>>
>> On Tue, Nov 9, 2010 at 1:11 PM, scarlett xu <[hidden email]>
>> wrote:
>>>
>>> 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/
>>>
>>> On Tue, Nov 9, 2010 at 12:28 PM, Jeff Butler <[hidden email]>
>>> wrote:
>>>>
>>>> Did you try it?
>>>>
>>>> Jeff
>>>>
>>>> On Mon, Nov 8, 2010 at 10:15 PM, scarlett xu <[hidden email]>
>>>> wrote:
>>>> > 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.
>>>> >
>>>> >
>>>> >
>>>> > On Tue, Nov 9, 2010 at 11:56 AM, Jeff Butler <[hidden email]>
>>>> > wrote:
>>>> >>
>>>> >> 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
>>>> >>
>>>> >>
>>>> >> On Mon, Nov 8, 2010 at 9:42 PM, scarlett xu <[hidden email]>
>>>> >> wrote:
>>>> >> > 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~~
>>>> >> >
>>>> >> >
>>>> >> > On Mon, Nov 8, 2010 at 11:13 PM, Jeff Butler
>>>> >> > <[hidden email]>
>>>> >> > wrote:
>>>> >> >>
>>>> >> >> 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
>>>> >> >>
>>>> >> >>
>>>> >> >> On Mon, Nov 8, 2010 at 3:41 AM, scarlettxu <[hidden email]>
>>>> >> >> wrote:
>>>> >> >> > 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: 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.
>>>> >> >> >
>>>> >> >
>>>> >> >
>>>> >
>>>> >
>>>
>>
>
>

Loading...