Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Unable to call Ms-Access DB from Spring application?  RSS feed

 
Riyaz Abdul Kareem
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
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • 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
 
Herman Schelti
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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!