Dear All,
I got an application which have many sql select statement and some are done withing the outler select statement. The problem is that sometimes I get this error com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.? What could be the cause is it because I am using the statements wrongly?
It is difficult (impossible) to help you without a bit more detail.
You are obviously connected to a MySQL database through Java.
Can you post ALL of the error message(s) that appear?
Can you provide some of the code; perhaps the SQL statements you suspect are causing trouble.
Does this error only occur when you are performing specific operations? What are those operations?
Was the application working well for a long time and now, suddenly, has begun spewing error messages?
Is this on a website for which you can provide a link?
Dear Parkin,
No it does not occur everytime that is the problem. When I reset my server it works back to normal. Below is my full code but I remove the actual statements it self if not the code becomes too long. Based on the error is referring to line 312 which in my simplified version is referring to ResultSet rs1 = stmt.executeQuery(selectQuery1);
try
{
dbconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/***?"+"user=******&password=*****");
stmt = dbconn.createStatement();
while (true)
{
try
{
String selectQuery1 = //Select Statement
ResultSet rs1 = stmt.executeQuery(selectQuery1);
if(rs1.next())
{
associateID = rs1.getInt("associateID");
//
}
int geoFenceIDFound=0;
if(associateID>0)
{
geoFenceIDFound=0;
String selectQuery3 = //select statement
ResultSet rs3 = stmt.executeQuery(selectQuery3);
stmt1 = dbconn.createStatement();
stmt2 = dbconn.createStatement();
while(rs3.next())
{
geoFenceID = rs3.getInt("geoFenceID");
if(geoFenceIDFound>0)
{
break;
}
else
{
if(geoFenceType.equals("POINT"))
{
String selectQuery4= //select statement
ResultSet rs4 = stmt1.executeQuery(selectQuery4);
if(rs4.next())
{
geoFenceDistance = rs4.getDouble("distance");
if(geoFenceDistance*1000<=geoFenceRadius)
{
geoFenceIDFound=geoFenceID;
if(lastGeoFenceID==geoFenceID) {
{
break;
}
else
{
if(previousGeoFenceID==geoFenceID &&previousTimeDifferenceInt<0) //check the funny problem double entry back into the geo fence
{
lastGeoFenceID=geoFenceID;
break;
}
else
{
String updateQuery2 = //update statement
count = stmt.executeUpdate(updateQuery2);
if(lastGeoFenceID>0)
{
String insertQuery2 = //insert statement
count = stmt.executeUpdate(insertQuery2);
String insertQuery3 =//insert statement;
count = stmt.executeUpdate(insertQuery3);
}
else
{
String insertQuery3 =//insert statement;
count = stmt.executeUpdate(insertQuery3);
}
break;
}
}
}
}
}
else
{
String selectQuery4 =//select statement
ResultSet rs4 = stmt1.executeQuery(selectQuery4);
if(rs4.next())
{
//rs4.getInt(("geoFenceStatus")
if(rs4.getInt("geoFenceStatus")==1)
{
geoFenceIDFound=geoFenceID;
if(lastGeoFenceID==geoFenceID)
{
break;
}
else
{
if(previousGeoFenceID==geoFenceID &&previousTimeDifferenceInt<0) //check the funny problem double entry back into the geo fence
{
lastGeoFenceID=geoFenceID;
break;
}
else
{
String updateQuery2 = //update statement
count = stmt.executeUpdate(updateQuery2);
if(lastGeoFenceID>0)
{
String insertQuery2 =//insert statement;
count = stmt.executeUpdate(insertQuery2);
String insertQuery3 =//insert statement;
count = stmt.executeUpdate(insertQuery3);
}
else
{
String insertQuery3 =//insert statement;
count = stmt.executeUpdate(insertQuery3);
}
break;
}
}
}
}
}
}
}//while next compare all the geoFences.
if(geoFenceIDFound==0)
{
geoFenceID=0;
if(lastGeoFenceID>0)
{
String updateQuery2 = //update statement
count = stmt.executeUpdate(updateQuery2);
String insertQuery3 =//insert statement
count = stmt.executeUpdate(insertQuery3);
}
}
if(!eventAlertType.equals(""))
{
if(evenAlertCode.substring(0,1).equals("B"))
{
String selectQuery4 = //select statement
ResultSet rs4 = stmt.executeQuery(selectQuery4);
while(rs4.next())
{
slaveEventUnitID = rs4.getInt("slaveID");
}
String insertQuery4 =//insert statement;
count = stmt.executeUpdate(insertQuery4);
}
else
{
String insertQuery5 =//insert statement;
count = stmt.executeUpdate(insertQuery5);
if(evenAlertCode.equals("602"))
{
String updateQuery3 = //update statement;
count = stmt.executeUpdate(updateQuery3);
}
if(evenAlertCode.equals("612"))
{
String updateQuery4 = //update statement;
count = stmt.executeUpdate(updateQuery4);
String updateQuery5 = //update statement;
count = stmt.executeUpdate(updateQuery5);
String updateQuery6 = //update statement;
count = stmt.executeUpdate(updateQuery6);
String updateQuery7 = //update statement;
count = stmt.executeUpdate(updateQuery7);
String updateQuery8 = //update statement;
count = stmt.executeUpdate(updateQuery8);
}
}
}
}
String selectQuery2 = //select statement;
String updateQuery = "";
if(rs2.next())
{
updateQuery = //update statement
}
else
{
updateQuery = //update statement
}
count = stmt.executeUpdate(updateQuery);
String insertQuery = //insert statement;
count = stmt.executeUpdate(insertQuery);
if(addExist!=-1)
{
for(int iSlave=1; iSlave<slave.length ; iSlave++)
{
String insertQuery2 = //insert statement
count = stmt.executeUpdate(insertQuery2);
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
}//while true
}
catch (SQLException ex)
{
System.out.println("MyError:Error SQL Exception : "+ex.toString());
ex.printStackTrace(System.out);
}
finally
{
try
{
if ( stmt != null )
{
stmt.close();
}
else
{
System.out.println("MyError:stmt is null in finally close");
}
}
catch(SQLException ex)
{
System.out.println("MyError:SQLException has been caught for stmt close");
ex.printStackTrace(System.out);
}
try
{
if ( dbconn != null )
{
dbconn.close();
}
else
{
//logger.log(Level.SEVERE, "MyError:dbconn is null in finally close", "");
System.out.println("MyError:dbconn is null in finally close");
}
}
catch(SQLException ex)
{
System.out.println("MyError:SQLException has been caught for dbconn close");
ex.printStackTrace(System.out);
}
}//finally
Below is the full error.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
Service commServer|11-08-29 21:20:11| at sun.reflect.GeneratedConstructorAccessor1.newInstance(Unknown Source)
Service commServer|11-08-29 21:20:11| at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
Service commServer|11-08-29 21:20:11| at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.Util.getInstance(Util.java:382)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:402)
Service commServer|11-08-29 21:20:11| at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1339)
Service commServer|11-08-29 21:20:11| at commServer$DatabaseProcessor.run(commServer.java:312)
Service commServer|11-08-29 21:20:11| at java.lang.Thread.run(Thread.java:619)
I have tried to copy directly from java editor with all the tab and spacing nicely done but over here all is removed.
I have not worked with Java for quite a while (since Swing was introduced; the Stone Age) but my instinct, looking at your code, is to suspect the “ResultSet” assignment (constructor) inside your WHILE may cause the error you see. Depending upon scoping rules, your code is trying to REDEFINE that variable.
Put all of the ‘rsx’ assignment statements outside the while loop and try it again.
With all this information it will be much easier for someone with the right experience to help you.
Dear Parkin,
I am not clear with what you mean by Put all of the ‘rsx’ assignment statements outside the while loop and try it again? What is rsx?