Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can you use nested sql statements in jsp??

 
Rich Barry
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
HI, I was wondering if you can use nexted sql statements...
example...
<%@ page language="java" contentType="text/html" %>
<%@ page import="java.sql.*" %>
<%@ page import="forum.DataAccess" %>
<%
// Create a new instance of the DataAccess Class
DataAccess d = new DataAccess();
// This call the database connection method from the DataAccess Class
d.ConnectDatabase();
/* This gets the threads from database for the categories */
// This is initilzing the variables
String threadname = null;
int threadid = 0;
// This sets the sql variable to nothing
String sql = "";
String sql1 = "";
// This sets the rs variable to null
ResultSet rs = null;
ResultSet rs1 = null;
// This is doing a SELECT on the thread table based on the category id
sql += "select * from forum_threads where ft_catid = 2";
rs = d.SQLQuery(sql);
/* End of categories */

while (rs.next()){
threadid = rs.getInt("ft_threadid");
threadname = rs.getString("ft_threadname");
sql1 += "select * from forum_messages where fm_catid = 2 and fm_threadid = " + threadid + "";
rs1 = d.SQLQuery(sql1);
}
%>
THis is the error I get.....
javax.servlet.ServletException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
I know what that is.. it is because I am trying to open another result set before the first one is closed... but I need that one open cause I need to loop through the ids and run it against the second query??
Any suggestions...
 
gautam shah
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Rich Barry:
HI, I was wondering if you can use nexted sql statements...
example...
<%@ page language="java" contentType="text/html" %>
<%@ page import="java.sql.*" %>
<%@ page import="forum.DataAccess" %>
<%
// Create a new instance of the DataAccess Class
DataAccess d = new DataAccess();
// This call the database connection method from the DataAccess Class
d.ConnectDatabase();
/* This gets the threads from database for the categories */
// This is initilzing the variables
String threadname = null;
int threadid = 0;
// This sets the sql variable to nothing
String sql = "";
String sql1 = "";
// This sets the rs variable to null
ResultSet rs = null;
ResultSet rs1 = null;
// This is doing a SELECT on the thread table based on the category id
sql += "select * from forum_threads where ft_catid = 2";
rs = d.SQLQuery(sql);
/* End of categories */

while (rs.next()){
threadid = rs.getInt("ft_threadid");
threadname = rs.getString("ft_threadname");
sql1 += "select * from forum_messages where fm_catid = 2 and fm_threadid = " + threadid + "";
rs1 = d.SQLQuery(sql1);
}
%>
THis is the error I get.....
javax.servlet.ServletException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
I know what that is.. it is because I am trying to open another result set before the first one is closed... but I need that one open cause I need to loop through the ids and run it against the second query??
Any suggestions...

i have done this kind of thing lot of time. and never got such exception. i think its the kind of limitations imposed by driver u r using. try by creating two different statement objects and then execute those two query in that. i am not sure that whether it works or not there. but i had done this kind of job a lot of time successfully.
one more thing u can do is just create two different connection with database and do what ever u want with those two.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic