• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Junilu Lacar
  • Martin Vashko
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Scott Selikoff
  • salvin francis
  • Piet Souris

Unable to call Ms-Access DB from Spring application?

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The error tells you exactly what is wrong. You supplied too few parameters to the stored procedure.
 
Riyaz Abdul Kareem
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Let's get him boys! We'll make him read this tiny ad!
Sauce Labs - World's Largest Continuous Testing Cloud for Websites and Mobile Apps
https://coderanch.com/t/722574/Sauce-Labs-World-Largest-Continuous
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!