I wouldn’t write a 1000 line long insert statement for starters
It takes a ton of memory
I would use transactions & commit every 1000 or so
Something like
If Not IsConnected Then
MsgBox("Create the database and create the table first.")
'Return False
End If
Dim tab, CR As String
tab=Encodings.ASCII.Chr(9)
CR=Encodings.ASCII.Chr(13)
Dim Input As TextInputStream
Dim file As FolderItem
file = GetOpenFolderItem("")
If file <> Nil Then
Try
Input = TextInputStream.Open(file)
Input.Encoding = Encodings.UTF8
Catch e As IOException
MsgBox("Cannot open file: " + Str(e.ErrorNumber) + " " + e.Message)
Return
End Try
If Input <> Nil Then
// ****************************************************************************************** //
// ****************************** BEGIN SQLITE TABLE BUILD ****************************** //
// ****************************************************************************************** //
Dim line, vSQLCreateTable, vSQLRowData, vSQLInsertBatch As String
Dim vCounter As Integer = 1
Dim i, vLineNumber, vBatchSize, vBatchCounter As Integer
vBatchSize=1000 'set a size limit for the batch insert.
vLineNumber=1
line = Input.ReadLine
vSQLInsertBatch=""
app.DB.SQLExecute("begin transaction") // <<<<<<<<<<
While Not Input.EOF
If vLineNumber=1 Then
'In practice the first line is the table fields. But that is created
'in a separate button for this debuggin.
If mIsConnected Then
vLineNumber=vLineNumber+1
End If
Else
// ADD THE INDIVIDUAL RECORDS IN BATCHES OF 1000 OR LESS
Dim vRecordData(), vSQLInsertRecord As String
line = Input.ReadLine 'each line will be an arbitrary number of fields
vRecordData=line.Split(tab) 'create an array of the data fields
vSQLInsertBatch = ""
For i=0 To UBound(vRecordData)
If i<>UBound(vRecordData)Then
vSQLRowData=vSQLRowData+""""+vRecordData(i)+""", "
Else
vSQLRowData=vSQLRowData+""""+vRecordData(i)+""", "+""+Str(vCounter)+");"
vSQLInsertRecord="INSERT INTO `C1CID` VALUES ("+vSQLRowData+""
vSQLInsertBatch=vSQLInsertBatch+vSQLInsertRecord
app.DB.SQLExecute(vSQLInsertBatch) // <<<<<<<<<< insert this row but do not auto commit
vCounter=vCounter+1 'count to the upper bound of the array
End If
Next
If mIsConnected And vCounter >= vBatchSize Then
app.DB.SQLExecute("commit") // <<<<<<<<<<
vCounter = 0 // <<<<<<<<<<
app.DB.SQLExecute("begin transaction") // <<<<<<<<<<
End If
End If
Wend
If mIsConnected And vCounter > 0 Then
app.DB.SQLExecute("commit")
vCounter = 0
End If
End If
Else
MsgBox("Connection failed!")
End If
If App.DB.Error Then
AddDataStatusLabel.Text = "DB Error: " + App.DB.ErrorMessage
Else
AddDataStatusLabel.Text = "Data added."
'Return False
End If