aspose file tools*
The moose likes JDBC and the fly likes SQL error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL error" Watch "SQL error" New topic
Author

SQL error

Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
Hey,
I'm back with yet another SQLException I don't understand, or know how to fix. In this case my controller servlet forwards a string and a connection to my model and the model is to search for a that string within the artist field of my cd database.

Here is my model function:


Here is the exception:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source) at infostore.models.searchModel.findArtists(searchModel.java:14) at infostore.controllers.SearchController.doPost(SearchController.java:36) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2344) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:163) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1011) at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1106) at java.lang.Thread.run(Unknown Source)

I'm using MS Access, and if anyone has any suggestions I'd really appreciate it!!
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1124

Jason,
Although I'm not terribly familiar with Microsoft Access, as far as I know, the "wildcard" character in SQL is the percent sign -- '%' -- and not the asterisk -- '*'. Perhaps replace '*' with '%' in your SQL query string?

Try this:

Good Luck,
Avi.
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
Well I ran the query directly in Access with the * and it worked just fine. However, I'm in no position to be picky so I'll give it a shot!!
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
It didn't work, I got a missing operator exception. For those just tuning in , the purpose of this servlet and model is to search for a string that the user enters in. I can't get my query to work, any suggestions on what I can do to fix or another method around this one?
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
Sorry, one more thing; this is the exact query I created in MS Access which gets the results I'm looking for. I tried this in my model as well but it didn't work either.

SELECT cd.* FROM cd WHERE ((([cd].[artist]) Like \"*is*\") And (([cd].[active])=True))
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Jason Kwok:
this is the exact query I created in MS Access which gets the results

SELECT cd.* FROM cd WHERE ((([cd].[artist]) Like \"*is*\") And (([cd].[active])=True))


Did you look at Avi's post? do you find any difference in your query and what he suggested.


Shailesh
[ April 26, 2005: Message edited by: Shailesh Chandra ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
Using the % sign instead of the * gives me the following SQLException:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source) at infostore.models.searchModel.findArtists(searchModel.java:14) at infostore.controllers.SearchController.doPost(SearchController.java:36) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2344) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:462) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:163) at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1011) at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1106) at java.lang.Thread.run(Unknown Source)
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Put the code here

Shailesh
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
Hey Shailesh, I'm not too sure what code you're referring to so I'll post it all.

This is the doPost method in my SearchController


and this is my search model:
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081


String query = "SELECT * FROM cd WHERE (((artist) Like "*"+criteria+"*\"))";


String query = "SELECT * FROM cd WHERE (((artist) Like '%"+criteria+"%'))";


Shailesh
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
Hey Shailesh,

Yet again I'm in your debt!! It seems to work just fine now!! I really appreciate your help once again!! How Microsoft Access allowed % as a wildcard when the * and ? are it's only wildcards... I'll never know!!

Jason
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Jason Kwok:
How Microsoft Access allowed % as a wildcard when the * and ? are it's only wildcards...


* and ? are the wild cards for operating system not sql.

had you paid attention to Avi's post he has mentioned same and following his advice you could have solve this problem even before.

any way good to know its working

Shailesh
Jason Kwok
Ranch Hand

Joined: Mar 31, 2005
Posts: 126
Shailesh,
Thanks for the tip, I did try Avi's advice. If you read my posts directly after Avi's you'll see I tried and responded to him. I used the % with double quotes at the time which perhaps made things over complicated in translation and resulted in my errors.

Jason
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

ok got it

actuually I was referring to query hint given by him which has no double quote


Shailesh
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL error