Suddenly getting SQL Exceptions when using sql variables in statements

Question

I have a SQL query, consisting of different statements (this is a simplified version, which also triggers the error) :

private static String getActiveKeyEventsSql =
        "SET @report_model_id = 2; " +
        "SELECT MAX(report_ts) AS report_ts " + 
        "FROM `pulse_data`.`key_event_reports` " + 
        "WHERE report_model_id = @report_model_id ";

I am trying to call that statement from inside my Java Application:

public static void main(String[] args) throws Exception {
    MySQLLayer _db = new MySQLLayer();

    Connection _conn = null;
    try {
        _conn = _db.getConnection();
        PreparedStatement getActiveKeyEventsStmt = _conn.prepareStatement(getActiveKeyEventsSql);
        ResultSet rs = getActiveKeyEventsStmt.executeQuery();

        while (rs.next()) {
            LOG.info(rs.getLong("report_ts"));
        }
    } catch (SQLException e) {
        LOG.error("COULD NOT GET MAX REPORT.", e);
    } finally {
        try {
            if (_conn != null && !_conn.isClosed()) {
                _conn.close();
            }
        } catch (SQLException e) {
            LOG.info("COULD NOT CLOSE CONNECTION.", e);
        }
    }
}

But it triggers the following error:

java.sql.SQLException: ResultSet is from UPDATE. No Data.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6870)
    at com.stockpulse.stockstorm.sentiment.JavaTest.main(JavaTest.java:36)

In other places of my application, this schema works just fine. When I copy this statement to the MySQL console, it works just fine.

Here is the String to init the DB:

config.setJdbcUrl(
"jdbc:mysql://" + cred.getHOST() + "/" + cred.getDB()
+ "?allowMultiQueries=true&characterEncoding=utf-8&useUnicode=true&rewriteBatchedStatements=true&relaxAutoCommit=true"
);

Why is JDBC behaving this way out of the sudden?

Answer 1

Try breaking your statement into

a = "SET @report_model_id = 2; ";
b = "SELECT MAX(report_ts) AS report_ts " + 
"FROM `pulse_data`.`key_event_reports` " + 
"WHERE report_model_id = @report_model_id ";

And do PreparedStatement.addBatch() for each.