aspose file tools*
The moose likes Other Application Frameworks and the fly likes Unable to call Ms-Access DB from Spring application? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Frameworks » Other Application Frameworks
Bookmark "Unable to call Ms-Access DB from Spring application?" Watch "Unable to call Ms-Access DB from Spring application?" New topic
Author

Unable to call Ms-Access DB from Spring application?

Riyaz Abdul Kareem
Greenhorn

Joined: Apr 23, 2007
Posts: 11
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
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15292
    
    6

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


GenRocket - Experts at Building Test Data
Riyaz Abdul Kareem
Greenhorn

Joined: Apr 23, 2007
Posts: 11
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
Greenhorn

Joined: Apr 23, 2007
Posts: 11
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
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
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
Greenhorn

Joined: Apr 23, 2007
Posts: 11
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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Unable to call Ms-Access DB from Spring application?
 
Similar Threads
Trouble with Hibernate3 & Postgresql 8.0.4
Hibernate Error when inserting into a table
Do you use a bean for creating database connection?
Transaction failure crashes program
Struts - Error creating form bean