cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joel McConaughy <j...@displayware.com>
Subject Re: binding params in dynamic queries in ESQL
Date Fri, 05 Mar 2004 17:53:49 GMT
I ran into the same problem and ended up building the entire sql 
statement in a String variable within an <xsp:logic> block.  This 
eliminated a number of parsing problems I was having and avoids having 
to root thru the esql logicsheet to figure out exactly whats going on.

R Hunt wrote:

> Thanks in advance for any help on this.
>
> I would like to build a SQL statement dynamically based on whether not 
> not certain parameters have been sent, but also use bound parameters 
> in my SQL query.
> I seem to be having troubles getting the parsing  quite right.
>
> To give a better idea of what I'm trying to do, I've included my code 
> from my first attempt:
>
> ----------------- start xsp snippet --------------------------
> <?xml version="1.0" encoding="ISO-8859-1"?>
>
> <xsp:page language="java"
>   xmlns:xsp="http://apache.org/xsp"
>   xmlns:xsp-request="http://apache.org/xsp/request/2.0"
>   xmlns:esql="http://apache.org/cocoon/SQL/v2"
> >
>
> <page>
>
>  <xsp:logic>
>    String paramopen = "&lt;esql:parameter&gt;";  // open esql 
> parameter tag
>    String paramclose = "&lt;/esql:parameter&gt;";  // close 
> esql:parameter tag
>
>    String region = <xsp-request:get-parameter name="region"/>;
>          String whereclause = new String();
>
>         if (region != null ) {
>      // if region defined in request parameters, include it in where 
> clause
>     // but make the value a bound parameter
>                 whereclause=  " AND cr.region = " + paramopen + region 
> + paramclose );
>         }
>
> </xsp:logic>
>
>   <esql:connection>
>    <esql:pool>utadoci</esql:pool>
>    <esql:execute-query>
>         <esql:query>SELECT  program.name, trip.title  FROM trip, 
> program, WHERE trip.program_id=program.program_id 
> <xsp:expr>whereclause</xsp:expr>
>     </esql:query>
>     <esql:results>
> ...
> <!--and so on-->
> -------- end xsp snippet ----------------------------
>
> Of course, the whereclause gets passed as an string to the esql:query 
> without interpreting the parameter tags, so the previous code 
> generates an error like :
> Original Exception: java.lang.RuntimeException: Error executing 
> statement:
>
> SELECT  region.name, trip.title
>   FROM trip, region  WHERE trip.region_id=region.region_id AND 
> cr.region = <esql:parameter>23</esql:parameter>   : 
> java.sql.SQLException: ORA-00936: missing expression
>
>
> It seems like maybe I should be able to embed the logic in the 
> esql:query instead, but then I have problems with the String 
> concatenation, and the quoting.
> Does anyone have any advice, or a basic plan for tackling this?
>
> Thanks in advance,
> Rebecca
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>


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


Mime
View raw message