getting error with prepareStatement

[Karen Wilson]

I am trying to use a prepareStatement to update the database but I am getting an error on setInt - cannot find symbol
and on the executeUpdate() I get error no suitable methods for executeUpdate (no arguments)

public Products UpdateProductsDB() {
       
       pool = ConnectionPool.getInstance();
       connection = pool.getConnection();
       try {
            stmt = connection.prepareStatement("UPDATE products SET qtyonhand=?, qtysold=? WHERE id=?");
        } catch (SQLException ex) {
           System.out.println(ex);
        }   
        return null;
    }
	
	public int updateRow(int qtyonhand, int qtysold){
                        int success=0;
			try{
				stmt.setInt(1,qtyonhand);
				stmt.setInt(2,qtysold);
				success=stmt.executeUpdate();
			}
			catch(Exception e){
				e.printStackTrace();
				success=0;
			}
                    return success;
		}

I used similar code for an insert statement and I don't have these problems

[Ron McLeod]

Where is the binding for the 3rd parameter?

"UPDATE products SET qtyonhand=?, qtysold=? WHERE id=?"

[Paul Clapham]

You're getting compiler errors about the "stmt" variable? You didn't post the line of code where it's declared, which is unfortunate since that's the line of code you need to look at. If it's declared as PreparedStatement then those compiler errors shouldn't appear, so my guess is that it's declared as something else. Perhaps as Statement?

[Ron McLeod]

Paul Clapham wrote:You're getting compiler errors about the "stmt" variable? You didn't post the line of code where it's declared, which is unfortunate since that's the line of code you need to look at. If it's declared as PreparedStatement then those compiler errors shouldn't appear, so my guess is that it's declared as something else. Perhaps as Statement?

If it was something other than PreparedStatement, wouldn't that cause an error with line 6 as well? As you say, it would be helpful to see how/where stmt is declared.

[Ron McLeod]

Ron McLeod wrote:If it was something other than PreparedStatement, wouldn't that cause an error with line 6 as well?

PS: I just tried it and was surprised that the compiler did not indicate an error with line 6 when stmt was declared as Statement.

[Tim Holloway]

The SQL command string in line 6 is a string constant, not Java code, so the Java compiler cannot detect syntactic (or semantic) problems with the SQL. They only get caught when the SQL is compiled, and that doesn't happen until runtime.

It's why I like to have unit-tests on all my database logic. So that I can catch mis-coded SQL without having to run through the long and tedious process of actually cranking up a webapp server (or whatever) and working my way through the app until the function in question gets executed.

[Paul Clapham]

Ron McLeod wrote:I just tried it and was surprised that the compiler did not indicate an error with line 6 when stmt was declared as Statement.[/i]

Yeah, Statement is a superinterface of PreparedStatement, so that is a legitimate assignment.

[Karen Wilson]

I was just missing the line

PreparedStatement stmtp;

So i updated the updateRow method to this:

public int updateRow(int id, int qtyonhand, int qtysold){
                        int success=0;
			try{  
				stmtp.setInt(1,qtyonhand);
				stmtp.setInt(2,qtysold);
                                stmtp.setInt(3,id);
				success=stmtp.executeUpdate();
			}
			catch(Exception e){
				e.printStackTrace();
				success=0;
			}
                    return success;
		}

I am using this code to create a check out for a shopping cart. As you can see the database has fields for quantity on hand, and quantity sold. When a customer enters the quantity they want to buy the database needs to be updated when they check out because the quantity values have changed.

Here is the form for the cart:

<c:forEach items="${alist}" var="aProduct">
                        <form action="" method="post">
                        <tr>
                          <td>
                              <input type="hidden" name="productID" value="${aProduct.id}">
                              <input type="hidden" name="qtysold" value="${aProduct.qtysold}">
                              <input type="hidden" name="productID" value="${aProduct.id}">
                              <input id="prodqty-${aProduct.id}" type="number" size="2" name="quantity" value="0" required="true">
                          </td>
                          <td>${aProduct.name}</td>
                          
                              <td id="prodprice-${aProduct.id}">${aProduct.price}</td>
                        </tr>
                        <input type="hidden" id="${aProduct.id}" value="${aProduct.price}"/>
                        <input type="hidden" id="qtyonhand-${aProduct.id}" value="${aProduct.qtyonhand}"/>
                        </form>
                        </c:forEach>
                  </table>
                    <br>
                    <label for="TotalPrice">Total Price: </label><div id="totprice"> </div>
                    
                    <br><br>
                    <form action="checkoutServlet" method="post">
                        <input type="submit" value="Check out">
                      </form>

The qtyonhand and qtysold are hidden in the form. When the customer clicks the check out button the checkoutServlet is run:

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException{
            response.setContentType("text/html");
           try
            {
                int id = Integer.parseInt(request.getParameter("id"));
                int qtyonhand = Integer.parseInt(request.getParameter("qtyonhand"));
                int qtysold = Integer.parseInt(request.getParameter("qtysold"));

                ProductDB pdb=new ProductDB();
		int successCode=pdb.updateRow(id, qtyonhand, qtysold);
                
           
            String address;
            address = "/checkout.jsp";
            
                     
            RequestDispatcher dispatcher = request.getRequestDispatcher(address);
            dispatcher.forward(request, response);
            }
            catch (Exception e){
                pw.println(e.getMessage());

            }
      }

When I run the code I get the error:
java.lang.NumberFormatException: null
at java.lang.Integer.parseInt(Integer.java:454)
at java.lang.Integer.parseInt(Integer.java:527)
at Servlets.checkoutServlet.doPost(checkoutServlet.java:33)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
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)

I am not sure how to change the qtyonhand and qtysold based on what the customer enters for the quantity in the form

[Paul Clapham]

The stack trace points to this line of code, right?

int qtyonhand = Integer.parseInt(request.getParameter("qtyonhand"));

That would be because the form doesn't have any input with name="qtyonhand", and thus request.getParameter("qtyonhand") returns null. You do have

<input type="hidden" id="qtyonhand-${aProduct.id}" value="${aProduct.qtyonhand}"/>

but that looks like id="qtyonhand-3447" or something like that. From the design point of view I don't know why it's done that way, since you already have the product ID in the previous <input> element.

Edit: Sorry, yes I do know why it's done that way. It's in a loop where you might have many of those product/quantityonhand pairs. How to fix the problem? Well, you didn't do that same thing with productID and qtysold, so you're going to have multiple entries with those names. And you should use "name=..." rather than "id=..." to name parameters. Once you get the JSP straightened out to write product ID, qty sold, price, and qty on hand in a consistent way you then need to put a corresponding loop in your servlet to go through the input parameters and update each of the product records accordingly. That might be harder than it looks.