0

I am trying to take relevant values from an existing table and put them into a Virtual table, which I can then search through and get the entries I want.

I have everything more or less working except the actual loading of the entries into this Virtual Table via the program. I have tested these statements to the best of my ability inside of sqliteManager and they seem to have no issues, but when I try to execute them through the program, nothing happens, and stmt.execute() returns false.

Code in question:

public void getResults() {

    String sqlGetData = "INSERT INTO VirtualProductTable SELECT * FROM 
    H4HProductTable WHERE id = ? AND price <= ?" + 
         " AND length <= ? AND width <= ? AND height <= ?";

    switch (((Categories)this.categorySelect.getValue()).toString()) {
    case "Furniture":
        sqlGetData = sqlGetData.concat(" AND isFurniture = 1");
        break;
    case "Appliances":
        sqlGetData = sqlGetData.concat(" AND isAppliance = 1");
        break;
    case "Building_Materials":
        sqlGetData = sqlGetData.concat(" AND isBuildingMaterial = 1");
        break;
    case "Tools":
        sqlGetData = sqlGetData.concat(" AND isTool = 1");
        break;
    default:
        break;
    }

    // corrects for empty/unimportant fields //
    try {
        Connection conn = DBConnection.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sqlGetData);

        if(this.idSearch.getText().equals(null) || 
        this.idSearch.getText().isEmpty()) {
            stmt.setString(1, "id");
        } else {
            stmt.setString(1, this.idSearch.getText());
        }

        if(this.priceSearch.getText().equals(null) || 
        this.priceSearch.getText().isEmpty()) {
            stmt.setString(2, "price");
        } else {
            stmt.setString(2, this.priceSearch.getText());
        }

        if(this.lengthSearch.getText().equals(null) || 
        this.lengthSearch.getText().isEmpty()) {
            stmt.setString(3, "length");
        } else {
            stmt.setString(3, this.lengthSearch.getText());
        }

        if(this.widthSearch.getText().equals(null) || 
        this.widthSearch.getText().isEmpty()) {
            stmt.setString(4, "width");
        } else {
            stmt.setString(4, this.widthSearch.getText());
        }

        if(this.heightSearch.getText().equals(null) || 
        this.heightSearch.getText().isEmpty()) {
            stmt.setString(5, "height");
        } else {
            stmt.setString(5, this.heightSearch.getText());
        }

        if(!stmt.execute()) {
            System.out.println("insert statement not executed");
        }
        stmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

this is then called by the searchProduct() function below:

public void searchProduct (ActionEvent event) throws Exception {
    // makes sure we are not just piling entry after entry every consecutive search //

    String drop = "DELETE FROM VirtualProductTable";
    PreparedStatement stmt = this.newSearchModel.connection.prepareStatement(drop);
    stmt.execute();
    stmt.close();


    // 1. Creates a virtual table and inserts all entries of the category we're looking for, that are also within our other criteria 
    // 2. Sort that table into a list and print out the fields we need row by row 
    getResults();
    showFirstSix();
}

Now, I know that my program is at the very least connected to my database, as the "DELETE FROM VirtualProductTable" statement works fine (I've tested it by having already manually entered items in the table in question).

The only other thing that I could possibly think might be causing this issue is that my stmt.setString(...)s aren't working properly, but I don't know how to properly test those and see what values are actually being taken.

I should make it clear, I am not receiving any actual error message from eclipse, so I think I am syntactically sound statement-wise.

Samuel Ludwig
  • 111
  • 1
  • 7
  • Your code is ripe for SQL injection; you should really use something that sanitizes the input to the parameters to the query (or use a real parameterized query vs. string substitution). Also your VirtualProductTable usage means only use user can do this at any given time. Why put it in a table vs. just returning the results of the SELECT? – Joe May 08 '18 at 15:40
  • Hi Joe, pardon if I say something blatantly senseless, this is actually part of a much larger school project, and I'm still trying to learn more about SQL stuff. The purpose for the Virtual table is the ability to sort via rank using FTS5, (thats used in the showFirstSix() function, but that actually works quite well thankfully, though it was difficult to figure out at first). As for the SQL injection stuff, it is something I wanted to address later, I'm trying however to just get the actual skeleton working first. – Samuel Ludwig May 08 '18 at 15:46
  • Result of `PreparedStatement.execute()` shows whether underlying SQL query returns `ResultSet` or update count. It doesn't show whether actual statement was executed or not. Have you checked table after insert or just checked result of `stmt.execute()`? – Ivan May 08 '18 at 15:49
  • Hi Ivan, I have checked the table as well, and no changes are being made to it via this statement. – Samuel Ludwig May 08 '18 at 15:53
  • So what have you tested with sqliteManager? `id = id` or `id = 'id'`? – Edwardth May 08 '18 at 15:57
  • Hi Edwardth, I want to think I tested it with id = id, but I'm honestly uncertain, I'll need to check when I get back home tonight, what I do know is that whatever I tested it with in sqliteManager did in-fact work, and inserted the tables i was trying to insert. – Samuel Ludwig May 08 '18 at 16:04
  • 1
    Based on your usage of the `PreparedStatement` I would assume that your "price", "length", "width", and "height" values are of the `VARCHAR` (or `TEXT` in SQLite) datatype. However, the names of these columns would suggest some kind of number based datatype. For instance, when there's no input you have things like `stmt.setString(2, "price")`. This means that part of the query will be `price <= 'price'`. I'm wondering if this is what you intended? – Slaw May 08 '18 at 16:10
  • Hmm, ok, I think I see what your getting at! So, the intent is that if the user does not specify a price (etc.) that they want, that specific field will no longer be exclusionary. In short, I AM going for `price <= price` (WHERE `theValueOfPrice <= theValueOfPrice`, more explicitly). So the remedy should be to change `stmt.setString(2, "price")` to `stmt.setString(2, 'price')`? Or is there a better notation? Regardless, I'll certainly check when I get home tonight, I really hope so, this thing's been plaguing me for _days_! – Samuel Ludwig May 08 '18 at 16:25
  • 1
    Assuming price is a decimal number (I believe SQLite calls this datatype `REAL`) then the correct method would be `stmt.setDouble(2, 0.0)` where `0.0` is whatever value you want price to be less than or equal to. In the end, the criteria regarding the price of this query would look like: `price <= 0.0`. Make sure you set the parameter to the correct type and choose an appropriate default value when the value is not specified – Slaw May 08 '18 at 16:42
  • Hm, I'm curious if there's a way to explicitly reference the actual value of the relevant column instead, as in, to get the aforementioned plain-text `price`, your suggestion would work for all fields save for the `id`, I am surprised SQL parses these statements like so – Samuel Ludwig May 08 '18 at 16:54
  • You already concatenate your SQL query in `switch` statement. So you'd better do the same for all other filters in `WHERE` clause. In this case you do not need to find default values for missing parameters – Ivan May 08 '18 at 18:08

0 Answers0