CODEWITHSUNDEEP
androidsqliteandroid-sqlite
sqlbuddy
sqlbuddy

Reputation: 79

SQLite problems on android - onCreate or insert

This chunk of my app is causing me some grief... either my table isn't getting created properly or the insert statements are out of line. I followed this tutorial which seemed to be well explained http://www.giantflyingsaucer.com/blog/?p=1342 but I can't seem to find the differences between my code and theirs. Any advice would be greatly appreciated!

Here is my MainActivity code:

private SQLiteIngAssistant sqlliteIngAssistant;

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

    sqlliteIngAssistant = new SQLiteIngAssistant(MainActivity.this);
    sqlliteIngAssistant.openDB();

    sqlliteIngAssistant.insertIng("Foo");
    String[] autofillIngs = sqlliteIngAssistant.getAllIngNames();

}

Here is my SQL assistant:

public class SQLiteIngAssistant extends SQLiteOpenHelper {
    private static final String DB_NAME = "usingsqlite.db";
    private static final int DB_VERSION_NUMBER = 1;
    private static final String DB_TABLE_NAME = "ings";
    private static final String DB_COLUMN_1_NAME = "ing_name";
    private static final String DB_COLUMN_2_NAME = "cons";

    private static final String DB_CREATE_SCRIPT = "create table " + DB_TABLE_NAME +
            " (_id string primary key " + DB_COLUMN_1_NAME + ", " + DB_COLUMN_2_NAME + " text not null);)";

    private SQLiteDatabase sqliteDBInstance = null;

    public SQLiteIngAssistant(Context context){
        super(context, DB_NAME, null, DB_VERSION_NUMBER);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
        // TODO: Implement onUpgrade
    }

    @Override
    public void onCreate(SQLiteDatabase sqliteDBInstance){
        Log.i("onCreate", "Creating the database...");
        sqliteDBInstance.execSQL(DB_CREATE_SCRIPT);
    }

    public void openDB() throws SQLException{
        Log.i("openDB", "Checking sqliteDBInstance...");
        if(this.sqliteDBInstance == null){
            Log.i("openDB", "Creating sqliteDBInstance...");
            this.sqliteDBInstance = this.getWritableDatabase();
        }
    }

    public long insertIng(String Name){
        ContentValues contentValues = new ContentValues();
        contentValues.put(DB_COLUMN_1_NAME, Name);
        return this.sqliteDBInstance.insert(DB_TABLE_NAME, null, contentValues);
    }


    public String[] getAllIngNames(){
        Cursor cursor = this.sqliteDBInstance.query(DB_TABLE_NAME, new String[] {DB_COLUMN_1_NAME}, null, null, null, null, null);

        if(cursor.getCount() >0){
            String[] str = new String[cursor.getCount()];
            int i = 0;

            while (cursor.moveToNext()){
                str[i] = cursor.getString(cursor.getColumnIndex(DB_COLUMN_1_NAME));
                i++;
            }
            return str;
        } else {
            return new String[] {};
        }
    }
}

Here is the stack trace:

01-11 19:23:04.382  14630-14630/com.example.ciltkick.nom E/SQLiteDatabase﹕ Error inserting ing_name=Foo
    android.database.sqlite.SQLiteException: table ings has no column named ing_name (code 1): , while compiling: INSERT INTO ings(ing_name) VALUES (?)
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:893)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:504)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1475)
            at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1347)
            at com.example.ciltkick.nom.SQLiteIngAssistant.insertIng(SQLiteIngAssistant.java:61)
            at com.example.ciltkick.nom.MainActivity.onCreate(MainActivity.java:65)
            at android.app.Activity.performCreate(Activity.java:5008)
            at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2035)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2096)
            at android.app.ActivityThread.access$600(ActivityThread.java:138)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1207)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:213)
            at android.app.ActivityThread.main(ActivityThread.java:4787)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:789)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:556)
            at dalvik.system.NativeStart.main(Native Method)
01-11 19:23:04.422  14630-14630/com.example.ciltkick.nom E/AndroidRuntime﹕ FATAL EXCEPTION: main
    java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.ciltkick.nom/com.example.ciltkick.nom.MainActivity}: android.database.sqlite.SQLiteException: no such column: ing_name (code 1): , while compiling: SELECT ing_name FROM ings
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2071)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2096)
            at android.app.ActivityThread.access$600(ActivityThread.java:138)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1207)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:213)
            at android.app.ActivityThread.main(ActivityThread.java:4787)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:789)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:556)
            at dalvik.system.NativeStart.main(Native Method)
     Caused by: android.database.sqlite.SQLiteException: no such column: ing_name (code 1): , while compiling: SELECT ing_name FROM ings
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:893)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:504)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1322)
            at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1169)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1040)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1208)
            at com.example.ciltkick.nom.SQLiteIngAssistant.getAllIngNames(SQLiteIngAssistant.java:89)
            at com.example.ciltkick.nom.MainActivity.onCreate(MainActivity.java:77)
            at android.app.Activity.performCreate(Activity.java:5008)
            at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2035)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2096)
            at android.app.ActivityThread.access$600(ActivityThread.java:138)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1207)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:213)
            at android.app.ActivityThread.main(ActivityThread.java:4787)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:789)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:556)
            at dalvik.system.NativeStart.main(Native Method)

Upvotes: 0

Views: 117

Answers (1)

laalto
laalto

Reputation: 152817

private static final String DB_CREATE_SCRIPT = "create table " + DB_TABLE_NAME +
        " (_id string primary key " + DB_COLUMN_1_NAME + ", " + DB_COLUMN_2_NAME + " text not null);)";

You're missing a , between primary key of the previous column def and the following DB_COLUMN_1_NAME.

After adding it there, uninstall your app to recreate the database.

I want DB_COLUMN_1_NAME to be the name of the primary key, how can I do that?

Change it to something like

private static final String DB_CREATE_SCRIPT = "create table " + DB_TABLE_NAME +
        " (" + DB_COLUMN_1_NAME + " string primary key," + DB_COLUMN_2_NAME + " text not null);)";

which also removes the primary key column _id you had before.

or can I access a row without using the primary key?

Yes, you can select rows by any criteria. Keep in mind though that if you have "a lot" of data and use a non-indexed column for lookups, the performance will be terrible. Primary key columns are automatically indexed.

Upvotes: 2

Related Questions