1

having a strange issue which seems to be common but I am unable to find a proper solution. Maybe you guys can help me figure out where I am going wrong.

This is my Common DB adapter class

package com.example.mobilebillforecaster;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class CommonDBclass {

public static final String DATABASE_NAME = "mbfDB";

public static final int DATABASE_VERSION = 1;

public static final String CREATE_ETI_TABLE =

"CREATE TABLE etisalatdb (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
        + EtiTariffDB.KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + EtiTariffDB.KEY_PACKNAME + " TEXT, " + EtiTariffDB.KEY_PACKRENT
        + " INTEGER, " + EtiTariffDB.KEY_SIMRATE + " INTEGER, "
        + EtiTariffDB.KEY_NATMINSBUN + " INTEGER, "
        + EtiTariffDB.KEY_NATMINSRATE + " INTEGER, "
        + EtiTariffDB.KEY_INTMINSBUN + " INTEGER, "
        + EtiTariffDB.KEY_INTMINSRATE + " INTEGER, "
        + EtiTariffDB.KEY_NATSMSBUN + " INTEGER, "
        + EtiTariffDB.KEY_NATSMSRATE + " INTEGER, "
        + EtiTariffDB.KEY_INTSMSBUN + " INTEGER, "
        + EtiTariffDB.KEY_INTSMSRATE + " INTEGER, "
        + EtiTariffDB.KEY_MMSBUN + " INTEGER, " + EtiTariffDB.KEY_MMSRATE
        + " INTEGER, " + EtiTariffDB.KEY_DATALBUN + " INTEGER, "
        + EtiTariffDB.KEY_DATALRATE + " INTEGER" + ");";

public static final String CREATE_DU_TABLE =

"CREATE TABLE dudatabase(_id INTEGER PRIMARY KEY AUTOINCREMENT, "
        + DuTariffDB.KEY_PACKNAME + " TEXT, " + DuTariffDB.KEY_PACKRENT
        + " INTEGER, " + DuTariffDB.KEY_NATMINSRATE + " INTEGER, "
        + DuTariffDB.KEY_NATMOBRATE + " INTEGER, "
        + DuTariffDB.KEY_INTMINSRATE + " INTEGER, "
        + DuTariffDB.KEY_NATSMSRATE + " INTEGER, "
        + DuTariffDB.KEY_INTSMSRATE + " INTEGER, "
        + DuTariffDB.KEY_DATALBUN + " INTEGER, " + DuTariffDB.KEY_DATALRATE
        + " INTEGER" + ");";

private final Context context;
private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public CommonDBclass(Context ctx) {
    this.context = ctx;
    this.DBHelper = new DatabaseHelper(this.context);
}

private static class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_ETI_TABLE);
        db.execSQL(CREATE_DU_TABLE);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

public CommonDBclass open() throws SQLException {
    this.db = this.DBHelper.getWritableDatabase();

    return this;
}

public void close() {
    this.DBHelper.close();
}

}

and this is one of my adapters

package com.example.mobilebillforecaster;

import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class EtiTariffDB {

public static final String KEY_ROWID = "_id";
public static final String KEY_PACKNAME = "pack_name";
public static final String KEY_PACKRENT = "pack_rent";
public static final String KEY_SIMRATE = "sim_rate";
public static final String KEY_NATMINSBUN = "natmins_bun";
public static final String KEY_NATMINSRATE = "natmins_rate";
public static final String KEY_INTMINSBUN = "intmins_bun";
public static final String KEY_INTMINSRATE = "intmins_rate";
public static final String KEY_NATSMSBUN = "natsms_bun";
public static final String KEY_NATSMSRATE = "natsms_rate";
public static final String KEY_INTSMSBUN = "intsms_bun";
public static final String KEY_INTSMSRATE = "intsms_rate";
public static final String KEY_MMSBUN = "mms_bun";
public static final String KEY_MMSRATE = "mms_rate";
public static final String KEY_DATALBUN = "datal_bun";
public static final String KEY_DATALRATE = "datal_rate";

private static final String DATABASE_TABLE = "etitarifftable";

private DbHelper ourHelper;
private final Context cctext;

private SQLiteDatabase ourDatabase;

private static class DbHelper extends SQLiteOpenHelper {

    DbHelper(Context ourContext) {
        super(ourContext, CommonDBclass.DATABASE_NAME, null,
                CommonDBclass.DATABASE_VERSION);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }

}

public EtiTariffDB(Context ctx) {
    this.cctext = ctx;

}

public EtiTariffDB open() {
    this.ourHelper = new DbHelper(this.cctext);
    this.ourDatabase = this.ourHelper.getWritableDatabase();
    return this;
}

public void close() {
    this.ourHelper.close();
}

public long createEtiTariff(String packname, String packrent,
        String simrent, String natbun, String natrate, String intbundle,
        String intrate, String natsmsbun, String natsmsrate,
        String intsmsbun, String intsmsrate, String mmsbun, String mmsrate,
        String datalbun, String datalrate) {
    ContentValues cv = new ContentValues();
    cv.put(KEY_PACKNAME, packname);
    cv.put(KEY_PACKRENT, packrent);
    cv.put(KEY_SIMRATE, simrent);
    cv.put(KEY_NATMINSBUN, natbun);
    cv.put(KEY_NATMINSRATE, natrate);
    cv.put(KEY_INTMINSBUN, intbundle);
    cv.put(KEY_INTMINSRATE, intrate);
    cv.put(KEY_NATSMSBUN, natsmsbun);
    cv.put(KEY_NATSMSRATE, natsmsrate);
    cv.put(KEY_INTSMSBUN, intsmsbun);
    cv.put(KEY_INTSMSRATE, intsmsrate);
    cv.put(KEY_MMSBUN, mmsbun);
    cv.put(KEY_MMSRATE, mmsrate);
    cv.put(KEY_DATALBUN, datalbun);
    cv.put(KEY_DATALRATE, datalrate);

    return this.ourDatabase.insert(DATABASE_TABLE, null, cv);

}

this is part of my java file that calls the DB

    EtiTariffDB etisalatDB = new EtiTariffDB(this);

    etisalatDB.open();

    etisalatDB.createEtiTariff(etipackname, etipackrent, etisimrent,
            etinatbun, etinatrate, etiintbundle, etiintrate,
            etinatsmsbundle, etinatsmsrate, etiintsmsbundle,
                            etiintsmsrate,etimmsbundle, etimmsrate, 
                            etidatalocalbundle, etidatalocalrate);

    etisalatDB.close();

The Error I get is:

04-10 20:17:47.958: E/SQLiteLog(30526): (1) no such table: etitarifftable
04-10 20:17:48.078: E/SQLiteDatabase(30526): Error inserting natmins_rate=0.30 pack_name=Corporate package natsms_bun=200 mms_rate=0.45 mms_bun=- intsms_rate=0.60 natsms_rate=0.18 intsms_bun=- sim_rate=29 intmins_bun=100 datal_rate=0.1 datal_bun=2000 intmins_rate=1.10 natmins_bun=100 pack_rent=249
04-10 20:17:48.078: E/SQLiteDatabase(30526): android.database.sqlite.SQLiteException: no such table: etitarifftable (code 1): , while compiling: INSERT INTO etitarifftable(natmins_rate,pack_name,natsms_bun,mms_rate,mms_bun,intsms_rate,natsms_rate,intsms_bun,sim_rate,intmins_bun,datal_rate,datal_bun,intmins_rate,natmins_bun,pack_rent) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

i have read many a post and gone through many a forum. The example I used to create this is found here:

Multiple Table SQLite DB Adapter(s) in Android?

I have checked and rechecked my coding. I am suspecting that I am not calling the commonDBclass on create method but I cant figure out how. Please help.

thanks.

Community
  • 1
  • 1
Rabeeu Roohie
  • 41
  • 1
  • 10
  • if you added the table afterwards you need to increment the db version and implement onUpgrade(...) so the new tables are created. – drunkenRabbit Apr 10 '13 at 20:31
  • 1
    You don't seem to have table called ``etitarifftable`` created. Only ``etisalatdb`` and ``dudatabase``. Could this be the reason? – harism Apr 10 '13 at 20:33
  • even if i deleted the entire db? i tried the onupgrade and versions, nothing happened. So I deleted the DB and tried..same issue.DB gets created but no tables. – Rabeeu Roohie Apr 10 '13 at 20:34
  • i specify the table name as etitarifftable. even if the table doesnt get created,I should at least have the etisalatdb and dudatabase tables created correct? this is also not there :( – Rabeeu Roohie Apr 10 '13 at 20:37

2 Answers2

1

You have a few problems I noticed right away:

  1. You simply don't have a String to create a table named etitarifftable.
  2. You aren't executing your CREATE TABLE Strings.
  3. You are changing the Java code, but not telling SQLiteOpenHelper to update the SQL schema.
  4. Tables can only have one Primary Key

Simple solutions:

  1. Either create etitarifftable or use the appropriate table name in your query.

  2. Add the appropriate execSQL() statements to DbHelper#onCreate().

    @Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_ETI_TABLE);
    db.execSQL(CREATE_DU_TABLE);
}

  3. Increment DATABASE_VERSION which will call onUpgrade(). In its most basic form onUpgrade() should look like this:

    @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS etisalatdb");
    db.execSQL("DROP TABLE IF EXISTS dudatabase");
    onCreate(db);
}

  4. Remove one of your Primary Key declarations from both tables:

    "CREATE TABLE etisalatdb (EtiTariffDB.KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + ...

Finally, I have a feeling there are more errors, but your LogCat will lead you right to them. (Also I'm not sure why you have two different adapters... )

Sam
  • 86,580
  • 20
  • 181
  • 179
  • Hi Sam, thanks for your detailed post. with regards to point 2, you have listed that it should be under DBhelper#oncreate() which is in my EtiTariffDB class. However, when i do add the execSQL line, it tries to create the variable (ex: CREATE_ETI_TABLE) since that is a variable that is defined in the commonDBclass file. – Rabeeu Roohie Apr 10 '13 at 21:11
  • Sorry, when I wrote that step I hadn't realized you created two SQLiteOpenHelpers... Since `DbHelper` only calls `super(ourContext, CommonDBclass.DATABASE_NAME, null, CommonDBclass.DATABASE_VERSION);` DatabaseHelper's information, you can probably remove `DbHelper`. – Sam Apr 10 '13 at 21:16
  • hi. Shouldnt i define a helper for the EtiTariffDB? if I remove the DBhelper, wont all the methods stop functioning? – Rabeeu Roohie Apr 10 '13 at 21:25
  • I assume you want `CommonDBclass` to have the common code for your database adapters, so `EtiTariffDB` should extend `CommonDBclass` and use the common methods, like the SQLiteOpenHelper. – Sam Apr 10 '13 at 22:06
  • hi Sam, can you give me a sample of how it should look.. I am not quite getting it sorry about that. thanks for the help. – Rabeeu Roohie Apr 11 '13 at 20:12
  • thanks Sam. You made me realize my mistake. ultimately I moved everything into one DB adapter and change the entries. all seems to be well now. – Rabeeu Roohie Apr 11 '13 at 21:51
0

You need to create the table before inserting a record, you do this via:

class DbHelper  extends SQLiteOpenHelper {
    ...

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " {definition} ");
    }

    ...
}

It doesn't look like your calling CommonDBclass at all. I think you need to rethink and restructure the way you are doing it. EtiTariffDB shouldn't be a "Database" it's a table and you should treat it as one. Use CommonDBclass to get a EtiTariff table object instead of the opposite. Then you can do something like:

DatabaseHelper db = new DatabaseHelper();
EtiTariffTable etiTariff = db.getEtiTariffTable();
JRomero
  • 4,878
  • 1
  • 27
  • 49