Android Question Clear data in SQLLite table [Solved]

Discussion in 'Android Questions' started by Roger Daley, Oct 10, 2019 at 3:08 AM.

  1. Roger Daley

    Roger Daley Well-Known Member Licensed User

    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

    Code:
    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
     
    Last edited: Oct 10, 2019 at 3:14 AM
  2. mangojack

    mangojack Well-Known Member Licensed User

    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.
     
    Last edited: Oct 10, 2019 at 6:02 AM
    Roger Daley likes this.
  3. mangojack

    mangojack Well-Known Member Licensed User

    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

    Code:
    SQL1.ExecNonQuery("DELETE FROM MyTable")
     
    Roger Daley likes this.
  4. Roger Daley

    Roger Daley Well-Known Member Licensed User


    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
     
  5. Roger Daley

    Roger Daley Well-Known Member Licensed User

    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
    Code:
    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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice