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
"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
>>> <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
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>
|