org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [ select straight_join r.record_id, r.oai_datestamp, r.format_id, r.status, r.type, r.prev_status , x.xml, max(u.date_updated) as date_updated from xc_testting2.record_updates u force index (idx_record_updates_record_id), xc_testting2.records r , xc_testting2.records_xml x where r.record_id = x.record_id and (r.record_id > ? or ? is null) and r.record_id = u.record_id and (u.date_updated >= ? or ? is null) and u.date_updated <= ? and status in ('A','D') group by u.record_id order by u.record_id limit 1000]; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Key 'idx_record_updates_record_id' doesn't exist in table 'u'

GitHub | patrickzurek | 2 months ago
  1. 0

    GitHub comment 572#253589367

    GitHub | 2 months ago | patrickzurek
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [ select straight_join r.record_id, r.oai_datestamp, r.format_id, r.status, r.type, r.prev_status , x.xml, max(u.date_updated) as date_updated from xc_testting2.record_updates u force index (idx_record_updates_record_id), xc_testting2.records r , xc_testting2.records_xml x where r.record_id = x.record_id and (r.record_id > ? or ? is null) and r.record_id = u.record_id and (u.date_updated >= ? or ? is null) and u.date_updated <= ? and status in ('A','D') group by u.record_id order by u.record_id limit 1000]; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Key 'idx_record_updates_record_id' doesn't exist in table 'u'
  2. 0

    Removed error-irrelevant translation stack frames from exception stack trace after exceptions were created by an SQLExceptionTranslator: When debugging Spring-JDBC queries, I find it distracting that the top stack frames always refer to SQLExceptionTranslator which is only called after an error occured that caused the exception. The translator only translates a "raw" exception into a richer exception. Therefore, I suggest that these stack entries are removed before the enriched exception is rethrown by the JdbcTemplate. It is a detail, but I believe the exception stack trace should not get polluted with details that merely reflect an implementation detail. This can also distract a user that tries debugging his/her application and who does not know how the exception handling is implemented in Spring. An example of how the stack trace looks like when using an invalid SQL statement within JdbcTemplate: {noformat} org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [This is not valid SQL]; nested exception is java.sql.SQLException: ORA-00900: invalid SQL statement at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:637) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:666) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:674) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:714) at com.example.Main.main(Main.java:17) Caused by: java.sql.SQLException: ORA-00900: invalid SQL statement {noformat} With the patch the stack trace would look like: {noformat} org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [This is not valid SQL]; nested exception is java.sql.SQLException: ORA-00900: invalid SQL statement at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:614) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:637) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:666) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:674) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:714) at com.example.Main.main(Main.java:17) Caused by: java.sql.SQLException: ORA-00900: invalid SQL statement {noformat} In this example, the exception is catched withing JdbcTemplate.execute, wrapped by an instance of SQLErrorCodeSQLExceptionTranslator and then rethrown. Since the SQLErrorCodeSQLExceptionTranslator is responsible for instantiating the wrapping exception, the exception's stack trace is pointing to the translator since Exception.fillInStackTrace is called in the exception's constructor (this is the default implementation of a Java exception.)

    Spring JIRA | 3 years ago | Rafael Winterhalter
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [This is not valid SQL]; nested exception is java.sql.SQLException: ORA-00900: invalid SQL statement
  3. 0

    How to use Stored procedures with parameter in Mule

    Stack Overflow | 2 years ago
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [CALL sp_myStoredProcedure(?) ]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Spring JDBCTemplate throwing BadSqlGrammarException

    Stack Overflow | 1 year ago | Chakri
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [ SELECT DISTINCT O.ORGID FROM ORGANISATIONS O WHERE O.ORGTYPE NOT IN ('P','G') AND O.USERID = 40]; nested exception is java.sql.SQLException: Invalid column name
  6. 0

    Nested exception is com.microsoft.sqlserver.jdbc.SQLServerException:The server failed to resume the transaction

    Stack Overflow | 7 months ago | User146378
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [{call storeproc1(?,?,?,?,?,?,?,?)}]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The server failed to resume the transaction. Desc:a600000018.

    4 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. org.springframework.jdbc.BadSqlGrammarException

      PreparedStatementCallback; bad SQL grammar [ select straight_join r.record_id, r.oai_datestamp, r.format_id, r.status, r.type, r.prev_status , x.xml, max(u.date_updated) as date_updated from xc_testting2.record_updates u force index (idx_record_updates_record_id), xc_testting2.records r , xc_testting2.records_xml x where r.record_id = x.record_id and (r.record_id > ? or ? is null) and r.record_id = u.record_id and (u.date_updated >= ? or ? is null) and u.date_updated <= ? and status in ('A','D') group by u.record_id order by u.record_id limit 1000]; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Key 'idx_record_updates_record_id' doesn't exist in table 'u'

      at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate()
    2. Spring Framework
      JdbcTemplate.query
      1. org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
      2. org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
      3. org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      4. org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      5. org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
      6. org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
      7. org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
      8. org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
      9. org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
      9 frames
    3. xc.mst.repo
      RepositoryDAO.getRecordsWSets
      1. xc.mst.repo.RepositoryDAO.getRecords(RepositoryDAO.java:1112)
      2. xc.mst.repo.RepositoryDAO.getRecordsWSets(RepositoryDAO.java:1436)
      2 frames