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: --- ... 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 tags, so I called the attribute "useResultMap"but 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 >>> >>> >>> javaType="java.lang.Integer" mode="IN"/> >>> >>> javaType="java.sql.ResultSet" mode="OUT"/> >>> >>> >>># 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:--- ... 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 tags, so I called the attribute "useResultMap"but 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 parameterMap>>> >>>>>> javaType="java.lang.Integer" mode="IN"/>>>>>>> javaType="java.sql.ResultSet" mode="OUT"/>>>>>>>>>># 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 parameterMap>>> >>>>>> javaType="java.lang.Integer" mode="IN"/>>>>>>> javaType="java.sql.ResultSet" mode="OUT"/>>>>>>>>>># 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
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 # 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
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 # 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 parameterMapjavaType="java.lang.Integer " mode="IN"/>javaType="java.sql.ResultSet" mode="OUT"/> # 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 add"javaType="java.sql.ResultSet"" to their maps.Regards,Sven Boden
Oracle ref cursors
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 javaType="java.lang.Integer" mode="IN"/> javaType="java.sql.ResultSet" mode="OUT"/> # 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