Android SQLite insert method small clarification

Is this line of code (on line 8) inserting my values into the table ?

-> Yeah line 8 will insert your value into the table if db is initialized with SQLiteOpenHelper.getWritableDatabase() method.

or i am just storing the row id into the long type variable called "newRowId"?

-> When you insert with SQLiteDatabase.insert() it returns the row ID of the newly inserted row, or -1 if an error occurred.

The SQLiteDatabase insert method inserts a row and returns the rowid of the inserted row as a long or in the case of a conflict -1 to indicate that the row was not inserted. So the value of newRowId will either be a positive value greater than 0 if the row was inserted or -1 if not inserted and there was no other conflict (e.g. a Foreign Key constraint conflict will result in an exception being thrown).

• conflicts could be a PRIMARY KEY, UNIQUE, CHECK or NULL constraint violation.

The insert method is a convenience method that generates the underlying SQL binding any parameters and executes that. The SQL generated will be something like:-

INSERT OR IGNORE INTO the_table_name (the_name_column, the_breed_column, the_weight_column) VALUES(?,?,?);

Where:-

• the_table_name will be the value as per the first parameter i.e. whatever AppContract.HeadphoneEntry.TABLE_NAME resolves to.
• the_name_column will be the value as per the first value of the first in ContentValues (i.e. the value that AppContract.HeadphoneEntry.COLUMN_NAME resolves to)
• the_breed_column will be value as per the first value of the second ContentValues (i.e. the value that AppContract.HeadphoneEntry.COLUMN_BREED resolves to)
• the_weight_column being the first value as per the third ContentValues (i.e. the value that AppContract.HeadphoneEntry.COLUMN_WEIGHT resolves to)

The ?'s are replaced by the 2nd values of the respective ContentValues (first from the first i.e. 'Toto', second from the second i.e. 'Terrier' ....) when the SQL statement is bound. Binding statements correctly encloses the actual values in single quotes and thus protects against SQL Injection.

After executing the sqlite3_last_insert_rowid() interface is invoked the result returned with 0 being converted to -1.

If you weren't to use the convenience method, then to replicate all that it does you would have to code something like :-

    db.execSQL("INSERT OR IGNORE INTO " + AppContract.HeadphoneEntry.TABLE_NAME +
            "(" +
            AppContract.HeadphoneEntry.COLUMN_NAME +
            "," + AppContract.HeadphoneEntry.COLUMN_BREED +
            "," + AppContract.HeadphoneEntry.COLUMN_WEIGHT +
            ") " +
            "VALUES(?,?,?)", 
            new String[]
                    {
                            "Toto", 
                            "Terrier", 
                            "7"
                    }
    );
    Cursor csr = db.rawQuery("SELECT last_insert_rowid()",null);
    newRowID = -1;
    if (csr.moveToFirst()) {
        newRowID = csr.getLong(0);
        if (newRowID <= 0) {
            newRowID = -1;
        }
    }
    csr.close();

where db is an instantiated SQLiteDatabase object.

If your goal is to try to predict the next rowid to be used, then you could use :-

    Cursor csr = db.rawQuery("SELECT max(rowid)+1 FROM " + AppContract.HeadphoneEntry.TABLE_NAME + ";",null);
    long newRowID = 1; // 
    if (csr.moveToFirst()) {
        newRowID = csr.getLong(0);
    }

BUT BEWARE SQLite does not guarantee that the next inserted row will be 1 greater than the highest existing rowid. If you use AUTO INCREMENT then the sqlite_sequence table stores the highest used rowid for the table and the higher of that and the max(rowid) value will be used. Even then their is still no guarantee that the predicted value will be the value used. It is far better to not try to predict the value of the rowid but to retrieve it and thus for Android Java to use the convenience method.

• An exception is if you delve into utilising negative rowid values when -1 may then not indicate no insertion.