Without using stored procedures I get the desired results. When I use the first stored procedure the results are fine. When I add an additional stored procedure within the loop it bombs�. Giving me the following message (stackTrace):
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver] Connection is busy with results for another hstmt
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:2533)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:217)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:92)
at NameDemo.CreateLayers(NameDemo.java:203)
at NameDemo.<init>(NameDemo.java:40)
at NameDemo.main (NameDemo.java:257)
HERE IS THE CODE:
import java.sql.*;
import java.io.*;
�..
�..
�..
�..
private void createLayers(
String Layers) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:NamesDSN";
String user = "";
String password = "";
Connection con = DriverManager.getConnection(url, user, password);
String SQLstmt1 = "sp_GetMainName";
CallableStatement stmt = con.prepareCall(SQLstmt1);
//if last two statements replaced with the following� it works
//String SQLstmt1 = �Select MainName from firstDBT;�
//String stmt1 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
try{
//if uncomment the above uncomment this statement and comment the statement following it
//ResultSet rs= stmt.executeQuery(SQLstmt1);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
int name_ID = rs.getInt("nameID");
String main_Name = rs.getString("name");
//after this section has been
//added I am getting the error
//message
//here is the problem� here is the problem� here is the problem� here is the problem� here is the problem�
String SQLstmt2 = "GetSecondName(" + name_ID + ")";
CallableStatement stmt2 = con.prepareCall(SQLstmt2);
ResultSet rs2 = stmt2.executeQuery();
//if last three statements replaced with the following BOLD statements� it will work
//String SQLstmt2 = �Select SecondaryName from secondDBT where nameID = " + name_ID + "order by nameTwo";
//Statement stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
try{
//ResultSet rs2= stmt2.executeQuery(SQLstmt2);
while (rs2.next()){
int nameTwo_ID = rs2.getInt("nameTwoID");
String two_Name = rs2.getString("nameTwo");
int name_Rev = rs2.getInt("revName");
}
rs2.close();
}
catch(Exception ee){
System.out.println("Problem with execution of query");
ee.printStackTrace();
return;
}
//here is the problem� here is the problem� here is the problem� here is the problem� here is the problem�}
rs.close();
}
catch(Exception e){
System.out.println("Problem with execution of query");
e.printStackTrace();
return;
}
}
catch (Exception ex) {
System.out.println("Failed to load JDBC/ODBC driver.");
ex.printStackTrace();
return;
}
}