SQLite database android create table

Question

"I got error "No Such Table" and cause me to try everything possible to make it gone, now it's gone, but i'm confuse. Here is my situation

I have 2 tables need to create in the apps. and i put one class per table and it's look like code below. If i follow this it will hit me with "No Such Table" when i pull data from "Table2", so i need to change the DATABASE_NAME to some other value = "DB2"; then the "Table2" will create successfully and able to pull out data.

So my question is this a correct way to make sure 2 tables create successfully? or i should not seperate table into 2 classes? Pls advise

public class DB1 extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;

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

// Labels table name
private static final String TABLE_STORE = "Table1";

// Labels Table Columns names
public static final String KEY_1 = "col1"; 

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

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    // Category table create query
    String CREATE_TABLE = "CREATE TABLE " + TABLE_STORE + "("
            +KEY_1 + " INTEGER PRIMARY KEY)";
    db.execSQL(CREATE_TABLE);
}
}

Another class

public class DB2 extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;

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

// Labels table name
private static final String TABLE_STORE = "Table2";

// Labels Table Columns names
public static final String KEY_1 = "col1"; 

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

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    // Category table create query
    String CREATE_TABLE = "CREATE TABLE " + TABLE_STORE + "("
            +KEY_1 + " INTEGER PRIMARY KEY)";
    db.execSQL(CREATE_TABLE);
}
}

Answer 1

You should consider changing you model of a Table. That will help you organise things better and catch the errors the earliest. You can find an example here.

Below is an example of a table model.

public class ProductTable implements BaseColumns {
  public static final String NAME = "name";
  public static final String PRICE = "price";
  public static final String TABLE_NAME = "products";

  public static final String CREATE_QUERY = "create table " + TABLE_NAME + " (" +
      _ID + " INTEGER, " +
      NAME + " TEXT, " +
      PRICE + " INTEGER)";

  public static final String DROP_QUERY = "drop table " + TABLE_NAME;
  public static final String SElECT_QUERY = "select * from " + TABLE_NAME;
}

Now create the table using the below code in your DatabaseHelper.

 @Override
  public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL(ProductTable.CREATE_QUERY);
    seedProducts(sqLiteDatabase);
  }

Answer 2

you can´t create the second table because the db exist,only te first on create can creates tables try something like this

 public class DB1 extends SQLiteOpenHelper {
// Database Version
        private static final int DATABASE_VERSION = 1;

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

// Labels table name
        private static final String TABLE_STORE1 = "Table1";
        private static final String TABLE_STORE2 = "Table2";

// Labels Table Columns names
        public static final String KEY_1 = "col1"; 

//first query 
    String firstTable="CREATE TABLE " + TABLE_STORE1 + "("
        +KEY_1 + " INTEGER PRIMARY KEY)";
//second query

    String secondTable="CREATE TABLE " + TABLE_STORE2 + "("
        +KEY_1 + " INTEGER PRIMARY KEY)";

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

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(firstTable);
db.execSQL(secondTable);
}
}

sory for my english XD

Answer 3

No you should have one class (usually we named as DBopenHelper) that extends SQLiteOpenHelper, In that class you have to manipulate the creation of the tables.

so your code will look like the following:

    public class DBOpenHelper extends SQLiteOpenHelper {

        public static final String DATABASE_NAME = "DB";


            // Update the DATABASE_VERSION so that onUpgrade can be executed! 
        public static final int DATABASE_VERSION = 2;               

        // Labels table name
        private static final String TABLE_STORE1 = "Table1";

        // Labels Table Columns names
        public static final String TABLE1_KEY_1 = "Tcol1"; 

            // Labels table name
            private static final String TABLE_STORE2 = "Table2";

            // Labels Table Columns names
            public static final String TABLE2_KEY_1 = "Tcol1";              

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

        @Override
        public void onCreate(SQLiteDatabase db) {
            if (!db.isReadOnly()) {
                // Enable foreign key constraints
                db.execSQL("PRAGMA foreign_keys = ON;");
                Log.i("TAG", "FOREIGN KEY constraint enabled!");
            }       

            // table create query
                String CREATE_TABLE = "CREATE TABLE " + TABLE_STORE1 + "("
                        +TABLE1_KEY_1 + " INTEGER PRIMARY KEY)";

            // table create query
                String CREATE_TABLE = "CREATE TABLE " + TABLE_STORE2 + "("
                        +TABLE2_KEY_1 + " INTEGER PRIMARY KEY AUTOINCREMENT)";

// table create query
                String CREATE_TABLE = "CREATE TABLE " + TABLE_STORE + "("
                        +KEY_1 + " INTEGER PRIMARY KEY AUTOINCREMENT)";


    // ITS ALWAYS GOOD TO PUT execSQL in the try catch block for tracking // PROBLEMS/ERRORS/EXCEPTIONS
        try {
                    db.execSQL(CREATE_TABLE);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            } // end onCrate

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.i(LOG_TAG, "Upgrading database from " + oldVersion + " to "
                    + newVersion);
            // kill previous tables
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_STORE1);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_STORE2);
            onCreate(db);
        } // end onUpgrage

    }

Answer 4

No you should put tables in one database class. Just make sure that whenever you change your code to modify a table or add/remove a table that you clear your data or uninstall your app and run again. You most likely get "No Such Table" error because you add or modify a table, in this case onCreate would not be called again until you clear data or uninstall your app.