Prepared Statement SQL Exception "No Input Parameters"

Question

so I'm trying to retrieve data from a DB. Maybe I have looked at it too much but I can't find the problem.

Connection is fine, Statement is fine (works with static data), and data from the Item object is correct.

It's telling me there are no input parameters for the statement. The exception is being thrown in the getSelectPrepareStatement() method. The code is:

 public Result[] getItemsFromInput(Item item) throws SQLException {
    PreparedStatement statement;
    ArrayList<Result> results = new ArrayList<Result>();
    String query =  "SELECT Code1, Name, A.Code2 " +
                    "FROM ItemTable A " +
                    "INNER JOIN CategoryTable B " +
                    "ON A.CatCode = B.CatCode" +
                    "AND B.ID LIKE '?'";
    statement = getSelectPrepareStatement(query, item.getTail().getId()+"%");

    ...

    rs = queryForResultSet(statement);

    while(rs.next()) {
        results.add(new Result(
                rs.getString("ItemName"),
                rs.getInt("ItemCode"),
                rs.getString("ClassCode")));
    }

    return results.toArray(new Result[results.size()]);
}

Retrieving the PreparedStatement with set Values here:

private PreparedStatement getSelectPrepareStatement(String SQL, String data) throws SQLException {
    PreparedStatement pStmt = conn.prepareStatement(SQL);
    pStmt.setString(1, data);

    return pStmt;
}

The eexception is being thrown on the call to pStmt.setString(1, data); although I set the statement to have a paramenter.

The error is:

    java.sql.SQLException: No input parameters.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setString(Unknown Source)
        at connection.Communicator.getSelectPrepareStatement(Communicator.java:306)
        at connection.Communicator.getItemsFromInput(Communicator.java:56)
        at frame.PromotionsDialog$1.propertyChange(PromotionsDialog.java:126)
        at java.beans.PropertyChangeSupport.fire(PropertyChangeSupport.java:335)
        at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:328)
        at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:263)
        at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:303)
        at java.awt.Component.firePropertyChange(Component.java:8402)
        at javax.swing.JComponent.firePropertyChange(JComponent.java:4494)
        at frame.DataPanel$1.actionPerformed(DataPanel.java:130)
        at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
        at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
        at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
        at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
        at java.awt.Component.processMouseEvent(Component.java:6505)
        at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
        at java.awt.Component.processEvent(Component.java:6270)
        at java.awt.Container.processEvent(Container.java:2229)
        at java.awt.Component.dispatchEventImpl(Component.java:4861)
        at java.awt.Container.dispatchEventImpl(Container.java:2287)
        at java.awt.Component.dispatchEvent(Component.java:4687)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
        at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
        at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
        at java.awt.Container.dispatchEventImpl(Container.java:2273)
        at java.awt.Window.dispatchEventImpl(Window.java:2719)
        at java.awt.Component.dispatchEvent(Component.java:4687)
        at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:729)
        at java.awt.EventQueue.access$200(EventQueue.java:103)
        at java.awt.EventQueue$3.run(EventQueue.java:688)
        at java.awt.EventQueue$3.run(EventQueue.java:686)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
        at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
        at java.awt.EventQueue$4.run(EventQueue.java:702)
        at java.awt.EventQueue$4.run(EventQueue.java:700)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:699)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)
    Caused by: java.sql.SQLException: No input parameters.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 51 more
    Caused by: ERROR 07009: No input parameters.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.GenericParameterValueSet.checkPosition(Unknown Source)
        at org.apache.derby.impl.sql.GenericParameterValueSet.getParameterForSet(Unknown Source)
        ... 47 more

Answer 1

This is the problem, in your SQL.

AND B.ID LIKE '?'

That's not adding a parameter - that's specifying a value of ?, because it's in quotes. Basically, you're saying you want to find a row where B.ID is a single question mark.

You want:

AND B.ID LIKE ?

That way you're genuinely specifying a parameter.

Answer 2

No need of single quotes for ?

Try

String query =  "SELECT Code1, Name, A.Code2 " +
                    "FROM ItemTable A " +
                    "INNER JOIN CategoryTable B " +
                    "ON A.CatCode = B.CatCode" +
                    "AND B.ID LIKE ?%";
    statement = getSelectPrepareStatement(query, item.getTail().getId()+"");

Answer 3

Try removing the single quotes from around the bind parameter. That's what PreparedStatement does for you.

String query =  "SELECT Code1, Name, A.Code2 " +
                "FROM ItemTable A " +
                "INNER JOIN CategoryTable B " +
                "ON A.CatCode = B.CatCode" +
                "AND B.ID LIKE ?";