Announcement Announcement Module
Collapse
No announcement yet.
Bad SQL Grammer error with MVC tutorial Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Bad SQL Grammer error with MVC tutorial

    I get the following error after following part 4 of the MVC step-by-step tutorial:

    org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [SELECT id, description, price from products] in task 'executing PreparedStatementCallback [PreparedStatementCreatorFactory.PreparedStatementC reatorImpl: sql=[SELECT id, description, price from products]: params=[]]'; nested exception is java.sql.SQLException: Table not found: PRODUCTS in statement [SELECT id, description, price from products]
    org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator.translate(SQLErrorCodeSQLExcepti onTranslator.java:254)
    org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:340)
    org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:375)
    org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:403)
    org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:424)
    org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:110)
    org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:120)
    org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:135)
    db.ProductManagerDaoJdbc.getProductList(ProductMan agerDaoJdbc.java:67)
    bus.ProductManager.getProducts(ProductManager.java :50)
    web.SpringappController.handleRequest(SpringappCon troller.java:61)
    org.springframework.web.servlet.mvc.SimpleControll erHandlerAdapter.handle(SimpleControllerHandlerAda pter.java:44)
    org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:495)
    org.springframework.web.servlet.FrameworkServlet.s ervice(FrameworkServlet.java:321)
    javax.servlet.http.HttpServlet.service(HttpServlet .java:802)


    BUILD.PROPERTIES:
    # Ant properties for building the springapp

    deploy.path=F:/apache/jakarta-tomcat-5.0.27/webapps
    #deploy.path=F:/apache/jakarta-tomcat-5.0.27/webapps

    tomcat.home=F:/apache/jakarta-tomcat-5.0.27
    #tomcat.home= F:/apache/jakarta-tomcat-5.0.27
    tomcat.manager.url=http://localhost:8082/manager
    tomcat.manager.username=tomcat
    tomcat.manager.password=tomcat

    db.driver=org.hsqldb.jdbcDriver
    db.url=jdbc:hsqldb:db/test
    db.user=sa
    db.pw=



    THE FOLLOWING IS THE SETTINGS IN springapp-servlet.xml AT THE ROOT LEVEL:
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
    <property name="driverClassName"><value>org.hsqldb.jdbcDrive r</value></property>
    <property name="url">
    <value>jdbc:hsqldb:E:/Fin-IT/1softwaredevelopment/eclipse/workspace/springapp/db/test</value>
    </property>
    <property name="username"><value>sa</value></property>
    <property name="password"><value></value></property>
    </bean>

    QUESTIONS:
    1. do I have the jdbc:hsqldb paths set correctly?
    2. what is suppost to go into the db/test directory?
    3. why am I getting the BadGrammer error?

  • #2
    1. in BUILD.PROPERTIES: you must have
    db.url=jdbc:hsqldb:E:/Fin-IT/1softwaredevelopment/eclipse/workspace/springapp/db/test
    (I am not sure if this is the same thing as /db/test as I do not know where your program is being executed from)

    2. this folder contains your database file. You need to create and populate your database by calling
    Code:
      ant createTables loadData
    3. because your current database does not contain table PRODUCTS
    nested exception is java.sql.SQLException: Table not found: PRODUCTS in statement [SELECT id, description, price from products]
    HTH

    Comment


    • #3
      Bad SQL Grammer error with MVC tutorial

      I made the changes to the build.properties file. I have a question however, is the directory path I assign for db.url the deployment directory in tomcat and I should leave off all the path information except the directory inside the root application directory? Or, is the path for db.url the directory where I am building the application?

      In the Spring tutorial Risberg has the following in the build.properties:
      db.driver=org.hsqldb.jdbcDriver
      db.url=jdbc:hsqldb:db/test
      db.user=sa
      db.pw=


      He has the following in the springapp-servlet.xml file:
      <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
      <property name="driverClassName"><value>org.hsqldb.jdbcDrive r</value></property>
      <property name="url">
      <value>jdbc:hsqldb:/home/trisberg/workspace/springapp/db/test</value>
      </property>
      <property name="username"><value>sa</value></property>
      <property name="password"><value></value></property>
      </bean>

      QUESTIONS:
      1. why the full path of his workspace directory and just db/test in the build.properties?

      Comment


      • #4
        Re: Bad SQL Grammer error with MVC tutorial

        Originally posted by joegaber
        I made the changes to the build.properties file.
        did it work for you?
        Originally posted by joegaber
        1. why the full path of his workspace directory and just db/test in the build.properties?
        db/test is resolved to a directory that is relative to program start directory. You can absolutely change it and use a full path directory like the one used in springapp-servlet.xml.

        Comment


        • #5
          Bad SQL Grammer error with MVC tutorial

          That worked. I had to go back and clean everything and restart Tomcat. I still have a couple of questions.

          1. two files were created in the db directory. I was expecting something to be placed in the db/test directory. The two files create are test.properties and test.script. Are these the files that act as the database?

          2. why wasn't any files placed in db/test dir when that was the path set in the build.properties file?

          3. is there an instance of the database running I just don't know about?

          4. how does the application I created know how to read the data?

          Comment


          • #6
            Spring MVC tutorial uses hsqldb. hsqldb is a lightweight rdbms written 100% in java. For more information, take a look at hsqldb documentation

            Comment


            • #7
              Originally posted by irbouho
              Spring MVC tutorial uses hsqldb. hsqldb is a lightweight rdbms written 100% in java. For more information, take a look at hsqldb documentation
              Thanks. This solved the problem and my understanding.

              Comment


              • #8
                I found that the quickest way to fix this is to add a COMMIT and SHUTDOWN to the createTables ant task given in the tutorial. Otherwise the hsqldb doesn't save the newly created table and won't load the data because it thinks it already has a connection.

                <target name="createTables">
                <echo message="CREATE TABLES USING: ${db.driver} ${db.url}"/>
                <sql driver="${db.driver}"
                url="${db.url}"
                userid="${db.user}"
                password="${db.pw}"
                onerror="continue">
                <classpath refid="master-classpath"/>

                CREATE TABLE products (
                id INTEGER NOT NULL PRIMARY KEY,
                description varchar(255),
                price decimal(15,2)
                );
                CREATE INDEX products_description ON products(description);
                COMMIT;
                SHUTDOWN


                </sql>
                </target>

                Comment

                Working...
                X