RE: Oracle ref cursors
Generic cursor support with the ability to
map it using a result map would be nice too. I thought the whole point was to
avoid touching the result set.
From: Clinton Begin
[mailto:[EMAIL PROTECTED]
Sent: Thursday, February 09, 2006
9:47 PM
To: dev@ibatis.apache.org;
[EMAIL PROTECTED]
Subject: Re: Oracle ref cursors
Hmmm...ok. Here's what I'd like to see in a solution:
* No dependency upon Oracle (like you've said).
* ResultSet types should be supported in both OUT parameters, as well as
result set columns (ick).
* The regular result set returned by a select statement or procedure
should not be compromised.
So conceivably I should be able to do something like this:
---
resultMap
result ... jdbcType=ResultSet
useResultMap=.../
/resultMap
parameterMap
parameter jdbcType=ResultSet
useResultMap=.../
/parameterMap
proc id=blah ...
...
/
List list = sqlMap.queryForList(blah, myParam);
---
In this case, I may have dealt with 3 result maps. One to deal with the OUT
param, one to deal with result sets nested in columns, and of course the normal
one returned from a call to .executeQuery().
Note that the XML attribute resultMap is already used for
result tags, so I called the attribute
useResultMapbut perhaps there's a better name that's not too
long (e.g. cursorResultMap)?
Thoughts?
Clinton
On 2/9/06, Clinton
Begin
[EMAIL PROTECTED] wrote:
Yep. I agree on the need. Just being cautious about the
implementation.
Cheers,
Clinton
On 2/9/06, Sven
Boden
[EMAIL PROTECTED] wrote:
In case anyone is interested in having a look, I uploaded the patch to
the patch to the wiki,
http://opensource2.atlassian.com/confluence/oss/display/IBATIS/Oracle+REF+CURSOR+Solutions
We may need to think some other things over, but a lot of people seem to
need/want something in iBATIS to convert ResultSets from stored
procedures to maps.
Sven
There can be more than 1 out result sets, Michael's testcases on the
wiki show that.
Will check the second bullet tomorrow, we can probably find a way
around it it should also be just a resultset (but I vaguely
remember some problems with it from past experiments).
Sven
Clinton Begin wrote:
So the assumptions made here are:
* There can be only one out parameter of type ResultSet
* The default result set (possibly returned from a SELECT)
is lost, as
it's overidden by the ResultSet parameter.
Seems a bit hackey, but I'm interested in everyone else's opinion.
Clinton
On 2/9/06, Sven Boden [EMAIL PROTECTED] wrote:
I've been playing a bit with Michael Fagan's patch for supporting
ResultMaps using Oracle ref cursors. Sometimes I'm also a bit fond
of
pushing as much functionality as possible in Oracle procedures and
only
exposing a ref cursor to the (C) applications.
The patch works but having ORACLECURSOR in the iBATIS code puts me
a
bit
off. So I made some small cosmetic changes... what about the
following:
- Michael Fagan's patch (now already to be redone because of other
changes in 2.1.7)
- With following changes:
# Adding
javaType=java.sql.ResultSet to the parameterMap
parameterMap id=single-rs
class=map
parameter
property=in1 jdbcType=int
javaType=java.lang.Integer mode=IN/
parameter
property=output1 jdbcType=ORACLECURSOR
javaType=java.sql.ResultSet mode=OUT/
/parameterMap
# doing the ResultSet extraction as follows
(hardcoding ResultSet
instead of ORACLECURSOR).
if (mapping.isOutputAllowed()) {
if (
java.sql.ResultSet.equalsIgnoreCase(mapping.getJavaTypeName ())
){
ResultSet
rs = (ResultSet) cs.getObject( i + 1 );
# In the TypeHandlerFactory a shortcut
cursor could be made for
java.sql.ResultSet
The above change would not put something Oracle only
in iBATIS source
code, it would make the SQLMap still dependent on Oracle because
of
ORACLECURSOR but I don't see this as a big problem (as soon as you
use
SQL dialect in the SqlMaps you're also dependent upon a database).
As long as an other database supports a type for
cursor it can use
the
implementation above. Or is there something I'm missing, which
could
come back to haunt us?
People using the current patch should then only add
javaType=java.sql.ResultSet to their maps.
Regards,
Sven Boden
Re: Oracle ref cursors
So the assumptions made here are: * There can be only one out parameter of type ResultSet * The default result set (possibly returned from a SELECT) is lost, as it's overidden by the ResultSet parameter.
Seems a bit hackey, but I'm interested in everyone else's opinion. ClintonOn 2/9/06, Sven Boden [EMAIL PROTECTED]
wrote:I've been playing a bit with Michael Fagan's patch for supporting
ResultMaps using Oracle ref cursors. Sometimes I'm also a bit fond ofpushing as much functionality as possible in Oracle procedures and onlyexposing a ref cursor to the (C) applications.The patch works but having ORACLECURSOR in the iBATIS code puts me a bit
off. So I made some small cosmetic changes... what about the following:- Michael Fagan's patch (now already to be redone because of otherchanges in 2.1.7)- With following changes:# Adding javaType=
java.sql.ResultSet to the parameterMapparameterMap id=single-rs class=map parameter property=in1 jdbcType=intjavaType=java.lang.Integer
mode=IN/parameter property=output1 jdbcType=ORACLECURSORjavaType=java.sql.ResultSet mode=OUT//parameterMap
# doing the ResultSet extraction as follows (hardcoding ResultSetinstead of ORACLECURSOR).if (mapping.isOutputAllowed()) { if (java.sql.ResultSet.equalsIgnoreCase(
mapping.getJavaTypeName()) ){ResultSet rs = (ResultSet) cs.getObject( i + 1 );# In the TypeHandlerFactory a shortcut cursor could be made forjava.sql.ResultSet
The above change would not put something Oracle only in iBATIS sourcecode, it would make the SQLMap still dependent on Oracle because ofORACLECURSOR but I don't see this as a big problem (as soon as you use
SQL dialect in the SqlMaps you're also dependent upon a database).As long as an other database supports a type for cursor it can use theimplementation above. Or is there something I'm missing, which could
come back to haunt us?People using the current patch should then only addjavaType=java.sql.ResultSet to their maps.Regards,Sven Boden
Re: Oracle ref cursors
There can be more than 1 out result sets, Michael's testcases on the
wiki show that.
Will check the second bullet tomorrow, we can probably find a way around
it it should also be just a resultset (but I vaguely remember some
problems with it from past experiments).
Sven
Clinton Begin wrote:
So the assumptions made here are:
* There can be only one out parameter of type ResultSet
* The default result set (possibly returned from a SELECT) is lost, as
it's overidden by the ResultSet parameter.
Seems a bit hackey, but I'm interested in everyone else's opinion.
Clinton
On 2/9/06, Sven Boden [EMAIL PROTECTED] wrote:
I've been playing a bit with Michael Fagan's patch for supporting
ResultMaps using Oracle ref cursors. Sometimes I'm also a bit fond of
pushing as much functionality as possible in Oracle procedures and only
exposing a ref cursor to the (C) applications.
The patch works but having ORACLECURSOR in the iBATIS code puts me a bit
off. So I made some small cosmetic changes... what about the following:
- Michael Fagan's patch (now already to be redone because of other
changes in 2.1.7)
- With following changes:
# Adding javaType=java.sql.ResultSet to the parameterMap
parameterMap id=single-rs class=map
parameter property=in1 jdbcType=int
javaType=java.lang.Integer mode=IN/
parameter property=output1 jdbcType=ORACLECURSOR
javaType=java.sql.ResultSet mode=OUT/
/parameterMap
# doing the ResultSet extraction as follows (hardcoding ResultSet
instead of ORACLECURSOR).
if (mapping.isOutputAllowed()) {
if (
java.sql.ResultSet.equalsIgnoreCase(mapping.getJavaTypeName()) ) {
ResultSet rs = (ResultSet) cs.getObject( i + 1 );
# In the TypeHandlerFactory a shortcut cursor could be made for
java.sql.ResultSet
The above change would not put something Oracle only in iBATIS source
code, it would make the SQLMap still dependent on Oracle because of
ORACLECURSOR but I don't see this as a big problem (as soon as you use
SQL dialect in the SqlMaps you're also dependent upon a database).
As long as an other database supports a type for cursor it can use the
implementation above. Or is there something I'm missing, which could
come back to haunt us?
People using the current patch should then only add
javaType=java.sql.ResultSet to their maps.
Regards,
Sven Boden
Re: Oracle ref cursors
Yep. I agree on the need. Just being cautious about the implementation.Cheers,ClintonOn 2/9/06, Sven Boden
[EMAIL PROTECTED] wrote:In case anyone is interested in having a look, I uploaded the patch to
the patch to the wiki,http://opensource2.atlassian.com/confluence/oss/display/IBATIS/Oracle+REF+CURSOR+Solutions
We may need to think some other things over, but a lot of people seem toneed/want something in iBATIS to convert ResultSets from storedprocedures to maps.Sven There can be more than 1 out result sets, Michael's testcases on the
wiki show that. Will check the second bullet tomorrow, we can probably find a way around it it should also be just a resultset (but I vaguely remember some problems with it from past experiments).
Sven Clinton Begin wrote: So the assumptions made here are:* There can be only one out parameter of type ResultSet* The default result set (possibly returned from a SELECT) is lost, as
it's overidden by the ResultSet parameter. Seems a bit hackey, but I'm interested in everyone else's opinion. Clinton On 2/9/06, Sven Boden
[EMAIL PROTECTED] wrote: I've been playing a bit with Michael Fagan's patch for supporting ResultMaps using Oracle ref cursors. Sometimes I'm also a bit fond of
pushing as much functionality as possible in Oracle procedures and only exposing a ref cursor to the (C) applications. The patch works but having ORACLECURSOR in the iBATIS code puts me a
bit off. So I made some small cosmetic changes... what about the following: - Michael Fagan's patch (now already to be redone because of other changes in
2.1.7) - With following changes:# Adding javaType=java.sql.ResultSet to the parameterMapparameterMap id=single-rs class=map
parameter property=in1 jdbcType=int javaType=java.lang.Integer mode=IN/parameter property=output1 jdbcType=ORACLECURSOR
javaType=java.sql.ResultSet mode=OUT//parameterMap# doing the ResultSet extraction as follows (hardcoding ResultSet
instead of ORACLECURSOR).if (mapping.isOutputAllowed()) { if ( java.sql.ResultSet.equalsIgnoreCase(mapping.getJavaTypeName
()) ){ResultSet rs = (ResultSet) cs.getObject( i + 1 );# In the TypeHandlerFactory a shortcut cursor could be made for
java.sql.ResultSet The above change would not put something Oracle only in iBATIS source code, it would make the SQLMap still dependent on Oracle because of
ORACLECURSOR but I don't see this as a big problem (as soon as you use SQL dialect in the SqlMaps you're also dependent upon a database). As long as an other database supports a type for cursor it can use
the implementation above. Or is there something I'm missing, which could come back to haunt us? People using the current patch should then only add
javaType=java.sql.ResultSet to their maps. Regards, Sven Boden
Re: Oracle ref cursors
Hmmm...ok. Here's what I'd like to see in a solution: * No dependency upon Oracle (like you've said). * ResultSet types should be supported in both OUT parameters, as well as result set columns (ick).
* The regular result set returned by a select statement or procedure should not be compromised.So conceivably I should be able to do something like this:---resultMap result ... jdbcType=ResultSet useResultMap=.../
/resultMapparameterMap parameter jdbcType=ResultSet useResultMap=...//parameterMapproc id=blah ... ...
/List list = sqlMap.queryForList(blah, myParam);---In this case, I may have dealt with 3 result maps. One to deal with the OUT param, one to deal with result sets nested in columns, and of course the normal one returned from a call to .executeQuery().
Note that the XML attribute resultMap is already used for result tags, so I called the attribute useResultMapbut perhaps there's a better name that's not too long (e.g. cursorResultMap)?
Thoughts?ClintonOn 2/9/06, Clinton Begin
[EMAIL PROTECTED] wrote:
Yep. I agree on the need. Just being cautious about the implementation.Cheers,ClintonOn 2/9/06,
Sven Boden
[EMAIL PROTECTED] wrote:In case anyone is interested in having a look, I uploaded the patch to
the patch to the wiki,http://opensource2.atlassian.com/confluence/oss/display/IBATIS/Oracle+REF+CURSOR+Solutions
We may need to think some other things over, but a lot of people seem toneed/want something in iBATIS to convert ResultSets from storedprocedures to maps.Sven There can be more than 1 out result sets, Michael's testcases on the
wiki show that. Will check the second bullet tomorrow, we can probably find a way around it it should also be just a resultset (but I vaguely remember some problems with it from past experiments).
Sven Clinton Begin wrote: So the assumptions made here are:* There can be only one out parameter of type ResultSet* The default result set (possibly returned from a SELECT) is lost, as
it's overidden by the ResultSet parameter. Seems a bit hackey, but I'm interested in everyone else's opinion. Clinton On 2/9/06, Sven Boden
[EMAIL PROTECTED] wrote: I've been playing a bit with Michael Fagan's patch for supporting
ResultMaps using Oracle ref cursors. Sometimes I'm also a bit fond of
pushing as much functionality as possible in Oracle procedures and only exposing a ref cursor to the (C) applications. The patch works but having ORACLECURSOR in the iBATIS code puts me a
bit off. So I made some small cosmetic changes... what about the following: - Michael Fagan's patch (now already to be redone because of other changes in
2.1.7) - With following changes:# Adding javaType=java.sql.ResultSet to the parameterMapparameterMap id=single-rs class=map
parameter property=in1 jdbcType=int javaType=java.lang.Integer mode=IN/parameter property=output1 jdbcType=ORACLECURSOR
javaType=java.sql.ResultSet mode=OUT//parameterMap# doing the ResultSet extraction as follows (hardcoding ResultSet
instead of ORACLECURSOR).if (mapping.isOutputAllowed()) { if ( java.sql.ResultSet.equalsIgnoreCase(mapping.getJavaTypeName
()) ){ResultSet rs = (ResultSet) cs.getObject( i + 1 );# In the TypeHandlerFactory a shortcut cursor could be made for
java.sql.ResultSet The above change would not put something Oracle only in iBATIS source code, it would make the SQLMap still dependent on Oracle because of
ORACLECURSOR but I don't see this as a big problem (as soon as you use SQL dialect in the SqlMaps you're also dependent upon a database). As long as an other database supports a type for cursor it can use
the implementation above. Or is there something I'm missing, which could come back to haunt us? People using the current patch should then only add
javaType=java.sql.ResultSet to their maps. Regards, Sven Boden
