CODEWITHSUNDEEP
androidsqlitecrash
Matej Mohar
Matej Mohar

Reputation: 37

Application crashes when creating tables in database with SQLiteOpenHelper

I have an application and I want to use database in ti. But I can't get it working. I will provide source code:

~tabActivity.java --> this activity extends another activity with name deviceActivity.java. Hera are some code snippets. 

private DatabaseHandler DBHandler;

public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    DBHandler = new DatabaseHandler(getApplication());
}

in this activity, I've got listView of some names and imageView. When I click on imageView, this code is executed. And here you can see, thet I call method from DBHandler class. It returns the List.

device_status_image.setOnClickListener(new OnClickListener() {

public void onClick(View v) {
    final Dialog dialog = new Dialog(LightsTabLightActivity.this
            .getParent());
    dialog.setContentView(R.layout.list_view_icon);
    listView = (ListView) dialog.findViewById(R.id.list_view);
    IconAdapter adapter = new IconAdapter(getLayoutInflater(),
        light.id, light.oldCategory, light.name, DBHandler
                    .getAllIconsOfOldCategory(light.oldCategory));
    listView.setAdapter(adapter);
    dialog.show();
    }
});

Here is DatabaseHandler.java, which is suppose to create database, 2 tables and fill one table. So, when in method getAllIconsOfOldCategory() comes to the line SQLiteDatabase db = this.getWritableDatabase();, it starts onCreate. And here is where the application crash. It stops working when running commands to execute query. But I have no idea, what could be wrong.

db.execSQL(createDeviceTable);
db.execSQL(createIconTable);

And this is full DatabaseHandler class:

public class DatabaseHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 4;

    // Database Name
    private static final String DATABASE_NAME = "myDatabase";

    // Table names
    private static final String TABLE_DEVICE = "device";
    private static final String TABLE_ICON = "icon";

    // Column names of device table
    private static final String ID_DEVICE = "idDevice";
    private static final String UNIT_NUMBER = "unitNumber";
    private static final String DEVICE_ID = "deviceId";
    private static final String FK_ICON = "kdIcon";

    // Colimn names of icon table
    private static final String ID_ICON = "idIcon";
    private static final String CATEGORY = "category";
    private static final String ON = "on";
    private static final String OFF = "off";
    private static final String ON_COOLING = "onCooling";
    private static final String ON_HEATING = "onHeating";
    private static final String ICON_0 = "icon0";
    private static final String ICON_10 = "icon10";
    private static final String ICON_20 = "icon20";
    private static final String ICON_25 = "icon25";
    private static final String ICON_30 = "icon30";
    private static final String ICON_40 = "icon40";
    private static final String ICON_50 = "icon50";
    private static final String ICON_60 = "icon60";
    private static final String ICON_75 = "icon75";
    private static final String ICON_80 = "icon80";
    private static final String ICON_90 = "icon90";
    private static final String ICON_100 = "icon100";

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

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createDeviceTable = "CREATE  TABLE " + TABLE_DEVICE + " ("
                + ID_DEVICE
                + " INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "
                + UNIT_NUMBER + " INTEGER NOT NULL  UNIQUE , " + DEVICE_ID
                + " INTEGER NOT NULL  UNIQUE , " + FK_ICON + " INTEGER)";

        String createIconTable = "CREATE  TABLE " + TABLE_ICON + " (" + ID_ICON
                + " INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "
                + CATEGORY + " INTEGER NOT NULL , " + ON + " TEXT, " + OFF
                + " TEXT, " + ON_COOLING + " TEXT, " + ON_HEATING + " TEXT, "
                + ICON_0 + " TEXT, " + ICON_10 + " TEXT, " + ICON_20
                + " TEXT, " + ICON_25 + " TEXT, " + ICON_30 + " TEXT, "
                + ICON_40 + " TEXT, " + ICON_50 + " TEXT, " + ICON_60
                + " TEXT, " + ICON_75 + " TEXT, " + ICON_80 + " TEXT, "
                + ICON_90 + " TEXT, " + ICON_100 + " TEXT)";

        db.execSQL(createDeviceTable);
        db.execSQL(createIconTable);

        ContentValues values = new ContentValues();
        values.put(ID_ICON, 1);
        values.put(CATEGORY, 3);
        values.put(ON, "lightbulb_0_v2");
        values.put(OFF, "lightbulb_100_v2");
        db.insert(TABLE_ICON, null, values);
        values.clear();

        values.put(ID_ICON, 2);
        values.put(CATEGORY, 3);
        values.put(ON, "lightbulb_0_v1");
        values.put(OFF, "lightbulb_max_v1");
        db.insert(TABLE_ICON, null, values);
        values.clear();

        values.put(ID_ICON, 3);
        values.put(CATEGORY, 3);
        values.put(ON, "electric_outlet_on");
        values.put(OFF, "electric_outlet_off");
        db.insert(TABLE_ICON, null, values);
        values.clear();

        db.close();

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_DEVICE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ICON);

        // Create tables again
        onCreate(db);

    }

    public List<String> getAllIconsOfOldCategory(int oldCategory) {
        List<String> iconsList = new ArrayList<String>();

        String selectIconsQuery = "";
        if (oldCategory == 3)
            selectIconsQuery = "SELECT " + ON + " FROM " + TABLE_ICON;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectIconsQuery, null);

        if (cursor.moveToFirst()) {
            do {
                iconsList.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }

        return iconsList;
        // return null;
    }

}

Thank you for your help!

EDIT 1: So, I removed AUTOINCREMENT from both Strings I use to create tables. And also, I found that program run good, without error, untill I change DATABASE_VERSION to higher value. And if I change, this error occurs: 

09-15 20:07:28.539: E/AndroidRuntime(8622): FATAL EXCEPTION: main
09-15 20:07:28.539: E/AndroidRuntime(8622): java.lang.IllegalStateException: database /data/data/com.netichome.android/databases/netichome (conn# 0) already closed
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.database.sqlite.SQLiteDatabase.verifyDbIsOpen(SQLiteDatabase.java:2189)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.database.sqlite.SQLiteDatabase.verifyLockOwner(SQLiteDatabase.java:2195)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.database.sqlite.SQLiteDatabase.endTransaction(SQLiteDatabase.java:740)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:176)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at com.netichome.mobile.backend.DatabaseHandler.getAllIconsOfOldCategory(DatabaseHandler.java:121)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at com.netichome.mobile.tabActivity$3.onClick(tabActivity.java:129)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.view.View.performClick(View.java:3549)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.view.View$PerformClick.run(View.java:14393)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.os.Handler.handleCallback(Handler.java:605)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.os.Handler.dispatchMessage(Handler.java:92)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.os.Looper.loop(Looper.java:154)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at android.app.ActivityThread.main(ActivityThread.java:4945)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at java.lang.reflect.Method.invokeNative(Native Method)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at java.lang.reflect.Method.invoke(Method.java:511)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
09-15 20:07:28.539: E/AndroidRuntime(8622):     at dalvik.system.NativeStart.main(Native Method)

BTW: If I understand correctly, when I change DATABASE_VERSION program run onUpgrade() method, which drops both tables and then calls onCreate(). And when it comes to db.close() app crashes. But why? How can database be already closed, if this is the only db.close() command? And if I remove this, the application works. But I don't think it is a good idea to remove this, or it is?

Upvotes: 0

Views: 839

Answers (1)

Crwydryn
Crwydryn

Reputation: 852

There's a couple of things that looks wrong in the code.

First, in your TABLE_ICON create script you're setting ID_ICON to AUTOINCREMENT, but then you're explicitly setting an ID_ICON value in your insert.

Second, in your getAllIconsOfOldCategory method, you are only ever setting selectIconsQuery if oldCategory == 3, but it seems you're passing it into db.rawQuery regardless of whether the query is set or not. This will definitely cause an error.

Other than that, perhaps you could post your error log so that we can investigate further?

Upvotes: 1

Related Questions