JDBC: Nesting querys? 1

[matthewking]

Hi,

I have a table holding Categories and SubCategories for a menu system. I want to loop through the Categories, running a seperate query for each to extract the subCats for that cat.

heres what im trying at the moment:

	String query="select CatID, Description, Name, SubCatRef from categories";

	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

	Connection con = DriverManager.getConnection(url, userName, password);
	Statement stmt = con.createStatement();
	ResultSet rs = stmt.executeQuery(query);
			
	while (rs.next())
	{
		%>
			<img src=&quot;arrow.gif&quot;> <a href=&quot;index.jsp?CatID=<%= rs.getInt(&quot;CatID&quot;)%>&quot; title=&quot;<%= rs.getString(&quot;Description&quot;)%>&quot;><%= rs.getString(&quot;Name&quot;)%></a><br>
			<%
				String innerQuery = &quot;select Name from Categories where SubCatRef='&quot; + rs.getInt(&quot;SubCatRef&quot;) + &quot;'&quot;;
				ResultSet Res = stmt.executeQuery(innerQuery);
				
				while (Res.next())
				{
					%> <%= Res.getString(&quot;Name&quot;)%> <br> <%
				}
			%>
		<%
	}
	
	stmt.close();
	con.close();

This is just returning javax.servlet.ServletException: ResultSet is closed. And I cant seem to figure it out.

Any Solutions? Tips?

Thanks

 

[matthewking]

OK, It made a mess of the code. But you can see what im basically trying to do from the rs.next() loops.

 

[Hattusas]

No no no.You are using stmt.executeQuery("blah blah"
then your ResultSet rs is activated.

BUT you are using
ResultSet Res = stmt.executeQuery(innerQuery);
then your ResultSet crashes. Instead use this:

String query="select CatID, Description, Name, SubCatRef from categories";

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"

Connection con = DriverManager.getConnection(url, userName, password);
Statement stmt1 = con.createStatement();
Statement stmt2 = con.createStatement();
ResultSet rs = stmt1.executeQuery(query);

while (rs.next())
{
%>
<img src=&quot;arrow.gif&quot;> <a href=&quot;index.jsp?CatID=<%= rs.getInt(&quot;CatID&quot%>&quot; title=&quot;<%= rs.getString(&quot;Description&quot%>&quot;><%= rs.getString(&quot;Name&quot%></a><br>
<%
String innerQuery = &quot;select Name from Categories where SubCatRef='&quot; + rs.getInt(&quot;SubCatRef&quot + &quot;'&quot;;
ResultSet Res = stmt2.executeQuery(innerQuery);

while (Res.next())
{
%> <%= Res.getString(&quot;Name&quot%> <br> <%
}
%>
<%
}

stmt1.close();
stmt2.close();
con.close();

Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[matthewking]

Thanks for replying.

I tried it out and it gives me this:

description The server encountered an internal error (Internal Server Error) that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

Any Ideas?

Thanks,
morefire

 

[Hattusas]

It seems your connection does not allow you to open more than one statement.(I don't know why)
You can try this:

Connection con1 = DriverManager.getConnection(url, userName, password);

Connection con2 = DriverManager.getConnection(url, userName, password);
Statement stmt1 = con1.createStatement();
Statement stmt2 = con2.createStatement();



.........
.........
con1.close();
con2.close(); Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[matthewking]

Thanks, that did the trick
One more thing if you know:
if I do &quot;select CatID, Name from Categories&quot; then i must use <%=Res.getString(&quot;CatID&quot%> then <%=Res.getString(&quot;Name&quot%>

If I try to print Name before CatID, it says theres no such thing. So basically I have to print in the order that I select, is there a way to turn this off? especially for select * ?

Anyway,

Thanks for all your help, much appreciated.