Thanks Clinton,
I agree, if the tree is arbitrarily sized then this would
certainly be a very inefficient way of getting the data.
For this particular object structure i don't expect to
have to go up more than about 5 levels (parent objects)
but i can't hard code it to 5.
I have since got around this problem in Java by removing
the self-join in the sqlmap and simply loading the parent
object onto the actual object when a call is made to
getParent() on the actual object (a form of lazy loading i
suppose). It would be nice if IBatis could do this work
for me but i guess its not imperative, i just wondered if
i had discovered an issue that IBatis won't allow you to
perform n-level self-joins? or as you suggest some config
issue. I am using MSDE at present so i will investigate
the cursor issue as you suggested first.
Thanks, Mark.
On Fri, 19 Oct 2007 08:15:26 -0600
"Clinton Begin" <[EMAIL PROTECTED]> wrote:
Your JDBC driver may only allow a certain number of
result sets open at a
time (per connection). If your tree is a fixed depth,
you can see if you
can try to configure it such that you have enough (in ms
sql server, make
sure your select mode is "cursor").
If your tree is arbitrarily sized or simply very deep,
then this whole
approach may be very inefficient and you'd be better off
trying something
else.
You could try doing an actual SQL self join, or just load
the flat structure
into an array of objects and use a more efficient tree
mapping algorithm to
link it all up.
It's not a super easy or clean thing to do...but my first
question to you
would be...do you actually need the whole tree? And does
it need to be a
tree?
Regards,
Clinton
-----Original Message-----
From: Mark McKenzie [mailto:[EMAIL PROTECTED]
Sent: October-19-07 1:15 AM
To: [email protected]
Subject: PLEASE HELP - self-joins to obtain a tree
structure
Hi, I am trying to use the following IBatis SQL map to
retrieve a single object (ProductGroup) which will
contain
a parent object of the same type. The parent object
itself
could also potentially have a parent object ?.. and so on
and so on until we reach the top of the tree:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map
2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd";>
<sqlMap namespace="ProductGroup">
<resultMap id="result" class="ProductGroup">
<result property="primaryKey" column="PRGP_ID"/>
<result property="code"
column="PRGP_CODE"/>
<result property="name"
column="PRGP_NAME"/>
<result property="parentGroup"
column="groupId=PARENT_ID"
select="ProductGroup.getById"/>
</resultMap>
<!-- fetch a complex product group object containing
all parent groups -->
<select id="getById" resultMap="result">
SELECT prgp.*
,pgsl.prgp_id AS PARENT_ID
FROM product_group prgp
INNER JOIN prodgp_str_link pgsl ON
prgp.prgp_id = pgsl.prgp_child_id
WHERE prgp.prgp_id = #groupId#
</select>
</sqlMap>
Unfortunately this does not appear to work when I use it
to retrieve a product group with more than 1 parent
object. Here is a partial stack trace of the problem I
get:
<!--
com.pcmsgroup.v21.star2.inf.dao.exception.PersistenceException:
SqlMapClient operation; uncategorized SQLException for
SQL
[]; SQL state [null]; error code [0];
--- The error occurred in conf/ibatis/ProductGroup.xml.
--- The error occurred while applying a result map.
--- Check the ProductGroup.result.
--- Check the result mapping for the 'parentGroup'
property.
--- Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in conf/ibatis/ProductGroup.xml.
--- The error occurred while applying a result map.
--- Check the ProductGroup.result.
--- The error happened while setting a property on the
result object.
--- Cause:
com.microsoft.sqlserver.jdbc.SQLServerException: The
result set is closed.; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in conf/ibatis/ProductGroup.xml.
--- The error occurred while applying a result map.
--- Check the ProductGroup.result.
--- Check the result mapping for the 'parentGroup'
property.
--- Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in conf/ibatis/ProductGroup.xml.
--- The error occurred while applying a result map.
--- Check the ProductGroup.result.
--- The error happened while setting a property on the
result object.
--- Cause:
com.microsoft.sqlserver.jdbc.SQLServerException: The
result set is closed.
at
com.pcmsgroup.v21.star2.inf.dao.BaseSqlMapClientDao.queryForObject(BaseSqlMa
pClientDao.java:166)
at
com.pcmsgroup.v21.star2.persistence.productgroup.ProductGroupDAO.getGroup(Pr
oductGroupDAO.java:46)
Does anyone have any idea what the issue is and possibly
how to resolve it?
Many Thanks, Mark
The information contained in this e-mail is intended only
for the person or
entity to which it is addressed and may contain
confidential and/or
privileged material. If you are not the intended
recipient of this e-mail,
the use of this information or any disclosure, copying or
distribution is
prohibited and may be unlawful. If you received this in
error, please
contact the sender and delete the material from any
computer. The views
expressed in this e-mail may not necessarily be the views
of the PCMS Group
plc and should not be taken as authority to carry out any
instruction
contained. The PCMS Group reserves the right to monitor
and examine the
content of all e-mails.
The PCMS Group plc is a company registered in England and
Wales with company
number 1459419 whose registered office is at PCMS House,
Torwood Close,
Westwood Business Park, Coventry CV4 8HX, United Kingdom.
VAT No: GB
705338743
Thanks
Mark
The information contained in this e-mail is intended only for the person or
entity to which it is addressed and may contain confidential and/or privileged
material. If you are not the intended recipient of this e-mail, the use of
this information or any disclosure, copying or distribution is prohibited and
may be unlawful. If you received this in error, please contact the sender and
delete the material from any computer. The views expressed in this e-mail may
not necessarily be the views of the PCMS Group plc and should not be taken as
authority to carry out any instruction contained. The PCMS Group reserves the
right to monitor and examine the content of all e-mails.
The PCMS Group plc is a company registered in England and Wales with company
number 1459419 whose registered office is at PCMS House, Torwood Close,
Westwood Business Park, Coventry CV4 8HX, United Kingdom. VAT No: GB 705338743
