CODEWITHSUNDEEP
javasql
deanTheBean
deanTheBean

Reputation: 47

How to process ResultSet you know has only one record in it

I'm struggling with a homework assignment and am getting hung up on some SQL queries.

My query is interrogating an inventory database for the quantity of some item. The query requests the column with the name quantity_in_stock from the table, given the primary key.

I have initialized some prepared statements. This is the one I'm using here:

stmtFindColumn = Database.getConnection().prepareStatement(String.format("select ? from %s where %s = ?",
            INVENTORY_TABLE_NAME, SKU) );

Now a separate method is called. I pass it a static const QTY_IN_STOCK, which is defined as "quantity_in_stock" and the item's SKU number, which is the primary key in the table.

private int getIntegerFromTable(String column, String key) {

    int toReturn = 0;

    try {
        // Complete the prepared statement 
        stmtFindColumn.setString(1, column);
        stmtFindColumn.setString(2, key);

        ResultSet result = stmtFindColumn.executeQuery();               
        toReturn = result.getInt(column);

    } catch (SQLException e) {
        LOG.error(e.getMessage());
        e.printStackTrace();
    }
    return toReturn;
}

When I run the query I get an sql exception that tells me: Invalid column name quantity_in_stock.

I have tried using a while loop processing result.next() and get the same error. I can't find any examples of how to properly get the results when you know only a single record is being returned.

Help!

EDIT: OK, I've found that part of my problem is I'm not getting a result set, where I should expect one. Any ideas?

UPDATE: I've tested my code, using a garden variety statement and a plain string query instead and it works just fine. So the problem is in my use of the prepared statement. Can someone check if I'm using the ? wildcards correctly? Thanks!

Upvotes: 1

Views: 647

Answers (2)

Jim Flood
Jim Flood

Reputation: 8467

DarkSquirrel42 is right -- you can't replace the column list of the select using a ? parameter marker. Instead, you can String.format that into place too, for example.

bad:

*select ? from INVENTORY_TABLE_NAME where SKU = ?

good:

select QUANTITY_IN_STOCK from INVENTORY_TABLE_NAME where SKU = ?

Upvotes: 2

DarkSquirrel42
DarkSquirrel42

Reputation: 10257

as far as i know, the column name may not be a parameter ...

Upvotes: 3

Related Questions