Android Question Update if a SQL record exist, insert if it does not.

[Marvel]

I have been facing a problem that I can't seem to solve. I have a SQL table that I want to update regularly with the timer function. The goal is to automatically update existing records or insert records if it is not existing. The code below is what I came up with but it is not working, I just can't get it to update a record, instead, it creates multiple records. I want the code to check if a record exists (using the package name to check the table) and update if it exists or insert new record into the table if it does not (in a case of a newly installed app)

To better understand the context in which I am using this code, the routine is meant to check the app usage of each app every 2 seconds and update the SQL data accordingly.

Sub Timer1_Tick
    For i = 0 To usage1.Size -1
        Dim us As UsageStats = usage1.Get(i)
        Dim package As String = us.PackageName
        Dim time As String = DateTime.Time(us.lasttimeused)
        Dim date As String = DateTime.Date(us.LastTimeUsed)
        Dim foregroundtime As Long = us.TotalTimeInForeground
        
        Dim Cursor As ResultSet = sql.ExecQuery("SELECT * FROM AppList WHERE PackageName =  ' "& package &" '")
    
        If Cursor.RowCount > 0  And date = DateTime.Date(DateTime.Now) Then
            sql.ExecNonQuery("UPDATE AppList SET LastTimeUsed = ' "& time & " ', LastDateUsed = ' "& date & " ', TotalTime = ' "& totaltime & " ', foregroundtime = ' "& foregroundtime &" ' WHERE PackageName = ' "& package &" ';")
            Log(Cursor.RowCount)
        
            If Cursor.RowCount = 0 And  date = DateTime.Date(DateTime.Now) Then
                sql.ExecNonQuery2("INSERT INTO AppList VALUES(?,?,?,?,?)", Array As String(package,time,date,totaltime,foregroundtime))
                Log(Cursor.RowCount)
            End If
        End If
    Next
    
    sql.ExecQuery("SELECT * FROM AppList ORDER BY LastTimeUsed DESC")
End Sub

 

[josejad]

Do you get Log(Cursor.RowCount) > 0 in some cases in the log window?

 

[Marvel]

Do you get Log(Cursor.RowCount) > 0 in some cases in the log window?

I do not get that at all. It always returns 0 even when I check the database and the records clearly exist

 

[Mahares]

sql.ExecQuery("SELECT * FROM AppList ORDER BY LastTimeUsed DESC")

The statement returns a cursor. It should be:

Cursor = sql.ExecQuery("SELECT * FROM AppList ORDER BY LastTimeUsed DESC")

Not sure if it is your isssue. Also you are better off using parameterized query for the UPDATE like you used for the INSERT.
Also, you have an extra space here at the end AFTER THE WORD PACKAGE , it should not be there :

Dim Cursor As ResultSet = sql.ExecQuery("SELECT * FROM AppList WHERE PackageName =  ' "& package &" '")

 

[Marvel]

Hmmm

The statement returns a cursor. It should be:

Cursor = sql.ExecQuery("SELECT * FROM AppList ORDER BY LastTimeUsed DESC")

Not sure if it is your isssue. Also you are better off using parameterized query for the UPDATE like you used for the INSERT.
Also, you have an extra space here at the end AFTER THE WORD PACKAGE , it should not be there :

Dim Cursor As ResultSet = sql.ExecQuery("SELECT * FROM AppList WHERE PackageName =  ' "& package &" '")

..let me make these changes and see if it'll work. I'll get back to you.

 

[Mahares]

Dim Cursor As ResultSet = sql.ExecQuery("SELECT * FROM AppList WHERE PackageName = ' "& package &" '")

It should be: (remove the extra space)

Dim Cursor As ResultSet = sql.ExecQuery("SELECT * FROM AppList WHERE PackageName =  ' "& package &"'")

In other words: This: package &" '") should be: package &"'")

 

[Mahares]

Dim Cursor As ResultSet = sql.ExecQuery("SELECT * FROM AppList WHERE PackageName = ' "& package &" '")

If you type it like this you would not have to worry about single quotes escaping:

Dim Cursor As ResultSet = sql.ExecQuery2("SELECT * FROM AppList WHERE PackageName =  ?", Array As String (package))

 

[Marvel]

It should be: (remove the extra space)

Dim Cursor As ResultSet = sql.ExecQuery("SELECT * FROM AppList WHERE PackageName =  ' "& package &"'")

In other words: This: package &" '") should be: package &"'")

I removed the extra space, it did not solve the issue. It is still inserting multiple records.

 

[Mahares]

I removed the extra space, it did not solve the issue. It is still inserting multiple records.

Please try the syntax I used in post #7 using parameterized query

 

[Marvel]

I tried t

If you type it like this you would not have to worry about single quotes escaping:

Dim Cursor As ResultSet = sql.ExecQuery2("SELECT * FROM AppList WHERE PackageName =  ?", Array As String (package))

I tried this too. It eliminated the multiple records but the table is not updating. It is almost like it is ignoring the Cursor ResultSet totally

 

[Mahares]

I tried this too. It eliminated the multiple records but the table is not updating. It is almost like it is ignoring the Cursor ResultSet totally

If you look at your UPDATE statement, you also have an unnecessary space there too at the end. That is why a parameterized query is always much better.

 

[Marvel]

If you look at your UPDATE statement, you also have an unnecessary space there too at the end. That is why a parameterized query is always much better.

I have gotten the Insert Statement to work but I've not gotten the update statement to work. I will try switching it to parameterized statement. Thanks.

 

[Mahares]

I have gotten the Insert Statement to work but I've not gotten the update statement to work.

It should be something like this:

sql.ExecNonQuery2("UPDATE AppList SET LastTimeUsed = ?, LastDateUsed = ?, TotalTime = ?, foregroundtime = ? WHERE PackageName = ?", _ 
    Array As String(time,date,totaltime,foregroundtime, package))

 

[Marvel]

I finally got it to work thanks to you @Mahares. I never saw the importance of using parameterized statements until now. And the syntax is actually simpler and easy to understand.

This is the code that worked:

Sub Timer1_Tick
    Dim totaltime As Int = 0
    For i = 0 To usage1.Size -1
        Dim us As UsageStats = usage1.Get(i)
        Dim package As String = us.PackageName
        Dim time As String = DateTime.Time(us.lasttimeused)
        Dim date As String = DateTime.Date(us.LastTimeUsed)
        Dim foregroundtime As Long = us.TotalTimeInForeground
        
        Dim Cursor As ResultSet = sql.ExecQuery2("SELECT * FROM AppList WHERE PackageName =  ?", Array As String (package))
    
        If Cursor.RowCount > 0  And date = DateTime.Date(DateTime.Now) Then
            sql.ExecNonQuery2("UPDATE AppList SET LastTimeUsed = ?, LastDateUsed = ?, TotalTime = ?, foregroundtime = ? Where PackageName = ?", _
                Array As String(time,date,totaltime,foregroundtime,package))
    
        Log(Cursor.RowCount)
        End If
        
        If Cursor.RowCount = 0 And  date = DateTime.Date(DateTime.Now) Then
        sql.ExecNonQuery2("INSERT INTO AppList VALUES(?,?,?,?,?)", _
            Array As String(package,time,date,totaltime,foregroundtime))
            Log(Cursor.RowCount)
            End If

    Next

    sql.ExecQuery("SELECT * FROM AppList ORDER BY LastTimeUsed DESC")
End Sub

 

[sorex]

you insert is probably always happening after the update as it is in the same if part of the if/then statement and the rowcount is being reset after the update