cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marty McClelland" <mmcclell...@eduprise.com>
Subject RE: Cocoon2: couldn't make nested queries work for ESQL in XSP.
Date Mon, 25 Mar 2002 16:16:55 GMT
I've done a number of nested queries in C1.8.2 and as far as I know the
approach is the same in C2.  Here is an example:


   <esql:execute-query>
<!-- for each rid in Collection - get the centity's -->
<!-- SQL Server query
SELECT DISTINCT Contribute.centity_id, Contribute.role
FROM         Contribute INNER JOIN
                      Collection_resources ON Contribute.rid =
Collection_resources.rid
WHERE     (Collection_resources.Collection_id = 18)
-->
   <esql:query>select Distinct Contribute.Centity_id, Contribute.role
FROM         Contribute , Collection_resources where
Collection_resources.Collection_id=<xsp:expr>collectionID</xsp:expr> and
Collection_resources.rid=Contribute.rid</esql:query>
<esql:error-results>
		<!-- error-results occur when there is a sql error -->
		<!-- db connection errors are thrown to xsp -->
		<!-- stacktrace is shown for errors in xsp -->
			<error>SQL error - contact the ilumina
administrator</error>
</esql:error-results>

<esql:results>
<resources>
<esql:row-results>
<resource>
<centityID><esql:get-int column="1"/></centityID>
<role><esql:get-string column="2"/></role>
 
 <!-- get the count -->
  <esql:execute-query>
    <esql:query>select count(distinct Contribute.rid) from Contribute,
Collection_resources where centity_id=<esql:parameter
type="int"><esql:get-int ancestor="1" column="1" /></esql:parameter> and
Contribute.rid=Collection_resources.rid and
Collection_resources.Collection_id=<xsp:expr>collectionID</xsp:expr>
</esql:query>
    <esql:results>
      <esql:row-results>
          <count><esql:get-int column="1"/></count>
     </esql:row-results>
    </esql:results>
  </esql:execute-query>
<!-- get the name -->
  <esql:execute-query>
    <esql:query>select first_name, last_name from Centity where
centity_id=<esql:parameter type="int"><esql:get-int ancestor="1"
column="1" /></esql:parameter></esql:query>
    <esql:results>
      <esql:row-results>
          <name><esql:get-string column="2"/>, <esql:get-string
column="1"/></name>
     </esql:row-results>
    </esql:results>
  </esql:execute-query>


</resource>
</esql:row-results>
</resources>
</esql:results>


  </esql:execute-query>

marty

> -----Original Message-----
> From: Argyn Kuketayev [mailto:Argyn.Kuketayev@plateau.com] 
> Sent: Monday, March 25, 2002 10:34 AM
> To: 'cocoon-users@xml.apache.org'
> Subject: Cocoon2: couldn't make nested queries work for ESQL in XSP.
> 
> 
> I've to make a report on the table M, then for each row I 
> need to show corresponding rows from tables F and C.
> 
> Something like this:
> 
> <rowset name="M">
>   <row>
>     <ID>1</ID>
> 
>     <rowset name="F">    
>       <row>
>         <f_name>f1</f_name>
>       <row>
>       ...
>     </rowset>
> 
>     <rowset name="C">    
>       <row>
>         <c_name>c1</c_name>
>       <row>
>       ....
>     </rowset>
>   </row>
> ...
> </rowset>
> 
> I tried this :
> 
>  <esq
> l:parameter type="int"><esql:get-int ancestor="1" 
> column="id"/></esql:parameter>
> 
> It didn't work with an exception: 
> 
> org.apache.cocoon.ProcessingException: Exception in
> ServerPagesGenerator.generate(): java.lang.RuntimeException: 
> Error executing prepared statement: select ... from f where M_ID = ? :
> java.sql.SQLException: ORA-01722: invalid number
> 
> 
> Can somebody give me a WORKING and tested example of nested 
> queries in Cocoon2?
> 
> thanks,
> Argyn
> 
> ---------------------------------------------------------------------
> Please check that your question has not already been answered 
> in the FAQ before posting. 
<http://xml.apache.org/cocoon/faqs.html>

To unsubscribe, e-mail: <cocoon-users-unsubscribe@xml.apache.org>
For additional commands, e-mail: <cocoon-users-help@xml.apache.org>


---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>

To unsubscribe, e-mail: <cocoon-users-unsubscribe@xml.apache.org>
For additional commands, e-mail: <cocoon-users-help@xml.apache.org>


Mime
View raw message