Help me to fix the bug?

Code :

---

 private String available_stmt = "select tickets from shows where show_name =? and tid=? and showdate=?";
 
    public int availablity(String theater, String show, java.sql.Date date) {
        int i = -1;
        try {
            Context ctx = new InitialContext();
            if (ctx == null) {
                throw new RuntimeException("JNDI Contex could not be found");
            }
 
            ds = (DataSource) ctx.lookup("java:comp/env/jdbc/mindu");
            if (ds == null) {
                throw new RuntimeException("Datasource could not be found");
            }
 
            // get a database connection
            connection = ds.getConnection();
            stmt = connection.prepareStatement(available_stmt);
 
            stmt.setString(1, show);
            System.out.println("show set");
            stmt.setInt(2, objectid(theater));
            System.out.println("tid set"+objectid(theater));
            System.out.println("date recieved "+date);
            stmt.setDate(3,date);
            System.out.println("date set");
            results = stmt.executeQuery();
            while (results.next()) {
                i = (results.getInt("tickets"));
            }
 
            return i;
 
 
        } catch (SQLException sqle) {
            throw new RuntimeException("A Database error occured." + sqle.getMessage());
        } catch (NamingException ne) {
            throw new RuntimeException("A JNDI error occured" + ne.getMessage());
        }
    }

---

this is the method to access database and when ever i excute my web app server log is as follows

Quote:

---

show set
tid set1
date recieved 2011-03-22


Oct 12, 2011 4:15:16 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [action] in context with path [/rainbow] threw exception
java.lang.RuntimeException: A Database error occured.Parameter index out of range (3 > number of parameters, which is 1).
at model.theaterDAO.availablity(theaterDAO.java:216)
at formbeans.eupdatebean.exceed(eupdatebean.java:88)
at formbeans.eupdatebean.validate(eupdatebean.java:75 )
at org.apache.struts.action.RequestProcessor.processV alidate(RequestProcessor.java:945)
at org.apache.struts.action.RequestProcessor.process( RequestProcessor.java:198)
at org.apache.struts.action.ActionServlet.process(Act ionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doPost(Acti onServlet.java:462)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:641)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:722)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:304)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:210)
at org.netbeans.modules.web.monitor.server.MonitorFil ter.doFilter(MonitorFilter.java:393)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:240)
at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:164)
at org.apache.catalina.authenticator.AuthenticatorBas e.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:164)
at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(A ccessLogValve.java:563)
at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:399)
at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:317)
at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.process(Http11Protocol.java:204)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProce ssor.run(JIoEndpoint.java:311)
at java.util.concurrent.ThreadPoolExecutor.runWorker( ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:722)

---

Note: i tried excuting the same sql string in sql server and it worked there!!
i tried many things, even googled it but not having any luck.

What data type is showdate? What database are you using?

Quote:

---

Originally Posted by copeg

What data type is showdate? What database are you using?

---

here is the table details

Quote:

---

sid int(10) NO PRI
show_name varchar(20) NO
show_start time NO
show_end time NO
duration int(10) NO
mid int(10) NO MUL
showdate date NO
tid int(10) NO MUL
tickets int(10) NO 0

---

and database is mysql 5.5! all other query works fine, some of them are used to insert into this table also. i'm shocked how can this happens?

At quick glance I cannot see anything that would cause this error. You have 3 parameters in your PreparedStatement, and the first 2 parameters placements appear to work based upon the logging, so the exception stating you only have 1 parameter doesn't make much sense. It does appear to happen on the setDate method call, so I would say play around with the query to determine if this truly is the problem - if you remove the requirement for date, does the query work? If you set the date as the first parameter, how does it behave?

here one more surprise ! i declared that same method ( copy and paste) in another class show( before it was theater). it worked there with no problem every thing works fine.

now that my problem is solved but still i would like to know why it doesn't worked there?? i'm sure there is something wrong here may be either hidden under or ignored?? well one important thing i want to tell is this class theater is having children as tid(int),theatername(String) and location( string again).
and class show is having children all those that table needs and is provided in previous post of mine!
regards