The logicsheets are prob being applied in the wrong order... try
<esql:query>
select name as reg_name,
deviceID as reg_deviceID,
description as reg_description,
startTime as reg_startTime,
endTime as reg_endTime
from user, terminal, deviceType, registration
where terminal.userID =
"<xsp:expr>request.getParameter("userID")[0]</xsp:expr>"
and user.userID = terminal.userID
and deviceType.deviceType = terminal.deviceType
and registration.terminalID = terminal.terminalID;
</esql:query>
There are several 'variables' available in the java src for you to use
directly, take a look in the generated java.
J.
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 14 August 2001 10:23 am
> To: [EMAIL PROTECTED]
> Subject: request taglib + query
>
>
> Hi all,
>
> and thanks for helping me so much!
>
> Unfortunately, I still cannot get my page to do what I want:
>
> *I am able to pick up the http request parameter from the URL
> and display it
> *I am able to run the query if I hardcode the value
> *But I am not able to run the query with the value that I
> pick up from the
> URL!
>
> I have also included the xml file and the generated java
> code. It looks fine
> to me. You can see that I have put a request in the xml
> document just to
> display, and this displays OK on the screen. But for some
> reason it won't
> work in the query... The java code generated in each case is
> the same, isn't
> it?
>
> *I have tried with ?userID=55512345
> ?userID='55512345'
> ?userID="55512345"
> *I have tried to have the database field as a string and as an integer
> *Using xsp-request instead of request doesn't work (I think
> maybe that's for
> C2?)
> *I have tried EVERY other suggestion that I have received,
> but with no luck
>
> I guess the reason must be that the query is attempted run BEFORE the
> parameter is found. Does anybody have any idea how I can work
> around this to
> use the parameter in the query?
>
> I would also like to try to store the parameter in a
> variable, and then try
> and use the variable in the query to see if that works. Does
> anybody now how
> I can do that? Christopher and Max, you both had an idea of
> building the
> query as a string with xsp:logic, but I am not able to do it
> right! If I try
> to use request.GetParameter I get error messages...
>
> Thanks again!
>
> Regards
> Anne Marie
>
> Welcome.xml:
>
> <?xml version="1.0" standalone="no"?>
> <?xml-stylesheet href="welcome-html.xsl" type="text/xsl"?>
> <?xml-stylesheet href="welcome-wml.xsl" type="text/xsl" media="wap"?>
>
> <?cocoon-process type="xsp"?>
> <?cocoon-process type="xslt"?>
>
> <!-- Welcome.xml -->
> <!-- Written by Anne Marie Hartvigsen -->
>
> <!DOCTYPE document [
> <!ENTITY email "[EMAIL PROTECTED]">
> ]>
>
> <xsp:page language="java"
> xmlns:xsp="http://www.apache.org/1999/XSP/Core";
> xmlns:esql="http://apache.org/cocoon/SQL/v2";
> xmlns:request="http://www.apache.org/1999/XSP/Request";
> >
>
> <document>
> <head>
> <pagetitle title="Titleinfo"/>
> </head>
>
> <body>
> <h1>
> Welcome,
> <request:get-parameter name="userID"/>
> </h1>
>
> <p>
> Below the query results should be displayed.
> </p>
> <p/>
>
> <br></br>
>
> <reg_data>
> <esql:connection>
>
> <esql:pool>
> annemarie
> </esql:pool>
>
> <esql:execute-query>
> <esql:query>
> select name as reg_name,
> deviceID as reg_deviceID,
> description as reg_description,
> startTime as reg_startTime,
> endTime as reg_endTime
> from user, terminal, deviceType, registration
> where terminal.userID = <request:get-parameter name="userID"/>
> and user.userID = terminal.userID
> and deviceType.deviceType = terminal.deviceType
> and registration.terminalID = terminal.terminalID;
> </esql:query>
> <esql:results>
> <header>
> These are your registrations,
> </header>
> <esql:row-results>
> <username>
> <esql:get-string column="reg_name"/>
> </username>
> <registration>
> <deviceID>
> <esql:get-string column="reg_deviceID"/>
> </deviceID>
> <description>
> <esql:get-string column="reg_description"/>
> </description>
> <startTime>
> <esql:get-string column="reg_startTime"/>
> </startTime>
> <endTime>
> <esql:get-string column="reg_endTime"/>
> </endTime>
> </registration>
> </esql:row-results>
> </esql:results>
> </esql:execute-query>
> </esql:connection>
>
> </reg_data>
>
>
>
> <br></br>
>
> <signature>
> Made by: &email;
> </signature>
> </body>
> </document>
> </xsp:page>
>
> ---------------------------
>
> welcome.java:
>
> package _var._tomcat._webapps._cocoon._vt._welcome;
>
> import java.io.*;
> import java.net.*;
> import java.util.*;
> import org.w3c.dom.*;
> import org.xml.sax.*;
> import javax.servlet.*;
> import javax.servlet.http.*;
>
> import org.apache.cocoon.parser.*;
> import org.apache.cocoon.producer.*;
> import org.apache.cocoon.framework.*;
>
> import org.apache.cocoon.processor.xsp.*;
> import org.apache.cocoon.processor.xsp.library.*;
>
> /* User Imports */
>
> import java.sql.DriverManager;
>
> import java.sql.Connection;
>
> import java.sql.Statement;
>
> import java.sql.PreparedStatement;
>
> import java.sql.ResultSet;
>
> import java.sql.ResultSetMetaData;
>
> import java.sql.SQLException;
>
> import java.text.SimpleDateFormat;
>
> import java.text.DecimalFormat;
>
> import java.io.StringWriter;
>
> import java.io.PrintWriter;
>
> import org.apache.turbine.services.db.PoolBrokerService;
>
> import org.apache.turbine.util.db.pool.DBConnection;
>
>
> public class _welcome extends XSPPage {
> /* User Class Declarations */
>
> static PoolBrokerService _esql_pool =
> PoolBrokerService.getInstance();
>
>
> class EsqlConnection {
>
> DBConnection db_connection = null;
>
>
> Connection connection = null;
> String dburl = null;
> String username = null;
> String password = null;
> }
> class EsqlQuery {
> String query;
> Statement statement;
> PreparedStatement prepared_statement;
> ResultSet resultset;
> ResultSetMetaData resultset_metadata;
> /** the position of the current row in the resultset **/
> int position = -1;
> int max_rows = -1;
> int skip_rows = 0;
> boolean results;
> }
>
>
> public void populateDocument(
> HttpServletRequest request,
> HttpServletResponse response,
> Document document
> )
> throws Exception
> {
> // Node stack logic variables
> Node xspParentNode = null;
> Node xspCurrentNode = document;
> Stack xspNodeStack = new Stack();
>
>
>
>
>
> // Make session object readily available
> HttpSession session = request.getSession(false);
>
>
> document.appendChild(
> document.createProcessingInstruction(
> "xml-stylesheet",
> "href=\"welcome-html.xsl\" type=\"text/xsl\""
> )
> );
>
> document.appendChild(
> document.createProcessingInstruction(
> "xml-stylesheet",
> "href=\"welcome-wml.xsl\" type=\"text/xsl\"
> media=\"wap\""
> )
> );
>
> document.appendChild(
> document.createProcessingInstruction(
> "cocoon-process",
> "type=\"xslt\""
> )
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("document");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> ((Element) xspCurrentNode).setAttribute(
> "xmlns:request",
> "http://www.apache.org/1999/XSP/Request";
> );
>
> Stack _esql_connections = new Stack();
> EsqlConnection _esql_connection = null;
> Stack _esql_queries = new Stack();
> EsqlQuery _esql_query = null;
> SQLException _esql_exception = null;
> StringWriter _esql_exception_writer = null;
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("head");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("pagetitle");
> xspParentNode.appendChild(xspCurrentNode);
>
>
>
>
>
> ((Element) xspCurrentNode).setAttribute(
> "title",
> "Titleinfo"
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode(" \n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("body");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("h1");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n Welcome,\n ")
> );
>
> xspCurrentNode.appendChild(
> xspExpr(
> XSPRequestLibrary.getParameter(
> request,
> String.valueOf("userID"),
> null
> )
> , document)
> );
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n \n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("p");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n Below the query
> results should be
> displayed.\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("p");
> xspParentNode.appendChild(xspCurrentNode);
>
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("br");
> xspParentNode.appendChild(xspCurrentNode);
>
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode(" \n\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("reg_data");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n")
> );
>
> if (_esql_connection != null) {
> _esql_connections.push(_esql_connection);
> }
> _esql_connection = new EsqlConnection();
> try {
>
> try {
> _esql_connection.db_connection =
> _esql_pool.getConnection(String.valueOf(
> "annemarie"
> ));
> _esql_connection.connection =
> _esql_connection.db_connection.getConnection();
> } catch (Exception _esql_exception_N2c97cc) {
> throw new RuntimeException("Error opening pooled
> connection:
> "+String.valueOf(
> "annemarie"
> )+": "+_esql_exception_N2c97cc.getMessage());
> }
> if (_esql_connection.connection == null) {
> throw new RuntimeException("Could not open pooled
> connection:
> "+String.valueOf(
> "annemarie"
> ));
> }
>
>
> try {
> if ("false".equals(String.valueOf(
> ""
> ))) {
> _esql_connection.connection.setAutoCommit(false);
> } else {
> _esql_connection.connection.setAutoCommit(true);
> }
> } catch (Exception _esql_exception_N2c97cc) {
> throw new RuntimeException("Error setting connection
> autocommit");
> }
>
>
>
>
>
> if (_esql_query != null) {
> _esql_queries.push(_esql_query);
> }
> _esql_query = new EsqlQuery();
> _esql_query.query = String.valueOf(
> ""
>
> + " select name as reg_name,
> deviceID as
> reg_deviceID, description as reg_description,
> startTime as reg_startTime, endTime as
> reg_endTime from
> user, terminal, deviceType, registration where
> terminal.userID = "
>
> +
> XSPRequestLibrary.getParameter(
> request,
> String.valueOf("userID"),
> null
> )
>
> + " and user.userID = terminal.userID
> and deviceType.deviceType = terminal.deviceType and
> registration.terminalID = terminal.terminalID; "
> );
> try {
> _esql_query.max_rows = Integer.parseInt(String.valueOf(
> ""
> ));
> } catch (NumberFormatException e) {}
> try {
> _esql_query.skip_rows = Integer.parseInt(String.valueOf(
> ""
> ));
> } catch (NumberFormatException e) {}
> try {
>
> _esql_query.statement =
> _esql_connection.connection.createStatement();
> _esql_query.results =
> _esql_query.statement.execute(_esql_query.query);
>
>
> if (_esql_query.results) {
> _esql_query.resultset = _esql_query.statement.getResultSet();
> _esql_query.resultset_metadata =
> _esql_query.resultset.getMetaData();
> _esql_query.position = 0;
> if (_esql_query.skip_rows > 0) {
> while (_esql_query.resultset.next()) {
> _esql_query.position++;
> if (_esql_query.position == _esql_query.skip_rows) {
> break;
> }
> }
> }
>
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("header");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n These are your
> registrations,\n
> ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
>
> while (_esql_query.resultset.next()) {
>
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("username");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspCurrentNode.appendChild(
> xspExpr(_esql_query.resultset.getString("reg_name")
> , document)
> );
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("registration");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("deviceID");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspCurrentNode.appendChild(
> xspExpr(_esql_query.resultset.getString("reg_deviceID")
> , document)
> );
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("description");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspCurrentNode.appendChild(
> xspExpr(_esql_query.resultset.getString("reg_description")
> , document)
> );
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("startTime");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspCurrentNode.appendChild(
> xspExpr(_esql_query.resultset.getString("reg_startTime")
> , document)
> );
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("endTime");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
> xspCurrentNode.appendChild(
> xspExpr(_esql_query.resultset.getString("reg_endTime")
> , document)
> );
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
>
> _esql_query.position++;
> if (_esql_query.max_rows != -1 && _esql_query.position -
> _esql_query.skip_rows == _esql_query.max_rows-1) {
> break;
> }
> }
>
>
> _esql_query.resultset.close();
> } else {
> _esql_query.position = _esql_query.statement.getUpdateCount();
> if (_esql_query.position >= 0) {
>
> }
> }
> _esql_query.statement.close();
> } catch (SQLException _esql_exception_N32fb1e) {
> try {
>
> if (!_esql_connection.connection.getAutoCommit()) {
> _esql_connection.connection.rollback();
> }
>
>
> } catch (Exception _esql_exception_N32fb1e_2) {}
> }
> if (_esql_queries.empty()) {
> _esql_query = null;
> } else {
> _esql_query = (EsqlQuery)_esql_queries.pop();
> }
>
>
> } finally {
> try {
> if(!_esql_connection.connection.getAutoCommit()) {
> _esql_connection.connection.commit();
> }
>
>
> _esql_pool.releaseConnection(_esql_connection.db_connection);
>
>
> if (_esql_connections.empty()) {
> _esql_connection = null;
> } else {
> _esql_connection = (EsqlConnection)_esql_connections.pop();
> }
> } catch (Exception _esql_exception_N2c97cc) {}
> }
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n \n \n\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("br");
> xspParentNode.appendChild(xspCurrentNode);
>
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n\n ")
> );
>
> xspParentNode = xspCurrentNode;
> xspNodeStack.push(xspParentNode);
> xspCurrentNode =
> document.createElement("signature");
> xspParentNode.appendChild(xspCurrentNode);
>
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n Made by:
> [EMAIL PROTECTED]\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n ")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> xspCurrentNode.appendChild(
> document.createTextNode("\n")
> );
>
>
> ((Element) xspCurrentNode).normalize();
> xspCurrentNode = (Node) xspNodeStack.pop();
>
> }
> }
>
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
>
> To unsubscribe, e-mail: <[EMAIL PROTECTED]>
> For additional commands, e-mail: <[EMAIL PROTECTED]>
>
=======================================================================
Information in this email and any attachments are confidential, and may
not be copied or used by anyone other than the addressee, nor disclosed
to any third party without our permission. There is no intention to
create any legally binding contract or other commitment through the use
of this email.
Experian Limited (registration number 653331).
Registered office: Talbot House, Talbot Street, Nottingham NG1 5HF
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail: <[EMAIL PROTECTED]>
