It's not a secret anymore!*
The moose likes JSP and the fly likes Can you use nested sql statements in jsp?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "Can you use nested sql statements in jsp?? " Watch "Can you use nested sql statements in jsp?? " New topic
Author

Can you use nested sql statements in jsp??

Rich Barry
Greenhorn

Joined: Jan 28, 2002
Posts: 19
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

Joined: Oct 29, 2000
Posts: 72
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can you use nested sql statements in jsp??
 
Similar Threads
Trouble setting an integer to a session ID
Sequencial ID by Date
Has anyone seen this error before??
Help with JSP Page
Having trouble with SELECT statement is JSP