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]>
