preparedStatement.executeUpdate() not inserting values

Question

I am working in JBehave non-Maven framework and I am running an insert statement which is inside a text file which I am passing to my method as qry. The insert statement is:

Insert into croutreach.ACME_OUTREACH_EMT_STG 
(SEQ,EID,EVENT_TYPE_HINT,LOADER_TYPE,CLIENT_ID_LIST,ACCT_NUM_LIST,PROCS_DT)
values (49,<EID>,'new','CI',null,null,'02-SEP-13 06.55.41.952000000 PM')

The code below is executing and printing the below statement

Executing to run insert query

But after this it is not proceeding to next step and that is why the connection is not getting closed. It is getting stuck as preparedstatement.executeupdate().

private Connection dbConnection = null;
public boolean runInsertQuery(String qry) {
int result;
System.out.println("Preparing to run insert query");
try {
    PreparedStatement preparedStatement =dbConnection.prepareStatement(qry);
    System.out.println("Execuing to run insert query");
    result = preparedStatement.executeUpdate();
    System.out.println("Completed to run insert query");
    return result == 1;
    } catch (SQLException e) {
        LOG.fatal("Error in insertion into table  :", e);
        return false;
    } finally {
        this.disconnectDb();
    }
}

My output is coming as this :-

Method public org.jbehave.core.configuration.Configuration com.TEA.framework.Functional.OnlineCoachingStory.configuration() has a @Test annotation but also a return value: ignoring it. Use <suite allow-return-values="true"> to fix this
Method public org.jbehave.core.steps.InjectableStepsFactory com.TEA.framework.Functional.OnlineCoachingStory.stepsFactory() has a @Test annotation but also a return value: ignoring it. Use <suite allow-return-values="true"> to fix this  
[TestNG] Running:  
  C:\Users\M41974\AppData\Local\Temp\testng-eclipse-1062921629\testng-customsuite.xml

Processing system properties {}  
Using controls EmbedderControls[batch=false,skip=false,generateViewAfterStories=true,ignoreFailureInStories=false,ignoreFailureInView=false,verboseFailures=false,verboseFiltering=false,storyTimeoutInSecs=900000000,threads=1]

(BeforeStories)

Running story com/TEA/framework/Story/Copy of OnlineCoaching.story  

(com/TEA/framework/Story/Copy of OnlineCoaching.story)  
Scenario: Setup the Environment  
2015-11-03 04:46:49,425 FATAL pool-1-thread-1- ACME.SAT.Server.Path not found in the Config.properties file  
2015-11-03 04:46:49,440 INFO pool-1-thread-1- Host:- lsh1042a.sys.cigna.com  Username:- ank_1   password:- abc_123 path:- null  
Given Set the environment to SAT  
2015-11-03 04:46:49,768 INFO pool-1-thread-1- DB Connection successful  
Insert into croutreach.ACME_OUTREACH_EMT_STG (SEQ,EID,EVENT_TYPE_HINT,LOADER_TYPE,CLIENT_ID_LIST,ACCT_NUM_LIST,PROCS_DT) values (49,'1234567890','new','CI',null,null,'02-SEP-13 06.55.41.952000000 PM')  
Preparing to run insert query  
Executing to run insert query  
|

Previously I had kept my SQL developer window open and when tried after closing that and again ran the same script it shows the fatal error asORA-00001: unique constraint violated.

I am facing 3 different issues:

1. When SQL Developer was open, running the script got stuck at the insert.

2. While that was stuck I closed the SQL Developer window session and after that output is as below:

   2015-11-03 05:01:35,061 INFO main- DB Connection successful
   Insert into croutreach.ACME_OUTREACH_EMT_STG (SEQ,EID,EVENT_TYPE_HINT,LOADER_TYPE,CLIENT_ID_LIST,ACCT_NUM_LIST,PROCS_DT) values (49,'02123456789','new','CI',null,null,'02-SEP-13 06.55.41.952000000 PM')
   Preparing to run insert query
   Executing to run insert query
   Completed to run insert query
   2015-11-03 05:02:11,128 INFO main- DB Disconnected!!
   Insert complete
   

3. After closing the SQL Developer window, running the script again completed but with fatal error as below:

   2015-11-03 05:13:08,177 INFO main- DB Connection successful
   Insert into croutreach.ACME_OUTREACH_EMT_STG (SEQ,EID,EVENT_TYPE_HINT,LOADER_TYPE,CLIENT_ID_LIST,ACCT_NUM_LIST,PROCS_DT) values (49,'02123456789','new','CI',null,null,'02-SEP-13 06.55.41.952000000 PM')
   Preparing to run insert query
   Executing to run insert query
   2015-11-03 05:13:08,319 FATAL main- Error in insertion into table  :
   java.sql.SQLException: ORA-00001: unique constraint (CROUTREACH.ACME_OUTREACH_EMT_STG_PK) violated
   
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
       at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
       at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
       at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
       at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3400)
       at com.TEA.framework.Technology.DatabaseClass.runInsertQuery(DatabaseClass.java:169)
       at com.TEA.framework.Business.AcmeDB.insertACME_OUTREACH_EMT_STG(AcmeDB.java:186)
       at com.TEA.framework.Technology.MainClass.DBinsertTest(MainClass.java:123)
       at com.TEA.framework.Technology.MainClass.main(MainClass.java:24)
   2015-11-03 05:13:08,350 INFO main- DB Disconnected!!
   2015-11-03 05:13:08,365 INFO main- DB Disconnected!!
   

Answer 1

You're just seeing one session block another. When you do the insert in SQL Developer and leave the session running without committing or rolling back you have a lock on the newly-inserted row and a (sort of pending) index entry.

If you run your program and do the same insert again then that is in a different session. It cannot see the data you inserted through SQL Developer, but it can see the lock, and it waits for that lock to be cleared. While its blocked it just sits there waiting, which you're interpreting as it being stuck.

If you roll back the SQL Developer insert then the lock is cleared and your program's blocked insert then continues, successfully. The new record is inserted, and will either be committed or rolled back depending on what your code does next, or the connection's autocommit setting.

It looks like it commits, since running it again tries to repeat the insert, and now it does see the existing committed data - hence the ORA-00001 error, as you're trying to create a second entry with the same primary key value. You would have seen the same thing if you had committed the SQL Developer session instead of rolling back.

If you want to run your program a second time you will need to delete the row (and commit that change!) before trying the insert again.