SELECT *
FROM batchload
WHERE (LOAD_STAT = 'N' OR ERROR_MSG LIKE '%File not found%') AND RETRY_CTR < 3;


SELECT * FROM BATCHLOAD WHERE (LOAD_STAT ='N' OR ERROR_MSG LIKE '%File not found%') AND RETRY_CTR < 3;



// method for updateBatchLoad


public void updateBatchLoad(String batchId, int tranSl, String loadStatus, Connection conn, UserInfoBean userInfo) throws ITMException {
    PreparedStatement psUpdateBl = null;
    boolean isLocalConn = false;

    try {
        if (conn == null) {
            ConnDriver connDriver = new ConnDriver();
            conn = connDriver.getConnectDB(userInfo.getTransDB());
            connDriver = null;
            isLocalConn = true;
        }

        // Check if loadStatus is 'E'
        if ("E".equals(loadStatus)) {
            // If 'E', update RETRY_CTR
            updateRetryCounter(conn, batchId, tranSl);
        }

        // Continue with the existing update logic
        String update = "UPDATE BATCHLOAD SET LOAD_STAT = ?, CHG_DATE = ? WHERE BATCH_ID = ? AND TRAN_SL = ?";
        psUpdateBl = conn.prepareStatement(update);

        psUpdateBl.setString(1, loadStatus);
        psUpdateBl.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));
        psUpdateBl.setString(3, batchId);
        psUpdateBl.setInt(4, tranSl);

        int updateCnt = psUpdateBl.executeUpdate();

        psUpdateBl.close();
        psUpdateBl = null;
        BaseLogger.log("3", null, null, "BATCHLOAD : updateCnt :: [" + updateCnt + "]");

        conn.commit();
    } catch (SQLException ex) {
        BaseLogger.log("0", null, null, "SQLException : updateBatchLoad :: [" + ex.getMessage() + "]");
        ex.printStackTrace();
        throw new ITMException(ex);
    } catch (Exception ex) {
        BaseLogger.log("0", null, null, "Exception : updateBatchLoad :: [" + ex.getMessage() + "]");
        ex.printStackTrace();
        throw new ITMException(ex);
    } finally {
        try {
            if (psUpdateBl != null) {
                psUpdateBl.close();
                psUpdateBl = null;
            }
            if (conn != null && isLocalConn) {
                conn.close();
                conn = null;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
        }
    }
}

// Helper method to update RETRY_CTR

public void updateBatchLoad(String batchId, int tranSl, String loadStatus, Connection conn, UserInfoBean userInfo) throws ITMException {
    PreparedStatement psUpdateBl = null;
    boolean isLocalConn = false;

    try {
        if (conn == null) {
            ConnDriver connDriver = new ConnDriver();
            conn = connDriver.getConnectDB(userInfo.getTransDB());
            connDriver = null;
            isLocalConn = true;
        }

        String update = "UPDATE BATCHLOAD SET LOAD_STAT = ?, CHG_DATE = ? WHERE BATCH_ID = ? AND TRAN_SL = ?";
        psUpdateBl = conn.prepareStatement(update);

        psUpdateBl.setString(1, loadStatus);
        psUpdateBl.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));
        psUpdateBl.setString(3, batchId);
        psUpdateBl.setInt(4, tranSl);

        int updateCnt = psUpdateBl.executeUpdate();

        psUpdateBl.close();
        psUpdateBl = null;
        BaseLogger.log("3", null, null, "BATCHLOAD : updateCnt :: [" + updateCnt + "]");

        conn.commit();

        // Check if loadStatus is 'E'
        if ("E".equals(loadStatus)) {
            // If 'E', update RETRY_CTR
            updateRetryCounter(conn, batchId, tranSl);
        }
    } catch (SQLException ex) {
        BaseLogger.log("0", null, null, "SQLException : updateBatchLoad :: [" + ex.getMessage() + "]");
        ex.printStackTrace();
        throw new ITMException(ex);
    } catch (Exception ex) {
        BaseLogger.log("0", null, null, "Exception : updateBatchLoad :: [" + ex.getMessage() + "]");
        ex.printStackTrace();
        throw new ITMException(ex);
    } finally {
        try {
            if (conn != null && isLocalConn) {
                conn.close();
                conn = null;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
        }
    }
}

// Helper method to update RETRY_CTR
private void updateRetryCounter(Connection conn, String batchId, int tranSl) throws SQLException {
    PreparedStatement psUpdateRetryCounter = null;
    try {
        String selectRetryCounter = "SELECT RETRY_CTR, LOAD_STAT FROM BATCHLOAD WHERE BATCH_ID = ? AND TRAN_SL = ?";
        String updateRetryCounter = "UPDATE BATCHLOAD SET RETRY_CTR = ? WHERE BATCH_ID = ? AND TRAN_SL = ?";

        // Check if the record exists
        psUpdateRetryCounter = conn.prepareStatement(selectRetryCounter);
        psUpdateRetryCounter.setString(1, batchId);
        psUpdateRetryCounter.setInt(2, tranSl);

        ResultSet resultSet = psUpdateRetryCounter.executeQuery();

        if (resultSet.next()) {
            // Get the current retry counter and load status
            int currentRetryCounter = resultSet.getInt("RETRY_CTR");
            String loadStatus = resultSet.getString("LOAD_STAT");

            if ("E".equals(loadStatus)) {
                // If the load status is 'E', increase the retry counter by 1
                int newRetryCounter = currentRetryCounter + 1;

                // Update the retry counter
                try (PreparedStatement updateStatement = conn.prepareStatement(updateRetryCounter)) {
                    updateStatement.setInt(1, newRetryCounter);
                    updateStatement.setString(2, batchId);
                    updateStatement.setInt(3, tranSl);
                    updateStatement.executeUpdate();
                    BaseLogger.log("3", null, null, "RETRY_CTR updated successfully: " + newRetryCounter);
                }
            }
        } else {
            // If the record does not exist, insert a new record with a retry counter of 1
            try (PreparedStatement insertStatement = conn.prepareStatement(
                    "INSERT INTO BATCHLOAD (BATCH_ID, TRAN_SL, RETRY_CTR, LOAD_STAT) VALUES (?, ?, 1, 'E')")) {
                insertStatement.setString(1, batchId);
                insertStatement.setInt(2, tranSl);
                insertStatement.executeUpdate();
                BaseLogger.log("3", null, null, "New record inserted with retry counter set to 1");
            }
        }
    } finally {
        if (psUpdateRetryCounter != null) {
            psUpdateRetryCounter.close();
        }
    }
}

    }
}



