aspose file tools*
The moose likes JDBC and the fly likes Something wrong with the PreparedStatement 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 » Databases » JDBC
Bookmark "Something wrong with the PreparedStatement" Watch "Something wrong with the PreparedStatement" New topic
Author

Something wrong with the PreparedStatement

Varun Nambuthiri
Ranch Hand

Joined: Sep 20, 2010
Posts: 76
Hi

I am trying to execute the query which gives me error .

Heres the Query :



and the StackTrace

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3813)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3795)
at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4616)
at pkg.AnotherSample.getandreturnTutorInfo(AnotherSample.java:97)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:212)
at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:117)
at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40)
at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114)
at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:181)
at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:172)
at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:146)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Unknown Source)

Please help me ! Thank you !
Regards Varun

All's well that Debugs well .
Saikat Mukherjee
Greenhorn

Joined: Mar 06, 2008
Posts: 13
'?%' <== this is the problem area.

Most probably you are doing something like preparedStarement.setString(1,...).

Remove the single quotes from the query and pass the filter directly as the parameter value.

Varun Nambuthiri
Ranch Hand

Joined: Sep 20, 2010
Posts: 76
Thank you for the quick reply ,

But the problem is I want to be able to actually pass it through some variables .

Saikat Mukherjee
Greenhorn

Joined: Mar 06, 2008
Posts: 13
You can simply append the filter criteria with the variable.
Varun Nambuthiri
Ranch Hand

Joined: Sep 20, 2010
Posts: 76
Can you give an example ?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

What about this: ?
Varun Nambuthiri
Ranch Hand

Joined: Sep 20, 2010
Posts: 76
That seems plausible .. will try and let you know ..

Thanks !
Varun Nambuthiri
Ranch Hand

Joined: Sep 20, 2010
Posts: 76
Yes ! Thanks that has worked ! But another one has popped up which i am trying to figure out .

It says Illegal Operation on an Empty ResultSet

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

It would be better if you posted the relevant code and stack trace.

But in this case I'd say it is safe to guess that the statement didn't return any row, and you don't handle this situation correctly in your code.
Varun Nambuthiri
Ranch Hand

Joined: Sep 20, 2010
Posts: 76
Yes . That is what happned . I managed to clear the errors but its still not displaying the desired output !
Varun Nambuthiri
Ranch Hand

Joined: Sep 20, 2010
Posts: 76
This is the code that works on displaying the result set

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

Please have a look at the following pages: TellTheDetails and UseCodeTags. Then use the Edit button and shorten the lines in the code tag of your last post. Thanks!

Varun Nambuthiri wrote:Yes . That is what happned . I managed to clear the errors but its still not displaying the desired output !


Only you know what the actual and desired output is. Unless you tell us, we cannot know what happened.

In any case, the SELECT * part of your code is a bug waiting to happen. Please read Always use column list.
Varun Nambuthiri
Ranch Hand

Joined: Sep 20, 2010
Posts: 76
Thank you

I am sorry about that . So now back to what my desired output is . See my table contains 23 columns . And I need to compare 4 columns with the users input ( those columns being Name , Course , City Speciality) and then generate all the rows which have are comparable to the users input .

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

So you need to find all rows matching the given criteria and process them somehow?

In this case, you'll probably want to create a class that would represent individual database rows (say, Detail). When processing the resultset, you'll use while(rs.next()) to process all rows the resultset contains (now you're processing only the first row). You'll create the instance of your class (Detail) for each processed row and add it to a list you'll create for this purpose. After processing all rows return the list to the caller. And don't forget to stop using the SELECT *

This way you'll separate loading the list of database rows from the subsequent processing, which results in code which is much easier to maintain.
Varun Nambuthiri
Ranch Hand

Joined: Sep 20, 2010
Posts: 76
Thank you !

I think I have got it .. Let me see if it works ..

Regards
Varun
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Something wrong with the PreparedStatement
 
Similar Threads
MySQL no default value error and getGeneratedKeys
please reply on this
ibatis query problem
SQLException:Parameter Index out of Range
parameter not set!