You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Markus Vaterlaus <mv...@gmail.com> on 2004/11/22 09:50:30 UTC
Dynamic adoption of SQL-Query in ESQL based on parameter
Hi list,
Is there a way to dynamically adopt an SQL-Query in a ESQL based on
request-parameters? Based on the following excerpt from an ESQL I can
think of two use cases for such a possibility:
1. the parameter "sort" is not present in the request, the query
is anyhow processed and the result displayed, but the result rows are
unsorted.
2. the query is enhanced with an additional where clauses
( e.q. AND c1.id > <xsp-request:get-parameter name="limit"/>).
The additional condition is taken into account only, if the
parameter limit exists and is valid.
...
<esql:execute-query>
<esql:query>
SELECT
c1.id, c1.intl_id, c1.descn, o1.key, o1.text_ge
FROM
table1 c1, table2 o1
WHERE
c1.id = o1.key
AND c1.id > <xsp-request:get-parameter name="limit"/>
ORDER BY by <xsp-request:get-parameter name="sort"/>
</esql:query>
</esql:execute-query>
....
Is there an easy way to do this? I didn't find any references in the docu.
Thank you for your support!
Markus
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Dynamic adoption of SQL-Query in ESQL based on parameter
Posted by Markus Vaterlaus <mv...@gmail.com>.
Hello all,
I got some other work to do, therefore the solution of my own problem
had to wait a bit... However this morning I found time and implemented
a solution. The information at
<http://cocoon.apache.org/2.1/userdocs/xsp/esql.html#Dynamic+Queries>
gave me a hint. For anybody else interested, I'll post here the
relevant parts of my actual ESQL. Be aware, that this solution has the
drawback of possible code injection. Actually for my application this
is not a major issue, as all users are internal and the connection
pool used is read only.
--mv
...
<page>
<xsp:logic>
String mytype = <xsp-request:get-parameter name="limit"/>;
String where_obj = "";
String mysort = <xsp-request:get-parameter name="sort"/>;
String order_by = "";
// where clause is added to the query if limit has any value
if ( mytype!= null ) {
where_obj += "AND c1.id = " + mytype;
}
// order by added to the query if sort has any value
if ( mysort!= null ) {
order_by += "order by " + mysort;
}
</xsp:logic>
...
<esql:execute-query>
<esql:query>
SELECT
c1.id, c1.intl_id, c1.descn, o1.key, o1.text_ge
FROM
table1 c1, table2 o1
WHERE
c1.id = o1.key
<xsp:expr>where_obj</xsp:expr>
<xsp:expr>order_by</xsp:expr>
</esql:query>
</esql:execute-query>
...
</page>
On Mon, 22 Nov 2004 09:50:30 +0100, Markus Vaterlaus
<mv...@gmail.com> wrote:
> Hi list,
>
> Is there a way to dynamically adopt an SQL-Query in a ESQL based on
> request-parameters? Based on the following excerpt from an ESQL I can
> think of two use cases for such a possibility:
>
> 1. the parameter "sort" is not present in the request, the query
> is anyhow processed and the result displayed, but the result rows are
> unsorted.
> 2. the query is enhanced with an additional where clauses
> ( e.q. AND c1.id > <xsp-request:get-parameter name="limit"/>).
> The additional condition is taken into account only, if the
> parameter limit exists and is valid.
>
> ...
> <esql:execute-query>
> <esql:query>
> SELECT
> c1.id, c1.intl_id, c1.descn, o1.key, o1.text_ge
> FROM
> table1 c1, table2 o1
> WHERE
> c1.id = o1.key
> AND c1.id > <xsp-request:get-parameter name="limit"/>
> ORDER BY by <xsp-request:get-parameter name="sort"/>
> </esql:query>
> </esql:execute-query>
> ....
>
> Is there an easy way to do this? I didn't find any references in the docu.
>
> Thank you for your support!
>
> Markus
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org