error inserting and executing query in android sqlite database

Question

so in my app i am using get and set methods. when it executes the set method, the app doesnt crash but when it gets to the get method, it crashes. i worked out that it crashes when it gets to the db.query() line in the get method but i think the problem could also be in the set method since they both give error messages.

here is my code:

public class MyDBHandler extends SQLiteOpenHelper {

public static final int DATABASE_VERSION = 1;
public static final String COLUMN_ID = "_id";
public static final String TABLE_VALUES = "values";
public static final String COLUMN_TARGET = "target";
public static final String COLUMN_CURRENT = "current";

public MyDBHandler(Context context){

    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {

    String query1 = "CREATE TABLE " + TABLE_VALUES + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            COLUMN_TARGET + " INT, " +
            COLUMN_CURRENT + " INT);";

    db.execSQL(query1);


}

@Override
public void onUpgrade(SQLiteDatabase db, int oldversion, int newversion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_VALUES);
    onCreate(db);
}


public void setTarget(int tar){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_TARGET, tar);
        values.put(COLUMN_CURRENT, 0);
        db.insert(TABLE_VALUES, null, values);
        db.close();

}


public int getTarget() {
   SQLiteDatabase db = this.getReadableDatabase();
   int targetVal=0;

String[] columns = {COLUMN_TARGET};
Cursor c = db.query(TABLE_VALUES, columns, null, null, null, null, String.valueOf(1));


if(c.getCount()>0)
    {
        c.moveToFirst();

        targetVal=c.getInt(0);
    }


   db.close();
    c.close();
    return targetVal;
}

}

the set function is used here:

public class home extends AppCompatActivity {

MyDBHandler db;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_home);

    db = new MyDBHandler(this);

}

public void enter(View view)
{
    EditText target = (EditText) findViewById(R.id.target);
    TextView warning = (TextView) findViewById(R.id.warning);

    String check = target.getText().toString();
    if(check.equals("")) {
        // Intent a = new Intent(this, MainActivity.class);
        //startActivity(a);
        warning.setText("Please enter a value");
        return;
    }
    else {
        int input = Integer.parseInt(check);
        Log.d("tag", "111\n");
        db.setTarget(input);
        Log.d("tag", "222\n");
        //int i = db.getTarget();

        //Log.d("tag","input is " + i );


        Intent a = new Intent(this, MainActivity.class);
        startActivity(a);

    }




}


}

the get function is used here:

public class MainActivity extends AppCompatActivity {

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
}

public void onTrack(View view){
    Log.d("tag", "traaaack\n");
    Intent i = new Intent(this, Track.class);
    startActivity(i);
}

}

and here are the error messages parts from the log.

this is when the set method is called:

E/SQLiteLog: (1) near "values": syntax error E/SQLiteDatabase: Error inserting current=0 target=2000 android.database.sqlite.SQLiteException: near "values": syntax error (code 1): , while compiling: INSERT INTO values(current,target) VALUES (?,?)

this is when the get method is called:

E/SQLiteLog: (1) near "values": syntax error E/AndroidRuntime: FATAL EXCEPTION: main java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.user.calorietracker/com.example.user.calorietracker.Track}: android.database.sqlite.SQLiteException: near "values": syntax error (code 1): , while compiling: SELECT target FROM values LIMIT 1

I have tried so many things to fix it but cant work out what i am doing wrong. literally any suggestion would be helpful! thanks

Answer 1

According to the list of SQLite Keywords - the word values is one of the words that you can't use for the name of your table (or column or many other things).

There are several ways to fix this:

1. Don't use this word. You can always use tbl_values for example.
2. You can quote that word:

'keyword'         A keyword in single quotes is a string literal.
"keyword"       A keyword in double-quotes is an identifier.
[keyword]       A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
`keyword`       A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.