Android Question Bulk Insert into SQLite

[Derek Jee]

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.

 

[DonManfred]

you are writing to an sqlite db on your device, yes?
try to use transactions...

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

 

[Derek Jee]

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.

 

[Derek Jee]

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..

 

[DonManfred]

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))

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...

 

[Derek Jee]

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...

No apologies needed Manfred.. You helped greatly. I am running before I can walk with this project, it's great though