Android Question Bulk Insert into SQLite

D

Derek Jee

Active Member
Licensed User
Longtime User
Hello there

I am adding many records from the response to a HttpUtils2 PostString into my SQLite db on my device. I just want to know if there is a faster way other than looping through each row in the response and executing this code:

SQL1.ExecNonQuery("INSERT INTO Inspections VALUES('" & Var1 & "', '" & Var2 & "', '" & Var3 & "', '" & Var4 & "')")

There will be a few thousand rows and it fetches the data to the device really quickly. Inserting into the db takes a good 20 seconds..

Thanks in advance for your advice..

Derek.
 
Sort by date Sort by votes

DonManfred

Expert
Licensed User
Longtime User
you are writing to an sqlite db on your device, yes?
try to use transactions...
B4X: 
Sub InsertManyRows
    SQL1.BeginTransaction
    Try
        For i = 1 To 500
            SQL1.ExecNonQuery2("INSERT INTO table1 VALUES ('def', ?, ?)", Array As Object(i, i))
        Next
        SQL1.TransactionSuccessful
    Catch
        Log(LastException.Message)
    End Try
    SQL1.EndTransaction
End Sub
 
Upvote 0
D

Derek Jee

Active Member
Licensed User
Longtime User
Thank you Manfred

I see the Insert statement is different to my original statement in your suggestion. I guess that I can do something like this or does it have to be an array, as I already have it in a response JSON string.. ?

SQL1.ExecNonQuery2("INSERT INTO table1 VALUES ('def', ?, ?)", Var1 & ", " & Var2 & "," & Var3)

Thank you,


Derek.
 
Upvote 0
D

Derek Jee

Active Member
Licensed User
Longtime User
I think I just got it.. Thank you for the assistance Manfred. My statement now looks like this and is speedy!!!..

SQL1.ExecNonQuery2("INSERT INTO Inspections VALUES(?,?,?,?)", ArrayAsObject( Var1, Var2 , Var3 , Var4))


Derek..
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Derek Jee said:
I think I just got it.. Thank you for the assistance Manfred. My statement now looks like this and is speedy!!!..

SQL1.ExecNonQuery2("INSERT INTO Inspections VALUES(?,?,?,?)", ArrayAsObject( Var1, Var2 , Var3 , Var4))
Click to expand...

Great you managed it!
And sorry that i did not noticed that my code is just an example and you have to change it to your needs...
 
Upvote 0
D

Derek Jee

Active Member
Licensed User
Longtime User
DonManfred said:
Great you managed it!
And sorry that i did not noticed that my code is just an example and you have to change it to your needs...
Click to expand...

No apologies needed Manfred.. You helped greatly. I am running before I can walk with this project, it's great though
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…