Android Question Clear data in SQLLite table [Solved]

[Roger Daley]

High all,

I am loading a CSV file into a SQLLite table, the code below works well for appending data to the existing data but i want to give the user the option to replace the existing data. It may require data to be cleared before loading new data or possibly there is an overwrite option.
I see 3 options:
1. Drop the table and recreate it. [Brutal]
2. Delete each record by iteration. [Tedious]
3. Overwrite existing data?

Is there some way [DBUtils??] to clear data from the table in the db?

Regards Roger

Sub BtnImpSiteList_click
    ' This routine:
    ' Checks PERMISSION_WRITE_EXTERNAL_STORAGE.  Creates folder ABT if required
    ' Checks for the file "sitelist.csv" in the directory File.DirRootExternal&"/ABT/", "sitelist.csv".
    VibBip
    'Close Menu
    PnlMenu.Left = -PnlMenu.Width

    rp.CheckAndRequest(rp.PERMISSION_WRITE_EXTERNAL_STORAGE)
    Wait For Activity_PermissionResult (Permission As String, Result As Boolean)
    If Permission =  "android.permission.WRITE_EXTERNAL_STORAGE" And Result =  True  Then
        If File.Exists(File.DirRootExternal&"/ABT/", "") = False Then File.MakeDir(File.DirRootExternal&"/ABT/", "")
    End If
  
    ' If the file is not found the user briefly sees a short "File not found" message.
    ' If the file is found the user is given the choice to append this file to the existing sites db, replace the existing data or cancel the operation.  WIP
    ' If the user chooses to proceed the routine will check the file for valid data and no futher action is required by the user.   WIP
  
    If File.Exists(File.DirRootExternal&"/ABT/", "sitelist.csv") = False Then
        CustomToastMsg.Show("File 'sitelist.csv' not found",1000,Gravity.TOP,0,25%y)
    Else
        ' CHECK VALIDITY OF DATA.
        ' First record may be heading.
        ' Each record check first colum for text.  If no text assume end of list and exit.
        ' If text is found check the next colum for a valid number -180 to +180.  If true check 3rd colum for valid number -90 to +90.
        ' Write 3 colums

        Private su As StringUtils
        Private Table As List
        Table = su.LoadCSV(File.DirRootExternal&"/ABT/", "sitelist.csv",",")
        Private Table2 As List
        Private Items() As String
        Private TestTrue As Boolean
        Table2.Initialize
        For i = 0 To Table.Size - 1
            TestTrue = True
            Items = Table.Get(i)
          
    'checks for blank in first colum? if so loading is complete
            If Items(0) = "" Then Exit
          
    'Next     check if second colum is valid Longitude   -180 to 180. 
            If IsNumber (Items(1)) Then
                If Items(1) < -180  Or Items(1) > 180 Then TestTrue = False
            Else
                TestTrue = False
            End If
      
    'Next     check if second colum is valid Latitude  -90 to 90.     
            If IsNumber (Items(2)) Then
                If Items(2) < -90 Or Items(2)> 90 Then TestTrue = False
            Else             
                TestTrue = False
            End If

            If TestTrue Then
                Private m As Map
                m.Initialize
                m.Put("SiteName", Items(0))
                m.Put("Longitude", Items(1))
                m.Put("Latitude", Items(2))
                Table2.Add(m)
            End If
        Next
        DBUtils.InsertMaps(SQL1, "sites", Table2)
      
        ShowTable
'Display site list
        wbvTable.Left = 0
        wbvTable.BringToFront
        lblSelectedItem.Left = 0
        lblSelectedItem.BringToFront
        BtnSitesBack.BringToFront
        BtnSitesBack.Left = 0
        pnlToolBox.Left = 0
        pnlToolBox.BringToFront
        BtnStore.BringToFront
        BtnLandMark.BringToFront
        BtnRecall.BringToFront
        BtnDel.BringToFront
        BtnHelp.BringToFront
        BtnSunDial.Visible = False
        LblBtnHide.BringToFront
        SitesFlag = 1
      
        CustomToastMsg.Show("Upload complete",1000,Gravity.TOP,0,25%y)
    End If
End Sub

 

[mangojack]

I have not tried it , but there is a Truncate command.

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

EDIT ** It would appear that TRUNCATE is NOT a SQLite Keyword / action.

 

[mangojack]

I have just realized you are using DBUtils ... so unsure if there is any way to clear a db Table from there.

For your knowledge ... the DELETE keyword without any Trigger / Where clause has the same result as TRUNCATE , ie:

It will delete all records and clear / reset the internal record ID's

SQL1.ExecNonQuery("DELETE FROM MyTable")

 

[Roger Daley]

I have just realized you are using DBUtils ... so unsure if there is any way to clear a db Table from there.

For your knowledge ... the DELETE keyword without any Trigger / Where clause has the same result as TRUNCATE , ie:

It will delete all records and clear / reset the internal record ID's

SQL1.ExecNonQuery("DELETE FROM MyTable")

Thanks mangojack,

Delete looks good. I'm on the road at the moment but I will give it a try when I get back and report on the results.

Regards Roger

 

[Roger Daley]

mangojack,

Your suggestion worked perfectly. I inserted the line into the code above just before the DBUtils.insertmap line and it did the job.

I also tried

        Private MP As Map
        MP.Initialize    
        DBUtils.DeleteRecord(SQL1,"sites",MP)

but the program did not like the empty map. Perhaps I have the wrong syntax.

Thanks again
Regards Roger