cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marty McClelland" <mmcclell...@eduprise.com>
Subject esql nested query not working - 1.8.2
Date Mon, 17 Sep 2001 15:54:43 GMT
I would like to execute a nested query.  When I try to reference a parameter from the ancestor
query I get a java.lang.RuntimeException: Error setting parameter on statement : Select title
from Main where Main.rid='?'

I would greatly appreciate any suggestions.  I have tried to emulate the example esql - but
I can not get it to pick up the parameter.  If I hardcode the value - the nested query works.

In searching the list - I found a similar posting in January for this error - but no suggestion/solution
was posted.  

I am running tomcat/cocoon 1.8.2 on Windows 98 and pointing to an access database.  
 
marty

I am attaching the related code below:
<?xml version="1.0"?>

<?cocoon-process type="xsp"?>

<!--

<?cocoon-process type="xslt"?>



<?xml-stylesheet type="text/xsl" href="browseResultsProto.xsl"?>

-->

<xsp:page

xmlns:xsp="http://www.apache.org/1999/XSP/Core"

xmlns:esql="http://apache.org/cocoon/SQL/v2"

>

<page>

<xsp:logic>

String table = request.getParameter("table");

String column = request.getParameter("column");

String value = request.getParameter("value");

</xsp:logic>

<browsevalue><xsp:expr>value</xsp:expr></browsevalue>

<browsetable><xsp:expr>table</xsp:expr></browsetable>

<browsecolumn><xsp:expr>column</xsp:expr></browsecolumn>


<esql:connection>

<esql:driver>sun.jdbc.odbc.JdbcOdbcDriver</esql:driver>

<esql:dburl>jdbc:odbc:imsdb</esql:dburl>

<esql:username></esql:username>

<esql:password></esql:password> 

<esql:execute-query>

<esql:query>Select rid from <xsp:expr>table</xsp:expr> where <xsp:expr>column</xsp:expr>
= '<xsp:expr>value</xsp:expr>' order by rid</esql:query>

<esql:results>

<esql:row-results>

<rid><esql:get-string column="rid"/></rid>

<esql:connection>

<esql:driver>sun.jdbc.odbc.JdbcOdbcDriver</esql:driver>

<esql:dburl>jdbc:odbc:imsdb</esql:dburl>

<esql:username></esql:username>

<esql:password></esql:password> 

<esql:execute-query>

<!-- here is the problem line - if I hardcode the rid this works -->

<esql:query>Select title from Main where Main.rid='<esql:parameter type="string"><esql:get-string
ancestor="1" column="rid"/></esql:parameter>'</esql:query>

<esql:results>

<esql:row-results>

<rtitle><esql:get-string column="title"/></rtitle>

</esql:row-results>

</esql:results>

</esql:execute-query>

</esql:connection>

</esql:row-results>

</esql:results>

</esql:execute-query>

</esql:connection>

</page>

</xsp:page>

package _C_._tomcat._webapps._ilumina;

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 _browseResults 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);


xspParentNode = xspCurrentNode;

xspNodeStack.push(xspParentNode);

xspCurrentNode =

document.createElement("page");

xspParentNode.appendChild(xspCurrentNode);


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")

);


String table = request.getParameter("table");

String column = request.getParameter("column");

String value = request.getParameter("value");



xspCurrentNode.appendChild(

document.createTextNode("\n\n")

);


xspParentNode = xspCurrentNode;

xspNodeStack.push(xspParentNode);

xspCurrentNode =

document.createElement("browsevalue");

xspParentNode.appendChild(xspCurrentNode);


xspCurrentNode.appendChild(

xspExpr(value, document)

);


((Element) xspCurrentNode).normalize();

xspCurrentNode = (Node) xspNodeStack.pop();


xspCurrentNode.appendChild(

document.createTextNode("\n")

);


xspParentNode = xspCurrentNode;

xspNodeStack.push(xspParentNode);

xspCurrentNode =

document.createElement("browsetable");

xspParentNode.appendChild(xspCurrentNode);


xspCurrentNode.appendChild(

xspExpr(table, document)

);


((Element) xspCurrentNode).normalize();

xspCurrentNode = (Node) xspNodeStack.pop();


xspCurrentNode.appendChild(

document.createTextNode("\n \n")

);


xspParentNode = xspCurrentNode;

xspNodeStack.push(xspParentNode);

xspCurrentNode =

document.createElement("browsecolumn");

xspParentNode.appendChild(xspCurrentNode);


xspCurrentNode.appendChild(

xspExpr(column, document)

);


((Element) xspCurrentNode).normalize();

xspCurrentNode = (Node) xspNodeStack.pop();


xspCurrentNode.appendChild(

document.createTextNode("\n \n \n")

);


if (_esql_connection != null) {

_esql_connections.push(_esql_connection);

}

_esql_connection = new EsqlConnection();

try {


try {

Class.forName(String.valueOf(

"sun.jdbc.odbc.JdbcOdbcDriver"

)).newInstance();

} catch (Exception _esql_exception_N4a96a) {

throw new RuntimeException("Error loading driver: "+String.valueOf(

"sun.jdbc.odbc.JdbcOdbcDriver"

));

}


try {


_esql_connection.connection = DriverManager.getConnection(

String.valueOf(

"jdbc:odbc:imsdb"

),

String.valueOf(

""

),

String.valueOf(

""

)

);



} catch (Exception _esql_exception_N4a96a) {

throw new RuntimeException("Error opening connection to dburl: "+String.valueOf(

"jdbc:odbc:imsdb"

));

}



try {

if ("false".equals(String.valueOf(

""

))) {

_esql_connection.connection.setAutoCommit(false);

} else {

_esql_connection.connection.setAutoCommit(true);

}

} catch (Exception _esql_exception_N4a96a) {

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 rid from "


+ table

+ " where "


+ column

+ " = '"


+ value

+ "' order by rid"

);

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;

}

}

}



while (_esql_query.resultset.next()) {



xspParentNode = xspCurrentNode;

xspNodeStack.push(xspParentNode);

xspCurrentNode =

document.createElement("rid");

xspParentNode.appendChild(xspCurrentNode);


xspCurrentNode.appendChild(

xspExpr(_esql_query.resultset.getString("rid")

, document)

);


((Element) xspCurrentNode).normalize();

xspCurrentNode = (Node) xspNodeStack.pop();


if (_esql_connection != null) {

_esql_connections.push(_esql_connection);

}

_esql_connection = new EsqlConnection();

try {


try {

Class.forName(String.valueOf(

"sun.jdbc.odbc.JdbcOdbcDriver"

)).newInstance();

} catch (Exception _esql_exception_N2eef15) {

throw new RuntimeException("Error loading driver: "+String.valueOf(

"sun.jdbc.odbc.JdbcOdbcDriver"

));

}


try {


_esql_connection.connection = DriverManager.getConnection(

String.valueOf(

"jdbc:odbc:imsdb"

),

String.valueOf(

""

),

String.valueOf(

""

)

);



} catch (Exception _esql_exception_N2eef15) {

throw new RuntimeException("Error opening connection to dburl: "+String.valueOf(

"jdbc:odbc:imsdb"

));

}



try {

if ("false".equals(String.valueOf(

""

))) {

_esql_connection.connection.setAutoCommit(false);

} else {

_esql_connection.connection.setAutoCommit(true);

}

} catch (Exception _esql_exception_N2eef15) {

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 title from Main where Main.rid='"


+ "?"

+ "'"

);

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 {


try {

_esql_query.prepared_statement = _esql_connection.connection.prepareStatement(_esql_query.query);

} catch (SQLException _esql_exception_N6ed7a) {

throw new RuntimeException("Error preparing statement: "+_esql_query.query);

}

_esql_query.statement = _esql_query.prepared_statement;


try {

_esql_query.prepared_statement.setString(1,((EsqlQuery)_esql_queries.elementAt(_esql_queries.size()-1)).resultset.getString("rid")

);



} catch (SQLException _esql_exception_N6a3932) {

throw new RuntimeException("Error setting parameter on statement: "+_esql_query.query);

}


try {

_esql_query.results = _esql_query.prepared_statement.execute();

} catch (SQLException _esql_exception_N6ed7a) {

throw new RuntimeException("Error executed prepared statement: "+_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;

}

}

}



while (_esql_query.resultset.next()) {



xspParentNode = xspCurrentNode;

xspNodeStack.push(xspParentNode);

xspCurrentNode =

document.createElement("rtitle");

xspParentNode.appendChild(xspCurrentNode);


xspCurrentNode.appendChild(

xspExpr(_esql_query.resultset.getString("title")

, document)

);


((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_N6ed7a) {

try {


if (!_esql_connection.connection.getAutoCommit()) {

_esql_connection.connection.rollback();

}



} catch (Exception _esql_exception_N6ed7a_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_connection.connection.close();



if (_esql_connections.empty()) {

_esql_connection = null;

} else {

_esql_connection = (EsqlConnection)_esql_connections.pop();

}

} catch (Exception _esql_exception_N2eef15) {}

}





_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_N1079ff) {

try {


if (!_esql_connection.connection.getAutoCommit()) {

_esql_connection.connection.rollback();

}



} catch (Exception _esql_exception_N1079ff_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_connection.connection.close();



if (_esql_connections.empty()) {

_esql_connection = null;

} else {

_esql_connection = (EsqlConnection)_esql_connections.pop();

}

} catch (Exception _esql_exception_N4a96a) {}

}


xspCurrentNode.appendChild(

document.createTextNode("\n\n")

);


((Element) xspCurrentNode).normalize();

xspCurrentNode = (Node) xspNodeStack.pop();


}

}







Mime
View raw message