Something wrong with the PreparedStatement

[Varun Nambuthiri]

Hi

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

Heres the Query :

 PreparedStatement ps = con.prepareStatement("SELECT * FROM details WHERE  first_name LIKE  '?%' AND  last_name LIKE  '?%' AND  subject_taught LIKE  '?%' AND  city_id LIKE  '?%' Speciality like '?%' ");

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

[Saikat Mukherjee]

'?%' <== 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.

query : SELECT * FROM details WHERE  first_name LIKE  ? AND  last_name LIKE  ? .....

preparedStarement.setString(1,"A%");

[Varun Nambuthiri]

Thank you for the quick reply ,

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

[Saikat Mukherjee]

You can simply append the filter criteria with the variable.

[Varun Nambuthiri]

Can you give an example ?

[Martin Vashko]

What about this:

preparedStarement.setString(1, someVariable + "%");

?

[Varun Nambuthiri]

That seems plausible .. will try and let you know ..

Thanks !

[Varun Nambuthiri]

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 Vashko]

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]

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

[Varun Nambuthiri]

This is the code that works on displaying the result set

Class.forName("com.mysql.jdbc.Driver");
		 Connection	con = DriverManager.getConnection("jdbc:mysql://localhost/test","root","");
		 
		 PreparedStatement ps = con.prepareStatement("SELECT * FROM details WHERE  first_name LIKE  ? AND  last_name LIKE  ? AND 
 subject_taught LIKE  ? AND  city_id LIKE  ? and Speciality like ? ");
		 ps.setString(1,name+"%");
		 ps.setString(2, name+"%");
		 ps.setString(3,Course+"%");
		 ps.setString(4, city+"%");
		 ps.setString(5,Specialisation+"%");
		 ResultSet rs = ps.executeQuery();
		 if(rs.next())		 
		 return "title "+rs.getString(2)+"First Name "+rs.getString(3)+" Last Name "+rs.getString(4)+"Date Of Birth "+rs.getString(5)+
"Gender "+rs.getString(6)+"Email Address "+rs.getString(7)+"Mobile Number "+rs.getInt(7)+"Office Number "+rs.getInt(8)+"Fax Number "+rs.getInt(9)+
"Address Line 1 "+rs.getString(10)+"Address Line 2 "+rs.getString(11)+"Landmark "+rs.getString(12)+" City "+rs.getString(13)+"District "+rs.getString(14)+
"State "+rs.getString(15)+"Country "+rs.getString(16)+"Website URL "+rs.getString(17)+"User Type "+rs.getString(18)+"Qualification "+rs.getString(19)+
"Subjects Taught "+rs.getString(20)+"Speciality"+rs.getString(24)+"Experience "+rs.getInt(21)+"User Name"+rs.getString(22);

[Martin Vashko]

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]

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 Vashko]

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]

Thank you !

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

Regards
Varun