You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Jason Bennett <Ja...@3nonline.com> on 2008/04/17 21:32:01 UTC
iBatis-362 - Caching broken for stored procs
I ran across this bug today, and was surprised that there hasn't been
any discussion. As far as I can tell, iBatis caching does not work for
stored procedure return values - you always get a null (or empty) value
out of the cache.
Has anyone else experienced this (or not), and found a workaround?
jason
Re: iBatis-362 - Caching broken for stored procs
Posted by Clinton Begin <cl...@gmail.com>.
I'm not implying anything. I was having a little fun with Jason, who said:
"I'm a little confused about the difference between regular results and OUT
results. I assume the return result of a stored function is always an OUT
parameter."
Clinton
On Sun, Apr 27, 2008 at 10:29 AM, Mississippi John Hurt <jo...@gmail.com>
wrote:
> Are you implying that Oracle stored procs can only return output via the
> OUT parameters, and cannot return resultsets like a return value for a
> function?
>
>
> On Tue, Apr 22, 2008 at 3:01 PM, Clinton Begin <cl...@gmail.com>
> wrote:
>
> >
> > You must be an Oracle user... :-)
> >
> > CallableStatements can return ResultSets just like any PreparedStatement
> > can. The difference is that CallableStatements can also have results of
> > their own, which is the more traditional "output parameter" from the JDBC
> > API perspective. It is these results that are not currently cached by
> > iBATIS.
> >
> > http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html
> >
> > In practice the difference is:
> >
> > ResultSet rs = cs.executeQuery(...); // would be cached.
> > vs.
> > cs.getObject(...); // would NOT be cached, even if it's a ResultSet
> > mapped to a ref cursor returned from an Oracle proc.
> >
> > Cheers,
> > Clinton
> >
> >
> >
> > On Tue, Apr 22, 2008 at 3:47 PM, Jason Bennett <
> > Jason.Bennett@3nonline.com> wrote:
> >
> > > Thanks for the response, Clint.
> > >
> > >
> > >
> > > I'm a little confused about the difference between regular results and
> > > OUT results. I assume the return result of a stored function is always an
> > > OUT parameter.
> > >
> > >
> > >
> > > My current fix is to use LRUMap from commons-collections, but what I'd
> > > really like is manual access to the iBatis cache, so that I can use the
> > > flush interval and other features. Could a fix for this be to expose the
> > > cache through the sqlmap interface?
> > >
> > >
> > >
> > > jason
> > >
> > >
> > > ------------------------------
> > >
> > > *From:* Clinton Begin [mailto:clinton.begin@gmail.com]
> > > *Sent:* Tuesday, April 22, 2008 2:10 PM
> > > *To:* user-java@ibatis.apache.org
> > > *Subject:* Re: iBatis-362 - Caching broken for stored procs
> > >
> > >
> > >
> > > The bug is actually invalid. The reason it "worked" in 2.1.5 is that
> > > procs weren't cached at all. In 2.2.0+ proc statements were cached, but
> > > there's no way to cache output parameters, as you're passing the object in
> > > (so we can't cache it). We could build something in to mock the parameter
> > > object and hold the out params, but that's nontrivial. So for now, no,
> > > there is no way to cache output parameters. You can cache regular results
> > > from a proc of course, just not the OUT params...
> > >
> > > Clinton
> > >
> > > On Thu, Apr 17, 2008 at 1:32 PM, Jason Bennett <
> > > Jason.Bennett@3nonline.com> wrote:
> > >
> > > I ran across this bug today, and was surprised that there hasn't been
> > > any discussion. As far as I can tell, iBatis caching does not work for
> > > stored procedure return values – you always get a null (or empty)
> > > value out of the cache.
> > >
> > > Has anyone else experienced this (or not), and found a workaround?
> > >
> > > jason
> > >
> > >
> > >
> >
> >
>
Re: iBatis-362 - Caching broken for stored procs
Posted by Mississippi John Hurt <jo...@gmail.com>.
Are you implying that Oracle stored procs can only return output via the OUT
parameters, and cannot return resultsets like a return value for a function?
On Tue, Apr 22, 2008 at 3:01 PM, Clinton Begin <cl...@gmail.com>
wrote:
>
> You must be an Oracle user... :-)
>
> CallableStatements can return ResultSets just like any PreparedStatement
> can. The difference is that CallableStatements can also have results of
> their own, which is the more traditional "output parameter" from the JDBC
> API perspective. It is these results that are not currently cached by
> iBATIS.
>
> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html
>
> In practice the difference is:
>
> ResultSet rs = cs.executeQuery(...); // would be cached.
> vs.
> cs.getObject(...); // would NOT be cached, even if it's a ResultSet mapped
> to a ref cursor returned from an Oracle proc.
>
> Cheers,
> Clinton
>
>
>
> On Tue, Apr 22, 2008 at 3:47 PM, Jason Bennett <Ja...@3nonline.com>
> wrote:
>
> > Thanks for the response, Clint.
> >
> >
> >
> > I'm a little confused about the difference between regular results and
> > OUT results. I assume the return result of a stored function is always an
> > OUT parameter.
> >
> >
> >
> > My current fix is to use LRUMap from commons-collections, but what I'd
> > really like is manual access to the iBatis cache, so that I can use the
> > flush interval and other features. Could a fix for this be to expose the
> > cache through the sqlmap interface?
> >
> >
> >
> > jason
> >
> >
> > ------------------------------
> >
> > *From:* Clinton Begin [mailto:clinton.begin@gmail.com]
> > *Sent:* Tuesday, April 22, 2008 2:10 PM
> > *To:* user-java@ibatis.apache.org
> > *Subject:* Re: iBatis-362 - Caching broken for stored procs
> >
> >
> >
> > The bug is actually invalid. The reason it "worked" in 2.1.5 is that
> > procs weren't cached at all. In 2.2.0+ proc statements were cached, but
> > there's no way to cache output parameters, as you're passing the object in
> > (so we can't cache it). We could build something in to mock the parameter
> > object and hold the out params, but that's nontrivial. So for now, no,
> > there is no way to cache output parameters. You can cache regular results
> > from a proc of course, just not the OUT params...
> >
> > Clinton
> >
> > On Thu, Apr 17, 2008 at 1:32 PM, Jason Bennett <
> > Jason.Bennett@3nonline.com> wrote:
> >
> > I ran across this bug today, and was surprised that there hasn't been
> > any discussion. As far as I can tell, iBatis caching does not work for
> > stored procedure return values – you always get a null (or empty) value
> > out of the cache.
> >
> > Has anyone else experienced this (or not), and found a workaround?
> >
> > jason
> >
> >
> >
>
>
Re: iBatis-362 - Caching broken for stored procs
Posted by Clinton Begin <cl...@gmail.com>.
You must be an Oracle user... :-)
CallableStatements can return ResultSets just like any PreparedStatement
can. The difference is that CallableStatements can also have results of
their own, which is the more traditional "output parameter" from the JDBC
API perspective. It is these results that are not currently cached by
iBATIS.
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html
In practice the difference is:
ResultSet rs = cs.executeQuery(...); // would be cached.
vs.
cs.getObject(...); // would NOT be cached, even if it's a ResultSet mapped
to a ref cursor returned from an Oracle proc.
Cheers,
Clinton
On Tue, Apr 22, 2008 at 3:47 PM, Jason Bennett <Ja...@3nonline.com>
wrote:
> Thanks for the response, Clint.
>
>
>
> I'm a little confused about the difference between regular results and OUT
> results. I assume the return result of a stored function is always an OUT
> parameter.
>
>
>
> My current fix is to use LRUMap from commons-collections, but what I'd
> really like is manual access to the iBatis cache, so that I can use the
> flush interval and other features. Could a fix for this be to expose the
> cache through the sqlmap interface?
>
>
>
> jason
>
>
> ------------------------------
>
> *From:* Clinton Begin [mailto:clinton.begin@gmail.com]
> *Sent:* Tuesday, April 22, 2008 2:10 PM
> *To:* user-java@ibatis.apache.org
> *Subject:* Re: iBatis-362 - Caching broken for stored procs
>
>
>
> The bug is actually invalid. The reason it "worked" in 2.1.5 is that
> procs weren't cached at all. In 2.2.0+ proc statements were cached, but
> there's no way to cache output parameters, as you're passing the object in
> (so we can't cache it). We could build something in to mock the parameter
> object and hold the out params, but that's nontrivial. So for now, no,
> there is no way to cache output parameters. You can cache regular results
> from a proc of course, just not the OUT params...
>
> Clinton
>
> On Thu, Apr 17, 2008 at 1:32 PM, Jason Bennett <Ja...@3nonline.com>
> wrote:
>
> I ran across this bug today, and was surprised that there hasn't been any
> discussion. As far as I can tell, iBatis caching does not work for stored
> procedure return values – you always get a null (or empty) value out of
> the cache.
>
> Has anyone else experienced this (or not), and found a workaround?
>
> jason
>
>
>
RE: iBatis-362 - Caching broken for stored procs
Posted by Jason Bennett <Ja...@3nonline.com>.
Thanks for the response, Clint.
I'm a little confused about the difference between regular results and
OUT results. I assume the return result of a stored function is always
an OUT parameter.
My current fix is to use LRUMap from commons-collections, but what I'd
really like is manual access to the iBatis cache, so that I can use the
flush interval and other features. Could a fix for this be to expose the
cache through the sqlmap interface?
jason
________________________________
From: Clinton Begin [mailto:clinton.begin@gmail.com]
Sent: Tuesday, April 22, 2008 2:10 PM
To: user-java@ibatis.apache.org
Subject: Re: iBatis-362 - Caching broken for stored procs
The bug is actually invalid. The reason it "worked" in 2.1.5 is that
procs weren't cached at all. In 2.2.0+ proc statements were cached, but
there's no way to cache output parameters, as you're passing the object
in (so we can't cache it). We could build something in to mock the
parameter object and hold the out params, but that's nontrivial. So for
now, no, there is no way to cache output parameters. You can cache
regular results from a proc of course, just not the OUT params...
Clinton
On Thu, Apr 17, 2008 at 1:32 PM, Jason Bennett
<Ja...@3nonline.com> wrote:
I ran across this bug today, and was surprised that there hasn't been
any discussion. As far as I can tell, iBatis caching does not work for
stored procedure return values - you always get a null (or empty) value
out of the cache.
Has anyone else experienced this (or not), and found a workaround?
jason
Re: iBatis-362 - Caching broken for stored procs
Posted by Clinton Begin <cl...@gmail.com>.
Yes, that will work, as long as it's not an out param.
Clinton
On Tue, Apr 22, 2008 at 3:33 PM, <Ch...@sybase.com> wrote:
>
> Caching regular results is all that I am interested in. So does this
> currently work?
>
> I have the following defined:
>
> <cacheModel id="SupportPlanCache" type="LRU" readOnly="true" serialize=
> "true">
> <flushInterval hours="24"/>
> <property name="size" value="1000"/>
> </cacheModel>
>
>
> And here is where I use it:
> <procedure id="listSupportPlans" parameterClass="java.util.Map"
> resultMap="SupportPlanResult" cacheModel="SupportPlanCache">
>
>
> So am I to understand that this is all I should need for it to work with a
> procedure?
>
> Thanks for the help Clinton...
>
>
>
>
> *"Clinton Begin" <cl...@gmail.com>*
>
> 04/22/2008 02:09 PM
> Please respond to
> user-java@ibatis.apache.org
>
> To
> user-java@ibatis.apache.org
> cc
> Subject
> Re: iBatis-362 - Caching broken for stored procs
>
>
>
>
> The bug is actually invalid. The reason it "worked" in 2.1.5 is that
> procs weren't cached at all. In 2.2.0+ proc statements were cached, but
> there's no way to cache output parameters, as you're passing the object in
> (so we can't cache it). We could build something in to mock the parameter
> object and hold the out params, but that's nontrivial. So for now, no,
> there is no way to cache output parameters. You can cache regular results
> from a proc of course, just not the OUT params...
>
> Clinton
>
> On Thu, Apr 17, 2008 at 1:32 PM, Jason Bennett <*
> Jason.Bennett@3nonline.com* <Ja...@3nonline.com>> wrote:
> I ran across this bug today, and was surprised that there hasn't been any
> discussion. As far as I can tell, iBatis caching does not work for stored
> procedure return values – you always get a null (or empty) value out of
> the cache.
>
> Has anyone else experienced this (or not), and found a workaround?
>
> jason
>
>
Re: iBatis-362 - Caching broken for stored procs
Posted by Ch...@sybase.com.
Caching regular results is all that I am interested in. So does this
currently work?
I have the following defined:
<cacheModel id="SupportPlanCache" type="LRU" readOnly="true" serialize=
"true">
<flushInterval hours="24"/>
<property name="size" value="1000"/>
</cacheModel>
And here is where I use it:
<procedure id="listSupportPlans" parameterClass="java.util.Map"
resultMap="SupportPlanResult" cacheModel="SupportPlanCache">
So am I to understand that this is all I should need for it to work with a
procedure?
Thanks for the help Clinton...
"Clinton Begin" <cl...@gmail.com>
04/22/2008 02:09 PM
Please respond to
user-java@ibatis.apache.org
To
user-java@ibatis.apache.org
cc
Subject
Re: iBatis-362 - Caching broken for stored procs
The bug is actually invalid. The reason it "worked" in 2.1.5 is that
procs weren't cached at all. In 2.2.0+ proc statements were cached, but
there's no way to cache output parameters, as you're passing the object in
(so we can't cache it). We could build something in to mock the parameter
object and hold the out params, but that's nontrivial. So for now, no,
there is no way to cache output parameters. You can cache regular results
from a proc of course, just not the OUT params...
Clinton
On Thu, Apr 17, 2008 at 1:32 PM, Jason Bennett <Jason.Bennett@3nonline.com
> wrote:
I ran across this bug today, and was surprised that there hasn't been any
discussion. As far as I can tell, iBatis caching does not work for stored
procedure return values ? you always get a null (or empty) value out of
the cache.
Has anyone else experienced this (or not), and found a workaround?
jason
Re: iBatis-362 - Caching broken for stored procs
Posted by Clinton Begin <cl...@gmail.com>.
The bug is actually invalid. The reason it "worked" in 2.1.5 is that procs
weren't cached at all. In 2.2.0+ proc statements were cached, but there's
no way to cache output parameters, as you're passing the object in (so we
can't cache it). We could build something in to mock the parameter object
and hold the out params, but that's nontrivial. So for now, no, there is no
way to cache output parameters. You can cache regular results from a proc
of course, just not the OUT params...
Clinton
On Thu, Apr 17, 2008 at 1:32 PM, Jason Bennett <Ja...@3nonline.com>
wrote:
> I ran across this bug today, and was surprised that there hasn't been any
> discussion. As far as I can tell, iBatis caching does not work for stored
> procedure return values – you always get a null (or empty) value out of
> the cache.
>
> Has anyone else experienced this (or not), and found a workaround?
>
> jason
>
>
RE: iBatis-362 - Caching broken for stored procs
Posted by Sundar Sankaranarayanan <Su...@phoenix.edu>.
I have observed that batch execution of stored procs pretty much takes
the same amount of time as executing the procedure in a loop.
Warm Regards,
Sundar Sankarnarayanan
Software Engineer
@University of Phoenix
________________________________
From: Christopher.Mathrusse@sybase.com
[mailto:Christopher.Mathrusse@sybase.com]
Sent: Tuesday, April 22, 2008 9:54 AM
To: user-java@ibatis.apache.org
Subject: Re: iBatis-362 - Caching broken for stored procs
Is this true? Caching does not work with stored procedures? Is there a
work around or is a patch expected?
Thanks...
Chris
"Jason Bennett" <Ja...@3nonline.com>
04/17/2008 12:32 PM
Please respond to
user-java@ibatis.apache.org
To
<us...@ibatis.apache.org>
cc
Subject
iBatis-362 - Caching broken for stored procs
I ran across this bug today, and was surprised that there hasn't been
any discussion. As far as I can tell, iBatis caching does not work for
stored procedure return values - you always get a null (or empty) value
out of the cache.
Has anyone else experienced this (or not), and found a workaround?
jason
Re: iBatis-362 - Caching broken for stored procs
Posted by Ch...@sybase.com.
Is this true? Caching does not work with stored procedures? Is there a
work around or is a patch expected?
Thanks...
Chris
"Jason Bennett" <Ja...@3nonline.com>
04/17/2008 12:32 PM
Please respond to
user-java@ibatis.apache.org
To
<us...@ibatis.apache.org>
cc
Subject
iBatis-362 - Caching broken for stored procs
I ran across this bug today, and was surprised that there hasn?t been any
discussion. As far as I can tell, iBatis caching does not work for stored
procedure return values ? you always get a null (or empty) value out of
the cache.
Has anyone else experienced this (or not), and found a workaround?
jason