MyBatis call stored procedure problem

[GoogleCodeExporter]

MyBatis version 3.0.1

I use MyBatis 3 as the OR mapping between the java application and sybase 
database.
The sql used to query data from the database is a stored procedure, it is ok 
for simple procedure, but if there is inner variable declared and used in the 
stored procedure, it seems does not work correctly, the query result is null, 
while there is no exception throwing.

below is the example code, and i also attach as the attachment.
JavaBean:
<code>
    public class Test {

    private String input1;
    private String input2;

    public String getInput1() {
        return input1;
    }

    public void setInput1(String input1) {
        this.input1 = input1;
    }

    public String getInput2() {
        return input2;
    }

    public void setInput2(String input2) {
        this.input2 = input2;
    }
    }
</code>

sqlMap:
<code>
    <mapper namespace="cargoStatus_shipment">
    <resultMap id="testMap"     type="com.icil.esolution.cargoStatus.AS.model.Test">
    <result column="result1" jdbcType="VARCHAR" property="input1" />
    <result column="result2" jdbcType="VARCHAR" property="input2" />
    </resultMap>

    <select id="getValidData" statementType="CALLABLE"     resultMap="testMap"     parameterType="String">
     {call tempdb..testSP #{in}}
    </select> 

    </mapper>
</code>

stored procedure:
<code>
    use tempdb
    go
    drop proc testSP
    go
    create proc testSP
      @in varchar(10)

    as
     declare @var char(3)
     select @var="XXX"
     select result1= '1', result2=@in
    go
    grant exec on testSP  to public
    go
</code>

Java code:
<code>
public class TestSP {

  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 session=null;
    try {
          session = createSqlMapper().openSession(ExecutorType.SIMPLE, true); //autocommit = true

    } catch (Exception e) {
       e.printStackTrace();
       System.out.println("Error in open Session. Cause: " + e);
       System.exit(1);
    }

    List<Test> result = (List<Test>) session.selectList("getValidData", "mydata"); 

    System.out.println("Result = "+result);
    System.out.println(result.get(2).getInput2());

  }

}
</code>

normally the result should be:
DEBUG PreparedStatement - ==>  Executing: {call tempdb..testSP ?} 
DEBUG PreparedStatement - ==> Parameters: mydata(String)
DEBUG ResultSet - <==    Columns: result1, result2
DEBUG ResultSet - <==        Row: 1, mydata

but actually, there is no result get, neither exceptions:
DEBUG PreparedStatement - ==>  Executing: {call tempdb..testSP ?} 
DEBUG PreparedStatement - ==> Parameters: mydata(String)

after counter test, if i remove the inner variable @var from the sp, then it 
will be ok.
<code>
    use tempdb
    go
    drop proc testSP
    go
    create proc testSP
      @in varchar(10)

    as
     select result1= '1', result2=@in
    go
    grant exec on testSP  to public
    go
</code>

Could you pls check what is the problem and what shall I do to make sure I can 
call this kind of stored procedure? 

Original issue reported on code.google.com by scarlett...@gmail.com on 4 Nov 2010 at 11:01

Attachments:

• Test.java
• test.sql
• TestSP.java
• sqlMap.xml

[GoogleCodeExporter]

pls help asap, i have searched it for several days~

Original comment by scarlett...@gmail.com on 4 Nov 2010 at 11:05

[GoogleCodeExporter]

Hi Xu Han Zi. Please post this also to the mailing list.
groups.google.com/group/mybatis-user

Maybe other users can help.

Original comment by eduardo.macarron on 4 Nov 2010 at 1:02

• Added labels: Component-SqlMaps, Version-Release3.x

[GoogleCodeExporter]

Hi eduardo,

thanks for your suggestions.
Any comments from you for this problem?

Original comment by scarlett...@gmail.com on 5 Nov 2010 at 7:18

[GoogleCodeExporter]

I have posted this issue on stackoverflow.com: 
http://stackoverflow.com/questions/4104116/mybatis-sybase-stored-procedure-retur
ns-zero-rows

Original comment by kospiotr on 5 Nov 2010 at 7:24

[GoogleCodeExporter]

Hi kospiotr,

Thank you!
But you have missed some parts during copy.
ormally the result should be:
<code>
DEBUG PreparedStatement - ==>  Executing: {call tempdb..testSP ?} 
DEBUG PreparedStatement - ==> Parameters: mydata(String)
DEBUG ResultSet - <==    Columns: result1, result2
DEBUG ResultSet - <==        Row: 1, mydata
</code>

from the result, we can see it get the result succesfully.

but you put as 
DEBUG PreparedStatement - ==>  Executing: {call tempdb..testSP ?} 
DEBUG PreparedStatement - ==> Parameters: mydata(String)
DEBUG ResultSet - 

couls you pls revise it a little?

Original comment by scarlett...@gmail.com on 5 Nov 2010 at 8:05

[GoogleCodeExporter]

You are using the wrong syntax for calling stored procedures.  Change it to 
this (note the added parentheses):

{call tempdb..testSP (#{in})}

Original comment by jeffgbut...@gmail.com on 5 Nov 2010 at 11:10

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I'm going to assume this is working now after the fix in comment 6.  If you 
still have a problem with this, please ask on the user list first.

Original comment by jeffgbut...@gmail.com on 29 Dec 2010 at 11:51

• Changed state: Invalid