SQLite - Database Helper problem

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

SQLite - Database Helper problem

Postby 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
  1.  
  2.     private static final String DATABASE_CREATE =
  3.  
  4.             "create table hours (_id integer primary key autoincrement, "
  5.  
  6.                     + "day text not null, hour integer not null, "
  7.  
  8.                     + "minutes integer not null);";
  9.  
  10.  
  11.  
  12.     private static final String DATABASE_NAME = "data";
  13.  
  14.     private static final String DATABASE_TABLE = "hours";
  15.  
  16.     private static final int DATABASE_VERSION = 2;
  17.  
  18.  
  19.  
  20.     private static class DatabaseHelper extends SQLiteOpenHelper {
  21.  
  22.  
  23.  
  24.         DatabaseHelper(Context context) {
  25.  
  26.             super(context, DATABASE_NAME, null, DATABASE_VERSION);
  27.  
  28.         }
  29.  
  30.  
  31.  
  32.         @Override
  33.  
  34.         public void onCreate(SQLiteDatabase db) {
  35.  
  36.             db.execSQL(DATABASE_CREATE);
  37.  
  38.         }
  39.  
  40.  
  41.  
  42.         @Override
  43.  
  44.         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  45.  
  46.             Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
  47.  
  48.                     + newVersion + ", which will destroy all old data");
  49.  
  50.             db.execSQL("DROP TABLE IF EXISTS notes");
  51.  
  52.             onCreate(db);
  53.  
  54.         }
  55.  
  56.     }
  57.  
  58.  
Parsed in 0.035 seconds, using GeSHi 1.0.8.4


My open function I call:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. public HoursDbAdapter open() throws SQLException {
  3.  
  4.         mDbHelper = new DatabaseHelper(mCtx);
  5.  
  6.         mDb = mDbHelper.getWritableDatabase();
  7.  
  8.         return this;
  9.  
  10.     }
  11.  
  12.  
Parsed in 0.031 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)
droozen
Developer
Developer
 
Posts: 45
Joined: Wed Feb 25, 2009 10:10 pm

Top

Postby 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.)
User avatar
MrSnowflake
Moderator
Moderator
 
Posts: 1439
Joined: Sat Feb 16, 2008 3:11 pm
Location: Flanders, Belgium

Postby 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!
droozen
Developer
Developer
 
Posts: 45
Joined: Wed Feb 25, 2009 10:10 pm

Postby 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!
User avatar
MrSnowflake
Moderator
Moderator
 
Posts: 1439
Joined: Sat Feb 16, 2008 3:11 pm
Location: Flanders, Belgium

Postby 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
Developer
Developer
 
Posts: 45
Joined: Wed Feb 25, 2009 10:10 pm

Postby 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???
droozen
Developer
Developer
 
Posts: 45
Joined: Wed Feb 25, 2009 10:10 pm

Top

Postby FQsupp » Fri Feb 27, 2009 11:09 pm

Only one SQL statement per .execSQL() call. Android doc mentions this.
FQsupp
Freshman
Freshman
 
Posts: 4
Joined: Tue Dec 23, 2008 5:02 am
Location: Ottawa ON

Postby 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!
droozen
Developer
Developer
 
Posts: 45
Joined: Wed Feb 25, 2009 10:10 pm

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 5 guests