getWritableDatabase does not work

Question

I am writing a function for as android application which can update sqlite database by importing the latest csv files. When the user click on the button, the program was supposed to delete the old table and create another new table according to the latest data. The app was able to write data when the button was first time pressed. However, error was found if data was already existed.

05-09 16:41:34.088: E/SQLiteDatabase(3056): Error inserting order_qty=1                                                                                                                                                                                                                                                                  tran_code=NS item_code=IC559 line_no=1 customer_ref_no="PO# 25192600" order_date=20130221 cust_code=099496
05-09 16:41:34.088: E/SQLiteDatabase(3056): android.database.sqlite.SQLiteConstraintException: columns order_date, cust_code, item_code, tran_code are not unique (code 19)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at test.andftpclient.MainActivity$2.onClick(MainActivity.java:118)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.view.View.performClick(View.java:4438)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.view.View$PerformClick.run(View.java:18422)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.os.Handler.handleCallback(Handler.java:733)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.os.Handler.dispatchMessage(Handler.java:95)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.os.Looper.loop(Looper.java:136)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at android.app.ActivityThread.main(ActivityThread.java:5017)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at java.lang.reflect.Method.invokeNative(Native Method)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at java.lang.reflect.Method.invoke(Method.java:515)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
05-09 16:41:34.088: E/SQLiteDatabase(3056):     at dalvik.system.NativeStart.main(Native Method)

I know the main reason for this error occurs is that the inserting data has the same unique primary key as the existing data but i have no idea on why the old table cannot be dropped. Here is my code:

Main Activity

public void onCreate(Bundle savedInstanceState) 
{
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);      



    Button button_import_csv = (Button) findViewById(R.id.button_import);
    button_import_csv.setOnClickListener(new View.OnClickListener(){
        public void onClick(View v){


            DatabaseHelper helper = new DatabaseHelper(getApplicationContext());
            SQLiteDatabase db = helper.getWritableDatabase();


            try{

                FileReader file = new FileReader("/sdcard/downloadedfolder/A1/adv_sales_order.csv");
                BufferedReader buffer = new BufferedReader(file);
                ContentValues contentValues=new ContentValues();
                String line = "";
                String tableName ="adv_sales_order";

                db.beginTransaction();
                while ((line = buffer.readLine()) != null) {
                    String[] str = line.split("\t");


                    contentValues.put("order_date", str[0]);
                    contentValues.put("cust_code", str[1]);
                    contentValues.put("customer_ref_no", str[2]);
                    contentValues.put("line_no", str[3]);
                    contentValues.put("item_code", str[4]);
                    contentValues.put("tran_code", str[5]);
                    contentValues.put("order_qty", str[6]);
                    db.insert(tableName, null, contentValues);

                }
                db.setTransactionSuccessful();
                db.endTransaction();
            }catch (IOException e){

            }

            helper.close();
        }
    });
}

}

DatabaseHelper Class:

public class DatabaseHelper extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "icedb.db";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_1 = "adv_sales_order";



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

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub

    String CREATE_TB;

    CREATE_TB = "CREATE TABLE adv_sales_order ("
            + "order_date text not null, "
            + "cust_code text not null, "
            + "customer_ref_no text, "
            + "line_no integer not null, "
            + "item_code text not null, "
            + "tran_code text not null, "
            + "order_qty real not null, "
            + "constraint pk_adv_sales_order "
            + "primary key (order_date, "
            + "cust_code, "
            + "item_code, "
            + "tran_code))";
    db.execSQL(CREATE_TB);


}

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

    db.execSQL("DROP TABLE IF EXISTS "+ TABLE_1);

    onCreate(db);
}

public void insert_adv_sales_order (ContentValues values) {
    SQLiteDatabase db = getWritableDatabase();
    db.insert("adv_sales_order", null, values);
    db.close();
}

}

Can anyone give me some hints?Thank you.

1) getWriteableDatabse works fine. 2) I see nothing here that would cause your table to be dropped. 3) The call to onCreate within onUpgrade is likely to cause recursion until you run out of stack. 4) Don't close the database. 5) I suspect that you don't understand what onUpdate does. — G. Blake Meike, May 12 '14 at 03:46
Thanks Meike.So how can I drop the table before never each time i need to update the database? When i call the getWritableDatabase function, the onCreate and onUpgrade function should be called. The drop sql statement was included in onUpgrade function. I don't why the old data is still existing. — Helloheyyyy, May 12 '14 at 04:18
http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run — laalto, May 12 '14 at 05:54
@G.BlakeMeike calling onCreate inside of onUpgrade will not cause a stack overflow. onCreate does not (and will not) trigger a call to onUpgrade. — Karakuri, May 12 '14 at 05:54
@Iaalto i have just read your information. In order to trigger the onupgrade function, i need to update the version number each time i press the button, right??? — Helloheyyyy, May 12 '14 at 08:14
@Karakuri Don't know what I was thinking. You are, of course, correct. It is perfectly safe to call onCreate from onUpgrade. Done it myself. — G. Blake Meike, May 12 '14 at 13:42
No, consider designing the logic that the button press doesn't rely on the `onCreate()` or `onUpgrade()` lifecycle methods but rather just execute the SQL you need. — laalto, May 12 '14 at 14:45
@Helloheyyyy I believe that the answer below is just what you want! — G. Blake Meike, May 13 '14 at 03:45
@Meike Thank you. I think i have to modify the program. Once the button was pressed the database_version was increment by 1 in order to trigger the onupgrade — Helloheyyyy, May 13 '14 at 03:54

score 1 · Answer 1 · answered May 12 '14 at 13:56

android.database.sqlite.SQLiteConstraintException:
columns order_date, cust_code, item_code, tran_code are not unique (code 19)

In your create table statement, you have this:

"primary key (order_date, cust_code, item_code, tran_code))";

This means your primary key is a combination of four column values. Each record must have a unique combination of these values. Whatever the values you inserted, this appears not to have been the case.

score 1 · Accepted Answer · answered May 12 '14 at 14:30

I think you misunderstand the use of onCreate and onUpdate. onCreate is called, not each time you call getWriteableDatabase, but any time you call getWriteableDatabase, when the database does not yet exist. Similarly, onUpdate is called when the version number in the existing database does not match the version number in the code.

In order to delete the table, given your code, you could, I suppose, call onUpdate, explicitly, as the second line of the function insert_adv_sales_order.

Facing same issue. It was due to database version was not matched, current and previous one. Thanks you, you save my lots of time. — Smeet, Aug 20 '15 at 14:47

getWritableDatabase does not work

2 Answers2