binding params in dynamic queries in ESQL

[R Hunt]

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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]