Feature request: callable statement handling output ref cursor

[Erwan Letessier]

Hi,

I have got a feature that I would like to be integrated in Mybatis, so following the new issue disclaimer, here I am to request your feedback.

For select statement, the default behavior is to use a DefaultResultHandler that will add every mapped result in a List.

In case when a customer ResultHandler is passed as argument, then it is used instead to do whatever you want for each mapped result.

My use case is about calling a stored procedure that will not return a result set, but has a Ref Cusror bound as OUT parameter.

The fact is that in Mybatis current code, only the default behavior (fetching cursor till the end to fill up bound result list) is implemented.

For example, in my case, I need to transform every result in write it to a stream, the result object it self in thrown away just afterward. And I don't want/need to store millions of record into in-memory list to iterate over it then.

So here is the update I propose in private method org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRefCursorOutputParameter.

Added coded is highlighted in green. Moved code in yellow. 

try {
      final String resultMapId = parameterMapping.getResultMapId();
      final ResultMap resultMap = configuration.getResultMap(resultMapId);
      final ResultSetWrapper rsw = new ResultSetWrapper(rs, configuration);
      mappedStatement.getStatementLog().debug("Fetching output ref cursor");
      if (resultHandler == null) {
          final DefaultResultHandler resultHandler = new DefaultResultHandler(objectFactory);
          handleRowValues(rsw, resultMap, resultHandler, new RowBounds(), null);
          metaParam.setValue(parameterMapping.getProperty(), resultHandler.getResultList());
      } else {
          handleRowValues(rsw, resultMap, this.resultHandler, new RowBounds(), null);
      }
} finally {

Here is the mapper 

@Select("{ CALL getOrders(#{customerId}, "
            + "#{resultContainer.returnCode, mode=OUT, jdbcType=INTEGER}, "
            + "#{resultContainer.returnMessage, mode=OUT, jdbcType=VARCHAR}, "
            + "#{resultContainer.resultList, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=orderResultMap}"
            + ") }")
@ResultMap("orderResultMap")
@Options(statementType = StatementType.CALLABLE)
void getOrders(@Param("customerId") Integer customerId, @Param("resultContainer") ResultContainer<OrderDto> resultContainer, ResultHandler<OrderDto> resultHandler);

called this way:

mapper.getOrdersWithRH(14383, resultContainer, new OrderResultHandler());

Regarding unit tests, I'm currently working on it. It is pretty painful.But it is a good opportunity to improve coverage on this part.

Anyway, I have cloned project and my branch is called feature/callable_statement_handling. But I cannot push it so far:

git push --set-upstream origin feature/callable_statement_handling
remote: Permission to mybatis/mybatis-3.git denied to blackwizard1812.
fatal: unable to access 'https://github.com/mybatis/mybatis-3.git/': The requested URL returned error: 403

So what should I do? I can imagine permission are restricted on purpose, to prevent trolls from flooding the repository?

Then I do not mind if an authorized contributor pushes it for me.

Cheers

[Kazuki Shimizu]

> Anyway, I have cloned project and my branch is called feature/callable_statement_handling. But I cannot push it so far:

You need forking mybatis/mybatis-3 as your account repository.

See https://help.github.com/articles/fork-a-repo/ .

[Iwao AVE!]

Hi Erwan,

This issue has been reported several times.

https://github.com/mybatis/mybatis-3/issues/493

https://github.com/mybatis/mybatis-3/issues/457

Both reporters proposed a similar solution as yours, but it may not work when there are multiple cursors returned from a stored procedure.

I'm not sure how to fix it yet, but am open to suggestions.

As my knowledge about stored procedures is limited, please let me know if I am missing something.

Regards,

Iwao

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Erwan Letessier]

Hi Iwao,

Regarding the case when procedure returns multiple cursors, I am currently working on it.

I'm giving a try to a solution would be based on management of cases described in following enum (I guess it is self explanatory):

public enum UseCustomHandlerOnRefCursorOutputParameter {

    /**
     * Default value, provided result handler is ignored.
     */
    NONE,

    /**
     * Provided result handler is used on each of statement RefCursor output parameter.
     */
    ALL,

    /**
     * Provided result handler is used on first of statement RefCursor output parameter.
     * DefaultResultHandler is used for all other cursors.
     */
    FIRST,

    /**
     * Provided result handler is used on first of statement RefCursor output parameter
     * if there is only one, otherwise DefaultResultHandler is used for all cursors.
     */
    IF_ONLY_ONE_CURSOR;

}

It will be disabled by default, to keep current behavior so that no one is annoyed.

It has the inconvenient to be global.

A statement level configuration seems to be more suitable, but I have not checked that yet.

It would also be possible to add a property in parameter mapping, but that would involve passing only a class name that Mybatis would instantiate, while I want to pass my own instance of ResultHandler.

Let me know what you think about that.

Cheers,

Erwan

Hi Erwan,

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.

[Iwao AVE!]

Hi Erwan,

Thank you for the idea!

After the last post, I got an idea and am evaluating it right now.

Please give me some time!

Regards,

Iwao

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

[Iwao AVE!]

Hi Erwan,

I have committed the fix that works with a stored procedure with a single refcursor out parameter.
Could you verify the fix with the latest 3.4.6-SNAPSHOT ?
https://github.com/mybatis/mybatis-3/wiki/Maven

Regarding a stored procedure that has multiple refcursor out parameters, I have a fix, but didn’t like it very much, so I’ll reconsider it when/if someone actually needs it (my fix won’t break existing solutions).
And even with the current implementation, checking the instance type could be used as a workaround, I guess.

Object obj = resultContext.getObject();
if (obj instanceof Person) {
  // ...
} else if (obj instanceof Pet) {
  // ...

Regards,
Iwao

​

[Erwan Letessier]

Thanks Iwao,

It is working fine on my app.

Any date foreseen for the release?

Now, just need to add in the documentation that result handler are now supported for callable statement with ref Cursor output parameter. 

But as only on result handler can be used, the same will be used on all output ref cursors (if multiple), the workaround you specify may be used in case when result types are different.

Cheers,

Erwan

[Erwan Letessier]

Hi,

I have got another question on the same method handleRefCursorOutputParameter:

on instantiation 

final ResultSetWrapper rsw = new ResultSetWrapper(rs, configuration);

In case when resultSet rs is null, a NullPointerException is thrown when it is dereferenced.

This issue results on a change we are just trying in procedure error management.

Previously, procedure raised errors on some functional conditions (e.g: customer id does not exist), so none of the output parameters are read.

Now, the procedure just returns after setting output string message (an error code mapped to appropriate behavior) to and the cursor is not open then the result set is null.

I guess that, as external components, stored procedures shall not be "trusted" then I wonder if the handleRefCursorOutputParameter method should check for null resultSet and return in that case, indeed there is nothing to handle.

Cheers

Erwan

[Iwao AVE!]

Hi Erwan,

Thank you for verifying the fix!
3.4.6 will be out soon, but not too soon.

I'll update the doc when I have time.

> In case when resultSet rs is null, a NullPointerException is thrown when it is dereferenced.

This sounds like #492 .

https://github.com/mybatis/mybatis-3/issues/492

Do you still get the NPE with the latest snapshot?

If so, please provide the details for how to reproduce the issue.

A portable unit test like this is the best.

https://github.com/mybatis/mybatis-3/tree/65769382ea5ce651ec123775532dc68d5e6520a4/src/test/java/org/apache/ibatis/submitted/usesjava8/refcursor

Thank you,

Iwao

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

[Iwao AVE!]

Forgot to mention. Please post the stack trace.

[Erwan Letessier]

Yes, my bad,  the null check has been here for a long time, but my project is using old version 3.3.0 (was the latest release back then)

So for this, 3.4.5 will be fine.

But a test case for this fix:

Create a procedure with an output refcursor, and do not open it, then it is null

CREATE OR REPLACE FUNCTION mbtest.get_order_out_params_with_null_cursor(
            order_number integer,
            detail_count out integer,
            header_curs out refcursor
       ) AS $BODY$
BEGIN
  select count(*) into detail_count from mbtest.order_detail where order_id = ORDER_NUMBER;
END;

[Iwao AVE!]

No worries.

Thank you for the procedure. I will add a new test case.

// Iwao

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.