E/SQLiteLog(1)近“表”:语法错误。

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:

我想从安卓的SQLite开始，但是我有一些问题。我从2012年写的一篇教程中学习了代码，但现在它对我不起作用，并向我展示了这个错误:

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

E/SQLiteLog(1)近“表”:语法错误。

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.");
        }

    }
}

3 个解决方案

#1

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

您不能创建一个名为table的表，因为它是一个保留的关键字。您最好创建一个名为MyTable的表(或_Table，或者更好地给出一个更健谈的名称，比如person -注意复数的s)。

So:

所以:

private final String TABLE_NAME = "MyTable"

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

供你方参考: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"

#2

Try VARCHAR(100) and remove the trailing ;

尝试VARCHAR(100)并删除拖尾;

#3

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:

如果您想要使用一个关键字作为名称，您需要引用它。在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.

[关键词]在方括号内的关键字是标识符。这不是标准的SQL。该引用机制被MS Access和SQL Server使用，并被包含在SQLite中以实现兼容性。

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.

关键字一个关键字(ASCII码96)是一个标识符。这不是标准的SQL。这个引用机制是MySQL使用的，并被包含在SQLite中以实现兼容性。

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

顺便说一下，您的进一步选择查询也会失败，因为在where子句中键入:

where Blcok == 9

#1

private final String TABLE_NAME = "Table"

So:

所以:

private final String TABLE_NAME = "MyTable"