• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

stored procedures in a loop... what gives?

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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;
}
}
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 = "jdbc:odbc: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
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic