Execute a SQL query in a loop

Question

I have this scenario. I will trigger a job in the server and as soon as the job is triggered an entry will be made into the job table with Execution_status_code as 1. I need to wait for some time say 5 mins and recheck the Execution_status_code value. As soon as the value is changed to 2, I need to proceed further.

I am using an existing connection for connecting to the database. I need to execute the SQL and if the SQL output is In progress, I need to wait for some time and then again execute the statement. Do this until the SQL output is success, until then keep waiting.

Below is the code I have tried.

Thread t = new Thread();
java.sql.Connection conn_javaComp = (java.sql.Connection)globalMap.get("conn_tNetezzaConnection_1");
java.sql.Statement st = null;
java.sql.ResultSet rs = null;
String check = null;
String dbquery_javaComp = "select case when EXECUTION_STATUS_CODE = 2 then 'Success' when EXECUTION_STATUS_CODE = 1 then 'In progress' else 'Failure' end as EXECUTION_STATUS_CODE from JOB_BKUP_NCR where JOB_TYPE_CODE="+context.JobTypeCode+" and Load_id = (select max(load_id) from JOB_BKUP_NCR where job_type_code="+context.JobTypeCode+") and START_DATETIME = (select max(START_DATETIME) from JOB_BKUP_NCR where job_type_Code="+context.JobTypeCode+")";
try 
    {
        do 
        {
            st = conn_javaComp.createStatement();
            rs = st.executeQuery(dbquery_javaComp);
            if(rs.next()) 
            {
            check = rs.getString(1);
            System.out.println(check);
            if (check.equalsIgnoreCase("In Progress"))
            {
                t.sleep(1000);
                System.out.println("thread executed1");
                System.out.println(dbquery_javaComp);
                System.out.println(check);
            } 
            } 
            else {
            System.out.println(" No data found");
            }
        }while (!"Success".equals(check));

    }
catch (Exception e) {
    e.printStackTrace();
} finally {
    try {
        if( rs != null) 
            rs.close();
        if( st!= null)
            st.close();
    } 
    catch (Exception e1) {
        e1.printStackTrace();
    }
}

The output i am getting is 'In Progress'. The loop is struck at In progress even after i change the value in the database. I am not sure where i am doing wrong. Any suggestions?

TA Nguyen · Accepted Answer · 2014-02-26 05:03:35Z

3

You are creating a new statement and a new resultset inside the loop, and so, they should be close inside the loop. I am thinking that your connection got corrupted with multiple statements and resultset without closing them. Please try to close them and see if that work.

answered Feb 26, 2014 at 5:03

TA Nguyen

4731 gold badge3 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scary Wombat · Accepted Answer · 2014-02-26 05:07:48Z

1

The data that you are seeing will be cached.

Try closing and re-opening your DB connection. This may not even be good enough if you are using DB pooling.

answered Feb 26, 2014 at 5:07

Scary Wombat

44.9k6 gold badges38 silver badges67 bronze badges

Comments

gerrytan · Accepted Answer · 2014-02-26 05:08:25Z

0

There are many things I can foresee going wrong with your code. For once most DBMS will either lock the rows until you commit / close the connection or give you a snapshot of the data instead, hence you don't see the updated value or the transaction that supposed to update it wouldn't go through. Try comitting or close/reopen the transaction per loop iteration.

I would also doubt if this is a good code design as you are doing "polling". Consider if you can find other method of getting notified of the event.

answered Feb 26, 2014 at 5:08

gerrytan

41.2k12 gold badges88 silver badges99 bronze badges

Comments

Rishi Dwivedi · Accepted Answer · 2014-02-26 05:13:46Z

0

try 
{
 //declare here your statement and resultset
  st = conn_javaComp.createStatement();
  rs = st.executeQuery(dbquery_javaComp);
    do 
    {
        if(rs.next()) 
        {
        check = rs.getString(1);
        System.out.println(check);
        if (check.equalsIgnoreCase("In Progress"))
        {
            t.sleep(1000);
            System.out.println("thread executed1");
            System.out.println(dbquery_javaComp);
            System.out.println(check);
        } 
        } 
        else {
        System.out.println(" No data found");
        }
    }while (!"Success".equals(check));

answered Feb 26, 2014 at 5:13

Rishi Dwivedi

9385 silver badges19 bronze badges

1 Comment

Mohit Bagadia Over a year ago

I need to execute the sql in loop and fetch the status.

Collectives™ on Stack Overflow

Execute a SQL query in a loop

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related