Re: Query from a property file (Dynamic Query Generation)

[Richard In Public <ed...@blueyonder.co.uk>]

Hi Panna

You can use the following techniques to dynamically generate a query within
XSP:

1.  _Generate the query using a *java method* within the *<xsp:logic>* tag:_

e.g.

<xsp:logic>
   private String generateQuery()
   {
      int supplier_id = request.getParameter("supplier_id");  /* grab
parameters - the request variable will be in scope by the time this is
compiled */
      String queryString = "select * from products" + (supplier_id == -1 ?
"" : " where supplier_id = " + Integer.toString(supplier_id));  /* insert
logic as required */
      return queryString;
   }
</xsp:logic>

...//...

<esql:execute-query>
   <esql:query><xsp:expr>*getQuery()*</xsp:expr></esql:query>
<esql:res... etc.

..//...

2.  _Use XSL within a logic sheet:

I haven't tred this yet (because my queries are usually so specific to their
XSP in question) but you can probably use the following steps:

- define a stylesheet with a template that accepts the relevant parameters
(i.e. using <xsl:param name="....) and returns the appropriate query string
(making use of xsl:choose, xsl:if etc.)
- declare a namespace for the stylesheet within cocoon.xsconf using (I
think) a <builtin-logicsheet tag>
- declare this namespace within your XSP document
- from within your XSP, invoke the stylesheet with the appropriate
parameters: e.g. <esql:query><myqueryformat:format_query
supplier_id="-1">...(I'm afraid I don't know how one passes the parameters
to the stylesheet but there is, I believe, a way)

I've just sketched the solutions here.  Feel free to mail me if you need
additional help.  I hope this helps -  I cannot see where you are defining
the 'column' and 'order' variables in your example so I am not
sure whether I've correctly understood what you need to achieve:

> Order By <xsp:expr>column</xsp:expr> <xsp:expr>order</xsp:expr>

Regards,

Richard

----- Original Message -----
From: "Panna Bodupali" <pa...@bsil.com>
To: <co...@xml.apache.org>
Sent: Friday, February 21, 2003 8:35 AM
Subject: Query from a property file


> Hi All,
>
> I need some help regading fetching query from a properties file.
>
> Currently I have written the query below in my XSP file under the
> <esq:query> tag
>
>       select
>               UPPER(CHN.CHANNEL_NAME) AS CHNL_NAME,
>       CHN.CHANNEL_NAME as CHANNEL_NAME,
>       CHN.CHANNEL_ID as CHANNEL_ID,
>       MAX(PCU.PEAK_CONC_USERS) as PEAK_CONC_USERS,
>                       MAX(PCU.PEAK_CONC_TIME) as PEAK_CONC_TIME
> from
>       PEAKCONCUSERSREPORT PCU,
>       CHANNELS CHN
> where
>       PCU.PRODUCT_ID = <esql:parameter><xsp-request:get-parameter
> name="productType"/></esql:parameter>
>       and PCU.DATE_ID =
> to_date(<esql:parameter><xsp:expr>enddt</xsp:expr></esql:parameter>,'MON
DD,
> YYYY')
>                       and CHN.CHANNEL_ID = PCU.CHANNEL_ID
> group by CHN.CHANNEL_ID, CHANNEL_NAME
> Order By <xsp:expr>column</xsp:expr> <xsp:expr>order</xsp:expr>
>
>
> Now I need to fetch this query from a property file instead of writing the
> query in the esql tag.
>
> My question is : How do we write such query when it has dynamic values,
like
> the Product id depends on the PrductType parameter and the date_id depends
> on the date selected at runtime, and the order by clause also depends on
> some logic.
>
> Is it possible to do this in cocoon.
>
> I was thinking of using the java.util.Properties to load the properties
file
> and then get the query string.
>
> Thanks in advance,
> Panna
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org
>


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