aspose file tools*
The moose likes JDBC and the fly likes Nested resultsets with 1 connection, possible? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Nested resultsets with 1 connection, possible?" Watch "Nested resultsets with 1 connection, possible?" New topic
Author

Nested resultsets with 1 connection, possible?

Sandy Goh
Greenhorn

Joined: Aug 25, 2003
Posts: 6
I have nested resultsets but I can only use 1 connection to the database.
Is it possible?
Thanks ppl!
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%
String url = "jdbc dbc:company";
Connection conn=null;
String classPath = "sun.jdbc.odbc.JdbcOdbcDriver";
try{
Class.forName(classPath);
conn = DriverManager.getConnection(url,username,password);
}catch(Exception exc){
out.println(exc.toString());
}
Statement stm=null;
Statement stm1=null;
stm1=conn.createStatement();
stm=conn.createStatement();
ResultSet rst=null;
ResultSet rst1=null;

try{
rst=stm.executeQuery("Select DISTINCT comagent from company");

%>

<% while(rst.next()){ %>

<%String agent=rst.getString("comagent");%>
<%rst1=stm1.executeQuery("Select * from company where comagent='"+agent+"' ORDER BY comlead DESC");
rst1.next();%>
<%=rst1.getString("comname")%>
<%=rst1.getString("comlead")%>
<%
}%>

<%
%>
<%}catch(Exception exc){
out.println(exc.toString());
}
stm1.close();
stm.close();
conn.close();

%>
Dana Hanna
Ranch Hand

Joined: Feb 28, 2003
Posts: 227
Yes, but I've found problems with some databases...
Sandy Goh
Greenhorn

Joined: Aug 25, 2003
Posts: 6
I don't get what you mean?
Dana Hanna
Ranch Hand

Joined: Feb 28, 2003
Posts: 227
This did not work for us when using DB2 UDB 6.1 fixpack 3. Upgrading to fixpack 9 fixed it.
I've never had any other problems though. You should be fine, and you are doing everything correctly.
 
wood burning stoves
 
subject: Nested resultsets with 1 connection, possible?