Quartz scheduler not executing the SQL Query in a dropwizard application

Question

I've an application created using dropwizard framework where I've registered a quartz-scheduler job scheduled to run after every specified duration. This job fires a SQL query to SQL Server DB and iterates the ResultSet and sets the data to a POJO class which is later pushed to a queue.

The SQL query has UNION joining multiple tables which fetches the data for the records modified in a delta time using the last_modified_time column of the related table in where clause. DB jar included in pom.xml is sqljdbc-4.4.0 and quartz version is 2.2.1

The query looks like this:

SELECT 
    u.last_modified_date, 
    u.account_id, 
    u.user_id, 
    ud.is_active 
FROM user u WITH (NOLOCK) 
JOIN user_details ud with (NOLOCK) ON u.account_id = ud.account_id AND u.user_id = ud.user_id 
WHERE u.last_modifed_date > ? AND ud.last_modifed_date <= ?

UNION

SELECT 
    u.last_modified_date, 
    u.account_id, 
    u.user_id, 
    ud.is_active 
FROM user u WITH (NOLOCK) 
JOIN user_details ud with (NOLOCK) ON u.account_id = ud.account_id AND u.user_id = ud.user_id 
JOIN user_registration_details urd WITH (NOLOCK) ON urd.account_id = u.account_id AND urd.user_id = u.user_id AND urd.reg_id = ud.reg_id
WHERE urd.last_modifed_date > ? AND urd.last_modifed_date <= ?

This query is called by simple connection statement and resultset like this

final ManagedDataSource datasource configuration.getDatabase().build(environment.metrics(), "sql");
// configuration is the configuration class in a drop wizard application and configuration.getDatabase() returns 
// the DataSourceFactory with all credentials like user, password and url set into it
try (Connection conn = dataSource.getConnection()) {
      int resultSetType = SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY;
      int resultSetConcurrency = ResultSet.CONCUR_READ_ONLY;
      LOGGER.info("Starting execution: ");
      try (PreparedStatement pstmt = conn.prepareStatement(getQuery(), resultSetType,resultSetConcurrency))
      {
         setQueryParameters(pstmt);
         try (ResultSet rs = pstmt.executeQuery();) 
         {
           //process results 
         }
     }
  } catch (SQLException | IOException ex) {
        LOGGER.error(“Error occurred “ +  ex);
}
LOGGER.info("Completed execution: ");

In a simple execution, it prints the logs "Starting execution" and then processes the records and prints "completed execution". But sometimes during the execution, it's printing the logs "Starting execution" and "completed execution" but this query is not actually fired to the SQL DB.

As I didn't get the records which I modified in that delta time, I put the profiler to check if the query is actually fired and didn't found this query firing to the DB. Also, I've tried adding log4jdbc library http://code.google.com/p/log4jdbc/wiki/FAQ to print the query to the logs but no logs were printed for this query.

Answer 1

with (NOLOCK) is not MySQL syntax. Look at the settings in the wizard and see if you have specified the correct RDBMS engine. In particular, it sounds like SQL Server syntax.

The equivalent may involve setting the TRANSACTION ISOLATION LEVEL to something like READ UNCOMMITTED.