I'm trying to add some records to a SQLite table, but LogCat is telling me the table does not exist. And DDMS shows that, yes, that table is not being/has not been created.
Yet I do create the table in the SQLiteOpenHelper class:
public class SQLiteHandlerDeliveryItem extends SQLiteOpenHelper {
. . .
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String CREATE_DELIVERYITEMS_TABLE = "CREATE TABLE " +
TABLE_DELIVERYITEMS + "("
+ COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_INVOICENUM + " TEXT,"
+ COLUMN_UPCPLU + " TEXT," + COLUMN_VENDORITEMID + " TEXT,"
+ COLUMN_PACKSIZE + " INTEGER," + COLUMN_DESCRIPTION + " TEXT,"
//+ COLUMN_COST + " REAL," + COLUMN_MARGIN + " REAL," + COLUMN_LISTPRICE + " REAL,"
+ COLUMN_COST + " REAL DEFAULT 0," + COLUMN_MARGIN + " REAL DEFAULT 0," +
COLUMN_LISTPRICE + " REAL DEFAULT 0,"
+ COLUMN_DEPTNUM + " INTEGER," + COLUMN_SUBDEPT + " TEXT," + COLUMN_QTY + " TEXT"
+ ")";
sqLiteDatabase.execSQL(CREATE_DELIVERYITEMS_TABLE);
}
I call the class's method which adds records:
SQLiteHandlerDeliveryItem sqliteHandler = new SQLiteHandlerDeliveryItem(SQLiteActivity.this, null);
sqliteHandler.addDeliveryItem(delItem);
This should call SQLiteHandlerDeliveryItem's constructor (when sqliteHandler is instantiated), but it doesn't! I've got a breakpoint in the onCreate() method, and sure enough - it's never reached.
Why? And how can I force the constructor to be called, so that the table is created?
The odd[est] thing is that I also put a breakpoint in the other (working) SQLiteOpenHelper class, and it is also not reached...what?!? It worked at least once, as the table does exist/was created from that code.
So there's obviously a hole in my swing somehwere; what am I misunderstanding or doing wrong?
I marked the answer as THE answer too soon.
As to:
"1. You must call getWritableDatabase() or getReadableDatabase() at some point."
I do call getWritableDatabase() in each method that creates or reads records, like so:
public long addDeliveryItem(DeliveryItem delItem) {
long IdAdded = 0;
ContentValues values = new ContentValues();
values.put(COLUMN_INVOICENUM, delItem.get_invoiceNumber());
. . .
values.put(COLUMN_QTY, delItem.get_quantity());
SQLiteDatabase db = this.getWritableDatabase(); <= Rot Cheer
if (db != null) {
IdAdded = db.insert(TABLE_DELIVERYITEMS, null, values);
}
. . .
...and as regards:
"2. In your constructor for SQLiteHandlerDeliveryItem, you must call super(...)."
I do call super in the class that extends SQLiteOpenHelper:
public SQLiteHandlerDeliveryItem(Context context, SQLiteDatabase.CursorFactory factory)
{
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
So...I still don't know what the problem/solution is...
So what I see when attempting to insert a record into this table in LogCat is:
E/SQLiteLog﹕ (1) no such table: deliveryitems
Yet my code that attempts to add the record instantiates the corresponding/appropriate class that extends SQLiteOpenHelper like so:
else if ("Delivery Items".equals(tableName)) {
try {
JSONArray jsonArr = new JSONArray(result);
for (int i = 0; i < jsonArr.length(); i++) {
JSONObject jsonObj = jsonArr.getJSONObject(i);
String invNum = jsonObj.getString("invoiceNumber");
. . .
// Prepare for writing to db
DeliveryItem delItem = new DeliveryItem();
delItem.set_invoiceNumber(invNum);
. . .
SQLiteHandlerDeliveryItem sqliteHandler = new
SQLiteHandlerDeliveryItem(SQLiteActivity.this, null);
sqliteHandler.addDeliveryItem(delItem);
}
}
...and that class has the code to create the table:
public class SQLiteHandlerDeliveryItem extends SQLiteOpenHelper {
. . .
public SQLiteHandlerDeliveryItem(Context context, SQLiteDatabase.CursorFactory factory)
{
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String CREATE_DELIVERYITEMS_TABLE = "CREATE TABLE " +
TABLE_DELIVERYITEMS + "("
+ COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_INVOICENUM + " TEXT,"
+ COLUMN_UPCPLU + " TEXT," + COLUMN_VENDORITEMID + " TEXT,"
+ COLUMN_PACKSIZE + " INTEGER," + COLUMN_DESCRIPTION + " TEXT,"
+ COLUMN_COST + " INTEGER," + COLUMN_MARGIN + " INTEGER," + COLUMN_LISTPRICE + " INTEGER,"
+ COLUMN_DEPTNUM + " INTEGER," + COLUMN_SUBDEPT + " TEXT," + COLUMN_QTY + " TEXT"
+ ")";
sqLiteDatabase.execSQL(CREATE_DELIVERYITEMS_TABLE);
}
So...what am I doing wrong, or failing to do right?
It's true the table is not being created. The err msg in LogCat indicates that to be the case (it's "no such table: delivertitems").
My constructor looks like this:
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_DELIVERYITEMS_TABLE = "CREATE TABLE " +
TABLE_DELIVERYITEMS + "("
+ COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_INVOICENUM + " TEXT,"
+ COLUMN_UPCPLU + " TEXT," + COLUMN_VENDORITEMID + " TEXT,"
+ COLUMN_PACKSIZE + " INTEGER," + COLUMN_DESCRIPTION + " TEXT,"
+ COLUMN_COST + " INTEGER," + COLUMN_MARGIN + " INTEGER," + COLUMN_LISTPRICE + " INTEGER,"
+ COLUMN_DEPTNUM + " INTEGER," + COLUMN_SUBDEPT + " TEXT," + COLUMN_QTY + " TEXT"
+ ")";
db.execSQL(CREATE_DELIVERYITEMS_TABLE);
}
This code does, indeed, not get entered. So what hoop must I leap through to get the constructor to be called?
I would think that would happen when I instantiate the class:
SQLiteHandlerDeliveryItem sqliteHandler = new
SQLiteHandlerDeliveryItem(SQLiteActivity.this, null);
Even when it is called, how does the constructor know what the arg (SQLiteDatabase db) is - where does it get this value from?
I do have a database, with one table. It just refuses to add this second table.
As someone somewhere recommended, I'm adding a separate class that extends SQLiteOpenHelper for each table I want to add to the database.
When I get to that line:
SQLiteHandlerDeliveryItem sqliteHandler = new
SQLiteHandlerDeliveryItem(SQLiteActivity.this, null);
...and mash F7 to step into it, I reach the class constructor:
public SQLiteHandlerDeliveryItem(Context context, SQLiteDatabase.CursorFactory factory)
{
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
...but not the onCreate event.
So it's "no wonder" I have no deliveryitems table, as the code to create said table never gets reached; but WHY does it not get reached - what must I do to get it to BE reached?
As I was hoping, it actually turns out to be an easy fix: simply increment the value of your database version. I read this on p. 262 of O'Reilly's "Programming Android":
DATABASE_VERSION ...If the version of the database on the machine is less than DATABASE_VERSION, the system runs your onUpgrade method to upgrade the database to the current level.
Thus, all you need to do is increment that number:
private static final int DATABASE_VERSION = 2;
// I changed it from "1" to "2", but you could change it to anything you want, I reckon (42, or 1776, or whatever).
Incrementing the version value causes onUpgrade to run, which drops the old version of the database and then calls onCreate:
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_DELIVERYITEMS);
onCreate(db);
}
The onCreate() event then does exactly that - adding the DDL to create the table.
Here is the pertinent code in the class that extends SQLiteOpenHelper in context:
public class SQLiteHandlerDeliveryItem extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 2;
private static final String DATABASE_NAME = "HHS.db";
private static final String TABLE_DELIVERYITEMS = "deliveryitems";
private static final String COLUMN_ID = "_id";
. . .
private static final String COLUMN_QTY = "quantity";
public SQLiteHandlerDeliveryItem(Context context, SQLiteDatabase.CursorFactory factory)
{
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_DELIVERYITEMS_TABLE = "CREATE TABLE " +
TABLE_DELIVERYITEMS + "("
+ COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_INVOICENUM + " TEXT,"
. . .
+ COLUMN_DEPTNUM + " INTEGER," + COLUMN_SUBDEPT + " TEXT," + COLUMN_QTY + "
TEXT"
+ ")";
db.execSQL(CREATE_DELIVERYITEMS_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_DELIVERYITEMS);
onCreate(db);
}
. . .
So it seems to me having multiple classes that extend SQLiteOpenHelper is a good thing, since you can thus keep your code separated, rather than have one gigantic/humongous pair of onUpgrade/onCreate spaghetti.