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
