Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (athena.apache.org: domain of clinton.begin@gmail.com
 designates 209.85.198.188 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:from:to:references:in-reply-to:subject:date:mime-version:content-type:content-transfer-encoding:x-mailer:thread-index:content-language:message-id;
        b=r1jdRJMcnqWYXXKoogW4DRmUWFGAIlvxPX6JXn1Dweb52Drq3wtljOIKtgUbeHoSisO3Ty6d2/NrKpZRYbUZEenUdShCtBEnB+pxsbCAUChTKKvVdzqiKtBfvyLg9sba4H+jxQKHnMtdtPlW1fIr0emP4h3wT4Zp4vPlAjG0tEE=
From: "Clinton Begin" <clinton.begin@gmail.com>
To: <user-java@ibatis.apache.org>
References: <web-30455996@pcmsgroup.com>
In-Reply-To: <web-30455996@pcmsgroup.com>
Subject: RE: PLEASE HELP - self-joins to obtain a tree structure
Date: Fri, 19 Oct 2007 08:15:26 -0600
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Thread-Index: AcgSIEjRCqe7v46GQN2Yuq89UsZdwAAOY7xQ
Content-Language: en-ca
Message-ID: <4718bc03.22578c0a.249c.ffff8385@mx.google.com>

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:mark.mckenzie@pcmsgroup.com] 
Sent: October-19-07 1:15 AM
To: user-java@ibatis.apache.org
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