Android sqlite syntax error_问答_开发者_运维开发者技术经验分享

Hello my favorite help resource...

I am a average android programmer making an application designed to simply randomly draw a name from an array and display it... The issue in putting this app into a release version is that it needs to have editable strings stored in a database, so I naturally turned to android's implementation of sql lite. I'll be honest, I didn't know the first thing about it up until after looking through the source code of the example I found. I am using a modified version of that example to simply store a name (the string) and the ID of the string... I run into a syntax error, I will display the logcat data later in the question... any other things you may find in my code that may be wrong, or even some tips, would be greatly appreciated... Here comes the onslaught of code...

peopleDatabaseHelper

package com.b.wom.peopledatabase;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class peopleDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "applicationdata";

    private static final int DATABASE_VERSION = 1;

    // Database creation sql statement
    private static final String DATABASE_CREATE = "create table todo (_id integer primary key autoincrement, "
            + "name text not null);";

    public peopleDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Method is called during creation of the database
    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    // Method is called during an upgrade of the database, e.g. if you increase
    // the database version
    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion,
            int newVersion) {
        Log.w(peopleDatabaseHelper.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data");
        datab开发者_开发百科ase.execSQL("DROP TABLE IF EXISTS todo");
        onCreate(database);
    }
}

peopleDbAdapter

    package com.b.wom.peopledatabase;

    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;

    public class peopleDbAdapter {

        // Database fields
        public static final String KEY_ROWID = "_id";
        public static final String KEY_NAME = "name";
        private static final String DATABASE_TABLE = "table";
        private Context context;
        private SQLiteDatabase database;
        private peopleDatabaseHelper dbHelper;

        public peopleDbAdapter(Context context) {
            this.context = context;
        }

        public peopleDbAdapter open() throws SQLException {
            dbHelper = new peopleDatabaseHelper(context);
            database = dbHelper.getWritableDatabase();
            return this;
        }

        public void close() {
            dbHelper.close();
        }

        /**
         * Create a new todo If the todo is successfully created return the new
         * rowId for that note, otherwise return a -1 to indicate failure.
         */
        public long createPerson(String name) {
            ContentValues initialValues = createContentValues(name);

            return database.insert(DATABASE_TABLE, null, initialValues);
        }

        /**
         * Update the todo
         */
        public boolean updatePerson(long rowId, String name) {
            ContentValues updateValues = createContentValues(name);

            return database.update(DATABASE_TABLE, updateValues, KEY_ROWID + "="
                    + rowId, null) > 0;
        }

        /**
         * Deletes todo
         */
        public boolean deletePerson(long rowId) {
            return database.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
        }

        /**
         * Return a Cursor over the list of all people in the database
         * 
         * @return Cursor over all people
         */
        public Cursor fetchAllPeople() {
            return database.query(DATABASE_TABLE, new String[] { KEY_ROWID,
                    KEY_NAME }, null, null, null, null, null);
        }

        /**
         * Return a Cursor positioned at the defined person
         */
        public Cursor fetchPerson(long rowId) throws SQLException {
            Cursor mCursor = database.query(true, DATABASE_TABLE, new String[] {
                    KEY_ROWID, KEY_NAME }, KEY_ROWID + "=" + rowId, null, null,
                    null, null, null);
            if (mCursor != null) {
                mCursor.moveToFirst();
            }
            return mCursor;
        }

        private ContentValues createContentValues(String name) {
            ContentValues values = new ContentValues();
            values.put(KEY_NAME, name);
            return values;
        }
    }

peoplesetting (where the user enters in new people to add to the database)

package com.b.wom;

import com.b.wom.peopledatabase.peopleDbAdapter;

import android.app.Activity;
import android.content.Intent;
import android.content.SharedPreferences;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.Toast;

public class peoplesettings extends Activity {
    public TableRow tr;
    public EditText n;
    public int currentimage;
    public TableLayout tl;
    public Button add;
    public EditText et;
    public SharedPreferences s;
    private peopleDbAdapter database;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.peoplesettings);
        // sets up database
        database = new peopleDbAdapter(this);
        database.open();
        // Adds button for adding more people
        Button add = (Button) findViewById(R.id.add);
        // allocates the namebox
        Button pb = (Button) findViewById(R.id.peoplebutton);
        pb.setBackgroundColor(0x0106000d);
        pb.setOnClickListener(new OnClickListener(){

            @Override
            public void onClick(View v) {
                Intent myIntent = new Intent(peoplesettings.this, peopleListView.class);
                startActivity(myIntent);
            }

        });
        n = (EditText) findViewById(R.id.namebox);
        add.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {
                // Adds another person
                add();
            }
        });
    }

    void add() {
        // checks the text in the name box to make sure it isn't empty
        if (n.toString() == "") {
            Toast.makeText(this, "Please enter a name into the text box...",
                    2000).show();
        }
        if (n.toString() != "") {
            database.open();
            database.createPerson(n.getText().toString());
            n.setText("");
            Toast.makeText(this, "Person added...",
                    2000).show();
            database.close();
        }
    }

}

peopleListView(where the app displays all the people in the database)

package com.b.wom;

import com.b.wom.peopledatabase.peopleDbAdapter;

import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class peopleListView extends ListActivity {
    private Cursor cursor;
    private peopleDbAdapter database;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        updatelistview();
    }

    void updatelistview() {
        ListView lv = getListView();
        database.open();
        cursor = database.fetchAllPeople();
        startManagingCursor(cursor);
        String[] from = new String[] { peopleDbAdapter.KEY_NAME };
        int[] to = { R.id.person_name, R.id.person_num };
        // Now create an array adapter and set it to display using our row
        SimpleCursorAdapter people = new SimpleCursorAdapter(this,
                R.layout.person_textview, cursor, from, to);
        lv.setAdapter(people);
        database.close();
    }
}

Logcat data...

    08-24 09:08:59.056: ERROR/Database(872): Error inserting name=BB
08-24 09:08:59.056: ERROR/Database(872): android.database.sqlite.SQLiteException: near "table": syntax error: , while compiling: INSERT INTO table(name) VALUES(?);
08-24 09:08:59.056: ERROR/Database(872):     at android.database.sqlite.SQLiteProgram.native_compile(Native Method)
08-24 09:08:59.056: ERROR/Database(872):     at android.database.sqlite.SQLiteProgram.compile(SQLiteProgram.java:110)
08-24 09:08:59.056: ERROR/Database(872):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
08-24 09:08:59.056: ERROR/Database(872):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:41)
08-24 09:08:59.056: ERROR/Database(872):     at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1027)
08-24 09:08:59.056: ERROR/Database(872):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1413)
08-24 09:08:59.056: ERROR/Database(872):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1286)
08-24 09:08:59.056: ERROR/Database(872):     at com.b.wom.peopledatabase.peopleDbAdapter.createPerson(peopleDbAdapter.java:40)
08-24 09:08:59.056: ERROR/Database(872):     at com.b.wom.peoplesettings.add(peoplesettings.java:65)
08-24 09:08:59.056: ERROR/Database(872):     at com.b.wom.peoplesettings$2.onClick(peoplesettings.java:53)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.View.performClick(View.java:2364)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.View.onTouchEvent(View.java:4179)
08-24 09:08:59.056: ERROR/Database(872):     at android.widget.TextView.onTouchEvent(TextView.java:6541)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.View.dispatchTouchEvent(View.java:3709)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at com.android.internal.policy.impl.PhoneWindow$DecorView.superDispatchTouchEvent(PhoneWindow.java:1659)
08-24 09:08:59.056: ERROR/Database(872):     at com.android.internal.policy.impl.PhoneWindow.superDispatchTouchEvent(PhoneWindow.java:1107)
08-24 09:08:59.056: ERROR/Database(872):     at android.app.Activity.dispatchTouchEvent(Activity.java:2061)
08-24 09:08:59.056: ERROR/Database(872):     at com.android.internal.policy.impl.PhoneWindow$DecorView.dispatchTouchEvent(PhoneWindow.java:1643)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
08-24 09:08:59.056: ERROR/Database(872):     at com.android.internal.policy.impl.PhoneWindow$DecorView.superDispatchTouchEvent(PhoneWindow.java:1659)
08-24 09:08:59.056: ERROR/Database(872):     at com.android.internal.policy.impl.PhoneWindow.superDispatchTouchEvent(PhoneWindow.java:1107)
08-24 09:08:59.056: ERROR/Database(872):     at android.app.Activity.dispatchTouchEvent(Activity.java:2061)
08-24 09:08:59.056: ERROR/Database(872):     at com.android.internal.policy.impl.PhoneWindow$DecorView.dispatchTouchEvent(PhoneWindow.java:1643)
08-24 09:08:59.056: ERROR/Database(872):     at android.view.ViewRoot.handleMessage(ViewRoot.java:1691)
08-24 09:08:59.056: ERROR/Database(872):     at android.os.Handler.dispatchMessage(Handler.java:99)
08-24 09:08:59.056: ERROR/Database(872):     at android.os.Looper.loop(Looper.java:123)
08-24 09:08:59.056: ERROR/Database(872):     at android.app.ActivityThread.main(ActivityThread.java:4363)
08-24 09:08:59.056: ERROR/Database(872):     at java.lang.reflect.Method.invokeNative(Native Method)
08-24 09:08:59.056: ERROR/Database(872):     at java.lang.reflect.Method.invoke(Method.java:521)
08-24 09:08:59.056: ERROR/Database(872):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
08-24 09:08:59.056: ERROR/Database(872):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
08-24 09:08:59.056: ERROR/Database(872):     at dalvik.system.NativeStart.main(Native Method)

Thanks a ton for whatever you can provide,

Cheers...

The database insertion code is this:
INSERT INTO table(name) VALUES(?)
So, you just miss the variable name "TABLE_NAME". You declared your table as todoand use it as table.
Just put:
private static final String DATABASE_TABLE = "todo";in peopleDBAdapter.