CODEWITHSUNDEEP
androidsqlite
Bitlejuce Do
Bitlejuce Do

Reputation: 340

How to create a new Table in SQLite database from android app

I faced a problem with creating simple application, here is one of it's option: let's say user input name and press button and application create new table in existing database with that name. My class DBHelper contains next methods:

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

@Override
public void onCreate(SQLiteDatabase db) {
    String query = "CREATE TABLE " + tableName + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_NAME + " TEXT, " +
            COLUMN_QUANTITY + " TEXT, " +
            COLUMN_STATUS + " INTEGER); ";
    db.execSQL(query);
}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    db.execSQL("DROP TABLE IF EXISTS " + tableName);
    onCreate(db);
}

And it works perfect under one first table, which was created at the first time with default name I specified. Now I create some UI and set listener to button (details are not important I guess). Listener call method: 

     public void createTableIfNotExists(String tableName) {
         String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_NAME + " TEXT, " +
                COLUMN_QUANTITY + " TEXT, " +
                COLUMN_STATUS + " INTEGER); ";
        db = dbHelper.getWritableDatabase();
        db.rawQuery(query, null);

        Log.d("TEST", "createTableIfNotExists called");
    }

It seems like everything OK, but table are not created! I tried to call onUpdate method with incremented database version number, but still resultless. What should I do to solve my issue? Note, that number of tables could be as much as user want to create, but all they will be similar (same schema).

P.S. once I ask this question, can anybody explain how to get list of tables from DB? I found solution:

public List<String> getListOfLists() {
        ArrayList<String> arrTblNames = new ArrayList<String>();
        Cursor c = shoppingListDB.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
        if (c.moveToFirst()) {
            while ( !c.isAfterLast() ) {
                arrTblNames.add( c.getString( c.getColumnIndex("name")) );
                c.moveToNext();
            }
        }
        c.close();
        return arrTblNames;

But the list it return a little bit weird: [android_metadata, sqlite_sequence, Newlist1]

Upvotes: 0

Views: 1835

Answers (1)

Ch4t4r
Ch4t4r

Reputation: 1407

In the second instance you are using db.rawQuery instead of db.execSQL. That's why the table is not created.

Regarding the second question: Android creates the android_metadata table to keep track of the current database version and past updates. The sqlite_sequence table is for managing PRIMARY KEY AUTOINCREMENT fields (it stores the highest available number). This is normal and they always have the same name (I believe..), just remove them from your list.

Upvotes: 1

Related Questions