Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL error

 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Put the code here

Shailesh
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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


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


Shailesh
 
Jason Kwok
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ok got it

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


Shailesh
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic