esql nested query not working - 1.8.2

[Marty McClelland]

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(); } }