Android Question I have a problem with SQLite that fails when inserting a large number of records into a table. Please Help.

[jose sanjuan]

Hello,

I have this problem with sqlite that fails when inserting records into a table.

The problem occurs when inserting a large number of records.

I attach an application with the problem.

Thanks.

 

[jahswant]

Sub Button1_Click
    
    Dim SQL2 As SQL
    SQL2.Initialize(DBFileDir,DBFileName,True)

    Log("0")
    Msgbox("0","")
    SQL2.ExecNonQuery("Delete From masclis")
    Log("1")
    Msgbox("1","")
    For wI=0 To 30000
        SQL2.AddNonQueryToBatch("Insert Into masclis (codigo,nombre,nif,direccion,otro) Values ('123ED','','','','')",Null)
    Next
    Dim SenderFilter As Object = SQL2.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)
    Msgbox("2","")
    Log("2")
            
    SQL2.Close
    
End Sub

 

[jose sanjuan]

wow!!! Works perfectly!!! thank you very much!!! i'm very grateful!!!

 

[klaus]

Or this code:

Sub Button1_Click
   
    Dim SQL2 As SQL
    SQL2.Initialize(DBFileDir,DBFileName,True)

    Log("0")
    Msgbox("0","")
    SQL2.ExecNonQuery("Delete From masclis")
    Log("1")
    Msgbox("1","")
   
    SQL2.BeginTransaction
    Try
        For wI=0 To 30000
            SQL2.ExecNonQuery("Insert Into masclis (codigo,nombre,nif,direccion,otro) Values ('123ED','','','','')")
        Next
        SQL2.TransactionSuccessful
    Catch
        Log(LastException.Message)
    End Try
   
    SQL2.EndTransaction

    Msgbox("2","")
    Log("2")
           
    SQL2.Close
   
End Sub

 

[Mahares]

Sometimes it is better and safer to use parameterized query.
So instead of:

SQL2.AddNonQueryToBatch("Insert Into masclis (codigo,nombre,nif,direccion,otro) Values ('123ED','','','','')",Null)

Use:

SQL2.AddNonQueryToBatch("Insert Into masclis (codigo,nombre,nif,direccion,otro) Values (?,?,?,?,?)", Array As String("123ED","","","",""))

Also, sometimes it works faster to drop the table and recreate it instead of using: SQL2.ExecNonQuery("Delete From masclis")
@klaus : you forgot to add after end try this line: SQL2.EndTransaction

 

[klaus]

@klaus : you forgot to add after try this line: SQL2.EndTransaction

Thank you !
I have updated post #4.

 

[Jeffrey Cameron]

If you're going to be doing this regularly on a table with a large number of records, I would recommend adding a VACUUM statement after your DELETE.