Android sqlite insert record if not exist

Question

I want to add a new element (cheese) to the sqlite table, but only if it does not exist. I have two columns only in the table: _id (KEY_ROWID_PR) and product_name (KEY_NAME_PR).

I try with this code, but it gives me an error:

public void populate_base_lists2(String tablename, String item) { ourDatabase.execSQL("INSERT INTO " + tablename + " (" + KEY_NAME_PR + ") SELECT * FROM (SELECT " + item + ") WHERE NOT EXISTS (SELECT " + KEY_NAME_PR + " FROM " + tablename + " WHERE " + KEY_NAME_PR + " = " + item + ") LIMIT 1;"); } 

03-11 17: 27: 20.972: E / AndroidRuntime (658): called: android.database.sqlite.SQLiteException: there is no such column: Cheese: INSERT INTO PR_Dairy_Products (product_name) SELECT * FROM (SELECT Cheese WHERE DO NOT EXIST (SELECT product_name FROM PR_Dairy_Products WHERE product_name = Cheese) LIMIT 1;

Code from here: MySQL: insert a record if it does not exist in the table

there is no such column: Cheese, ok, I changed this part to KEY_NAME_PR, then the error was the same, but without such a column: product_name. This column definitely exists.

Moreover, I need to clarify this code. I have sql knowledge at a certain level, but I cannot get it.

thanks

Answer 1

I think you may need single quotes around the variable element whenever it appears, because this value is not a column name. Try this code:

 public void populate_base_lists2(String tablename, String item) { ourDatabase.execSQL("INSERT INTO " + tablename + " (" + KEY_NAME_PR + ") SELECT * FROM (SELECT '" + item + "') WHERE NOT EXISTS ( SELECT " + KEY_NAME_PR + " FROM " + tablename + " WHERE " + KEY_NAME_PR + " = '" + item + "' ) LIMIT 1;"); } 

Hope this helps.