Issue:
I have some code that connects to a database, checks to see if a record is found based on a criteria, if a record is found, return 3 and produce a message for the user to say there already exists a record with these details. If a record does not already exist, then execute an insert query to create the record.
The issue is that the select query, when executed for the second time in short succession, seems to hang when the result is a record being found (return 3, produce the message to the user) - as in it does not progress through the code, no errors are reported and the GUI on the program is unresponsive (unable to click on any other object and fails to respond to closing the window). Netbeans still runs fine and I am able to stop the running of the program through that.
Tests:
A) If I run another query on the same database after my first successful execution, but on a different table, the code executes correctly, but going back to try and execute the original query again causes it to hang.
B) Executing the code and returning no results does not cause it to hang. (see code below)
C) Had a scan of other similar questions on SE but can't seem to find any solutions to my problem.
Resources used: ucanaccess-2.0.9.3, Netbeans IDE 8.1, JDK 1.8 (Compiled to 1.6 due to compatibility issues with older versions in the company), MS Access Database over a company network
Code:
public int insertAddPeriod(String strProjectNumber, String strYear, String strPeriod){
System.out.println("Checking to see if record already exists: " + strProjectNumber + ", Year: " + strYear + ", Period: " + strPeriod);
String strSQLString = null;
try{
strSQLString = "SELECT Program_No, Year, Period FROM tblCost WHERE Program_No = ? AND Year = ? AND Period = ?";
//SETTING PREPARED STATEMENT
PreparedStatement preStatement = con.prepareStatement(strSQLString, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
//SETTING FIRST CONDITION OF PREPARED STATEMENT IE ?
preStatement.setString(1, strProjectNumber);
preStatement.setString(2, strYear);
preStatement.setString(3, strPeriod);
ResultSet rs = preStatement.executeQuery();
if(rs.next()){
//CLOSES CONNECTIONS
rs.close();
preStatement.close();
System.out.println("Check Complete; Period Already Exists");
return 3;
}
else{
System.out.println("Check complete; No Previous Period");
System.out.println("Inserting record: " + strProjectNumber + ", Year: " + strYear + ", Period: " + strPeriod);
strSQLString = null;
try{
strSQLString = "INSERT INTO tblCost (Program_No, Year, Period) VALUES (?, ?, ?) ";
//SETTING PREPARED STATEMENT
PreparedStatement preStatement1 = con.prepareStatement(strSQLString, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
//SETTING FIRST CONDITION OF PREPARED STATEMENT IE ?
preStatement1.setString(1, strProjectNumber);
preStatement1.setString(2, strYear);
preStatement1.setString(3, strPeriod);
//EXECUTE QUERY
preStatement1.executeUpdate();
//CLOSES CONNECTIONS
preStatement1.close();
System.out.println("Insert Complete");
return 1;
}catch(Exception ex){
strEXMessage=ex.getMessage();
System.out.println(strEXMessage);
return 2;
}
}
}catch(Exception ex){
strEXMessage=ex.getMessage();
System.out.println(strEXMessage);
System.out.println("Check error; Unable to check for previous period");
return 2;
}
}
private void AddPeriod(){
//Create Connection
Database db = new Database();
int r = 0;
try {
r = db.CreateConnection();
} catch (SQLException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
//CONNECTION COMPLETE
if(r==1){
int q = 0;
q = db.insertAddPeriod((String)cbProjectNumber.getSelectedItem(),(String)cbCostYear.getSelectedItem(),(String)cbCostPeriod.getSelectedItem());
if(q==1){
System.out.println("Record");
JOptionPane.showMessageDialog(null,"Record added for the period: " + (String)cbCostPeriod.getSelectedItem(),"Complete",JOptionPane.WARNING_MESSAGE);
}
if(q==2){
System.out.println("Error");
JOptionPane.showMessageDialog(null,"There was an error processing the insert query","Error",JOptionPane.WARNING_MESSAGE);
}
if(q==3){
System.out.println("Missing");
JOptionPane.showMessageDialog(null,"Record already exists for the selected period","Error",JOptionPane.WARNING_MESSAGE);
}
}
//CONNECTION ERROR
if(r==2){
System.out.println("Error");
JOptionPane.showMessageDialog(null,"No connection made","Error",JOptionPane.WARNING_MESSAGE);
}
//CONNECTION RETURNS EMPTY
if(r==3){
System.out.println("No Records");
JOptionPane.showMessageDialog(null,"No records found in table","Missing Records",JOptionPane.WARNING_MESSAGE);
}
}
public int CreateConnection() throws SQLException, ClassNotFoundException{
try{
con = DriverManager.getConnection(strDBCon,strDBUser,strDBPass);
System.out.println("Connection Passed");
return 1;
}catch(SQLException ex){
strEXMessage=ex.getMessage();
System.out.println("Connection Failed: " + ex);
return 2;
}
}
public int CloseConnection(){
try{
con.close();
System.out.println("Connection Closed by User");
return 1;
}catch(SQLException ex){
strEXMessage=ex.getMessage();
return 2;
}
}
Please ignore the untidiness (unless that is the cause), cleaning it up is a WIP at the moment.
Usually a non responsive program in such a case is caused by a row lock of the database, due to an insert, update, delete which has not been commited.
As I can see in your code, you do not commit the prepared statement. Do you have autocommit set to true ? If not, try it with con.commit() after executing the update and the second call of the method should work fine.