Android Question Array / List - check against SQLite fieldnames - better solution ?

[Hubert Brandel]

Hi,
I have CSV-Files to load into SQLite DB, I have made some helper functions reading the header line in an array and data in maps (line by line). Those CSV-Files normaly have the same contense, but I have to handle older (less fields=no problem) and newer with more fields as in the DB.

If i could scan a array independend from upper or lower or scan a list in same way, live would be easier ;-)
Does some have some hints how to get it faster, robuster or even more elegant ?

Sub FixFelderCSV(cDB As String) ' cDB ist the name of the table in the DB.
    Dim cur As Cursor
    Dim DBFields As List 
    Dim x, nMax As Int
    Dim sSQL As StringBuilder
    Dim sCMD, sFeld As String
       
    Try
        cur = Main.SQL1.ExecQuery( "SELECT * FROM " & cDB & " LIMIT 1" )
        DBFields.Initialize 
        nMax = cur.ColumnCount-1
        ' read all the fieldnames in LowerCase
        For x = 0 To nMax
            sFeld = cur.GetColumnName(x).Trim.ToLowerCase
            DBFields.Add( sFeld ) 
        Next   
        For x = 0 To nFieldCount-1 ' CSV fieldnames
            sFeld = sHeader(x).Trim.ToLowerCase
            If DBFields.IndexOf( sFeld ) = -1 Then
                sSQL.Initialize
                sSQL.Append( "ALTER TABLE ")
                sSQL.Append( cDB )
                sSQL.Append( " ADD COLUMN ")
                sSQL.Append( sFeld )               
                sSQL.Append( " TEXT DEFAULT '' ;" )
                Log ("New field" & cDB & "  " & sFeld )               
                sCMD = sSQL.ToString
                Log ("WRITE " & sCMD )               
                Try
                    Main.SQL1.ExecNonQuery(    sCMD )
                    Log( "OK ! " & sCMD )
                Catch
                    Log( "Fehler ! " & sCMD )
                    Log( LastException.Message )
                    Msgbox("Error '" & sFeld & "' not in LG !","Structure error")
                    ExitApplication
                End Try
               
            End If   
        Next

        cur.close

    Catch
        Log( "Error CSV header " )
        Log( LastException.Message )
        Msgbox("Error CSV header ","Structure error")
        ExitApplication
    End Try
End Sub

I am missing array functions like IndexOfIgnoreCase() ... in lists too ;-)

PS: it works and I hope on faster devices even fast enough
My Galaxy S+ is a little bit slow

 

[sorex]

it will be faster when you concatenate all these update queries into one big sql query and execute that at the end.

lemme see if there is an example on this forum

 

[Hubert Brandel]

These here are only the missing fields, and I did not get a syntax to add more than one field once with ALTER TABLE ADD COLUMN
But there are normaly no new fields or only 1 or 2, so this will not be a problem.

With the massdata i block 10 updates as a transaction

begin transaction
10 updates
end transaction

(I do not have here the exact syntax now...)

 

[sorex]

here you go...

http://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content

search for begin transaction.

not sure if it will help in this case tho.

 

[sorex]

this will speed it up too as there is no need to scan through the fields when the field amount is the same.

if nFieldCount<>nmax then   ' added
   For x = 0 To nFieldCount-1 ' CSV fieldnames
            sFeld = sHeader(x).Trim.ToLowerCase
            If DBFields.IndexOf( sFeld ) = -1 Then
                sSQL.Initialize
                sSQL.Append( "ALTER TABLE ")
                sSQL.Append( cDB )
                sSQL.Append( " ADD COLUMN ")
                sSQL.Append( sFeld )            
                sSQL.Append( " TEXT DEFAULT '' ;" )
                Log ("New field" & cDB & "  " & sFeld )            
                sCMD = sSQL.ToString
                Log ("WRITE " & sCMD )            
                Try
                    Main.SQL1.ExecNonQuery(    sCMD )
                    Log( "OK ! " & sCMD )
                Catch
                    Log( "Fehler ! " & sCMD )
                    Log( LastException.Message )
                    Msgbox("Error '" & sFeld & "' not in LG !","Structure error")
                    ExitApplication
                End Try
            
            End If
        Next
end if  ' added

 

[Hubert Brandel]

Thanks

 

[sorex]

if nFieldCount>nmax then

might make more sense since you want to expand

 

[Hubert Brandel]

this will speed it up too as there is no need to scan through the fields when the field amount is the same.

This would be a good idea if the "producer app" would not be so flexible they can change the fields at the end (one less + one new)

 

[sorex]

ok, so that extra check ain't 100% safe to use then

 

[sorex]

what about this:

read in the field in the right order as in the csv and build a string of the columnnames

cur = Main.SQL1.ExecQuery( "SELECT column1,column2,column3 FROM " & cDB & " LIMIT 1" )
DBFields.Initialize
nMax = cur.ColumnCount-1
dim fields as string
For x = 0 To nMax
 sFeld = cur.GetColumnName(x).Trim.ToLowerCase
 DBFields.Add( sFeld ) 
 fields=fields&","&sfeld
Next 
fields=fields.right(fields.lenght-1)  'replace it with the right mid(?) syntac

this will give you "column1,column2,column3" as result

do the same for your csv header line


then compare both strings if they are equal or not, if not run that field lookup loop.