iOS Question SQL and GUI

[moore_it]

Hello again,

when i use an SQL.ExecNonQuery the gui go in halt mode ...
It's true ?
Ther's other methods for non halt the gui ?

Thanks for your patience ..

 

[Erel]

Currently there are no asynchronous methods in the SQL library (such as in B4A SQL library). They will be added.

However can you post your code? If you are using transactions correctly then it should be very fast.

 

[moore_it]

I need to drop table ...
sql.ExecNonQuery("DROP TABLE IF EXISTS " & Table)
re-create table
slq.ExecNonQuery( "CREATE TABLE IF NOT EXISTS " & Table (fields)")
and insert the data
sql.ExecNonQuery("INSERT INTO " & Table & "(fields)")

 

[Erel]

It will be 100 times faster if you create a single transaction for this.

 

[moore_it]

Please explain ...

thanks

 

[Erel]

Please post your current SQL code.

 

[moore_it]

' first - read from a file text the record format
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
myStreamList.Initialize
myStreamList = File.ReadList(File.DirAssets & "/DEFS/", filename)
myStructAr.Initialize
For i = 0 To myStreamList.Size - 1
Dim myArray() As String
Dim s As myStruct
myArray = Regex.Split(";",myStreamList.Get(i))
If myArray.Length >= 2 Then
s.Initialize
s.tipo = myArray(0)
s.width = myArray(1)
s.name = myArray(2)
myStructAr.Add(s)
End If
Next

' drop table
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
filename = filename.Replace(".DEF","")
Globals.dbDama.ExecNonQuery("Drop table if exists " & filename)

' make command create table
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim table As String
table = "CREATE TABLE IF NOT EXISTS " & filename & "("
For i = 0 To myStructAr.Size - 1
Dim s As myStruct
s = myStructAr.Get(i)
If i < 9 Then
If i < myStructAr.Size - 1 Then
table = table & s.name & " TEXT,"
Else
table = table & s.name & " TEXT)"
End If
Else
If i < myStructAr.Size - 1 Then
table = table & s.name & " TEXT,"
Else
table = table & s.name & " TEXT)"
End If
End If
Next
Globals.dbDama.ExecNonQuery(table)

' make import command
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
filename = filename & ".TXT"
Dim st As String
st = File.ReadString(File.DirAssets & "/IN/", filename) ', "Windows-1251")
Dim value() As String = Regex.Split( Chr(10), st)
filename = filename.Replace(".TXT","")
Dim insert As String
Dim row As String
Dim field as string
Dim fields As List
For i = 0 To value.Length - 1
row = value(i)
' data in the row ?
If row.Length = 0 Then
Continue
End If
row = row.Replace("'"," ")
' Load record data
fields.Initialize
Dim cnt As Int
For n = 0 To myStructAr.Size - 1
Dim s As myStruct
s = myStructAr.Get(n)
If s.width > 0 Then
field = row.SubString2(cnt,cnt + s.width)
Else
field = ""
End If
fields.Add(field.trim)
cnt = cnt + s.width
Next
' make insert command
''''''''''''''''''''''''''''''''''''''''''''''''''
insert = "INSERT INTO " & filename & " VALUES ("
For n = 0 To fields.Size - 1
insert = insert & "'" & fields.Get(n) & "'"
If n < fields.size - 1 Then
insert = insert & ","
End If
Next
insert = insert & " ) "
Globals.dbDama.ExecNonQuery(insert)
Next

 

[Erel]

Please use [code]code here...[/code] tags when posting code.

Your code is slow because you are not creating a transaction. The result is that a new transaction is created for each ExecNonQuery call.

Change your code to:

Globals.dbDama.BeginTransaction
   Try
    'all your current code
    '...
    Globals.dbDama.TransactionSuccessful
   Catch
    Log("Error: " & LastException)
    Globals.dbDama.Rollback
   End Try

 

[moore_it]

OOOOps sorry

WOW this solution is fastest ....

Thanks