CODEWITHSUNDEEP
javaandroideclipsesqliteandroid-sqlite
Daniel Wardin
Daniel Wardin

Reputation: 1858

Android SQLite created database but table can't be found

So I'm trying to fetch data from one of my database tables, could you please check this out for me and see if you can spot the error?

SQLiteDatabase db = SQLiteDatabase.openDatabase(DB_PATH, null,
            SQLiteDatabase.OPEN_READONLY);

    Cursor cursor = db.query(TABLE_CLASSES, new String[] { TABLE_C_DAY,
            TABLE_C_NAME, TABLE_C_DAY, TABLE_C_LOCATION, TABLE_C_TIMEHR,
            TABLE_C_TIMEMIN, TABLE_C_DURATION, TABLE_C_ONETIMEEVENT,
            TABLE_C_CTYPE, TABLE_C_OCCURINGWEEK}, TABLE_C_DAY + "=?",
            new String[] { String.valueOf(day) }, null, null, null, null);

Okay so DB_PATH is definitely correct. I tried to close the db connection and it works fine so db works fine and exists.

All of the table column names are correct as these were used in onCreate to create the db and that worked fine. I also tried using 

db.rawQuery("SELECT * FROM " + TABLE_CLASSES + " WHERE day = ?", new String[] { String.valueOf(day) });

So when I make the query the app stops and I get a Source code not found message in Eclipse. LogCat says that table 'classes' doesn't exist.

I tried the creation code manually and it worked. Here is the code from onCreate(SQLiteDatabase db);

String CREATE_DB_WITH_INIT_VALUES = 
            "CREATE TABLE %1$s (%4$s TEXT);" +
            "INSERT INTO %1$s VALUES('Lecture');" +
            "INSERT INTO %1$s VALUES('Lab');" +
            "INSERT INTO %1$s VALUES('Tutorial');" +
            "INSERT INTO %1$s VALUES('Meeting');" +
            "INSERT INTO %1$s VALUES('Examples Class');" +
            "CREATE TABLE %2$s (%5$s smallint, %6$s varchar(40), %7$s varchar(10), %8$s smallint, %9$s smallint, %10$s smallint, %11$s boolean, %12$s smallint, %13$s smallint);" +
            "INSERT INTO %2$s VALUES(1,'COMP12112 Computation',1.1,9,0,60,'False','Lecture',3);" +
            "INSERT INTO %2$s VALUES(1,'COMP16212 Java OOP 2',1.1,11,0,45,'False','Lecture',3);" +
            "INSERT INTO %2$s VALUES(1,'COMP18111 Distributed Systems','Unix',13,15,60,'False','Lab',3);" +
            "INSERT INTO %2$s VALUES(2,'Tutorial','LF13',15,0,60,'False','Tutorial',3);" +
            "INSERT INTO %2$s VALUES(2,'COMP14111 AI','LF31',10,30,60,'False','Lab',3);" +
            "CREATE TABLE %3$s (%14$s smallint, %15$s smallint, %16$s TEXT, %17$s date);";

    CREATE_DB_WITH_INIT_VALUES = String.format(CREATE_DB_WITH_INIT_VALUES, TABLE_CLASS_TYPES,
            TABLE_CLASSES, TABLE_DEADLINES, TABLE_CT_TYPE,
            TABLE_C_DAY, TABLE_C_NAME, TABLE_C_LOCATION, TABLE_C_TIMEHR, TABLE_C_TIMEMIN, TABLE_C_DURATION,
            TABLE_C_ONETIMEEVENT, TABLE_C_CTYPE, TABLE_C_OCCURINGWEEK,
            TABLE_D_TIMEHR, TABLE_D_TIMEMIN, TABLE_D_DTEXT, TABLE_D_DDATE
            );

    db.execSQL(CREATE_DB_WITH_INIT_VALUES);

This code doesn't crash and all seems fine but then during the query the table can't be found.

Any idea what is wrong?

Upvotes: 1

Views: 2274

Answers (2)

Adrián Pérez
Adrián Pérez

Reputation: 2216

What you've done is a valid list of SQL statements. The real problem here is that the method execSQL() only allows you the execution of a single sql statement (See documentation here).

If you want to execute multiple statements automatically, I'd write a method that first splits those statements (by the ending semicolon of each one) and then execute each one in a loop, checking for errors in every iteration so that when some sql statement fails, you can throw a SQLException indicating the sql statement that failed. Something like this:

public static void execSQLScript(SQLiteDatabase db, String script) throws SQLException {
    String[] statements = script.split(";");
    db.beginTransaction();
    for(String statement : statements) {
        try { 
        db.execSQL(statement); // Seems like this method already throws a decent exception
        }
        catch(SQLException e) {
            db.endTransaction(); // Rolling back the changes done
            throw e; 
        }
    }
    db.setTransactionSuccessful(); // Not rolling but commiting changes, since everything went fine.
    db.endTransaction();
}

Upvotes: 3

Jason Crosby
Jason Crosby

Reputation: 3573

I'm not a SQL expert but I think your trying to do a little too much when creating the tables. I would suggest creating the table and adding any initial data in 2 different steps.

I don't have any initial data in my database but this is what I'm using to create the table:

createTable = "CREATE TABLE " + TABLE_NAME + "(" + COLUMN_ID + " INTEGER PRIMARY KEY," + 
COLUMN_ONE + " TEXT," + COLUMN_TWO + " INTEGER," + COLUMN_THREE + " TEXT," +
COLUMN_FOUR + " TEXT," + COLUMN_FIVE + " TEXT)";

db.execSQL(createTable);

Now in your case after creating the table you can then insert the initial data you want. After

db.execSQL(createTable);

use a ContentValues to add the data you need to the database.

contentValues = new ContentValues();
contentValues.putXXX();
// keep adding data
// the insert method will add the data for you.
SQLiteDatabase.insert(TABLE_NAME, null, contentValues);

Upvotes: 1

Related Questions