sqlite table stores the first record but not after that

Question

I have created a database and added it to asset folder. Table name orders has three columns id, orderItems and orderDate. In orderItems i'm converting the arraylist to a string and then saving which stores successfully for the first record but when i try to store the second one it shows an error.

Here is the function which i'm using to add the records in my orders Table.

void addOrders(Order ord) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues orderValues = new ContentValues();
    orderValues.put(ORDER_ID, ord.get_orderId());
    orderValues.put(ORDER_ITEMS, ord.get_orderItems()); 
    orderValues.put(ORDER_DATE, ord.get_orderDate()); 

    // Inserting Row
    db.insert(TABLE_ORDERS_NEW, null, orderValues);
    db.close(); // Closing database connection

}

Here is the function which i'm using to convert the arraylist (selectedProducts) to string and saving to table.

public void saveOrders(){

     Gson gson = new Gson();
     String arrayList = gson.toJson(selectedProducts);

     db.addOrders(new Order(arrayList, formattedDate));

}

DatabaseHandler.class

public class DatabaseHandler extends SQLiteAssetHelper {

// All Static variables

private static String TAG = DatabaseHandler.class.getName();

// Database Path
 private static String DB_PATH = "/data/data/com.WaqasAsgharBhalli.za_traders/databases/";
  private static String DB_NAME = "myDatabase.sqlite";
  private SQLiteDatabase myDataBase = null; 
  private final Context myContext;

// Database Version
private static final int DATABASE_VERSION = 1;                                   

// Contacts table name
private static final String TABLE_CONTACTS_NEW = "customer";
// Medicine table name
private static final String TABLE_PRODUCT_NEW = "products";
// Medicine table name
private static final String TABLE_USERLOGIN_NEW = "userlogin";
// ORDERS table name
private static final String TABLE_ORDERS_NEW = "orders";                                                 

// Contacts Table Columns name
private static final String CUSTOMER_ID = "id";
private static final String CUSTOMER_CODE = "code";
private static final String CUSTOMER_NAME = "name";
private static final String CUSTOMER_ADDRESS = "address";
private static final String CUSTOMER_LICENCECENO = "licenceNo";

// Product Table Columns name
private static final String PRODUCT_ID = "id";
private static final String PRODUCT_CODE = "pcode";
private static final String PRODUCT_NAME = "pname";
private static final String PRODUCT_PRICE = "price";
private static final String PRODUCT_QUANTITY = "quantity";

// UserLogin Table Columns name
private static final String USERLOGIN_ID = "id";
private static final String USERLOGIN_USERCODE = "usercode";
private static final String USERLOGIN_NAME = "uname";
private static final String USERLOGIN_PASSWORD = "password";

// Order Table Columns name
private static final String ORDER_ID = "id";
private static final String ORDER_ITEMS = "orderItems";
private static final String ORDER_DATE = "orderDate";


// CREATING CUSTOMER CONTACT TABLE
private static final  String CREATE_CONTACTS_CUSTOMER = 
        "CREATE TABLE " + TABLE_CONTACTS_NEW + "("
        + CUSTOMER_ID + " INTEGER PRIMARY KEY," + CUSTOMER_CODE + " TEXT,"
        + CUSTOMER_NAME + " TEXT," + CUSTOMER_LICENCECENO + " TEXT," 
        + CUSTOMER_ADDRESS + " TEXT"+");";

// CREATING PRODUCT TABLE
private static final  String CREATE_CONTACTS_PRODUCTS = 
        "CREATE TABLE " + TABLE_PRODUCT_NEW + "("
        + PRODUCT_ID + " INTEGER PRIMARY KEY,"+ PRODUCT_CODE + " TEXT," 
        + PRODUCT_NAME + " TEXT,"  + PRODUCT_PRICE + " DOUBLE," 
        + PRODUCT_QUANTITY + " INTEGER" +");";

// CREATING USERLOGIN TABLE
private static final  String CREATE_CONTACTS_USERLOGIN = 
        "CREATE TABLE " + TABLE_USERLOGIN_NEW + "("
        + USERLOGIN_ID + " INTEGER PRIMARY KEY," + USERLOGIN_USERCODE + " TEXT,"
        + USERLOGIN_NAME + " TEXT,"  + USERLOGIN_PASSWORD + " TEXT"+");";

// CREATING ORDERS TABLE
private static final  String CREATE_CONTACTS_ORDERS = 
        "CREATE TABLE " + TABLE_ORDERS_NEW + "("
        + ORDER_ID + " INTEGER PRIMARY KEY," 
        + ORDER_ITEMS + " TEXT,"  + ORDER_DATE + " DATETIME"+");                                             

public DatabaseHandler (Context context) throws IOException  {
    super(context,DB_NAME,null,1);
    this.myContext=context;
    boolean dbexist = checkDatabase();
    if(dbexist)
    {
        //System.out.println("Database exists");
        openDatabase(); 
    }
    else
    {
        System.out.println("Database doesn't exist");
    createDatabase();
    }

}

/**
 * Creates a empty database on the system and rewrites it with your own database.
 * */

public void createDatabase() throws IOException{
    boolean dbexist = checkDatabase();
    if(dbexist)
    {
        //System.out.println(" Database exists.");
    }
    else{
        this.getReadableDatabase();
    try{
            copyDatabase();
        }
        catch(IOException e){
            throw new Error("Error copying database");
        }
    }
}

/**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */
public boolean checkDatabase() {
    //SQLiteDatabase checkdb = null;
    boolean checkdb = false;
    try{
        String myPath = DB_PATH + DB_NAME;
        File dbfile = new File(myPath);
        //checkdb = SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
        checkdb = dbfile.exists();
    }
    catch(SQLiteException e){
        System.out.println("Database doesn't exist");
    }

    return checkdb;
}

private void copyDatabase() throws IOException {

    //Open your local db as the input stream
    InputStream myinput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outfilename = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myoutput = new FileOutputStream("/data/data/com.WaqasAsgharBhalli.za_traders/databases/myDatabase.sqlite");

    // transfer byte to inputfile to outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myinput.read(buffer))>0)
    {
        myoutput.write(buffer,0,length);
    }

    //Close the streams
    myoutput.flush();
    myoutput.close();
    myinput.close();

}

public void openDatabase() throws SQLException
{
    //Open the database
    String mypath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READWRITE);

}

@Override
public synchronized void close(){
    if(myDataBase != null){
        myDataBase.close();
    }
    super.close();
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS_NEW);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCT_NEW);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERLOGIN_NEW);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORDERS_NEW);

    // Create tables again
    onCreate(db);
}

The error which i'm getting whenever i try to enter the second record.

07-19 00:43:03.198: I/SQLiteAssetHelper(1741): successfully opened database myDatabase.sqlite
07-19 00:43:03.386: E/SQLiteDatabase(1741): Error inserting id=0 orderItems=[{"_product_name":"BETNESOL TAB","_product_Code":"0343","_price":21.6,"_id":6,"_quantity":29},{"_product_name":"Q.PIN TAB 100MG","_product_Code":"2132","_price":221.0,"_id":10,"_quantity":11}] orderDate=19-Jul-2014
07-19 00:43:03.386: E/SQLiteDatabase(1741): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:113)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1718)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1591)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.WaqasAsgharBhalli.za_traders.DatabaseHandler.addOrders(DatabaseHandler.java:584)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.WaqasAsgharBhalli.za_traders.SelectedMedicineList.saveOrders(SelectedMedicineList.java:139)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.WaqasAsgharBhalli.za_traders.SelectedMedicineList.onOptionsItemSelected(SelectedMedicineList.java:117)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.app.Activity.onMenuItemSelected(Activity.java:2502)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.policy.impl.PhoneWindow.onMenuItemSelected(PhoneWindow.java:950)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.view.menu.MenuBuilder.dispatchMenuItemSelected(MenuBuilder.java:735)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.view.menu.MenuItemImpl.invoke(MenuItemImpl.java:149)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.view.menu.MenuBuilder.performItemAction(MenuBuilder.java:874)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.view.menu.ListMenuPresenter.onItemClick(ListMenuPresenter.java:163)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.widget.AdapterView.performItemClick(AdapterView.java:292)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.widget.AbsListView.performItemClick(AbsListView.java:1058)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.widget.AbsListView$PerformClick.run(AbsListView.java:2514)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.widget.AbsListView$1.run(AbsListView.java:3168)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.os.Handler.handleCallback(Handler.java:605)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.os.Handler.dispatchMessage(Handler.java:92)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.os.Looper.loop(Looper.java:137)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at android.app.ActivityThread.main(ActivityThread.java:4340)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at java.lang.reflect.Method.invokeNative(Native Method)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at java.lang.reflect.Method.invoke(Method.java:511)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
07-19 00:43:03.386: E/SQLiteDatabase(1741):     at dalvik.system.NativeStart.main(Native Method)

Answer 1

If you set id as autoincrement, you can't specifically set it (to ord.get_orderId(), in this case).

You should delete that orderValues.put(ORDER_ID, ord.get_orderId()); line. Or, create another field in the table for storing that ord.get_orderId().

Answer 2

You're explicitly setting the ID in the ContentValues you pass to db.insert instead of letting the database automatically generate IDs for you. With the ID set to auto increment the database will generate IDs unless you explicitly give an ID, in which case it will try to use the ID you specified.

Remove this line from your code:

orderValues.put(ORDER_ID, ord.get_orderId());

Answer 3

You have a Constraint error. That means you have to check the setup of you database.

If you for example try to insert a primary key twice with the same value it will give this error.

Could you post your db helper to see if your input is compliant with your db setup.

Can be unique value, primary key value etc.