E/SQLiteLog﹕ (1) near "Table": syntax error

Question

I'm trying to start with SQLite in android but I have some problems..
I took the code from a tutorial which was written in 2012, but it's not working for me now and shows me this error:

E/SQLiteLog﹕ (1) near "Table": syntax error

The problem is with creating/opening the Database.

package db.com.example.kids1.databasetest;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;

import org.w3c.dom.Text;

import java.io.IOException;
import java.util.ArrayList;

public class MainActivity extends ListActivity{

    private final String DB_NAME = "Database";
    private final String TABLE_NAME = "Table";
    SQLiteDatabase DB = null;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        ArrayList<String> results = new ArrayList<>();

        String[] res = {"Red", "Green", "Text"};



        try {
            DB = this.openOrCreateDatabase(DB_NAME, MODE_PRIVATE, null);
            DB.execSQL("CREATE TABLE IF NOT EXISTS " +
                TABLE_NAME +
                "(Name VARCHAR, Street VARCHAR, Block INT, City VARCHAR, Tel VARCHAR);");
            mFillDbsTable();

            Cursor c = DB.rawQuery("SELECT Name, Street, Block, City, Tel FROM " +
                    TABLE_NAME +
                    " where Blcok == 9 LIMIT 5", null);
            if (c!=null){

                if (c.moveToFirst()) {
                    do {

                        String name = c.getString(c.getColumnIndex("Name"));
                        String street = c.getString(c.getColumnIndex("Street"));
                        int block = c.getInt(c.getColumnIndex("Block"));
                        String city = c.getString(c.getColumnIndex("City"));
                        String tel = c.getString(c.getColumnIndex("Tel"));

                        results.add(name + "," + street + "," + block + "," + city + "," + tel);
                    } while (c.moveToNext());
                }
            }

            ListView list = (ListView)findViewById(android.R.id.list);


            ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, res);

            list.setAdapter(adapter);

        } catch (SQLiteException se){
            Log.e(getClass().getSimpleName(), "Create/Open Database Problem.");
        }

    }

    private void mFillDbsTable(){

        try {
            DB.execSQL("INSERT INTO " +
                    TABLE_NAME +
                    " Values('Noam', 'Shkolnik', 9, 'Rehovot', '054-4900807');");

            DB.execSQL("INSERT INTO " +
                    TABLE_NAME +
                    " Values('Eyal', 'Shkolnik', 9, 'Rehovot', '055-4488779');");

            DB.execSQL("INSERT INTO " +
                    TABLE_NAME +
                    " Values('Yehontan', 'Shkolnik', 9, 'Rehovot', '058-7789547');");
        } catch (SQLiteException se) {
            Log.e(getClass().getSimpleName(), "Could not create records.");
        }

    }
}

Answer 1

You got couple of errors. First, do not name your table Table. Table is reserved word (see docs) and cannot be used directly. It's basically not recommended to use reserved words, so I suggest you change your table name, yet if you insist of using it, you need to quote it first:

If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:

'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.

BTW: your further select query will fail too due to typo in where clause:

where Blcok == 9

Answer 2

private final String TABLE_NAME = "Table"

You can't create a table named Table, because it's a reserved keyword.
You better create a table named MyTable (or _Table or better give a more talkative name, such as Persons - note the s for plurals).

So:

private final String TABLE_NAME = "MyTable"

For your reference: https://sqlite.org/lang_keywords.html

---

You could (but it's not recommended) use reserved keywords, but then you have to use special delimiters everytime you refer to that table.

Something like

private final String TABLE_NAME = "[Table]"

---

And there's also another (double) error in your query:

" where Blcok == 9 LIMIT 5"

Should be

" where Block = 9 LIMIT 5"

Answer 3

Try VARCHAR(100) and remove the trailing ;