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 Paul Sanders <te...@gmail.com> on 2007/04/11 22:13:06 UTC
Stored Procedure Call Returns Empty List But Does Create Beans
[Hate to post again so quickly, I used to think I knew what I was doing in
this space but it seems not.... hopefully someone can help me here and then
I promise to go away.]
I have a stored procedure that is just a wrapper around a "SELECT *" and I
have straight JDBC code that uses it and works. So I'm trying to move to
Spring/iBATIS - my code to execute the query runs but the returned List
object is empty. If I log each constructor call and setter call I get the
appropriate output - the beans are being created, and with the right values,
but somehow aren't being returned in the List. Is this a common beginners
mistake??
Not sure how much of the code is valuable but here is some...
public List<BanPolicy>fetchBanPolicies()
{
return getSqlMapClientTemplate().queryForList("fetch-ban-policies"); //
returns empty list
}
the mapping file...
<resultMap class="BanPolicy" id="fetch-ban-policies-map">
<result property="banPolicyID" columnIndex="1" javaType="int" />
<result property="applicationID" columnIndex="2" javaType="int" />
<result property="minBanCount" columnIndex="3" javaType="int" />
<result property="maxBanCount" columnIndex="4" javaType="int" />
<result property="accountBanDays" columnIndex="5" javaType="float" />
<result property="dnasBanDays" columnIndex="6" javaType="float" />
<result property="banReasonId" columnIndex="7" javaType="int" />
</resultMap>
<parameterMap id="fetch-ban-policies-rs" class="map">
<parameter property="o" javaType="java.sql.ResultSet"
jdbcType="ORACLECURSOR" mode="OUT" resultMap="fetch-ban-policies-map" />
</parameterMap>
<procedure id="fetch-ban-policies" parameterMap="fetch-ban-policies-rs">
{ call .getBanPolicies(?) }
</procedure>
and the spring deftn.....
<bean id="banPolicyDAO"
class="persistence.dao.managers.BanPolicyManager">
<property name="sqlMapClient" ref="sqlMapClient"/>
</bean>
I'm going to do a hard coded SQL version just to get something working
today....
Cheers
Paul
--
View this message in context: http://www.nabble.com/Stored-Procedure-Call-Returns-Empty-List-But-Does-Create-Beans-tf3561741.html#a9947480
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Stored Procedure Call Returns Empty List But Does Create Beans
Posted by Paul Sanders <te...@gmail.com>.
Thanks again for the replies - I have a working method - yea! In the end it
seemed to take a little bit of everyone's suggestions for it to work. This
is what I ended up with - my DAO method:
public List<BanPolicy> fetchBanPolicies()
{
Map map = new HashMap();
getSqlMapClientTemplate().queryForList("fetch-ban-policies", map);
return (List<BanPolicy>)map.get("banPolicies");
}
and my SQLMap:
<parameterMap id="fetch-ban-policies-rs" class="map">
<parameter property="banPolicies" javaType="java.sql.ResultSet"
jdbcType="ORACLECURSOR" mode="OUT" resultMap="fetch-ban-policies-map"/>
</parameterMap>
<procedure id="fetch-ban-policies" parameterMap="fetch-ban-policies-rs"
>
{ call getBanPolicies(?) }
</procedure>
and I get a perfect List of BanPolicy objects. If I leave out the javaType
attribute then the banPolicies object in the Map turns out to be an
OracleResultSet rather than a List.
Your help much appreciated, feels like everything should be real
straightforward from here.
Jeff Butler-2 wrote:
>
> The list is not returned from the queryForList method because the
> resultSet
> is an output parameter - not a resultSet from the procedure call. So you
> need to try something like this:
>
> Map parms = new HashMap();
> getSqlMapClientTemplate().update("fetch-ban-policies", parms);
> return parms.get("o");
>
> Jeff Butler
>
--
View this message in context: http://www.nabble.com/Stored-Procedure-Call-Returns-Empty-List-But-Does-Create-Beans-tf3561741.html#a9948737
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Stored Procedure Call Returns Empty List But Does Create Beans
Posted by Jeff Butler <je...@gmail.com>.
The list is not returned from the queryForList method because the resultSet
is an output parameter - not a resultSet from the procedure call. So you
need to try something like this:
Map parms = new HashMap();
getSqlMapClientTemplate().update("fetch-ban-policies", parms);
return parms.get("o");
Jeff Butler
On 4/11/07, Paul Sanders <te...@gmail.com> wrote:
>
>
> [Hate to post again so quickly, I used to think I knew what I was doing in
> this space but it seems not.... hopefully someone can help me here and
> then
> I promise to go away.]
>
> I have a stored procedure that is just a wrapper around a "SELECT *" and I
> have straight JDBC code that uses it and works. So I'm trying to move to
> Spring/iBATIS - my code to execute the query runs but the returned List
> object is empty. If I log each constructor call and setter call I get the
> appropriate output - the beans are being created, and with the right
> values,
> but somehow aren't being returned in the List. Is this a common beginners
> mistake??
>
> Not sure how much of the code is valuable but here is some...
>
> public List<BanPolicy>fetchBanPolicies()
> {
> return getSqlMapClientTemplate().queryForList("fetch-ban-policies"); //
> returns empty list
> }
>
> the mapping file...
>
> <resultMap class="BanPolicy" id="fetch-ban-policies-map">
> <result property="banPolicyID" columnIndex="1" javaType="int" />
> <result property="applicationID" columnIndex="2" javaType="int" />
> <result property="minBanCount" columnIndex="3" javaType="int" />
> <result property="maxBanCount" columnIndex="4" javaType="int" />
> <result property="accountBanDays" columnIndex="5" javaType="float"
> />
> <result property="dnasBanDays" columnIndex="6" javaType="float" />
> <result property="banReasonId" columnIndex="7" javaType="int" />
> </resultMap>
>
> <parameterMap id="fetch-ban-policies-rs" class="map">
> <parameter property="o" javaType="java.sql.ResultSet"
> jdbcType="ORACLECURSOR" mode="OUT" resultMap="fetch-ban-policies-map" />
> </parameterMap>
>
> <procedure id="fetch-ban-policies" parameterMap="fetch-ban-policies-rs">
> { call .getBanPolicies(?) }
> </procedure>
>
> and the spring deftn.....
>
> <bean id="banPolicyDAO"
> class="persistence.dao.managers.BanPolicyManager">
> <property name="sqlMapClient" ref="sqlMapClient"/>
> </bean>
>
> I'm going to do a hard coded SQL version just to get something working
> today....
>
> Cheers
>
> Paul
> --
> View this message in context:
> http://www.nabble.com/Stored-Procedure-Call-Returns-Empty-List-But-Does-Create-Beans-tf3561741.html#a9947480
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
RE: Stored Procedure Call Returns Empty List But Does Create Beans
Posted by "Sudhir, Sudhir (Contractor)" <SS...@moneygram.com>.
Take a look at this post, you have couple of options to fetch the
resultset.
http://opensource.atlassian.com/confluence/oss/display/IBATIS/Oracle+REF
+CURSOR+Solutions
-----Original Message-----
From: Paul Sanders [mailto:tendancer@gmail.com]
Sent: Wednesday, April 11, 2007 3:13 PM
To: user-java@ibatis.apache.org
Subject: Stored Procedure Call Returns Empty List But Does Create Beans
[Hate to post again so quickly, I used to think I knew what I was doing
in
this space but it seems not.... hopefully someone can help me here and
then
I promise to go away.]
I have a stored procedure that is just a wrapper around a "SELECT *" and
I
have straight JDBC code that uses it and works. So I'm trying to move to
Spring/iBATIS - my code to execute the query runs but the returned List
object is empty. If I log each constructor call and setter call I get
the
appropriate output - the beans are being created, and with the right
values,
but somehow aren't being returned in the List. Is this a common
beginners
mistake??
Not sure how much of the code is valuable but here is some...
public List<BanPolicy>fetchBanPolicies()
{
return getSqlMapClientTemplate().queryForList("fetch-ban-policies");
//
returns empty list
}
the mapping file...
<resultMap class="BanPolicy" id="fetch-ban-policies-map">
<result property="banPolicyID" columnIndex="1" javaType="int" />
<result property="applicationID" columnIndex="2" javaType="int"
/>
<result property="minBanCount" columnIndex="3" javaType="int" />
<result property="maxBanCount" columnIndex="4" javaType="int" />
<result property="accountBanDays" columnIndex="5"
javaType="float" />
<result property="dnasBanDays" columnIndex="6" javaType="float"
/>
<result property="banReasonId" columnIndex="7" javaType="int" />
</resultMap>
<parameterMap id="fetch-ban-policies-rs" class="map">
<parameter property="o" javaType="java.sql.ResultSet"
jdbcType="ORACLECURSOR" mode="OUT" resultMap="fetch-ban-policies-map" />
</parameterMap>
<procedure id="fetch-ban-policies"
parameterMap="fetch-ban-policies-rs">
{ call .getBanPolicies(?) }
</procedure>
and the spring deftn.....
<bean id="banPolicyDAO"
class="persistence.dao.managers.BanPolicyManager">
<property name="sqlMapClient" ref="sqlMapClient"/>
</bean>
I'm going to do a hard coded SQL version just to get something working
today....
Cheers
Paul
--
View this message in context:
http://www.nabble.com/Stored-Procedure-Call-Returns-Empty-List-But-Does-
Create-Beans-tf3561741.html#a9947480
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
-----------------------------------------
This message may contain confidential information. If you are not
the intended recipient, please notify the sender immediately and
delete this email from your system.