esql and xsp...

[Michael Melhem <mi...@dresdnerbank.de>]

Hi,

Upon migrating to cocoon2 and esql, I have noticed that I can 
nolonger dynamically manipulate individual queries within a single
connection object (using xsp).

I often find it useful to dynamically break down a query into 
component sub-queries which are determined at runtime.
This way, each query will return into its own branch of the xml tree -
facilatating ease of computation in any subsequent steps/transforms.

----------------------------------------------
Example:
In the following example...each "where_clause" corresponds to a specific 
date range selected by the user at runtime. I want each range to return 
into its own branch to ease calculation of subtotals 
...
	<xsp:logic>
		// for-loop which performs the required
		// number of queries on the database
		//
		// one loop/query per specified range
		for (...)
		{
			In here determine the correct "where_clause" 
			for the corresponding range...			
	</xsp:logic>
	
			<query 
				connection="conn"
				error-stacktrace-attribute="stacktrace"
			>
				SELECT * 
    				FROM <xsp:expr>table</xsp:expr>
				WHERE <xsp:expr>where_clause</xsp:expr> 
				ORDER BY blah.. 
			</query>
	<xsp:logic>
		}// end for-loop	
	</xsp:logic>
...
--------------------------------

How do I manage the above under c2 and esql?

Any help or feedback would be much appreciated!!!

Kind Regards,
Michael Melhem

Re: esql and xsp...

[Herbert Hotz <he...@symmetrix.ch>]

Michael Melhem wrote:

> Hi,
>
> Upon migrating to cocoon2 and esql, I have noticed that I can
> nolonger dynamically manipulate individual queries within a single
> connection object (using xsp).
>
> I often find it useful to dynamically break down a query into
> component sub-queries which are determined at runtime.
> This way, each query will return into its own branch of the xml tree -
> facilatating ease of computation in any subsequent steps/transforms.
>
> ----------------------------------------------
> Example:
> In the following example...each "where_clause" corresponds to a specific
> date range selected by the user at runtime. I want each range to return
> into its own branch to ease calculation of subtotals
> ...
>         <xsp:logic>
>                 // for-loop which performs the required
>                 // number of queries on the database
>                 //
>                 // one loop/query per specified range
>                 for (...)
>                 {
>                         In here determine the correct "where_clause"
>                         for the corresponding range...
>         </xsp:logic>
>
>                         <query
>                                 connection="conn"
>                                 error-stacktrace-attribute="stacktrace"
>                         >
>                                 SELECT *
>                                 FROM <xsp:expr>table</xsp:expr>
>                                 WHERE <xsp:expr>where_clause</xsp:expr>
>                                 ORDER BY blah..
>                         </query>
>         <xsp:logic>
>                 }// end for-loop
>         </xsp:logic>
> ...
> --------------------------------
>
> How do I manage the above under c2 and esql?
>
> Any help or feedback would be much appreciated!!!
>
> Kind Regards,
> Michael Melhem
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org

Hi Michael,

As far as it concerns esql, see the following sample xml file. I do
not know, if this is valid also for C2. Give it a try!

The trick is, to use <esql:statement> instead of  <esql:query>. This
way a prepared statement is generated and you can use this for all the
other things like INSERT or UPDATE as well.

Hope it's of some help. In doupt esql.xsl is the reference! You find
it at

$COCOON_HOME/src/org/appache/cocoon/processor/xsp/library/sql/esql.xsl

Make sure that you have the latest esql.xsl version (v1.25).

--------------------------------------------------------------------
 <page title="esql sample">

  <xsp:logic>
   String sql = "SELECT Attr1, Attr2 FROM TheTable WHERE Attr1 &lt; ?";
   int lim = 15;
  </xsp:logic>

  <query>
  <esql:execute-query>
   <esql:driver>your driver</esql:driver>
   <esql:dburl>your dburl</esql:dburl>
   <esql:username>user</esql:username>
   <esql:password>pass</esql:password>

   <!-- this is what you want, right? -->
   <!-- see also the useage of the parameters -->
   <esql:statement><xsp:expr>sql</xsp:expr></esql:statement>
   <esql:parameter type="Int"><xsp:expr>lim</xsp:expr></esql:parameter>

   <esql:results>
     <Record>
    <!-- used here dixed column names -->
      <Attr1><esql:get-string column="Attr1"/></Attr1>
      <Attr2><esql:get-string column="Attr2"/></Attr2>
     </Record>
   </esql:results>

   <esql:no-results>
    <error>no results were found</error>
   </esql:no-results>

   <esql:error-results>
    <error-results>
     <message><esql:get-message/></message>
     <stacktrace><esql:get-stacktrace/></stacktrace>
    </error-results>
   </esql:error-results>

  </esql:execute-query>
  </query>

 </page>
--------------------------------------------------------------------

Greetings Herbert
--
+----------------------+-------------------------------------------+
| Herbert Hotz         | Voice:   +41 1 381 8880                   |
| Symmetrix AG         | Fax:     +41 1 381 2127                   |
| Muehle Tiefenbrunnen | GSM-SMS: +41 79 402 5704                  |
| Seefeldstrasse 231   | URL:     http://www.symmetrix.ch/         |
| CH-8008 Zurich       | E-Mail:  mailto:herbert.hotz@symmetrix.ch |
+----------------------+-------------------------------------------+