problrm while using while loop
I am facing problem.I am using jdbc odbc bridge and sql server as database.
what i am trying to do is as follows:
stmt=dbCon.createStatement();
rs=stmt.executeQuery(Select * from A.....);
while(rs.next())
{rs1=stmt.executeQuery(Select * from B.....);\\......int ff=stmt.executeUpdate("insert into another table....);
\\.....
while(rs1.next())
{ rs2=stmt.executeQuery(Select * from C.....);
\\......
\\.....
while(rs2.next())
{ rs3=stmt.executeQuery(Select * from D.....);
\\......
\\.....
}
}
}
The problem is that this nesting is not working properly and only executes once and not for whole loop.This was working fine on Ms Access but not on sql-server7 when I shifted my database to it.
thanks in advance
[842 byte] By [
boschd] at [2007-9-26 3:48:24]
Hi,
Problem is that a statement is only supposed to process one query at a time.
(i) Some JDBC drivers may let you get away with it, but in general it is much better to use a separate statement for each query,
For Example:
Statement stmt1=dbCon.createStatement();
rs1=stmt1.executeQuery(Select * from A.....);
while(rs1.next()){
Statement stmt2=dbCon.createStatement(); rs2=stmt2.executeQuery(Select * from B.....);
/ while(rs2.next()) {
Statement stmt3=dbCon.createStatement();rs3=stmt3.executeQuery(Select * from C.....);
while(rs3.next()){
Statement stmt4=dbCon.createStatement();rs4=stmt4.executeQuery(Select * from D.....);stmt4.close();
}stmt3.close();
} stmt2.close();
}stmt1.close();
(ii)Close the resultset using rs.close() at the end of the loop.
Hope this will help you.
Anil.
Developer Technical Support
Sun Microsystems, India.
http://www.sun.com/developers/support
Hi there!
I think the previous answer should have helped you but
I think you can do the same process by an appropiated SQL statement: "SELECT A.* , B.* , C.* FROM A,B,C WHERE ... ".
By the way I have a problem connecting my access db.
I'm connecting throw an odbc driver and I make my program fail with a big stack error.
(The problem is well identified, I'm sure it comes from the odbc connection).
You said you had no problem with you access connection...
How did you do?
What driver did you use?
What syntax?
I did the following:
<%@ page import="java.sql.*,javax.sql.*,allaire.taglib.*" %>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ;
String url = "jdbc:odbc:ademeregion" ;
Connection con = DriverManager.getConnection( url , "" , "" ) ;
Statement stmt = con.createStatement();
ResultSet itom = stmt.executeQuery( "SELECT * FROM ITOM" ) ;
ResultSetMetaData itomMetaData = itom.getMetaData( ) ;
%>
Thanks
Sylvain
>Some JDBC drivers may let you get away with it...
They would be non-compliant drivers then.
A single statement can only have one active result set.
Some drivers allow you to have more than one active statement per connection. However, they are allowed to block other active statements until one completes.
