Bad SQL Grammer error with MVC tutorial

[joegaber]

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?

[irbouho]

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

  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

[joegaber]

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?

[irbouho]

I made the changes to the build.properties file.

did it work for you?

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.

[joegaber]

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?

[irbouho]

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

[joegaber]

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.

[Jsah]

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>