Feature request: callable statement handling output ref cursor

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

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

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

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.

On Tuesday, November 7, 2017 at 10:41:22 PM UTC+9, Erwan Letessier wrote:
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 '<a href="https://github.com/mybatis/mybatis-3.git/" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;">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

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

Iwao AVE!
In reply to this post by Erwan Letessier
Hi Erwan,

This issue has been reported several times.

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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <[hidden email]>:
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

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

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


On Tuesday, November 7, 2017 at 4:22:25 PM UTC+1, Iwao AVE! wrote:
Hi Erwan,

This issue has been reported several times.
<a href="https://github.com/mybatis/mybatis-3/issues/493" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F493\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHYCkLd7JMWZxBxz6P5OzSy0Fb84w&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F493\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHYCkLd7JMWZxBxz6P5OzSy0Fb84w&#39;;return true;">https://github.com/mybatis/mybatis-3/issues/493
<a href="https://github.com/mybatis/mybatis-3/issues/457" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F457\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSgtK76eUAtB5FrkYkaewzfWCmTw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F457\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSgtK76eUAtB5FrkYkaewzfWCmTw&#39;;return true;">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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="5YfiANbtBwAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">erwan.l...@...>:
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 '<a href="https://github.com/mybatis/mybatis-3.git/" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;">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

--
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 <a href="javascript:" target="_blank" gdf-obfuscated-mailto="5YfiANbtBwAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

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

2017-11-09 2:47 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Tuesday, November 7, 2017 at 4:22:25 PM UTC+1, Iwao AVE! wrote:
Hi Erwan,

This issue has been reported several times.

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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <[hidden email]>:
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

--
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 [hidden email]om.
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

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


2017-11-10 4:46 GMT+09:00 Iwao AVE! <[hidden email]>:
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

2017-11-09 2:47 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Tuesday, November 7, 2017 at 4:22:25 PM UTC+1, Iwao AVE! wrote:
Hi Erwan,

This issue has been reported several times.

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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <[hidden email]>:
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

--
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 [hidden email]om.
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

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

On Saturday, November 11, 2017 at 12:35:44 PM UTC+1, Iwao AVE! wrote:

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 ?
<a href="https://github.com/mybatis/mybatis-3/wiki/Maven" style="background-color:transparent;color:rgb(3,102,214);text-decoration:none" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fwiki%2FMaven\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHsDze4LZbQu8D6YHCcYzIJYN29hQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fwiki%2FMaven\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHsDze4LZbQu8D6YHCcYzIJYN29hQ&#39;;return true;">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


2017-11-10 4:46 GMT+09:00 Iwao AVE! <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="EntCz6mAAAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">hara...@...>:
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

2017-11-09 2:47 GMT+09:00 Erwan Letessier <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="EntCz6mAAAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">erwan.l...@...>:
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


On Tuesday, November 7, 2017 at 4:22:25 PM UTC+1, Iwao AVE! wrote:
Hi Erwan,

This issue has been reported several times.
<a href="https://github.com/mybatis/mybatis-3/issues/493" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F493\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHYCkLd7JMWZxBxz6P5OzSy0Fb84w&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F493\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHYCkLd7JMWZxBxz6P5OzSy0Fb84w&#39;;return true;">https://github.com/mybatis/mybatis-3/issues/493
<a href="https://github.com/mybatis/mybatis-3/issues/457" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F457\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSgtK76eUAtB5FrkYkaewzfWCmTw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F457\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSgtK76eUAtB5FrkYkaewzfWCmTw&#39;;return true;">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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <[hidden email]>:
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 '<a href="https://github.com/mybatis/mybatis-3.git/" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;">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

--
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...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.


--
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 <a href="javascript:" target="_blank" gdf-obfuscated-mailto="EntCz6mAAAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

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


On Monday, November 13, 2017 at 11:46:24 AM UTC+1, Erwan Letessier wrote:
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

On Saturday, November 11, 2017 at 12:35:44 PM UTC+1, Iwao AVE! wrote:

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 ?
<a href="https://github.com/mybatis/mybatis-3/wiki/Maven" style="background-color:transparent;color:rgb(3,102,214);text-decoration:none" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fwiki%2FMaven\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHsDze4LZbQu8D6YHCcYzIJYN29hQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fwiki%2FMaven\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHsDze4LZbQu8D6YHCcYzIJYN29hQ&#39;;return true;">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


2017-11-10 4:46 GMT+09:00 Iwao AVE! <[hidden email]>:
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

2017-11-09 2:47 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Tuesday, November 7, 2017 at 4:22:25 PM UTC+1, Iwao AVE! wrote:
Hi Erwan,

This issue has been reported several times.
<a href="https://github.com/mybatis/mybatis-3/issues/493" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F493\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHYCkLd7JMWZxBxz6P5OzSy0Fb84w&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F493\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHYCkLd7JMWZxBxz6P5OzSy0Fb84w&#39;;return true;">https://github.com/mybatis/mybatis-3/issues/493
<a href="https://github.com/mybatis/mybatis-3/issues/457" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F457\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSgtK76eUAtB5FrkYkaewzfWCmTw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F457\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSgtK76eUAtB5FrkYkaewzfWCmTw&#39;;return true;">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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <[hidden email]>:
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 '<a href="https://github.com/mybatis/mybatis-3.git/" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;">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

--
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...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.


--
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...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

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 .

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.

Thank you,
Iwao

2017-11-14 23:44 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Monday, November 13, 2017 at 11:46:24 AM UTC+1, Erwan Letessier wrote:
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

On Saturday, November 11, 2017 at 12:35:44 PM UTC+1, Iwao AVE! wrote:

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


2017-11-10 4:46 GMT+09:00 Iwao AVE! <[hidden email]>:
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

2017-11-09 2:47 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Tuesday, November 7, 2017 at 4:22:25 PM UTC+1, Iwao AVE! wrote:
Hi Erwan,

This issue has been reported several times.

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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <[hidden email]>:
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

--
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 [hidden email]om.
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email]om.
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

Iwao AVE!
Forgot to mention. Please post the stack trace.

2017-11-15 1:25 GMT+09:00 Iwao AVE! <[hidden email]>:
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 .

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.

Thank you,
Iwao

2017-11-14 23:44 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Monday, November 13, 2017 at 11:46:24 AM UTC+1, Erwan Letessier wrote:
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

On Saturday, November 11, 2017 at 12:35:44 PM UTC+1, Iwao AVE! wrote:

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


2017-11-10 4:46 GMT+09:00 Iwao AVE! <[hidden email]>:
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

2017-11-09 2:47 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Tuesday, November 7, 2017 at 4:22:25 PM UTC+1, Iwao AVE! wrote:
Hi Erwan,

This issue has been reported several times.

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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <[hidden email]>:
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

--
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 [hidden email]om.
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email]om.
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

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;







On Tuesday, November 14, 2017 at 6:56:28 PM UTC+1, Iwao AVE! wrote:
Forgot to mention. Please post the stack trace.

2017-11-15 1:25 GMT+09:00 Iwao AVE! <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="ztIYcy6BAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">hara...@...>:
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 .
<a href="https://github.com/mybatis/mybatis-3/issues/492" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F492\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGah5tQh1w_hK2G9MgR0Aowox9sEw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F492\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGah5tQh1w_hK2G9MgR0Aowox9sEw&#39;;return true;">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.
<a href="https://github.com/mybatis/mybatis-3/tree/65769382ea5ce651ec123775532dc68d5e6520a4/src/test/java/org/apache/ibatis/submitted/usesjava8/refcursor" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Ftree%2F65769382ea5ce651ec123775532dc68d5e6520a4%2Fsrc%2Ftest%2Fjava%2Forg%2Fapache%2Fibatis%2Fsubmitted%2Fusesjava8%2Frefcursor\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEcloi2d0Tb2CzkIoZdQkwFRLYQCg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Ftree%2F65769382ea5ce651ec123775532dc68d5e6520a4%2Fsrc%2Ftest%2Fjava%2Forg%2Fapache%2Fibatis%2Fsubmitted%2Fusesjava8%2Frefcursor\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEcloi2d0Tb2CzkIoZdQkwFRLYQCg&#39;;return true;">https://github.com/mybatis/mybatis-3/tree/65769382ea5ce651ec123775532dc68d5e6520a4/src/test/java/org/apache/ibatis/submitted/usesjava8/refcursor

Thank you,
Iwao

2017-11-14 23:44 GMT+09:00 Erwan Letessier <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="ztIYcy6BAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">erwan.l...@...>:
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


On Monday, November 13, 2017 at 11:46:24 AM UTC+1, Erwan Letessier wrote:
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

On Saturday, November 11, 2017 at 12:35:44 PM UTC+1, Iwao AVE! wrote:

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 ?
<a href="https://github.com/mybatis/mybatis-3/wiki/Maven" style="background-color:transparent;color:rgb(3,102,214);text-decoration:none" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fwiki%2FMaven\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHsDze4LZbQu8D6YHCcYzIJYN29hQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fwiki%2FMaven\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHsDze4LZbQu8D6YHCcYzIJYN29hQ&#39;;return true;">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


2017-11-10 4:46 GMT+09:00 Iwao AVE! <[hidden email]>:
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

2017-11-09 2:47 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Tuesday, November 7, 2017 at 4:22:25 PM UTC+1, Iwao AVE! wrote:
Hi Erwan,

This issue has been reported several times.
<a href="https://github.com/mybatis/mybatis-3/issues/493" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F493\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHYCkLd7JMWZxBxz6P5OzSy0Fb84w&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F493\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHYCkLd7JMWZxBxz6P5OzSy0Fb84w&#39;;return true;">https://github.com/mybatis/mybatis-3/issues/493
<a href="https://github.com/mybatis/mybatis-3/issues/457" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F457\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSgtK76eUAtB5FrkYkaewzfWCmTw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3%2Fissues%2F457\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFSgtK76eUAtB5FrkYkaewzfWCmTw&#39;;return true;">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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <[hidden email]>:
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 '<a href="https://github.com/mybatis/mybatis-3.git/" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fmybatis%2Fmybatis-3.git%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF8AAP8Yoo8zPA23ZWuG2_9DcGSJg&#39;;return true;">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

--
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...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.


--
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...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.


--
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 <a href="javascript:" target="_blank" gdf-obfuscated-mailto="ztIYcy6BAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: callable statement handling output ref cursor

Iwao AVE!
No worries.
Thank you for the procedure. I will add a new test case.

// Iwao

2017-11-15 3:14 GMT+09:00 Erwan Letessier <[hidden email]>:
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;







On Tuesday, November 14, 2017 at 6:56:28 PM UTC+1, Iwao AVE! wrote:
Forgot to mention. Please post the stack trace.

2017-11-15 1:25 GMT+09:00 Iwao AVE! <[hidden email]>:
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 .

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.

Thank you,
Iwao

2017-11-14 23:44 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Monday, November 13, 2017 at 11:46:24 AM UTC+1, Erwan Letessier wrote:
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

On Saturday, November 11, 2017 at 12:35:44 PM UTC+1, Iwao AVE! wrote:

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


2017-11-10 4:46 GMT+09:00 Iwao AVE! <[hidden email]>:
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

2017-11-09 2:47 GMT+09:00 Erwan Letessier <[hidden email]>:
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


On Tuesday, November 7, 2017 at 4:22:25 PM UTC+1, Iwao AVE! wrote:
Hi Erwan,

This issue has been reported several times.

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

2017-11-07 4:53 GMT+09:00 Erwan Letessier <[hidden email]>:
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

--
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 [hidden email]om.
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email]om.
For more options, visit https://groups.google.com/d/optout.


--
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...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.