Android Studio - Kotlin - SQLite Database isn't changing the ID for one of the corresponding tables

Question

I'm trying to implement my first SQLite Database in an Android App regarding obtaining location coordinates to keep track of where the user has been.

I'm trying to add information from my entry into two tables:

• a Location table that contains information of the places name, id, latitude, and longitude information &

• a CheckIn table that contains information of the places address, corresponding location_id to know which location it corresponds to, latitude, longitude, and time of check in.

Whenever I try to do this, my entry is never updated for the Locations table, solely the CheckIn table, despite using the insert() function to insert into the Locations table as well the id is not updating for the Location table.

I've went through my app in a debugger and I can't figure out what's causing the problem here, as there's no error and the program proceeds just fine to add in the necessary info for the CheckIn table.

I've tried checking StackOverFlow but I can't quite find anything that has been able to help fix my problem. If there's anyone who could help me, it'd be greatly appreciated

My add function:

    fun addLoc_CheckIn(Entry: Locations)
    {
    val selectQuery = "SELECT * FROM $LOCATIONS ORDER BY ID"
    val db = this.readableDatabase
    val cursor = db.rawQuery(selectQuery, null)

    var con = 0

    if (cursor.moveToFirst())
    {
        while (cursor.moveToNext())
        {
            val pSLong = cursor.getDouble(cursor.getColumnIndex(SLONG))
            val pCLong = cursor.getDouble(cursor.getColumnIndex(CLONG))
            val pSLat = cursor.getDouble(cursor.getColumnIndex(SLAT))
            val pCLat = cursor.getDouble(cursor.getColumnIndex(CLAT))
            val Theta = (pCLong * Entry.cLong) + (pSLong * Entry.sLong)

            var dist = (pSLat * Entry.sLat) + (pCLat * Entry.cLat * Theta)
            // dist = (Math.acos(dist) * 180.00 / Math.PI) * (60 * 1.1516 * 1.609344) / 1000
            dist = Math.acos(dist) * 6380000

            if (dist <= 30)
            {
                con = 1

                val db1 = this.writableDatabase
                val values = ContentValues()
                values.put(LOC_ID, cursor.getInt(cursor.getColumnIndex(ID)))
                values.put(ADDRESS, Entry.Checks[0].Address)
                values.put(LATI, Entry.Lat)
                values.put(LONGI, Entry.Long)
                values.put(TIME, Entry.Checks[0].Date_Time)
                db1.insert(CHECKINS, null, values)

                break
            }
        }
    }

    if (con == 0)
    {
        val db1 = this.writableDatabase
        val values = ContentValues()
        values.put(LOC_NAME, Entry.Name)
        values.put(LAT, Entry.Lat)
        values.put(LONG, Entry.Long)
        values.put(CLAT, Entry.cLat)
        values.put(SLAT, Entry.sLat)
        values.put(CLONG, Entry.cLong)
        values.put(SLONG, Entry.sLong)
        Entry.Id = db1.insert(LOCATIONS, null, values)

        val cvalues = ContentValues()
        cvalues.put(LOC_ID, Entry.Id)
        cvalues.put(ADDRESS, Entry.Checks[0].Address)
        cvalues.put(LATI, Entry.Lat)
        cvalues.put(LONGI, Entry.Long)
        cvalues.put(TIME, Entry.Checks[0].Date_Time)
        db1.insert(CHECKINS, null, cvalues)
    }
}

My OnCreate function with the corresponding companion object:

    companion object {
    private val DATABASE_NAME = "LocationsDB"
    private val DATABASE_VERSION = 1

    // 1st Table - Unique Check Ins
    private val LOCATIONS = "LOCATIONS"
    private val ID = "ID"
    private val LOC_NAME = "LOC NAME"
    private val LAT = "LAT"
    private val LONG = "LONG"
    private val CLAT = "CLAT"
    private val SLAT = "SLAT"
    private val CLONG = "CLONG"
    private val SLONG = "SLONG"

    // 2nd Table - Repeated Check Ins
    private val CHECKINS = "CHECKINS"
    private val CHECKIN_ID = "CHECKIN_ID"
    private val LOC_ID = "LOC_ID"
    private val ADDRESS = "ADDRESS"
    private val TIME = "TIME"
    private val LATI = "LAT"
    private val LONGI = "LONG"
}

override fun onCreate(p0: SQLiteDatabase?) {
    val LOCATION_QUERY = "CREATE TABLE " + LOCATIONS + "(" + ID +
            " INTEGER PRIMARY KEY AUTOINCREMENT, " + LOC_NAME +
            " TEXT, " + LAT + " INTEGER, " + LONG + " INTEGER, " +
            CLAT + " INTEGER, "+ SLAT + " INTEGER, " + CLONG + " INTEGER, "+ SLONG + " INTEGER " + ")"

    val CHECKIN_QUERY = "CREATE TABLE " + CHECKINS + "(" +
            LOC_ID + " INTEGER, " + CHECKIN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + LATI + " INTEGER, " + LONGI + " INTEGER, " + ADDRESS +
            " TEXT, " + TIME + " TEXT " + ")"

    p0!!.execSQL(LOCATION_QUERY)
    p0.execSQL(CHECKIN_QUERY)
}

Now, in my constructor for the Location class and the CheckIns class, I have the id's set to -1, which is what the id for the location remains, even after using the insert() function. Now, this doesn't cause me any issues with regards to adding in my CheckIns as well incrementing the ids in my CheckIns table and I doubt it's causing an issue but I figured it'd be best to include the information, just in case.

Answer 1

I believe that you have an issue with the name of the column due to using

private val LOC_NAME = "LOC NAME"

A column name cannot have a space unless it is enclosed in special characters as per SQL As Understood By SQLite - SQLite Keywords.

This isn't an issue when the table is create (the column name will be LOC). However, when you attempt to insert you will get a syntax error, the row will not be inserted but as you are using the SQLiteDatabase insert method, the error is trapped and processing continues.

However, in the log you would see something similar to :-

2019-10-29 15:47:35.119 12189-12189/aso.so58600930insert E/SQLiteLog: (1) near "NAME": syntax error
2019-10-29 15:47:35.121 12189-12189/aso.so58600930insert E/SQLiteDatabase: Error inserting LOC NAME=MyLoc LAT=100 CLAT=120 LONG=110 SLAT=140 CLONG=130 SLONG=150
    android.database.sqlite.SQLiteException: near "NAME": syntax error (code 1 SQLITE_ERROR): , while compiling: INSERT INTO LOCATIONS(LOC NAME,LAT,CLAT,LONG,SLAT,CLONG,SLONG) VALUES (?,?,?,?,?,?,?)

You could circumvent the above by using :-

        val db1 = this.writableDatabase
        val values = ContentValues()
        values.put("LOC", Entry.Name)
        values.put(LAT, Entry.Lat)
        values.put(LONG, Entry.Long)
        values.put(CLAT, Entry.cLat)
        values.put(SLAT, Entry.sLat)
        values.put(CLONG, Entry.cLong)
        values.put(SLONG, Entry.sLong)
        Entry.Id = db1.insert(LOCATIONS, null, values)

However, it is not suggested that you use the above BUT that instead you correct the name, e.g. using :-

private val LOC_NAME = "LOC_NAME" 

then clear the App's data or uninstall the App and then rerun the App.

This fix assumes that you are developing the App and can afford to lose any existing data. You could retain data but this is a little more complicated as you basically have to create a new table with the appropriate column name, copy the data from the original table, rename or drop the original table and then rename the new table to be the original name.