This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes stored procedures in a loop... what gives? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "stored procedures in a loop... what gives?" Watch "stored procedures in a loop... what gives?" New topic
Author

stored procedures in a loop... what gives?

Red Green
Greenhorn

Joined: Sep 27, 2001
Posts: 15
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 = "jdbcdbc: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;
}
}
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

http://java.sun.com/products/jdbc/faq.html#21
you can only have one statement open per connection when using the jdbc dbc bridge. When you create your second statement it either destroys your first statement(and the associated resultset) or it just throws a SQLException because your trying to open another statement. Either way, I think this is what is causing your program to bomb.
how to fix:
change to a driver that supports multiple statements per connection or open a separate connection for each statement.
Jamie
[This message has been edited by Jamie Robertson (edited December 06, 2001).]
Red Green
Greenhorn

Joined: Sep 27, 2001
Posts: 15
STOP� I figured it out. I have put the corrections in BOLD for all other newbies
private void createLayers(String Layers) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbcdbc:NamesDSN";
String user = "";
String password = "";
Connection con = DriverManager.getConnection(url, user, password);
CallableStatement stmt = con.prepareCall("{call sp_GetMainName}", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
try{
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
int name_ID = rs.getInt("nameID");
String main_Name = rs.getString("name");

CallableStatement stmt2 = con.prepareCall("{call sp_GetPlanName(?)}", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt2.setInt(1,name_ID);
try{
ResultSet rs2 = stmt2.executeQuery();
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 ez){
System.out.println("Problem with execution of RS2 query");
ez.printStackTrace();
return;
}
}
rs.close();
}
catch(Exception ey){
System.out.println("Problem with execution of RS1 query");
ey.printStackTrace();
return;
}
}
catch (Exception ex) {
System.out.println("Failed to load JDBC/ODBC driver.");
ex.printStackTrace();
return;
}
}
Red Green
Greenhorn

Joined: Sep 27, 2001
Posts: 15
hi jamie,
thanks you so much. i didn't realize that you had made a post to my previous reply... i was over here chugging along trying to figure it all out.
i figured out my mistakes. the callableStatements and calling the stored procedures, and using my resultsets with SPs. i had use resultsets b4 the SPs, but when i put the SPs in everything appeared to go to hell in a hand basket. i can have the SPs in a loop i just have to make sure that my resultset correctly. also i was using this in one place (for example):
String SQLstmt1 = "GetMainName";
CallableStatement stmt = con.prepareCall("SQLstmt1", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
and this in another:
CallableStatement stmt2 = con.prepareCall("{call GetSecondName}", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
apparently java loves consistency. i spent most of my day trying to figure this one out... great learning experience to say the least.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

"Red Green",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp .
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please log in again with a new name which meets the requirements.
Thanks.
Dave
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: stored procedures in a loop... what gives?
 
Similar Threads
Moving Through a result Set on two tables
multiple processes... only want one
Problem in inserting from one table to another
two ResultSet for one connection
Are there any problems using the same statement for multiple queries?