Android Question Deleted SQL records are not Actually Deleted

Pelky

Active Member
Licensed User
Longtime User
I am hoping someone can assist me with this. The application is rather simple in that it records driver information/stats on a weekly basis and allows for monthly analysis over several months. When it starts up it checks for records that are old and no longer relevant - these are deleted.
You will see that it does a count before and after the delete. I get no errors and the logs show the dates correctly but the record count at the end is the same as before. I also don't get a "failed to delete" log.
I thank you in advance for any suggestions

B4X:
Dim RemovalCursor As ResultSet
    
    RemovalCursor = DB.ExecQuery("SELECT * FROM TotRecord Order By TotDate")
    Log("Total Records for deletion Check = "&RemovalCursor.RowCount)
    
    Do While RemovalCursor.NextRow
        Dim RecordDated As String = RemovalCursor.GetString("TotDate")
        If RecordDated < RemovalDate Then
           Log(DateTime.Date(RecordDated)&" Older Than "&DateTime.Date(RemovalDate)&" Will Be deleted")
            Try
                DB.ExecNonQuery2("Delete From LogRecord where LogDate = ?", Array As Object(RecordDated))
            Catch
                Log("Failed to delete Record")
            End Try
        End If
    Loop
    RemovalCursor.Close
    
' Count Remaining Records
    Dim RemovalCursor As ResultSet
    RemovalCursor = DB.ExecQuery("SELECT * FROM TotRecord Order By TotDate")
    Log("Total Records After Deletion = "&RemovalCursor.RowCount)
    RemovalCursor.Close
' End Count
 

LucaMs

Expert
Licensed User
Longtime User
The fact that no errors are reported does not mean that the records were deleted, but only that, in this case, no records matching the "Where" clause were found.

You don't need all that "Do While" loop to delete those records, the following line is sufficient (and necessary):
B4X:
DB.ExecNonQuery2("Delete From LogRecord where LogDate = ?", Array As Object(RecordDated))

Check the number of records that meet that condition, before and after deletion:
B4X:
Dim NumOfRecs As Int
NumOfRecs = DB.ExecQuerySingleResult2("SELECT COUNT(*) FROM LogRecord WHERE LogDate = ?", Array As Object(RecordDated))
Log(NumOfRecs)
 
Upvote 0

Pelky

Active Member
Licensed User
Longtime User
Thank you Lucas but surely DB.ExecNonQuery2("Delete From LogRecord where LogDate = ?", Array As Object(RecordDated)) will only delete the one record. There are a number of records and the loop is there to check each record in the database and only those older than RecordDated
are deleted.
Sorry maybe I wasn't clear before.
 
Upvote 0

Pelky

Active Member
Licensed User
Longtime User
sorry Lucas but I meant to say only records older than the RemovalDate are to be removed.
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
But the point is that since you don't get any errors, you think that some records have been deleted but the total number of records remains unchanged. In reality the query has not deleted any records and rightly does not generate errors.
Check the dates (and their formats) carefully.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
If RecordDated < RemovalDate Then
Are the date stored as Long integers? Why did you compare them using String?

Luca is right that you don't need to query and delete it one by one.
You can do a SELECT query if you want to know is there any rows matched the condition but this is optional step.

The real query is just 1.
B4X:
DB.ExecNonQuery2("DELETE FROM LogRecord WHERE LogDate < ?", Array As Object(RemovalDate))
 
Upvote 0

Pelky

Active Member
Licensed User
Longtime User
I did do that using the log to show me the actual detail held. Surely if it has entered into the 'IF' and shown me that the date, Extracted from the ResultSet, is older (correctly in the log) then by using the same criteria the delete should work.
 
Upvote 0

Pelky

Active Member
Licensed User
Longtime User
I seem to have found my error with the the assistance of the forum. You are all heroes

Thank you so very much
 
Upvote 0
Top