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);
}
}

Sorry. But why do you need two separate classes for two tables? Isn't that also creating two databases? — Siddharth Lele
– Siddharth Lele, Commented Mar 15, 2013 at 3:54
sorry i think i'm misunderstood on the concept, i thought it will treat it as same database and create table which is not exists , i try make it clean and easier for me to maintain by table/class. — tsohtan
– tsohtan, Commented Mar 15, 2013 at 4:21
I think you should give this tutorial a read. That will clear things up for you. vogella.com/articles/AndroidSQLite/article.html — Siddharth Lele
– Siddharth Lele, Commented Mar 15, 2013 at 4:23
Thansk for the link. i think i'm WRONG! hahaha... need to study more. — tsohtan
– tsohtan, Commented Mar 15, 2013 at 4:41

Hoan Nguyen · Accepted Answer · 2013-03-15 04:14:55Z

5

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.

edited Mar 15, 2013 at 4:14

answered Mar 15, 2013 at 3:57

Hoan Nguyen

18.2k3 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SKK Over a year ago

I go with the above answer. just to add a bit, you could use a single database handler class and create any two or more tables in it. just make sure to uninstall (or clear app data) for every change in your DB structure because the onCreate gets called only once i.e, during installation of the app.

Shajeel Afzal · Accepted Answer · 2013-03-15 13:57:42Z

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

    }

Omar Sanchez · Accepted Answer · 2015-05-18 16:04:50Z

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

Ajit Singh · Accepted Answer · 2015-08-23 07:03:39Z

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);
  }

Collectives™ on Stack Overflow

SQLite database android create table

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related