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 Jesse Reimann <jr...@ctigroup.com> on 2008/03/10 21:43:00 UTC
Question about queryForList and Oracle ref cursors
I've successfully been able to use iBATIS to retrieve and map data from
an Oracle ref cursor using the examples posted on the Wiki. I do have a
questions about queryForList(string) method.
I can retrieve my List successfully if my dao code looks like this:
public List <CallDataDetail> selectCDDRpt()
{
Map myMap = new HashMap();
getSqlMapClientTemplate().queryForList("TEST_TESTPROC.GET_CDDRPT",myMap)
;
return (List <CallDataDetail>)myMap.get("rs1");
}
However if I try to get the List this way it returns a List with 10
elements which are all NULL:
public List <CallDataDetail> selectCDDRpt()
{
return
getSqlMapClientTemplate().queryForList("TEST_TESTPROC.GET_CDDRPT");
}
My parameter map and procedure call looks as follows:
<parameterMap id="single-rs" class="map" >
<parameter property="rs1" javaType="java.sql.ResultSet"
jdbcType="ORACLECURSOR" mode="OUT" resultMap="CallDataDetailResult"/>
</parameterMap>
<procedure id="GET_CDDRPT" parameterMap="single-rs" >
{ ? = call RPTS_PCK.GET_CDDRPT }
</procedure>
Can anyone shed some light on why this is? Is there are way I can change
my parameter settings to have queryForList return a List directly when
calling a ref cursor?
Thanks,
Jesse Reimann
Re: Question about queryForList and Oracle ref cursors
Posted by Ryan Shelley <12...@gmail.com>.
Try:
*public* List <CallDataDetail> selectCDDRpt()
{
return getSqlMapClientTemplate().queryForList("TEST_TESTPROC.GET_CDDRPT");
}
You're returning a list of rows, no extra work is required. Use the Hashmap
if you need to pass values INTO the stored procedure.
-Ryan
On Mon, Mar 10, 2008 at 1:43 PM, Jesse Reimann <jr...@ctigroup.com>
wrote:
> I've successfully been able to use iBATIS to retrieve and map data from
> an Oracle ref cursor using the examples posted on the Wiki. I do have a
> questions about queryForList(string) method.
>
>
>
> I can retrieve my List successfully if my dao code looks like this:
>
>
>
> *public* List <CallDataDetail> selectCDDRpt()
>
> {
>
> Map myMap = *new* HashMap();
>
>
>
> getSqlMapClientTemplate().queryForList("TEST_TESTPROC.GET_CDDRPT"
> ,myMap);
>
> *return* (List <CallDataDetail>)myMap.get("rs1");
>
>
>
> }
>
>
>
> However if I try to get the List this way it returns a List with 10
> elements which are all NULL:
>
>
>
> *public* List <CallDataDetail> selectCDDRpt()
>
> {
>
> *return* getSqlMapClientTemplate().queryForList(
> "TEST_TESTPROC.GET_CDDRPT");
>
>
>
> }
>
>
>
> My parameter map and procedure call looks as follows:
>
>
>
> <parameterMap id="single-rs" class="map" >
>
> <parameter property="rs1" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR"
> mode="OUT" resultMap="CallDataDetailResult"/>
>
> </parameterMap>
>
> <procedure id="GET_CDDRPT" parameterMap="single-rs" >
>
> { ? = call RPTS_PCK.GET_CDDRPT }
>
> </procedure>
>
>
>
>
>
> Can anyone shed some light on why this is? Is there are way I can change
> my parameter settings to have queryForList return a List directly when
> calling a ref cursor?
>
>
>
> Thanks,
>
>
>
> Jesse Reimann
>
RE: Re: Question about queryForList and Oracle ref cursors
Posted by Jesse Reimann <jr...@ctigroup.com>.
Ok thanks to everyone for the info. I've switched over to using the
update method since it is the correct usage. I thought it might be
something like that but wasn't sure since I was able to use queryForList
when passing in the HashMap. I did notice as Sundar mentioned that with
the HashMap you are able to support multiple returned result sets which
is a really nice feature that I know I'll be taking advantage of in the
future.
Thanks,
Jesse
________________________________
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Monday, March 10, 2008 9:59 PM
To: user-java@ibatis.apache.org
Subject: Re: Re: Question about queryForList and Oracle ref cursors
queryForList only works for things that return result sets. Oracle ref
cursors are a "sort of" result set, but the ref cursor is a parameter to
a callable statement, rather than being returned from the stored proc.
This is an important difference.
Because of this, iBATIS (and JDBC) sees them as parameters - just a
wierd type of parameter. This is why the queryForXXX methods don't play
well with ref cursors - so yes, the update method is the best method to
use here.
Jeff Butler
On Mon, Mar 10, 2008 at 8:13 PM, Jason Bennett
<Ja...@3nonline.com> wrote:
One problem I had in this scenario is when I pass parameters to the
function, and expect a cursor back. I had to map the returning
parameter, since I had mapped all the in parameters. This prevented me
from using queryForList() as I wanted to. Am I doing anything wrong, or
does passing parameters to the SP force me to use the update() method?
jason
________________________________
From: Ryan Shelley [mailto:12gaugemedia@gmail.com]
Sent: Monday, March 10, 2008 2:38 PM
To: user-java@ibatis.apache.org
Subject: Re: Re: Question about queryForList and Oracle ref cursors
For reference:
http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+c
all+a+stored+procedure
Under: What SqlMapClient Method Should I Use?
...
If your procedure returns a result set (not a result set in an OUT
parameter, but a result set from the procedure itself), then use
queryForList() or queryForObject(). Use queryForList() if you expect
more than one result object, or queryForObject() if you expect only one
result Object.
...
If your procedure does not return result sets, or only returns result
sets in OUT parameters, then use the update() method.
-Ryan
On Mon, Mar 10, 2008 at 2:22 PM, Ryan Shelley <12...@gmail.com>
wrote:
In the one you had, you were creating an empty HashMap, executing the
queryForList method passing in the empty HashMap, and then converting
the still empty HashMap to a list and returning that to your method.
queryForList doesn't take an object parameter to store the results in,
it takes an object parameter to pass variables into your queries. The
return value of queryForList is a List of your ResultMapped Models.
-Ryan
On Mon, Mar 10, 2008 at 2:06 PM, Jesse Reimann <jr...@ctigroup.com>
wrote:
Ryan that's exactly what I did that wouldn't work. It returns to me a
List that has 10 elements all which are NULL.
Jesse
Re: Re: Question about queryForList and Oracle ref cursors
Posted by Jeff Butler <je...@gmail.com>.
queryForList only works for things that return result sets. Oracle ref
cursors are a "sort of" result set, but the ref cursor is a *parameter* to a
callable statement, rather than being *returned from* the stored proc. This
is an important difference.
Because of this, iBATIS (and JDBC) sees them as parameters - just a wierd
type of parameter. This is why the queryForXXX methods don't play well with
ref cursors - so yes, the update method is the best method to use here.
Jeff Butler
On Mon, Mar 10, 2008 at 8:13 PM, Jason Bennett <Ja...@3nonline.com>
wrote:
> One problem I had in this scenario is when I pass parameters to the
> function, and expect a cursor back. I had to map the returning parameter,
> since I had mapped all the in parameters. This prevented me from using
> queryForList() as I wanted to. Am I doing anything wrong, or does passing
> parameters to the SP force me to use the update() method?
>
>
>
> jason
>
>
> ------------------------------
>
> *From:* Ryan Shelley [mailto:12gaugemedia@gmail.com]
> *Sent:* Monday, March 10, 2008 2:38 PM
> *To:* user-java@ibatis.apache.org
> *Subject:* Re: Re: Question about queryForList and Oracle ref cursors
>
>
>
> For reference:
>
>
> http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+call+a+stored+procedure
>
> Under: What SqlMapClient Method Should I Use?
> ...
> If your procedure returns a result set (not a result set in an OUT
> parameter, but a result set from the procedure itself), then use *
> queryForList()* or *queryForObject()*. Use queryForList() if you expect
> more than one result object, or queryForObject() if you expect only one
> result Object.
> ...
> If your procedure does not return result sets, or only returns result sets
> in OUT parameters, then use the *update()* method.
>
> -Ryan
>
> On Mon, Mar 10, 2008 at 2:22 PM, Ryan Shelley <12...@gmail.com>
> wrote:
>
> In the one you had, you were creating an empty HashMap, executing the
> queryForList method passing in the empty HashMap, and then converting the
> still empty HashMap to a list and returning that to your method.
> queryForList doesn't take an object parameter to store the results in, it
> takes an object parameter to pass variables into your queries. The return
> value of queryForList is a List of your ResultMapped Models.
>
> -Ryan
>
>
>
> On Mon, Mar 10, 2008 at 2:06 PM, Jesse Reimann <jr...@ctigroup.com>
> wrote:
>
> Ryan that's exactly what I did that wouldn't work. It returns to me a List that has 10 elements all which are NULL.
>
>
>
>
>
>
>
> Jesse
>
>
>
>
>
>
>
RE: Re: Question about queryForList and Oracle ref cursors
Posted by Jason Bennett <Ja...@3nonline.com>.
That's what I'm doing now, and it works fine, thanks. I was just
wondering if there was a better way.
jason
________________________________
From: Sundar Sankar [mailto:fatboysuns@gmail.com]
Sent: Monday, March 10, 2008 7:03 PM
To: user-java@ibatis.apache.org
Subject: Re: Re: Question about queryForList and Oracle ref cursors
Jason,
This is what you will have to do,
1. Map an object to the columns being returned from the cursor in your
sq-map-config file. i.e you should have an entry like this
<result id="result" type="ORACLECURSOR" resultType="java.sql.ResultSet"
paramType="OUT"/>
2. You would have mapped your input Parameter in a map and passed it to
getSQLMapClientTemplate.queryForList("ProcName", inputMap").
This doesnt need to be associated to any List as the List will
always be null.
3. I have assumed that the parameterName declared for the cursor in the
procedure is result.
The output you would be expecting would be
List<UserDefinedObject> someList =
List<UserDefinedObject>inputMap.get("result");
If you get the result as null, you are either not marking the paramType
as OUT/INOUT in your config or parameter name is wrong and you have to
check the procedure to ensure the parameter name of the resultant cursor
is right.
My syntax could have been exactly right but this is the gist of the how
to do it. I am again reiterating the point that queryForList doesnt work
for SP's. It returns a list only for queries. The result of SP is in the
map that you passed. I hope I have helped.
-S
On Mon, Mar 10, 2008 at 6:13 PM, Jason Bennett
<Ja...@3nonline.com> wrote:
One problem I had in this scenario is when I pass parameters to the
function, and expect a cursor back. I had to map the returning
parameter, since I had mapped all the in parameters. This prevented me
from using queryForList() as I wanted to. Am I doing anything wrong, or
does passing parameters to the SP force me to use the update() method?
jason
________________________________
From: Ryan Shelley [mailto:12gaugemedia@gmail.com]
Sent: Monday, March 10, 2008 2:38 PM
To: user-java@ibatis.apache.org
Subject: Re: Re: Question about queryForList and Oracle ref cursors
For reference:
http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+c
all+a+stored+procedure
Under: What SqlMapClient Method Should I Use?
...
If your procedure returns a result set (not a result set in an OUT
parameter, but a result set from the procedure itself), then use
queryForList() or queryForObject(). Use queryForList() if you expect
more than one result object, or queryForObject() if you expect only one
result Object.
...
If your procedure does not return result sets, or only returns result
sets in OUT parameters, then use the update() method.
-Ryan
On Mon, Mar 10, 2008 at 2:22 PM, Ryan Shelley <12...@gmail.com>
wrote:
In the one you had, you were creating an empty HashMap, executing the
queryForList method passing in the empty HashMap, and then converting
the still empty HashMap to a list and returning that to your method.
queryForList doesn't take an object parameter to store the results in,
it takes an object parameter to pass variables into your queries. The
return value of queryForList is a List of your ResultMapped Models.
-Ryan
On Mon, Mar 10, 2008 at 2:06 PM, Jesse Reimann <jr...@ctigroup.com>
wrote:
Ryan that's exactly what I did that wouldn't work. It returns to me a
List that has 10 elements all which are NULL.
Jesse
Re: Re: Question about queryForList and Oracle ref cursors
Posted by Sundar Sankar <fa...@gmail.com>.
Jason,
This is what you will have to do,
1. Map an object to the columns being returned from the cursor in your
sq-map-config file. i.e you should have an entry like this
<result id="result" type="ORACLECURSOR" resultType="java.sql.ResultSet"
paramType="OUT"/>
2. You would have mapped your input Parameter in a map and passed it to
getSQLMapClientTemplate.queryForList("ProcName", inputMap").
This doesnt need to be associated to any List as the List will always
be null.
3. I have assumed that the parameterName declared for the cursor in the
procedure is result.
The output you would be expecting would be
List<UserDefinedObject> someList = List<UserDefinedObject>inputMap.get
("result");
If you get the result as null, you are either not marking the paramType as
OUT/INOUT in your config or parameter name is wrong and you have to check
the procedure to ensure the parameter name of the resultant cursor is right.
My syntax could have been exactly right but this is the gist of the how to
do it. I am again reiterating the point that queryForList doesnt work for
SP's. It returns a list only for queries. The result of SP is in the map
that you passed. I hope I have helped.
-S
On Mon, Mar 10, 2008 at 6:13 PM, Jason Bennett <Ja...@3nonline.com>
wrote:
> One problem I had in this scenario is when I pass parameters to the
> function, and expect a cursor back. I had to map the returning parameter,
> since I had mapped all the in parameters. This prevented me from using
> queryForList() as I wanted to. Am I doing anything wrong, or does passing
> parameters to the SP force me to use the update() method?
>
>
>
> jason
>
>
> ------------------------------
>
> *From:* Ryan Shelley [mailto:12gaugemedia@gmail.com]
> *Sent:* Monday, March 10, 2008 2:38 PM
> *To:* user-java@ibatis.apache.org
> *Subject:* Re: Re: Question about queryForList and Oracle ref cursors
>
>
>
> For reference:
>
>
> http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+call+a+stored+procedure
>
> Under: What SqlMapClient Method Should I Use?
> ...
> If your procedure returns a result set (not a result set in an OUT
> parameter, but a result set from the procedure itself), then use *
> queryForList()* or *queryForObject()*. Use queryForList() if you expect
> more than one result object, or queryForObject() if you expect only one
> result Object.
> ...
> If your procedure does not return result sets, or only returns result sets
> in OUT parameters, then use the *update()* method.
>
> -Ryan
>
> On Mon, Mar 10, 2008 at 2:22 PM, Ryan Shelley <12...@gmail.com>
> wrote:
>
> In the one you had, you were creating an empty HashMap, executing the
> queryForList method passing in the empty HashMap, and then converting the
> still empty HashMap to a list and returning that to your method.
> queryForList doesn't take an object parameter to store the results in, it
> takes an object parameter to pass variables into your queries. The return
> value of queryForList is a List of your ResultMapped Models.
>
> -Ryan
>
>
>
> On Mon, Mar 10, 2008 at 2:06 PM, Jesse Reimann <jr...@ctigroup.com>
> wrote:
>
> Ryan that's exactly what I did that wouldn't work. It returns to me a List that has 10 elements all which are NULL.
>
>
>
>
>
>
>
> Jesse
>
>
>
>
>
>
>
RE: Re: Question about queryForList and Oracle ref cursors
Posted by Jason Bennett <Ja...@3nonline.com>.
One problem I had in this scenario is when I pass parameters to the
function, and expect a cursor back. I had to map the returning
parameter, since I had mapped all the in parameters. This prevented me
from using queryForList() as I wanted to. Am I doing anything wrong, or
does passing parameters to the SP force me to use the update() method?
jason
________________________________
From: Ryan Shelley [mailto:12gaugemedia@gmail.com]
Sent: Monday, March 10, 2008 2:38 PM
To: user-java@ibatis.apache.org
Subject: Re: Re: Question about queryForList and Oracle ref cursors
For reference:
http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+c
all+a+stored+procedure
Under: What SqlMapClient Method Should I Use?
...
If your procedure returns a result set (not a result set in an OUT
parameter, but a result set from the procedure itself), then use
queryForList() or queryForObject(). Use queryForList() if you expect
more than one result object, or queryForObject() if you expect only one
result Object.
...
If your procedure does not return result sets, or only returns result
sets in OUT parameters, then use the update() method.
-Ryan
On Mon, Mar 10, 2008 at 2:22 PM, Ryan Shelley <12...@gmail.com>
wrote:
In the one you had, you were creating an empty HashMap, executing the
queryForList method passing in the empty HashMap, and then converting
the still empty HashMap to a list and returning that to your method.
queryForList doesn't take an object parameter to store the results in,
it takes an object parameter to pass variables into your queries. The
return value of queryForList is a List of your ResultMapped Models.
-Ryan
On Mon, Mar 10, 2008 at 2:06 PM, Jesse Reimann <jr...@ctigroup.com>
wrote:
Ryan that's exactly what I did that wouldn't work. It returns to me a
List that has 10 elements all which are NULL.
Jesse
Re: Re: Question about queryForList and Oracle ref cursors
Posted by Ryan Shelley <12...@gmail.com>.
For reference:
http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+call+a+stored+procedure
Under: What SqlMapClient Method Should I Use?
...
If your procedure returns a result set (not a result set in an OUT
parameter, but a result set from the procedure itself), then use *
queryForList()* or *queryForObject()*. Use queryForList() if you expect more
than one result object, or queryForObject() if you expect only one result
Object.
...
If your procedure does not return result sets, or only returns result sets
in OUT parameters, then use the *update()* method.
-Ryan
On Mon, Mar 10, 2008 at 2:22 PM, Ryan Shelley <12...@gmail.com>
wrote:
> In the one you had, you were creating an empty HashMap, executing the
> queryForList method passing in the empty HashMap, and then converting the
> still empty HashMap to a list and returning that to your method.
> queryForList doesn't take an object parameter to store the results in, it
> takes an object parameter to pass variables into your queries. The return
> value of queryForList is a List of your ResultMapped Models.
>
> -Ryan
>
>
> On Mon, Mar 10, 2008 at 2:06 PM, Jesse Reimann <jr...@ctigroup.com>
> wrote:
>
> > Ryan that's exactly what I did that wouldn't work. It returns to me a List that has 10 elements all which are NULL.
> >
> >
> >
> >
> >
> > Jesse
> >
> >
> >
>
>
Re: Re: Question about queryForList and Oracle ref cursors
Posted by Ryan Shelley <12...@gmail.com>.
In the one you had, you were creating an empty HashMap, executing the
queryForList method passing in the empty HashMap, and then converting the
still empty HashMap to a list and returning that to your method.
queryForList doesn't take an object parameter to store the results in, it
takes an object parameter to pass variables into your queries. The return
value of queryForList is a List of your ResultMapped Models.
-Ryan
On Mon, Mar 10, 2008 at 2:06 PM, Jesse Reimann <jr...@ctigroup.com>
wrote:
> Ryan that's exactly what I did that wouldn't work. It returns to me a List that has 10 elements all which are NULL.
>
>
>
>
>
> Jesse
>
>
>
RE: Re: Question about queryForList and Oracle ref cursors
Posted by Jesse Reimann <jr...@ctigroup.com>.
Ryan that's exactly what I did that wouldn't work. It returns to me a
List that has 10 elements all which are NULL.
Jesse
RE: Re: Question about queryForList and Oracle ref cursors
Posted by Sundar Sankaranarayanan <Su...@phoenix.edu>.
Jesse,
I don't think the query for list makes much of an impact in
calling stored procedures. U will have to do a
inputMap.get("resultParameter) to get the output. The surprising thing
is the map is loaded, if you call queryForList or update or any of the
methods. I really dont think you can do anything to return the result
directly. The output will be null if you did that.
One more reason why I feel that this has been implemented this way is
there can be multiple out parameters from the procedure.
If I am not wrong, this is mentioned in either the spring reference or
the ibatis document pdf's
Regards
Sundar
________________________________
From: Jesse Reimann [mailto:jreimann@ctigroup.com]
Sent: Monday, March 10, 2008 2:51 PM
To: user-java@ibatis.apache.org
Subject: Re: Re: Question about queryForList and Oracle ref cursors
Ryan I guess you didn't read my original post. I tried both the Hashmap
method of queryForList and the way you suggested. The only way I can
actually get a populated List back is using the HashMap. If I do it this
way :
return
getSqlMapClientTemplate().queryForList("TEST_TESTPROC.GET_CDDRPT");
The resulting List contains 10 elements which are all NULL. Using the
Hashmap method I get the proper List with all my records.
Jesse
Re: Re: Question about queryForList and Oracle ref cursors
Posted by Jesse Reimann <jr...@ctigroup.com>.
Ryan I guess you didn't read my original post. I tried both the Hashmap
method of queryForList and the way you suggested. The only way I can
actually get a populated List back is using the HashMap. If I do it this
way :
return
getSqlMapClientTemplate().queryForList("TEST_TESTPROC.GET_CDDRPT");
The resulting List contains 10 elements which are all NULL. Using the
Hashmap method I get the proper List with all my records.
Jesse