Unable to call Ms-Access DB from Spring application?

[Riyaz Abdul Kareem]

Getting log values has the following?
2007-05-10 20:23:55,174 INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader]11/27/2006 - <Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]>
2007-05-10 20:23:55,190 INFO [org.springframework.beans.factory.xml.XmlBeanFactory]11/27/2006 - <Creating shared instance of singleton bean 'DB2'>
2007-05-10 20:23:55,190 INFO [org.springframework.beans.factory.xml.XmlBeanFactory]11/27/2006 - <Creating shared instance of singleton bean 'HSQL'>
2007-05-10 20:23:55,190 INFO [org.springframework.beans.factory.xml.XmlBeanFactory]11/27/2006 - <Creating shared instance of singleton bean 'MS-SQL'>
2007-05-10 20:23:55,190 INFO [org.springframework.beans.factory.xml.XmlBeanFactory]11/27/2006 - <Creating shared instance of singleton bean 'MySQL'>
2007-05-10 20:23:55,190 INFO [org.springframework.beans.factory.xml.XmlBeanFactory]11/27/2006 - <Creating shared instance of singleton bean 'Oracle'>
2007-05-10 20:23:55,190 INFO [org.springframework.beans.factory.xml.XmlBeanFactory]11/27/2006 - <Creating shared instance of singleton bean 'Informix'>
2007-05-10 20:23:55,190 INFO [org.springframework.beans.factory.xml.XmlBeanFactory]11/27/2006 - <Creating shared instance of singleton bean 'PostgreSQL'>
2007-05-10 20:23:55,190 INFO [org.springframework.beans.factory.xml.XmlBeanFactory]11/27/2006 - <Creating shared instance of singleton bean 'Sybase'>
2007-05-10 20:23:55,206 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory]11/27/2006 - <SQLErrorCodes loaded: [HSQL Database Engine, Oracle, Sybase SQL Server, Microsoft SQL Server, Informix Dynamic Server, PostgreSQL, MySQL, DB2]>
2007-05-10 20:23:55,206 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory]11/27/2006 - <Looking up default SQLErrorCodes for DataSource>
2007-05-10 20:23:55,206 INFO [org.springframework.jdbc.datasource.DriverManagerDataSource]11/27/2006 - <Creating new JDBC connection to [jdbc dbc roduct]>
2007-05-10 20:23:55,331 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory]11/27/2006 - <Database Product Name is ACCESS>
2007-05-10 20:23:55,331 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory]11/27/2006 - <Driver Version is 2.0001 (04.00.6304)>
2007-05-10 20:23:55,331 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory]11/27/2006 - <Error Codes for ACCESS not found>
2007-05-10 20:23:55,331 INFO [com.java.springapp.ProductManagerDaoJdbc$ProductQuery]11/27/2006 - <RdbmsOperation with SQL [SELECT id, description, price from products] compiled>
2007-05-10 20:23:55,346 INFO [org.springframework.jdbc.datasource.DriverManagerDataSource]11/27/2006 - <Creating new JDBC connection to [jdbc dbc roduct]>
2007-05-10 20:23:55,377 WARN [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator]11/27/2006 - <Unable to translate SQLException with errorCode '-3010', will now try the fallback translator>
2007-05-10 20:23:55,377 INFO [org.springframework.jdbc.support.SQLStateSQLExceptionTranslator]11/27/2006 - <Translating SQLException with SQLState '07001' and errorCode '-3010' and message [[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.]; SQL was [SELECT id, description, price from products] for task [executing PreparedStatementCallback [PreparedStatementCreatorFactory.PreparedStatementCreatorImpl: sql=[SELECT id, description, price from products]: params=[]]]>
2007-05-10 20:23:55,424 ERROR [org.springframework.web.servlet.DispatcherServlet]11/27/2006 - <Could not complete request>
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [SELECT id, description, price from products] in task 'executing PreparedStatementCallback [PreparedStatementCreatorFactory.PreparedStatementCreatorImpl: sql=[SELECT id, description, price from products]: params=[]]'; nested exception is java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:89)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:336)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:292)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:329)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:351)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:372)
at org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java:111)
at org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java:121)
at org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java:136)
at com.java.springapp.ProductManagerDaoJdbc.getProductList(ProductManagerDaoJdbc.java:25)
at com.java.springapp.ProductManager.getProducts(ProductManager.java:24)
at com.java.springapp.SpringappController.handleRequest(SpringappController.java:34)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:485)
at org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:342)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:318)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)


Any Help will be greatly appreciated.
Thankx in advance

Regards
Riyaz

[Gregg Bolinger]

The error tells you exactly what is wrong. You supplied too few parameters to the stored procedure.

[Riyaz Abdul Kareem]

Hi

When i googled for this error SQLState '07001'.

They said "Number of host variables not valid"

It means that sql sattement is having some problem.

i have pasted my code below FYI

public List getProductList() {
logger.info("Getting products!");
ProductQuery pq = new ProductQuery(ds);
return pq.execute();
}






class ProductQuery extends MappingSqlQuery {

ProductQuery(DataSource ds) {
super(ds, "SELECT id, description, price from products");
logger.info("AFTER DB Conc------------------------>");
compile();
}

protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Product prod = new Product();
prod.setId(rs.getInt("id"));
prod.setDescription(rs.getString("description"));
prod.setPrice(rs.getFloat("price"));
return prod;
}

[Riyaz Abdul Kareem]

Hi Gregg Bolinger

Thanks for your reply.
But for your information this is a MappingSqlQuery class.

I'm provding whole code to you below.

package com.java.springapp;

import com.java.springapp.Product;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.core.SqlParameter;

public class ProductManagerDaoJdbc implements ProductManagerDao {

/** Logger for this class and subclasses */
protected final Log logger = LogFactory.getLog(getClass());

private DataSource ds;

public List getProductList() {
logger.info("Getting products!");
ProductQuery pq = new ProductQuery(ds);
return pq.execute();
}

public void setDataSource(DataSource ds) {
this.ds = ds;
}

class ProductQuery extends MappingSqlQuery {

ProductQuery(DataSource ds) {
super(ds, "SELECT id, description, price from products");
logger.info("AFTER DB Conc------------------------>");
compile();
}

protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Product prod = new Product();
prod.setId(rs.getInt("id"));
prod.setDescription(rs.getString("description"));
prod.setPrice(rs.getFloat("price"));
return prod;
}

}

}


Thanks in advance


Riyaz

[Herman Schelti]

hi,

did you double check the column names and table name?

I usually try a "select * from ..." first and take it from there.

Herman
PS: I've used the combination Spring/Access at home for 2 years now, so it is possible.

[Riyaz Abdul Kareem]

Herman,

Thanks a lot for your reply.

I have check that the field name was incorrect.

Now DB access is working with spring.

Really i felt very happy that records were called from access
into jsp page.

Thanks once again to "you and Java ranch team".

Happy learning Spring.
Riyaz.