Announcement Announcement Module
Collapse
No announcement yet.
BadSqlGrammarException thrown for the wrong reason - SQL Server and Spring JDBC Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • BadSqlGrammarException thrown for the wrong reason - SQL Server and Spring JDBC

    When I raise an exception in a stored proc (using RAISERROR) it always comes back to my Java code as BadSqlGrammarException, which isn't accurate. Have I missed some configuration? Shouldn't it throw UncategorizedSQLException by default?

    I'm using the MS SQL Server JDBC driver, v3.0 and Spring 3.

  • #2
    Post here your source code about the stored proc, and of course post the complete error stack trace to know exactly the situation

    Comment


    • #3
      Hi, thanks for the reply.

      Example application

      Code:
      public class SpringBadSqlGrammarExample {
      
      	private static final String DB_HOST = "localhost";
      	private static final int DB_PORT = 55215;
      	private static final String DB_NAME = "SpringJdbcTest";
      	private static final String DB_USER = "sa";
      	private static final String DB_PASSWORD = <whatever>;
      
      	public static void main(final String[] args) throws SQLException {
      		final Driver jdbcDriver = new SQLServerDriver();
      		final String connectionStringStart = "jdbc:sqlserver://" + DB_HOST
      				+ ":" + DB_PORT + ";DatabaseName=";
      		final Properties props = new Properties();
      		props.setProperty("user", DB_USER);
      		props.setProperty("password", DB_PASSWORD);
      		new JdbcTemplate(new SingleConnectionDataSource(jdbcDriver.connect(
      				connectionStringStart + DB_NAME, props), false)).update(
      				"EXEC spKablammo @Param1=?", 99);
      	}
      }
      Stored proc

      Code:
      CREATE PROCEDURE spKablammo
      	@Param1 INT
      AS
      BEGIN
        RAISERROR('Deliberate error raised [%d]' , 11, 1, @Param1)
      END
      Logging code

      Code:
      2011-06-21 10:04:22,317 [DEBUG] <org.springframework.jdbc.support.SQLErrorCodesFactory> Database product name cached for DataSource [[email protected]3aa6f]: name is 'Microsoft SQL Server'
      2011-06-21 10:04:22,319 [DEBUG] <org.springframework.jdbc.support.SQLErrorCodesFactory> SQL error codes for 'Microsoft SQL Server' found
      2011-06-21 10:04:22,322 [DEBUG] <org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator> Unable to translate SQLException with Error code '50000', will now try the fallback translator
      2011-06-21 10:04:22,324 [DEBUG] <org.springframework.jdbc.support.SQLStateSQLExceptionTranslator> Extracted SQL state class 'S0' from value 'S0001'

      Stack trace

      Code:
      Exception in thread "Main Thread" org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [EXEC spKablammo @Param1=?]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Deliberate error raised [99]
      	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
      	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
      	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
      	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
      	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:867)
      	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:875)
      	at org.ocathain.art.SpringBadSqlGrammarExample.main(SpringBadSqlGrammarExample.java:34)
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Deliberate error raised [99]
      	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
      	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
      	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
      	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
      	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
      	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:308)
      	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:817)
      	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
      	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
      	... 4 more
      Last edited by artbristol; Jun 21st, 2011, 04:05 AM. Reason: add logs

      Comment


      • #4
        It appears this issue was raised before https://jira.springsource.org/browse/SPR-2031 and resolved, so I can't understand why it's happening now.

        Comment

        Working...
        X