Announcement Announcement Module
Collapse
No announcement yet.
Error in SQL syntax using MYSQL Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error in SQL syntax using MYSQL

    All

    I have the following select statements

    Code:
    <bean id="findInstrumentByIdStmt" class="java.lang.String">
    		<constructor-arg value="SELECT FROM INSTRUMENTS WHERE ID = ? AND ENABLED = ?" />
    	</bean>
    And the code using this statement is

    Code:
    Object args[] = new Object[] {
    				new SqlParameterValue(Types.VARCHAR, id),
    				new SqlParameterValue(Types.BOOLEAN, true)
    				};
    			
    		List<Data> instr = getJdbcTemplate().query(this.findInstrumentByIdStmt, 
    				args,						            this.instrumentRowMapper);
    BUt when I run this in my Unit test I get this error back, is there something I am missing with the syntax, it seems to be working when I use it on the command line with mysql

    Code:
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT FROM INSTRUMENTS WHERE ID = ? AND ENABLED = ?]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM INSTRUMENTS WHERE ID = 'b03023e8-aa0a-4ea4-8f7e-60d1ae19fd9c' AND ENABLED =' at line 1
    	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.translate(SQLExceptionSubclassTranslator.java:78)
    	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:237)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:582)
    	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:616)
    	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:645)
    	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:653)
    	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:685)
    	at com.strader.staticdata.persist.spring.InstrumentSpringDaoImpl.findByID(InstrumentSpringDaoImpl.java:59)
    	at com.strader.staticdata.test.persist.instrument.InstrumentDaoTestCase.testCheckExists(InstrumentDaoTestCase.java:134)
    	at com.strader.staticdata.test.persist.instrument.InstrumentDaoTestCase.runTest(InstrumentDaoTestCase.java:59)
    	at junit.framework.TestCase.runBare(TestCase.java:134)
    	at junit.framework.TestResult$1.protect(TestResult.java:110)
    	at junit.framework.TestResult.runProtected(TestResult.java:128)
    	at junit.framework.TestResult.run(TestResult.java:113)
    	at junit.framework.TestCase.run(TestCase.java:124)
    	at junit.framework.TestSuite.runTest(TestSuite.java:232)
    	at junit.framework.TestSuite.run(TestSuite.java:227)
    	at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:91)
    	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
    	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM INSTRUMENTS WHERE ID = 'b03023e8-aa0a-4ea4-8f7e-60d1ae19fd9c' AND ENABLED =' at line 1
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    	at com.mysql.jdbc.Util.getInstance(Util.java:381)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
    	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:623)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:566)
    	... 21 more

  • #2
    As the message points out your sql is wrong.
    Code:
    SELECT * FROM INSTRUMENTS WHERE ID = ? AND ENABLED = ?

    Comment


    • #3
      Originally posted by Marten Deinum View Post
      As the message points out your sql is wrong.
      Code:
      SELECT * FROM INSTRUMENTS WHERE ID = ? AND ENABLED = ?
      Thanks, but why has spring not taken the args[] and subbed the params out so that the SQL is correct.

      IN the error line

      Code:
      Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM INSTRUMENTS WHERE ID = 'b03023e8-aa0a-4ea4-8f7e-60d1ae19fd9c' AND ENABLED =' at line 1
      The issue i see is that the boolean param "ENABLED = ' " has not closing single quote with the boolean value ? am I using is incorrectly.

      Comment


      • #4
        Thanks, but why has spring not taken the args[] and subbed the params out so that the SQL is correct.
        Because that is the way a jdbc PreparedStatement works. If you want that to happen you will have to use a jdbc driver (like p6spy.

        The issue i see is that the boolean param "ENABLED = ' " has not closing single quote with the boolean value ? am I using is incorrectly.
        That is how MySQL reports an error, it selects x characters from the query so you can locate your error.

        Comment


        • #5
          Originally posted by Marten Deinum View Post
          Because that is the way a jdbc PreparedStatement works. If you want that to happen you will have to use a jdbc driver (like p6spy.


          That is how MySQL reports an error, it selects x characters from the query so you can locate your error.
          Thanks for your help. but I am not sure why spring is not executing the statement. If i take that same statement and put in into the mysql console it returns my results. Am I not using the query correctly in spring ?

          Comment

          Working...
          X