I want to update my database like this: if row exists update it and if row doesn't exist add a new one. In TABLE_MAP_MARKER insert is working but update is not working and in TABLE_MAP_MARKER_INFO in general both, update and insert, don't work. Where is my mistake? Please help me!
Below is my code :
SQLiteDatabase db = this.getWritableDatabase();
ContentValues marker_position_values= new ContentValues();
marker_position_values.put(KEY_MARKER_LAT, map_marker_lat_value);
marker_position_values.put(KEY_MARKER_LNG, map_marker_long_value);
long marker_id_table = db.update(TABLE_MAP_MARKER, marker_position_values, HOME_ID + " = ?", new String[]String.valueOf(home_id)});
if (marker_id_table == 0) {
db.insertWithOnConflict(TABLE_MAP_MARKER, null, marker_position_values, SQLiteDatabase.CONFLICT_REPLACE);
}
ContentValues unit_values= new ContentValues();
unit_values.put(HOME_ID, marker_id_table);
unit_values.put(HOME_TYPE, home_type);
unit_values.put(AMOUNT_RENT, amount_rent);
long unit_id_table = db.update(TABLE_MAP_MARKER_INFO, unit_values, HOME_ID + " = ?", new String[]{String.valueOf(home_id)});
if (unit_id_table == 0) {
db.insert WithOnConflict(TABLE_MAP_MARKER_INFO, null, unit_values, SQLiteDatabase.CONFLICT_REPLACE);
}
There are a number of extraneous spaces in your code such as :-
String.value Of(home_id)db.insert With On ConflictFor a conflict to occur, a constraint must have been specified (implicitly or explicitly). It's not at all clear that you have defined suitable constraints or even what those should be. (example below assumes constraints)
Should there not be a row to update in TABLE_MAP_MARKER then marker_id_table will be 0. As such assuming a successful insert into TABLE_MAP_MARKER then 0 will be used for the HOME_ID column for the attempt to update TABLE_MAP_MARKER_INFO (assuming standard use of an ID column and that it is defined as INTEGER PRIMARY KEY (with or without AUTOINCREMENT)) then an id of 0 will not exist, So no row could be updated. However, an row may then be inserted with a HOME_ID of 0 and then the HOME_ID will be out of sync between the two tables. The fix used in the example was to use the HOME_ID value for _id (i.e. the HOME_ID column) column in the TABLE_MAP_MARKER_INFO update/insert.
The following based upon UNIQUE constraints :-
Then the tables could be created using the equivalent of :-
CREATE TABLE IF NOT EXISTS map_marker(_id INTEGER PRIMARY KEY,map_marker_lat INTEGER,map_marker_lng INTEGER, UNIQUE(map_marker_lat,map_marker_lng));
CREATE TABLE IF NOT EXISTS map_marker_info(_id INTEGER PRIMARY KEY,home_type TEXT,amount_rent REAL, UNIQUE(HOME_TYPE,AMOUNT_RENT));
Thus an explicit UNIQUE constraint is placed on the map_marker's map_marker_lat along with the map_marker_lng column. So both values in combination must be unique otherwise a conflict is raised. So 100,110 along with 100,120 would be fine but a second 100,110 would result in a conflict.
Additionally due to PRIMARY KEY the _id column has an implicit unique constraint (as it's INTEGER PRIMARY KEY then it must also be a integer value).
So with the DatabaseHelper class being (a guess as what you might have, including the addMarkerAndMarkerInfo method that is based upon your code (corrections applied and loggin added - see comments/commented out lines)):-
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DBNAME = "marker";
public static final int DBVERSION = 1;
public static final String TABLE_MAP_MARKER = "map_marker";
public static final String TABLE_MAP_MARKER_INFO = "map_marker_info";
public static final String HOME_ID = BaseColumns._ID;
public static final String KEY_MARKER_LAT = "map_marker_lat";
public static final String KEY_MARKER_LNG = "map_marker_lng";
public static final String HOME_TYPE = "home_type";
public static final String AMOUNT_RENT = "amount_rent";
public DatabaseHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String crtmm = "CREATE TABLE IF NOT EXISTS " + TABLE_MAP_MARKER + "(" +
HOME_ID + " INTEGER PRIMARY KEY, " +
KEY_MARKER_LAT + " INTEGER, " +
KEY_MARKER_LNG + " INTEGER, " +
" UNIQUE(" +
KEY_MARKER_LAT + "," +
KEY_MARKER_LNG +
")" +
")";
String crtmmi = "CREATE TABLE IF NOT EXISTS " + TABLE_MAP_MARKER_INFO + "(" +
HOME_ID + " INTEGER PRIMARY KEY, " +
HOME_TYPE + " TEXT," +
AMOUNT_RENT + " REAL," +
" UNIQUE(" +
HOME_TYPE + ", " +
AMOUNT_RENT +
")" +
")";
db.execSQL(crtmm);
db.execSQL(crtmmi);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
public void addMarkerAndMarkerInfo(long home_id,int map_marker_lat_value, int map_marker_long_value, String home_type, double amount_rent, String attempt) {
String TAG = "AMAMI-" + attempt;
SQLiteDatabase db = this.getWritableDatabase();
ContentValues marker_position_values= new ContentValues();
marker_position_values.put(KEY_MARKER_LAT, map_marker_lat_value);
marker_position_values.put(KEY_MARKER_LNG, map_marker_long_value);
Log.d(TAG,"Attempting Update of MAP MARKER TABLE.");
long marker_id_table = db.update(TABLE_MAP_MARKER, marker_position_values, HOME_ID + " = ?", new String[]{String.valueOf(home_id)});
if (marker_id_table == 0) {
Log.d(TAG,String.valueOf(marker_id_table) + " rows Updated for MAP MARKER TABLE, attempting insert");
//long insertid = db.insertWithOnConflict(TABLE_MAP_MARKER, null, marker_position_values, SQLiteDatabase.CONFLICT_REPLACE);
long insertid = db.insertWithOnConflict(TABLE_MAP_MARKER, null, marker_position_values, SQLiteDatabase.CONFLICT_IGNORE);
//long insertid = db.insert(TABLE_MAP_MARKER, null, marker_position_values); //<<<< used to check what conflicts occur
if (insertid < 0) {
Log.d(TAG,"No row inserted into MAP MARKER TABLE.");
} else {
Log.d(TAG,"Row inserted into MAP MARKER TABLE.");
}
} else {
Log.d(TAG,"Update of MARKER TABLE Successful (" + String.valueOf(marker_id_table) + " rows updated.)");
}
ContentValues unit_values= new ContentValues();
//unit_values.put(HOME_ID, marker_id_table); ????????
unit_values.put(HOME_ID,home_id);
unit_values.put(HOME_TYPE, home_type);
unit_values.put(AMOUNT_RENT, amount_rent);
Log.d(TAG,"Attempting Update of MAP MARKER INFO TABLE.");
long unit_id_table = db.update(TABLE_MAP_MARKER_INFO, unit_values, HOME_ID + " = ?", new String[]{String.valueOf(home_id)});
if (unit_id_table == 0) {
Log.d(TAG,String.valueOf(unit_id_table) + " rows Updated for MAP MARKER INFO TABLE, attempting insert");
long insertid = db.insertWithOnConflict(TABLE_MAP_MARKER_INFO, null, unit_values, SQLiteDatabase.CONFLICT_IGNORE);
//long insertid = db.insertWithOnConflict(TABLE_MAP_MARKER_INFO, null, unit_values, SQLiteDatabase.CONFLICT_REPLACE);
//long insertid = db.insert(TABLE_MAP_MARKER_INFO, null, unit_values); //<<<< used to check what conflicts occur
if (insertid < 0) {
Log.d(TAG,"No row inserted into MAP MARKER INFO TABLE");
} else {
Log.d(TAG,"Row inserted into MAP MARKER INFO TABLE.");
}
} else {
Log.d(TAG,"Update of MARKER INFO TABLE Successful (" + String.valueOf(unit_id_table) + " rows updated.)");
}
}
}
Then Invoking the above using (deletes any existing rows and attempts to add 4 rows):-
DatabaseHelper mDBHlp = new DatabaseHelper(this);
mDBHlp.getWritableDatabase().delete(DatabaseHelper.TABLE_MAP_MARKER,null,null);
mDBHlp.getWritableDatabase().delete(DatabaseHelper.TABLE_MAP_MARKER_INFO,null,null);
mDBHlp.addMarkerAndMarkerInfo(1,100,100,"House",45.64,"Attempt 1");
mDBHlp.addMarkerAndMarkerInfo(2,150,150,"House",65.64,"Attempt 2");
// Duplicate
mDBHlp.addMarkerAndMarkerInfo(3,100,100,"House",45.64,"Attempt 3");
// Another duplicate
mDBHlp.addMarkerAndMarkerInfo(1,100,100,"House",45.64,"Attempt 4");
results in :-
09-04 22:47:16.459 1917-1917/? D/AMAMI-Attempt 1: Attempting Update of MAP MARKER TABLE.
0 rows Updated for MAP MARKER TABLE, attempting insert
09-04 22:47:16.463 1917-1917/? D/AMAMI-Attempt 1: Row inserted into MAP MARKER TABLE.
Attempting Update of MAP MARKER INFO TABLE.
0 rows Updated for MAP MARKER INFO TABLE, attempting insert
09-04 22:47:16.467 1917-1917/? D/AMAMI-Attempt 1: Row inserted into MAP MARKER INFO TABLE.
09-04 22:47:16.467 1917-1917/? D/AMAMI-Attempt 2: Attempting Update of MAP MARKER TABLE.
0 rows Updated for MAP MARKER TABLE, attempting insert
09-04 22:47:16.471 1917-1917/? D/AMAMI-Attempt 2: Row inserted into MAP MARKER TABLE.
Attempting Update of MAP MARKER INFO TABLE.
0 rows Updated for MAP MARKER INFO TABLE, attempting insert
09-04 22:47:16.475 1917-1917/? D/AMAMI-Attempt 2: Row inserted into MAP MARKER INFO TABLE.
09-04 22:47:16.475 1917-1917/? D/AMAMI-Attempt 3: Attempting Update of MAP MARKER TABLE.
0 rows Updated for MAP MARKER TABLE, attempting insert
No row inserted into MAP MARKER TABLE.
Attempting Update of MAP MARKER INFO TABLE.
0 rows Updated for MAP MARKER INFO TABLE, attempting insert
No row inserted into MAP MARKER INFO TABLE
09-04 22:47:16.475 1917-1917/? D/AMAMI-Attempt 4: Attempting Update of MAP MARKER TABLE.
09-04 22:47:16.479 1917-1917/? D/AMAMI-Attempt 4: Update of MARKER TABLE Successful (1 rows updated.)
Attempting Update of MAP MARKER INFO TABLE.
09-04 22:47:16.483 1917-1917/? D/AMAMI-Attempt 4: Update of MARKER INFO TABLE Successful (1 rows updated.)
i.e.
It would also appear that there may be no need for the two tables, as there seems to be a 1-1 relationship between the two tables. Therefore it may be that a single table could have all the columns (latitude, longitude, type and rent).
Adding the method logAllRows (as follows) and calling this at the end of each update/insert. Allows you to see the actual data.
private void logAllRows(String tablename, String TAG) {
SQLiteDatabase db = this.getWritableDatabase();
StringBuilder sb = new StringBuilder();
Cursor csr = db.query(tablename,null,null,null,null,null,null);
while (csr.moveToNext()) {
sb.append("\nRow # = ").append(String.valueOf(csr.getPosition() + 1));
for (int i=0; i < csr.getColumnCount(); i++) {
sb.append("\n\tColumn is ").append(csr.getColumnName(i)).append(" Value is ");
int columntype = csr.getType(i);
switch (columntype) {
case Cursor.FIELD_TYPE_NULL:
sb.append("NULL");
break;
case Cursor.FIELD_TYPE_FLOAT:
sb.append(String.valueOf(csr.getDouble(i)));
break;
case Cursor.FIELD_TYPE_INTEGER:
sb.append(String.valueOf(csr.getInt(i)));
break;
case Cursor.FIELD_TYPE_STRING:
sb.append(csr.getString(i));
break;
case Cursor.FIELD_TYPE_BLOB:
sb.append("BLOB");
break;
}
}
}
csr.close();
Log.d(TAG,sb.toString());
}
logAllRows09-05 02:49:56.875 3016-3016/? D/AMAMI-Attempt 1: Attempting Update of MAP MARKER TABLE.
09-05 02:49:56.879 3016-3016/? D/AMAMI-Attempt 1: 0 rows Updated for MAP MARKER TABLE, attempting insert
Row inserted into MAP MARKER TABLE. ID = 1
Table is map_marker Row # = 1
Column is _id Value is 1
Column is map_marker_lat Value is 100
Column is map_marker_lng Value is 100
Attempting Update of MAP MARKER INFO TABLE.
0 rows Updated for MAP MARKER INFO TABLE, attempting insert
09-05 02:49:56.887 3016-3016/? D/AMAMI-Attempt 1: Row inserted into MAP MARKER INFO TABLE. ID = 1
Table is map_marker_info Row # = 1
Column is _id Value is 1
Column is home_type Value is House
Column is amount_rent Value is 45.64
09-05 02:49:56.887 3016-3016/? D/AMAMI-Attempt 2: Attempting Update of MAP MARKER TABLE.
0 rows Updated for MAP MARKER TABLE, attempting insert
09-05 02:49:56.903 3016-3016/? D/AMAMI-Attempt 2: Row inserted into MAP MARKER TABLE. ID = 2
Table is map_marker Row # = 1
Column is _id Value is 1
Column is map_marker_lat Value is 100
Column is map_marker_lng Value is 100
Table is map_marker Row # = 2
Column is _id Value is 2
Column is map_marker_lat Value is 150
Column is map_marker_lng Value is 150
Attempting Update of MAP MARKER INFO TABLE.
0 rows Updated for MAP MARKER INFO TABLE, attempting insert
09-05 02:49:56.907 3016-3016/? D/AMAMI-Attempt 2: Row inserted into MAP MARKER INFO TABLE. ID = 2
Table is map_marker_info Row # = 1
Column is _id Value is 1
Column is home_type Value is House
Column is amount_rent Value is 45.64
Table is map_marker_info Row # = 2
Column is _id Value is 2
Column is home_type Value is House
Column is amount_rent Value is 65.64
09-05 02:49:56.907 3016-3016/? D/AMAMI-Attempt 3: Attempting Update of MAP MARKER TABLE.
0 rows Updated for MAP MARKER TABLE, attempting insert
No row inserted into MAP MARKER TABLE.
Table is map_marker Row # = 1
Column is _id Value is 1
Column is map_marker_lat Value is 100
Column is map_marker_lng Value is 100
Table is map_marker Row # = 2
Column is _id Value is 2
Column is map_marker_lat Value is 150
Column is map_marker_lng Value is 150
Attempting Update of MAP MARKER INFO TABLE.
09-05 02:49:56.911 3016-3016/? D/AMAMI-Attempt 3: 0 rows Updated for MAP MARKER INFO TABLE, attempting insert
No row inserted into MAP MARKER INFO TABLE
Table is map_marker_info Row # = 1
Column is _id Value is 1
Column is home_type Value is House
Column is amount_rent Value is 45.64
Table is map_marker_info Row # = 2
Column is _id Value is 2
Column is home_type Value is House
Column is amount_rent Value is 65.64
09-05 02:49:56.911 3016-3016/? D/AMAMI-Attempt 4: Attempting Update of MAP MARKER TABLE.
09-05 02:49:56.915 3016-3016/? D/AMAMI-Attempt 4: Update of MARKER TABLE Successful (1 rows updated.)
Table is map_marker Row # = 1
Column is _id Value is 1
Column is map_marker_lat Value is 100
Column is map_marker_lng Value is 100
Table is map_marker Row # = 2
Column is _id Value is 2
Column is map_marker_lat Value is 150
Column is map_marker_lng Value is 150
Attempting Update of MAP MARKER INFO TABLE.
09-05 02:49:56.919 3016-3016/? D/AMAMI-Attempt 4: Update of MARKER INFO TABLE Successful (1 rows updated.)
Table is map_marker_info Row # = 1
Column is _id Value is 1
Column is home_type Value is House
Column is amount_rent Value is 45.64
Table is map_marker_info Row # = 2
Column is _id Value is 2
Column is home_type Value is House
Column is amount_rent Value is 65.64