SQLite - Database Helper problem

droozen · by **droozen** » Thu Feb 26, 2009 7:58 pm

So, I'm using nearly the exact same code from the Notepad tutorial, modified slightly for the database to contain the data I need. Here's my database helper class:

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

 
    private static final String DATABASE_CREATE =
 
            "create table hours (_id integer primary key autoincrement, "
 
                    + "day text not null, hour integer not null, "
 
                    + "minutes integer not null);";
 
    private static final String DATABASE_NAME = "data";
 
    private static final String DATABASE_TABLE = "hours";
 
    private static final int DATABASE_VERSION = 2;
 
    private static class DatabaseHelper extends SQLiteOpenHelper {
 
        DatabaseHelper(Context context) {
 
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
 
        }
 
        @Override
 
        public void onCreate(SQLiteDatabase db) {
 
            db.execSQL(DATABASE_CREATE);
 
        }
 
        @Override
 
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
 
                    + newVersion + ", which will destroy all old data");
 
            db.execSQL("DROP TABLE IF EXISTS notes");
 
            onCreate(db);
 
        }
 
    }
 
Parsed in 0.039 seconds,  using GeSHi 1.0.8.4

My open function I call:

Syntax: [ Download ] [ Hide ]

Using java Syntax Highlighting

 
public HoursDbAdapter open() throws SQLException {
 
        mDbHelper = new DatabaseHelper(mCtx);
 
        mDb = mDbHelper.getWritableDatabase();
 
        return this;
 
    }
 
Parsed in 0.040 seconds,  using GeSHi 1.0.8.4

It's my understanding that DatabaseHelper.getWritableDatabase() will run my create database statement, if the database doesn't exist yet. However, when I try to fetch any rows after opening it here, I get this error:

no such table: hours: , while compiling: SELECT _id, day, hour, minutes FROM hours

Am I missing something? (Probably simple and obvious, if I am. :S)

MrSnowflake · by **MrSnowflake** » Thu Feb 26, 2009 11:22 pm

Are you sure the database/table is successfully created? Maybe the name of the database needs to end on .db?

And if you followed the Notepad tut, why didn't you make the creation of the table the same as the NotePadProvider.java? (meaning, using the constants for the table and column name, instead of using literals.)

droozen · by **droozen** » Thu Feb 26, 2009 11:35 pm

Thanks for your reply.

I looked at the database and the table was created... with the name of notes. I must have ran the app once, then noticed I still had the name of notes on the table, changed the name, and then the getWriteableDatabase function wouldn't create a new table. Once I deleted the previous table, the code ran correctly.

And actually, I did use constants like the tut, I just didn't copy them here. (The tut didn't use constants in the create table string, but I should make sure to make that upgrade, as well!)

For any other developers encountering a similar problem, to look at/delete the old database from Eclipse, go to Window > Open Perspective > Other... Choose DDMS. Make sure the emulator is running then, in the DDMS view, go to File Explorer: /data/data/<your project package>/database/<your database> You can then pull your database using Eclipse to an external file and use a SQLite Database Browser application to look at the database. You can delete it from your device from Eclipse.

I found this solution by extensive Googling. I think looking at the device files should be part of those starting tuts. Definitely helpful!

MrSnowflake · by **MrSnowflake** » Thu Feb 26, 2009 11:49 pm

When you changed the name of the table, did you change the database version? I think you didn't and that's what the problem comes from!

droozen · by **droozen** » Thu Feb 26, 2009 11:53 pm

Oh, thanks! No, I didn't change the database version. (The app is still in pre-pre-Alpha stage, so I didn't think of it.)

Thanks. I guess I didn't realize it. It was like, OH! Typo! Fix move on. Forget it.

droozen · by **droozen** » Fri Feb 27, 2009 6:58 pm

Well, while we're on my sqlite problems, I can't seem to get two tables on the same database!

I have two DbAdapter classes that use SQLiteOpenHelper like the Notepad tuts. I even made them both use the same sql for database create:

Code: Select all: "create table hours (_id integer primary key autoincrement, " + "day text not null, hour integer not null, " + "minutes integer not null); " + "" + "create table quickbuild (_id integer primary key autoincrement, " + "day text not null, hour integer not null, " + "minutes integer not null); n";

Yes, they are very similar tables and I should probably just use a flag field to differentiate them, but that's not the point, at the moment. In the future I will need more than one table. However, putting the above sql in both of my adapter's create statements only gets the hours table created, not the quickbuild table. If I give each their own database create sql (one adapter creates hours, the other creates quick build) but the same database name... that doesn't seem to work either, even working off a fresh install. My other thought was that the ; in the sql above terminates the sql and the rest doesn't execute, but I removed it and that didn't work either. Then I tried a begin tran foo; at the beginning and a commit tran foo; at the end, but that didn't work, either.

Can I only have one table per database???

FQsupp · by **FQsupp** » Fri Feb 27, 2009 11:09 pm

Only one SQL statement per .execSQL() call. Android doc mentions this.

droozen · by **droozen** » Fri Feb 27, 2009 11:14 pm

I see. So, I should put several execSql calls in my onCreate for my DatabaseHelper classes, is all. Thanks!

SQLite - Database Helper problem

SQLite - Database Helper problem

Who is online