How to get output from stored procedure to ibatis class

[Chetan Nayak]

All

I have been able to execute stored procedure in IBATIS before
this .I'm trying to execute a stored procedure which returns 2 OUT
parameters and i want to be able to call the stored procedure using
IBATIS and get the return values to a java bean which is the
resultType.

Please see the query below .This is how im calling the procedure.

<select id="callSomeStoredProc"
parameterType="someBean" statementType="CALLABLE"
resultType="someResultType">

CALL MY_STORED_PROC('1','3','1',@param1,@param2);
SELECT @param1 as param1,@param2 as param2;

</select>

I'm getting the following error

org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause:
java.lang.ArrayIndexOutOfBoundsException: 2
### The error occurred while setting parameters
### Cause: java.lang.ArrayIndexOutOfBoundsException: 2
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
61)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
53)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:
38)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
66)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)


I'm not sure why im getting an arrayIndexOutOfBoundsException ,I have
provided the correct number of inputs to the stored procedure.

I'm not sure if im doing something wrong in the syntax of the query .

When i try to execute this in a sql editor its just works fine.

Please let me know if anyone has seen something like this before ,Any
help would be greatly appreciated.

Thanks
Chetan

[Chetan Nayak]

Hello

A small update to the problem i have mentioned below .I have made some
progress after googling about the issue
My query now looks like this

<select id="somequeryname"
parameterType="someInputBean" statementType="CALLABLE"
resultType="someOutputBean">

call someProcedure
(#{id1,mode=IN,jdbcType=VARCHAR},#{id2,mode=IN,jdbcType=VARCHAR},

#{id3,mode=IN,jdbcType=VARCHAR},#{output1,mode=OUT,jdbcType=VARCHAR},#{output2,mode=OUT,jdbcType=VARCHAR});
</select>


In the query above as per my understanding goes .............id1,id2
and id3 would be the input parameters to the query .
i want output1 and output2 to be mapped to my result type.
However when i execute this query IBATIS complains that it has to find
a setter method for output1 and output2 parameter . So ibatis thinks
that output1 and output2 are also input parameters.

Can someone tell me how to tell ibatis to treat output1 and output2 as
mapping in my resultType class.

Thanks a ton in advance
Chetan

[Mauricio Galdames]

Dont know if this can help u... i used it in ibatis...

http://www.apachebookstore.com/confluence/oss/display/IBATIS/Oracle+REF+CURSOR+Solutions

[Chetan Nayak]

Mauricio

Thank you for sharing the link . I think the link you sent me is
specific to how to return a catch a oracle cursor response.
In my case im using two simple OUT parameters and i want them mapped
to javaBeans which i set as a part of the resultType attribute.

Thanks again
Chetan

On Oct 28, 8:41 pm, Mauricio Galdames <mgalda...@gmail.com> wrote:
> Dont know if this can help u... i used it in ibatis...
>

> http://www.apachebookstore.com/confluence/oss/display/IBATIS/Oracle+R...

>
> El 28-10-2010, a las 12:38, Chetan Nayak escribió:
>
>
>
>
>
>
>
> > Hello
>
> > A small update to the problem i have mentioned below .I have made some
> > progress after googling about the issue
> > My query now looks like this
>
> > <select id="somequeryname"
> >            parameterType="someInputBean" statementType="CALLABLE"
> >            resultType="someOutputBean">
>
> >    call someProcedure
> >            (#{id1,mode=IN,jdbcType=VARCHAR},#{id2,mode=IN,jdbcType=VARCHAR},
>

> > #{id3,mode=IN,jdbcType=VARCHAR},#{output1,mode=OUT,jdbcType=VARCHAR},#{outp ut2,mode=OUT,jdbcType=VARCHAR});

[Jeff Butler]

That's not the way it works. For stored procedure parameters, MyBatis
will map both input and output parameters to properties in the
parameterType. So in your case, someBean must contain id1, id2, id3
AND output1 and output2. After invoking the procedure, output1 and
output2 will be updated with the output values.

The resultType is only used if the procedure returns a result set (not
a result set parameter, just a result set from invoking the
procedure).

So, change someBean to include the two output parameters, delete the
resultType, and things should be good to go.

Jeff Butler

[Chetan Nayak]

Jeff ,

Thank you so much for your advice that worked .

The way i was looking at it is that anything that is returned back
will be a resultType......:) thats y i got confused as to why would
that be an input paramter when its the result being returned back.

So just so that everyone knows what i did .

I just removed the resultType attribute and the values were set in the
parameterType javaBean itself.

Thanks for your help
Chetan

On Oct 28, 8:52 pm, Jeff Butler <jeffgbut...@gmail.com> wrote:
> That's not the way it works.  For stored procedure parameters, MyBatis
> will map both input and output parameters to properties in the
> parameterType.  So in your case, someBean must contain id1, id2, id3
> AND output1 and output2.  After invoking the procedure, output1 and
> output2 will be updated with the output values.
>
> The resultType is only used if the procedure returns a result set (not
> a result set parameter, just a result set from invoking the
> procedure).
>
> So, change someBean to include the two output parameters, delete the
> resultType, and things should be good to go.
>
> Jeff Butler
>
>
>
> On Thu, Oct 28, 2010 at 10:38 AM, Chetan Nayak <chetannay...@gmail.com> wrote:
> > Hello
>
> > A small update to the problem i have mentioned below .I have made some
> > progress after googling about the issue
> > My query now looks like this
>
> > <select id="somequeryname"
> >                parameterType="someInputBean" statementType="CALLABLE"
> >                resultType="someOutputBean">
>
> >        call someProcedure
> >                (#{id1,mode=IN,jdbcType=VARCHAR},#{id2,mode=IN,jdbcType=VARCHAR},
>

> > #{id3,mode=IN,jdbcType=VARCHAR},#{output1,mode=OUT,jdbcType=VARCHAR},#{outp­ut2,mode=OUT,jdbcType=VARCHAR});

> >> Chetan- Hide quoted text -
>
> - Show quoted text -

[scarlettxu]

Hi Jeff,

when i configured like this, the output value is not updated to the
JavaBean.

I use the example you posted before

sqlMap config:
<update id="adder2" parameterType="spTest.Parameter"
statementType="CALLABLE">
{call tempdb..adder(
#{addend1,jdbcType=INTEGER,mode=IN},
#{addend2,jdbcType=INTEGER,mode=IN},
#{sum,jdbcType=INTEGER,mode=OUT}
)}
</update>

JavaBean:
public class Parameter {
private Integer addend1;
private Integer addend2;
private Integer sum;
public Integer getAddend1() {
return addend1;
}
public void setAddend1(Integer addend1) {
this.addend1 = addend1;
}
public Integer getAddend2() {
return addend2;
}
public void setAddend2(Integer addend2) {
this.addend2 = addend2;
}
public Integer getSum() {
return sum;
}
public void setSum(Integer sum) {
this.sum = sum;
}
}

public interface SPMapper {
Object adder(Parameter parameter);
void adder2(Parameter parameter);
Name getName(Integer id);
List<Name> getNames(Map<String, Object> parms);
}

testing code:
SqlSession sqlSession = null;
try {
sqlSession = createSqlMapper().openSession();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
// initialize
Parameter parameter = new Parameter();
parameter.setAddend1(2);
parameter.setAddend2(3);

SPMapper spMapper = sqlSession.getMapper(SPMapper.class);

spMapper.adder2(parameter);
System.out.println("summary is: "+parameter.getSum());
// initialize
parameter = new Parameter();
parameter.setAddend1(2);
parameter.setAddend2(3);

spMapper.adder2(parameter);

} finally {
sqlSession.close();
}

and I use sybase database, the sp is:
create procedure adder
@addend1 int,
@addend2 int,
@theSum int output
as
select @theSum = @addend1 + @addend2

is it the sybase cause the problem or something wrong with my config?
--
View this message in context: http://mybatis-user.963551.n3.nabble.com/How-to-get-output-from-stored-procedure-to-ibatis-class-tp1785718p2056325.html
Sent from the mybatis-user mailing list archive at Nabble.com.

[scarlett xu]

And when i print the parameter.getSum(), it is always: null.
i debug the SP, theSum has been set the correct value which is the plus of addend1 and addend2.
so i cannot find where cause the JavaBean cannot get the updated value.

[scarlett xu]

Hi,

After counter testing, I find update method will not return any output parameter value.
while select method will do.

below is my testing.

Parameter class is same as posted in the first Email. It has addend1, addend2, result attributed.

SP in db:

create procedure adder

@addend1 int,

@addend2 int,

@theSum  int output

as

select @theSum = @addend1 + @addend2

the SqlMap config:

    <update id="adder2" parameterType="spTest.Parameter" statementType="CALLABLE">

        {call tempdb..adder(

          #{addend1,jdbcType=INTEGER,mode=IN},

          #{addend2,jdbcType=INTEGER,mode=IN},

          #{result,jdbcType=INTEGER,mode=OUT}

        )}

    </update>

And the testing code:

                        ...

            Parameter parameter = new Parameter();

            parameter.setAddend1(1);

            parameter.setAddend2(3);

            parameter.setResult(0);

            sqlSession.update("adder2",parameter);

            System.out.println("Addend1 is: "+parameter.getAddend1());

            System.out.println("Addend2 is: "+parameter.getAddend2());

            System.out.println("Result is: "+parameter.getResult());

The testing result is:

Addend1 is: 1

Addend2 is: 3

Result is: 0

When I change to sqlSession.selectOne("adder2",parameter);

The result is:

Addend1 is: 1

Addend2 is: 3

Result is: 4

can this testing prove that update will not return output parameter value?
or is there somewhere i config wrong that cause the problem? if so how to correct my config to make it work?

is the update is not supposed to return the output parameter value, is there any other way to get the updated data?
if in the update sp, i have select statement in the end:

create procedure adder

@addend1 int,

@addend2 int,

@theSum  int output

as

select @theSum = @addend1 + @addend2

  select add1 = @addend1, add2 = @addend2 , result = @theSum

and thus the sp will return an ResultSet, how to config to make update method can get the ResultSet and map to the JavaBean?

in our project, all update/insert SP will have ResultSet returned, if update/insert cannot handler either ResultSet or output parameter, it is terrible, we may have to do select again after each update and insert.

[scarlett xu]

Hi Jeff,

The strange thing is when I change from
sqlSession.update("adder2",parameter);
to
sqlSession.selectOne("adder2",parameter);

the output parameter will return value.

well, i have configured the log4j.xml just like the MyBatis wiki on google code says.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
  <appender name="console" class="org.apache.log4j.ConsoleAppender">
    <param name="Target" value="System.out"/>
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss} %m  (%F:%L) \n"/>
    </layout>
  </appender>
  <category name="java.sql">
        <priority value="debug" />
  </category>
  <category name="com.ibatis">
        <priority value="debug" />
  </category>
  <root>
    <priority value ="debug" />
    <appender-ref ref="console" />
  </root>
 
</log4j:configuration>

while run update, i didn't see any useful log for me to trace the problem.
DEBUG 12-13 14:34:07 Created connection 26828160.  (JakartaCommonsLoggingImpl.java:27)
DEBUG 12-13 14:34:07 ooo Connection Opened  (JakartaCommonsLoggingImpl.java:27)
DEBUG 12-13 14:34:07 xxx Connection Closed  (JakartaCommonsLoggingImpl.java:27)
DEBUG 12-13 14:34:07 Returned connection 26828160 to pool.  (JakartaCommonsLoggingImpl.java:27)

Addend1 is: 1
Addend2 is: 3
Result is: 0

have I turn on the MyBatis logging ? if not, what else i have do to turn on the logging? I have log4j.jar and commons-logging.jar

On Mon, Dec 13, 2010 at 6:52 AM, Jeff Butler <jeffg...@gmail.com> wrote:

You can retrieve output parameters with update - the SP examples do
just that.  You cannot retrieve a result set with update, but I don't
think that's what you really want.

Your first example looks right to me, don't know why it doesn't work.
I think it would be good at this point to turn on MyBatis logging to
see if you can spot the problem.

Jeff Butler

[scarlett xu]

Hi Jeff,

Pls ignore my previous email.

I think I found out the problem, which I think it is the MyBatis problem for ExecutorType.BATCH.

I checked the src code and add log to trace and find the calling procedure for ExecutorType.BATCH is:
DefaultSqlSession:update() -> BaseExecutor:update() -> BatchExecutor:doUpdate() -> CallableStatementHandler:batch() -> java.sql.PreparedStatement:addBatch() ...

you see  doUpdate() method of BatchExecutor class will call batch() method of CallableStatementHandler class, and then call addBatch() method of java.sql.PreparedStatement class.

I think this logic may be wrong, the doUpdate() method of BatchExecutor class should call update() method of CallableStatementHandler class, then everything will be fine. just like this:
DefaultSqlSession:update() -> BaseExecutor:update() -> BatchExecutor:doUpdate() -> CallableStatementHandler:update()...

the logic for ExecutorType.SIMPLE and ExecutorType.REUSE is ok.

could you pls check the BatchExecutor class and see if it is the problem?
by the way, I download the src file from http://mybatis.googlecode.com/svn/trunk/src/main/java

[Jeff Butler]

I don't think JDBC really supports stored procedure output parameters
in batches - at least I can't figure out how one would retrieve such
parameters. Batch is meant for bulk inserts or updates, I wouldn't
use it otherwise.

Jeff Butler

[scarlett xu]

Hi Jeff,

So it is correct for BatchExecutor.doUpdate() to call CallableStatementHandler.batch() ?
and the following handling is done by JDBC ?
if so, it should not return output parameters.
then I may search other work arounds.

[Jeff Butler]

Yes - I believe it is correct.

The bottom line is this - don't use the batch executer if you need to
retrieve output parameters. The workaround is simple - use the default
executer. It works perfectly.

Jeff Butler

--
Sent from my mobile device

[scarlett xu]

Thanks ~ Jeff.
know the situation and will not use Batch executor to retrieve output parameters.