java.sql.SQLException: no such column: after accepting sql query

Question

I am developing a system that manages stock for a project and am trying to get a list of stock items that are close to their expiration date

This is what the table is created as:

Query_Statement.executeUpdate("CREATE TABLE STOCK_PURCHASE ( SP_ID INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + "USER_ID INTEGER,STOCK_ID INTEGER,SUPPLIER_ID INTEGER,SP_ENTRY_DATE TEXT,"
                    + "SP_PURCHASE_PRICE REAL, SP_SELLBY_DATE TEXT, SP_QUANTITY REAL,"
                    + "FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID),"
                    + "FOREIGN KEY (STOCK_ID) REFERENCES STOCK (STOCK_ID),"
                    + "FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (SUPPLIER_ID))");

This is the function to get that I use:

public ArrayList<String> GetExpiredStock(){
    Connection dbConnection = null;
    ResultSet list = null;
    ArrayList<String> ls = new ArrayList<String>();

    LocalDate currentDate = LocalDate.now();
    //System.out.println(today);
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd/MM/yyyy");

    try {
        dbConnection = DriverManager.getConnection("jdbc:sqlite:"+dbName+".db");

        Statement Query_Statement = dbConnection.createStatement();
        Query_Statement.setQueryTimeout(10);

        list = Query_Statement.executeQuery("SELECT SP_ENTRY_DATE, STOCK_ID FROM STOCK_PURCHASE"); //this works

        while (list.next()) {
            try {
            LocalDate expDate = LocalDate.parse(list.getString("SP_SELLBY_DATE"), formatter);
            LocalDate monthAway = expDate.minusMonths(1);
            System.out.println(currentDate);
            if(currentDate.isAfter(monthAway)) {
                int id = list.getInt("STOCK_ID");
                ResultSet ids = Query_Statement.executeQuery("SELECT STOCK_NAME FROM STOCK WHERE STOCK_ID=" + id);

                ls.add(ids.getString("STOCK_NAME") + "\t\t" + 
                        list.getString("SP_SELLBY_DATE") + getStockQuant(list.getInt("STOCK_ID"), 
                                currentDate));

            }
            }catch(SQLException e) {
                System.err.println(e);
                continue;


            }
        }

    } catch (SQLException e) {
        System.err.println(e.getMessage());
    } finally {
        try {
            if (dbConnection != null)
                dbConnection.close();
        } catch (SQLException e) {
            System.err.println(e);
        }
    }

    return ls;
}

I expect it to get the expiration date. However it keeps saying:

java.sql.SQLException: no such column: 'SP_SELLBY_DATE'

Edit:

I changed the code to look like this:

    public ArrayList<String> GetExpiredStock(){
    Connection dbConnection = null;
    ResultSet list = null;
    ArrayList<String> ls = new ArrayList<String>();

    LocalDate currentDate = LocalDate.now();
    //System.out.println(today);
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd/MM/yyyy");

    try {
        dbConnection = DriverManager.getConnection("jdbc:sqlite:"+dbName+".db");

        Statement Query_Statement = dbConnection.createStatement();
        Query_Statement.setQueryTimeout(10);

        list = Query_Statement.executeQuery("SELECT SP_SELLBY_DATE, STOCK_ID FROM STOCK_PURCHASE"); //this works

        while (list.next()) {
            try {
            String da = list.getString("SP_SELLBY_DATE");
            int id = list.getInt("STOCK_ID");
            System.out.println("Executed on id = " + id);
            LocalDate expDate = LocalDate.parse(da, formatter);
            LocalDate monthAway = expDate.minusMonths(1);
            System.out.println(currentDate);
            if(currentDate.isAfter(monthAway)) {

                ResultSet ids = Query_Statement.executeQuery("SELECT STOCK_NAME FROM STOCK WHERE STOCK_ID=" + id);

                ls.add(ids.getString("STOCK_NAME") + "\t\t" + 
                        da + "\t\t"+  getStockQuant(id, 
                                currentDate));

            }
            }catch(SQLException e) {
                System.err.println(e);
                continue;


            }
        }

    } catch (SQLException e) {
        System.err.println(e.getMessage());
    } finally {
        try {
            if (dbConnection != null)
                dbConnection.close();
        } catch (SQLException e) {
            System.err.println(e);
        }
    }

    return ls;

But it still fails after the first iteration

Michael Laffargue · Accepted Answer · 2019-05-29 21:11:33Z

2

Your SQL query doesn't have the required column, add it :

SELECT SP_ENTRY_DATE, STOCK_ID, SP_SELLBY_DATE FROM STOCK_PURCHASE

answered May 29, 2019 at 21:11

Michael Laffargue

10.3k6 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Qingfei Yuan · Accepted Answer · 2019-05-29 21:13:38Z

0

I believe you query it list.getString("SP_SELLBY_DATE") twice in your loop, that should be root cause. Instead, you should use a variable when you get it to avoid to call it again as cursor has changed.

answered May 29, 2019 at 21:13

Qingfei Yuan

1,2121 gold badge9 silver badges12 bronze badges

1 Comment

Joshua Greyling Over a year ago

Thanks but it still fails after the first iteration

Collectives™ on Stack Overflow

java.sql.SQLException: no such column: after accepting sql query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related