Android Question App crash, but no information

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Using this code to write large .csv file to SQLite:

B4X:
Sub CSV2Table3(strFolder As String, _
               strFile As String, _
               bFileHasFieldNames As Boolean, _
               arrFieldNames() As String, _ 'this will be provided by GetCSVProperties
               btSeparatorByte As Byte, _
               btEncloserByte As Byte, _
               btEndOfLineByte As Byte, _
               lMaxBytes As Long, _
               iRowsToInsert As Int, _
               iRowsInCSV As Int, _
               strTable As String, _
               arrEmisDateCols() As Int, _
               bShowProgress As Boolean) As ResumableSub
             
    Dim i As Long
    Dim c As Int
    Dim x As Int
    Dim lRAFFileSize As Long
    Dim lPosition As Long
    Dim lBytes As Long
    Dim lUB As Long
    Dim arrBytes() As Byte
    Dim bFinalByteBatch As Boolean
    Dim iColumns As Int
    Dim iRows As Int
    Dim lSeparatorPos As Long
    Dim lEncloserPos As Long
    Dim lChr10Pos As Long
    Dim lChr10PosLast As Long
    Dim lFirstDataByte As Int
    Dim bCSVHasEncloserBytes As Boolean
    Dim bExit As Boolean
    Dim strSQL As String
    Dim iMod As Int
    Dim iRowsToDo As Int
    Dim iTableCols As Int
    Dim iAddNullColumns As Int
    Dim bEmisDates As Boolean
    
    bEmisDates = arrEmisDateCols.Length > 0
    
    If bEmisDates Then
        DateTime.DateFormat = "dd-MMM-yyyy"
    End If
    
    If iRowsToInsert = 0 Then
        iRowsToDo = iRowsInCSV
    Else
        iRowsToDo = iRowsToInsert
    End If

    'this will be a zero-based UTF8 byte array
    '-----------------------------------------
    RAF.Initialize(strFolder, strFile, True)
    lRAFFileSize = RAF.Size
    If iRowsToInsert = 0 Then iRowsToInsert = lRAFFileSize / 2
    
    'declare here as it also used to get the position of the first data byte (after the field names)
    Dim arrBytes(1000) As Byte 'this should be enough to get the first row
    RAF.ReadBytes(arrBytes, 0, 1000, 0)
    
    If bFileHasFieldNames = False Then
        iColumns = GetCSVHeaders(arrBytes, btSeparatorByte, btEncloserByte, btEndOfLineByte).Length
    Else
        iColumns = arrFieldNames.Length
    End If
    
    Dim arrRowData(iColumns) As String
        
    strSQL = "insert into " & strTable & " values(" & MakePlaceHolders(iColumns, iAddNullColumns) & ")"
    
    If bFileHasFieldNames Then
        'as we are skipping the the column names
        '---------------------------------------
        lFirstDataByte = GetCSVFirstDataByte(arrBytes, btEndOfLineByte)
        lPosition = lFirstDataByte - 1 'always start the batches of bytes with the previous last Chr(10)
    Else
        lFirstDataByte = 0
        lPosition = 0
    End If
    
    lEncloserPos = -1
    lSeparatorPos = -1
    
    If bShowProgress Then
        If B4XProgressBar1.IsInitialized = False Then
        Activity.LoadLayout("ProgressBar")
        End If
        iMod = CInt(iRowsToDo / 100)
        If iMod = 0 Then iMod = 1
        B4XProgressBar1.Show(Activity, iRowsToDo, False, 1, "CSV > Table, rows: ", "/", "")
        Sleep(0)
    End If
    
    Do While True
        
        If bExit Then
            Exit
        End If
        
        If lPosition + lBytes >= lRAFFileSize Then
            Exit
        End If
        
        lPosition = lPosition + lChr10PosLast
        
        If lPosition + lMaxBytes > lRAFFileSize Then
            lBytes = lRAFFileSize - lPosition
            bFinalByteBatch = True
        Else
            lBytes = lMaxBytes
        End If
        
        'ReadBytes (Buffer() As Byte, StartOffset As Int, Length As Int, Position As Long) As Int
        '----------------------------------------------------------------------------------------
        'Reads bytes from the stream And into To the given Array.
        'Buffer - Array of bytes where the data will be written To.
        'StartOffset - The first byte read will be written To Buffer(StartOffset).
        'Length - Number of bytes To read.
        'Position - The position of the first byte To read.
        'Returns the number of bytes read which Is equal To Length (unless the File Is smaller than the requested length).
        
        Dim arrBytes(lBytes) As Byte
        lBytes = RAF.ReadBytes(arrBytes, 0, lBytes, lPosition)
        
        'get the index of the last btEndOfLineByte in arrBytes
        'so we don't go past that in the next bit byte loop
        '-----------------------------------------------------
        If bFinalByteBatch = False Then
            lUB = lBytes - 1
            Do While arrBytes(lUB) <> btEndOfLineByte
                lUB = lUB - 1
            Loop
        Else
            lUB = lBytes - 1
        End If
        
        lEncloserPos = -1
        lSeparatorPos = -1
        lChr10Pos = 0 'as all the arrBytes will start with byte 10
        bExit = False
        
        'so we ignore enclosers in the fields row!
        '-----------------------------------------
        bCSVHasEncloserBytes = CSVHasEncloserBytes(arrBytes, 0, 0, btEncloserByte)
    
        'so we ignore enclosers in the fields row!
        '-----------------------------------------
        bCSVHasEncloserBytes = CSVHasEncloserBytes(arrBytes, 0, 0, btEncloserByte)
        
        'not sure this is needed, seems we can process even very large file in one transaction
        If General.cConn.bInTransaction Then
            General.cConn.EndTransaction
        End If
        General.cConn.BeginTransaction
        
        If bCSVHasEncloserBytes Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
            If arrBytes(0) = btEncloserByte Then
                lEncloserPos = 0
            End If
        
            If iColumns > 1 Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
                ''''''''''''''''''''''''''
                'SEPARATORS AND ENCLOSERS'
                ''''''''''''''''''''''''''
                For i = 1 To lUB
                    Select Case arrBytes(i)
                        Case btSeparatorByte
                            If c < iColumns And lEncloserPos = -1 Then
                                If lSeparatorPos = -1 Then
                                    If lChr10Pos = -1 Then
                                        'this will be a separator after a closing encloser!
                                        '--------------------------------------------------
                                        lSeparatorPos = i
                                    Else
                                        If i - lChr10Pos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - 1) - lChr10Pos, "UTF-8")
                                        End If    'If i - lChr10Pos > 1
                                        c = c + 1
                                    End If
                                    lChr10Pos = -1
                                Else    'If lSeparatorPos = -1
                                    If i - lSeparatorPos > 1 Then
                                        arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                    End If    'If (i - 1) - (lSeparatorPos + 1) > 1
                                    c = c + 1
                                    lChr10Pos = -1
                                End If    'If lSeparatorPos = -1
                                lSeparatorPos = i
                            End If    'If c < lColumns and lEncloserPos = -1
                            
                            If bFinalByteBatch And i = lUB Then
                                If bEmisDates Then
                                    For x = 0 To arrEmisDateCols.Length - 1
                                        arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                    Next
                                End If
                                General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                iRows = iRows + 1
                                If bShowProgress Then
                                    If iRows Mod iMod = 0 Then
                                        B4XProgressBar1.Progress = iRows
                                        Sleep(0)
                                    End If
                                End If
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If

                        Case btEncloserByte
                            If lEncloserPos = -1 Then
                                lEncloserPos = i
                            Else 'If lEncloserPos = -1
                                If c < iColumns Then
                                    If i - lEncloserPos > 1 Then
                                        arrRowData(c) = BytesToString(arrBytes, lEncloserPos + 1, (i - 1) - lEncloserPos, "UTF-8")
                                    End If    'If i - lEncloserPos = 1
                                    c = c + 1
                                End If    'If c < lColumns
                                lEncloserPos = -1
                            End If 'If lEncloserPos = -1
                            
                            If bFinalByteBatch And i = lUB Then
                                If bEmisDates Then
                                    For x = 0 To arrEmisDateCols.Length - 1
                                        arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                    Next
                                End If
                                General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                iRows = iRows + 1
                                If bShowProgress Then
                                    If iRows Mod iMod = 0 Then
                                        B4XProgressBar1.Progress = iRows
                                        Sleep(0)
                                    End If
                                End If
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            Else
                                lSeparatorPos = -1
                                lChr10Pos = -1
                            End If
                            
                        Case btEndOfLineByte
                            If lEncloserPos = -1 Then
                                If c < iColumns Then
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - 2) - lSeparatorPos > 0 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 2) - lSeparatorPos, "UTF-8")
                                        End If 'If (i - 2) - lSeparatorPos > 0
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If i - lSeparatorPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                        End If
                                        c = c + 1
                                    End If    'If arrBytes(i - 1) = 13
                                End If

                                iRows = iRows + 1
                                lSeparatorPos = -1
                                lChr10Pos = i
                                lChr10PosLast = i
                                c = 0
                                
                                If bEmisDates Then
                                    For x = 0 To arrEmisDateCols.Length - 1
                                        arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                    Next
                                End If
                                
                                General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                            
                                If bShowProgress Then
                                    If iRows Mod iMod = 0 Then
                                        B4XProgressBar1.Progress = iRows
                                        Sleep(0)
                                    End If
                                End If

                                If iRows = iRowsToInsert Then
                                    bExit = True
                                    Exit
                                End If
                            End If    'If lEncloserPos = -1
                            
                            If bFinalByteBatch And i = lUB Then
                                bExit = True 'row already processed, so no need to do this again here
                                Exit
                            End If
                            
                        Case Else
                            
                            If bFinalByteBatch And i = lUB Then
                                If c < iColumns Then
                                    If i - lSeparatorPos > 1 Then
                                        arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                        If bEmisDates Then
                                            For x = 0 To arrEmisDateCols.Length - 1
                                                arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                            Next
                                        End If
                                        General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                        'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                        iRows = iRows + 1
                                        If bShowProgress Then
                                            If iRows Mod iMod = 0 Then
                                                B4XProgressBar1.Progress = iRows
                                                Sleep(0)
                                            End If
                                        End If
                                    End If
                                End If
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If 'If bFinalByteBatch And i = lUB
                            
                    End Select
                    
                    If bExit Then Exit
            
                Next
                
            Else 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                '''''''''''''''''''''''''''''
                'NO SEPARATORS BUT ENCLOSERS'
                '''''''''''''''''''''''''''''
                For i = 1 To lUB
                    Select Case arrBytes(i)
                        Case btEncloserByte
                            If lEncloserPos = -1 Then
                                lEncloserPos = i
                            Else
                                If c < iColumns Then
                                    If i - lEncloserPos = 1 Then
                                        c = c + 1
                                    Else    'If i - lEncloserPos = 1
                                        arrRowData(c) = BytesToString(arrBytes, lEncloserPos + 1, (i - 1) - lEncloserPos, "UTF-8")
                                        c = c + 1
                                    End If    'If i - lEncloserPos = 1
                                End If    'If c < lColumns
                                lEncloserPos = -1
                            End If
                            
                            If bFinalByteBatch And i = lUB Then
                                If bEmisDates Then
                                    For x = 0 To arrEmisDateCols.Length - 1
                                        arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                    Next
                                End If
                                General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                iRows = iRows + 1
                                If bShowProgress Then
                                    If iRows Mod iMod = 0 Then
                                        B4XProgressBar1.Progress = iRows
                                        Sleep(0)
                                    End If
                                End If
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            Else
                                lChr10Pos = -1
                            End If
                            
                        Case btEndOfLineByte
                            If lEncloserPos = -1 Then
                                If c < iColumns Then
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - lChr10Pos) -1 > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8")
                                        End If
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If (i - lChr10Pos) > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                        End If
                                        c = c + 1
                                    End If    'If i - lSeparatorPos > 1
                                End If    'If c < lColumns
                    
                                iRows = iRows + 1
                                lSeparatorPos = -1
                                lChr10Pos = i
                                lChr10PosLast = i
                                c = 0
                                
                                If bEmisDates Then
                                    For x = 0 To arrEmisDateCols.Length - 1
                                        arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                    Next
                                End If
                                
                                General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                            
                                If bShowProgress Then
                                    If iRows Mod iMod = 0 Then
                                        B4XProgressBar1.Progress = iRows
                                        Sleep(0)
                                    End If
                                End If
                                
                                If iRows = iRowsToInsert Then
                                    bExit = True
                                    Exit
                                End If
                                
                            End If    'If lEncloserPos = -1
                            
                            If bFinalByteBatch And i = lUB Then
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If
                            
                        Case Else
                            If bFinalByteBatch Then
                                If i = lUB Then
                                    'If lEncloserPos = -1 Then
                                    If c < iColumns Then
                                        If (i - lChr10Pos) > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                            If bEmisDates Then
                                                For x = 0 To arrEmisDateCols.Length - 1
                                                    arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                                Next
                                            End If
                                            General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                            'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                            iRows = iRows + 1
                                            If bShowProgress Then
                                                If iRows Mod iMod = 0 Then
                                                    B4XProgressBar1.Progress = iRows
                                                    Sleep(0)
                                                End If
                                            End If
                                        End If
                                    End If    'If c < lColumns
                                    bExit = True
                                    Exit
                                End If 'If lPosition + i + 1 = lRAFFileSize Then 'last file byte
                            End If 'If bFinalByteBatch
                            
                    End Select
                    
                    If bExit Then Exit
                    
                Next
                
            End If 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
        Else  'If bCSVHasEncloserBytes<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
            '''''''''''''''''''''''''''''
            'SEPARATORS BUT NO ENCLOSERS'
            '''''''''''''''''''''''''''''
            If iColumns > 1 Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                For i = 1 To lUB
                    Select Case arrBytes(i)
                        Case btSeparatorByte
                            If c < iColumns Then
                                If lSeparatorPos = -1 Then
                                    If lChr10Pos = -1 Then
                                        'this will be a separator after a closing encloser!
                                        '--------------------------------------------------
                                        lSeparatorPos = i
                                    Else
                                        If i - lChr10Pos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - 1) - lChr10Pos, "UTF-8")
                                        End If    'If i - lChr10Pos > 1
                                        c = c + 1
                                    End If
                                    lChr10Pos = -1
                                Else    'If lSeparatorPos = -1
                                    If i - lSeparatorPos > 1 Then
                                        arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                    End If    'If (i - 1) - (lSeparatorPos + 1) > 1
                                    c = c + 1
                                    lChr10Pos = -1
                                End If    'If lSeparatorPos = -1
                                lSeparatorPos = i
                            End If    'If c < lColumns
                            
                            If bFinalByteBatch And i = lUB Then
                                If bEmisDates Then
                                    For x = 0 To arrEmisDateCols.Length - 1
                                        arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                    Next
                                End If
                                General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                iRows = iRows + 1
                                If bShowProgress Then
                                    If iRows Mod iMod = 0 Then
                                        B4XProgressBar1.Progress = iRows
                                        Sleep(0)
                                    End If
                                End If
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If

                        Case btEndOfLineByte
                            If c < iColumns Then
                                If lSeparatorPos > -1 Then
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - 2) - lSeparatorPos > 0 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 2) - lSeparatorPos, "UTF-8")
                                        End If
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If i - lSeparatorPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                        End If 'If i - lSeparatorPos > 1
                                        c = c + 1
                                    End If    'If arrBytes(i - 1) = 13
                                Else    'If lSeparatorPos > -1
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - lChr10Pos) - 1 > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8")
                                        End If 'If (i - lChr10Pos) - 1 > 1
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If (i - lChr10Pos) > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                        End If 'If (i - lChr10Pos) > 1
                                        c = c + 1
                                    End If    'If i - lSeparatorPos > 1
                                End If    'If lSeparatorPos > -1
                    
                                iRows = iRows + 1
                                lSeparatorPos = -1
                                lChr10Pos = i
                                lChr10PosLast = i
                                c = 0
                                
                                If bEmisDates Then
                                    For x = 0 To arrEmisDateCols.Length - 1
                                        arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                    Next
                                End If
                                General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                If bShowProgress Then
                                    If iRows Mod iMod = 0 Then
                                        B4XProgressBar1.Progress = iRows
                                        Sleep(0)
                                    End If
                                End If
                                
                                If iRows = iRowsToInsert Then
                                    bExit = True
                                    Exit
                                End If
                            
                            End If    'If c < iColumns
                            
                            If bFinalByteBatch And i = lUB Then
                                bExit = True
                                Exit
                            End If
                            
                        Case Else
                            If bFinalByteBatch And i = lUB Then
                                If c < iColumns Then
                                    If lSeparatorPos > -1 Then
                                        If i - lSeparatorPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, i - lSeparatorPos, "UTF-8")
                                        End If 'If i - lSeparatorPos > 1
                                    Else    'If lSeparatorPos > -1
                                        If (i - lChr10Pos) > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) + 1 , "UTF-8")
                                        End If 'If (i - lChr10Pos) > 1
                                    End If    'If lSeparatorPos > -1
                                    
                                    If bEmisDates Then
                                        For x = 0 To arrEmisDateCols.Length - 1
                                            arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                        Next
                                    End If
                                    General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                    'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    iRows = iRows + 1
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                End If    'If c < iColumns

                                bExit = True
                                Exit
                            End If 'If bFinalByteBatch And i = lUB
                            
                    End Select
                    
                    If bExit Then Exit

                Next
                
            Else 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                ''''''''''''''''''''''''''''''''
                'NO SEPARATORS AND NO ENCLOSERS'
                ''''''''''''''''''''''''''''''''
                For i = 1 To lUB
                    Select Case arrBytes(i)
                        Case btEndOfLineByte
                            If c < iColumns Then
                                If arrBytes(i - 1) = 13 Then
                                    If (i - lChr10Pos) -1 > 0 Then
                                        arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8")
                                    End If
                                    c = c + 1
                                Else    'If arrBytes(i - 1) = 13
                                    If (i - lChr10Pos) > 0 Then
                                        arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                    End If
                                    c = c + 1
                                End If    'If i - lSeparatorPos > 1
                            End If    'If c < lColumns
                    
                            iRows = iRows + 1
                            lSeparatorPos = -1
                            lChr10Pos = i
                            lChr10PosLast = i
                            c = 0
                            
                            If bEmisDates Then
                                For x = 0 To arrEmisDateCols.Length - 1
                                    arrRowData(arrEmisDateCols(x)) = General.EmisDate2XLDate(arrRowData(arrEmisDateCols(x)))
                                Next
                            End If
                            
                            General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                            'General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                            
                            If bShowProgress Then
                                If iRows Mod iMod = 0 Then
                                    B4XProgressBar1.Progress = iRows
                                    Sleep(0)
                                End If
                            End If
                            
                            If iRows = iRowsToInsert Then
                                bExit = True
                                Exit
                            End If

                            If bFinalByteBatch And i = lUB Then
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If

                    End Select
                    
                    If bExit Then Exit
                    
                Next
                
            End If 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
        End If 'If bCSVHasEncloserBytes<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    
    Loop
    
    'Just one big transaction seems OK, even with 2 million rows to insert!
    '----------------------------------------------------------------------
    General.cConn.EndTransaction
    
    If bShowProgress Then
        B4XProgressBar1.Reset
    End If
    
    Return iRows

End Sub

Using this on a large file (377887 rows) I get an app crash without any information (also looked at the unfiltered logs).
The other problem that the crashes happen at different rows of the .csv.
I have tried with smaller byte arrays and also with more frequent transaction and also with just one transaction but it makes no difference.
Have also tried with AddNonQueryToBatch but for some reason that doesn't work at all.
The code works fine on a smaller .csv (65000 rows)
I am using SQLCipher, latest version.

Any suggestions how to find out what the problem is here?

RBS

RBS
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
I've never seen a case where there is no error in the logs when an app crashes. Switch to USB debug mode and check the unfiltered logs.
I wouldn't use such complex (and incorrect as it ignores the string encoding) code to parse CSV.

The way I would have done it is with:
1. Split the large string.
2. Parse a part with CSVParser.
3. Add the queries with AddNonQueryToBatch. Make sure to remove the transaction related code.
4. Wait for the batch to complete and parse the next one.
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
I've never seen a case where there is no error in the logs when an app crashes. Switch to USB debug mode and check the unfiltered logs.
I wouldn't use such complex (and incorrect as it ignores the string encoding) code to parse CSV.

The way I would have done it is with:
1. Split the large string.
2. Parse a part with CSVParser.
3. Add the queries with AddNonQueryToBatch. Make sure to remove the transaction related code.
4. Wait for the batch to complete and parse the next one.

I am using USB debug mode and I have checked the unfiltered logs, but nil obvious to me pointing to the cause of the crash.
I can post the unfiltered logs but it is a large amount of text.

I do understand this code will fail with different encodings, but I don't use any other encodings in this app. I do realize the code is complex, but
it is very fast, but haven't compared to your suggested approach.
I have changed to AddNonQueryToBatch and that works fine, but exactly the same crash at random places in the file, all about half way after doing about
150.000 rows. I have a feeling it is come kind of memory problem as that could explain the random place where it crashes.
The produced SQLite table is fine, but has of course less rows than in the .csv.
The file I am testing on has only integer numbers (some large, but nil past the capabilities of SQLite) and there are no enclosers.

RBS
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
I am using USB debug mode and I have checked the unfiltered logs, but nil obvious to me pointing to the cause of the crash.
I can post the unfiltered logs but it is a large amount of text.

I do understand this code will fail with different encodings, but I don't use any other encodings in this app. I do realize the code is complex, but
it is very fast, but haven't compared to your suggested approach.
I have changed to AddNonQueryToBatch and that works fine, but exactly the same crash at random places in the file, all about half way after doing about
150.000 rows. I have a feeling it is come kind of memory problem as that could explain the random place where it crashes.
The produced SQLite table is fine, but has of course less rows than in the .csv.
The file I am testing on has only integer numbers (some large, but nil past the capabilities of SQLite) and there are no enclosers.

RBS

Have solved this now. The problem was that the SQLite commits were too large, having reduced this now to committing every 12000 rows it all works.
I am not sure why I previously could have this figure at 200000.

This is the code now that works fine:

B4X:
Sub CSV2Table3(strFolder As String, _
               strFile As String, _
               bFileHasFieldNames As Boolean, _
               arrFieldNames() As String, _ 'this will be provided by GetCSVProperties
               btSeparatorByte As Byte, _
               btEncloserByte As Byte, _
               btEndOfLineByte As Byte, _
               lMaxBytes As Long, _
               iRowsToInsert As Int, _
               iRowsInCSV As Int, _
               strTable As String, _
               bShowProgress As Boolean) As ResumableSub
             
    Dim i As Long
    Dim c As Int
    Dim x As Int
    Dim lRAFFileSize As Long
    Dim lPosition As Long
    Dim lBytes As Long
    Dim lUB As Long
    Dim arrBytes() As Byte
    Dim bFinalByteBatch As Boolean
    Dim iColumns As Int
    Dim iRows As Int
    Dim lSeparatorPos As Long
    Dim lEncloserPos As Long
    Dim lChr10Pos As Long
    Dim lChr10PosLast As Long
    Dim lFirstDataByte As Int
    Dim bCSVHasEncloserBytes As Boolean
    Dim bExit As Boolean
    Dim strSQL As String
    Dim iMod As Int
    Dim iRowsToDo As Int
    Dim iTableCols As Int
    Dim iAddNullColumns As Int
    Dim bEmisDates As Boolean
    Dim bReadyForNextBytes As Boolean
    Dim iCommittedAtRow As Int
    
    If iRowsToInsert = 0 Then
        iRowsToDo = iRowsInCSV
    Else
        iRowsToDo = iRowsToInsert
    End If

    'this will be a zero-based UTF8 byte array
    '-----------------------------------------
    RAF.Initialize(strFolder, strFile, True)
    lRAFFileSize = RAF.Size
    If iRowsToInsert = 0 Then iRowsToInsert = lRAFFileSize / 2
    
    'declare here as it also used to get the position of the first data byte (after the field names)
    Dim arrBytes(1000) As Byte 'this should be enough to get the first row
    RAF.ReadBytes(arrBytes, 0, 1000, 0)
    
    If bFileHasFieldNames = False Then
        iColumns = GetCSVHeaders(arrBytes, btSeparatorByte, btEncloserByte, btEndOfLineByte).Length
    Else
        iColumns = arrFieldNames.Length
    End If
    
    Dim arrRowData(iColumns) As String
    
    strSQL = "insert into " & strTable & " values(" & MakePlaceHolders(iColumns) & ")"
    
    If bFileHasFieldNames Then
        'as we are skipping the the column names
        '---------------------------------------
        lFirstDataByte = GetCSVFirstDataByte(arrBytes, btEndOfLineByte)
        lPosition = lFirstDataByte - 1 'always start the batches of bytes with the previous last Chr(10)
    Else
        lFirstDataByte = 0
        lPosition = 0
    End If
    
    lEncloserPos = -1
    lSeparatorPos = -1
    
    If bShowProgress Then
        If B4XProgressBar1.IsInitialized = False Then
            Activity.LoadLayout("ProgressBar")
        End If
        iMod = CInt(iRowsToDo / 100)
        If iMod = 0 Then iMod = 1
        B4XProgressBar1.Show(Activity, iRowsToDo, False, 1, "CSV > Table, rows: ", "/", "")
        Sleep(0)
    End If
    
    Do While True
        
        If bExit Then
            Exit
        End If
        
        If lPosition + lBytes >= lRAFFileSize Then
            Exit
        End If
        
        lPosition = lPosition + lChr10PosLast
        
        If lPosition + lMaxBytes > lRAFFileSize Then
            lBytes = (lRAFFileSize - lPosition) - 1
            bFinalByteBatch = True
        Else
            lBytes = lMaxBytes
        End If
        
        'ReadBytes (Buffer() As Byte, StartOffset As Int, Length As Int, Position As Long) As Int
        '----------------------------------------------------------------------------------------
        'Reads bytes from the stream And into To the given Array.
        'Buffer - Array of bytes where the data will be written To.
        'StartOffset - The first byte read will be written To Buffer(StartOffset).
        'Length - Number of bytes To read.
        'Position - The position of the first byte To read.
        'Returns the number of bytes read which Is equal To Length (unless the File Is smaller than the requested length).
        
        Dim arrBytes(lBytes) As Byte
        lBytes = RAF.ReadBytes(arrBytes, 0, lBytes, lPosition)
        
        'get the index of the last btEndOfLineByte in arrBytes
        'so we don't go past that in the next bit byte loop
        '-----------------------------------------------------
        If bFinalByteBatch = False Then
            lUB = lBytes - 1
            Do While arrBytes(lUB) <> btEndOfLineByte
                lUB = lUB - 1
            Loop
        Else
            lUB = lBytes - 1
        End If
        
        'Log(lPosition & " - " & lBytes & " - " & lUB)
        
        lEncloserPos = -1
        lSeparatorPos = -1
        lChr10Pos = 0 'as all the arrBytes will start with byte 10
        bExit = False
        
        'so we ignore enclosers in the fields row!
        '-----------------------------------------
        If btEncloserByte > -1 Then
            bCSVHasEncloserBytes = CSVHasEncloserBytes(arrBytes, 0, 0, btEncloserByte)
        End If
    
        'not sure this is needed, seems we can process even very large file in one transaction
'        If General.cConn.bInTransaction Then
'            General.cConn.EndTransaction
'        End If
'        General.cConn.BeginTransaction

        If iRows = 0 Then bReadyForNextBytes = True
        
        If bReadyForNextBytes Then
            If bCSVHasEncloserBytes Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
                If arrBytes(0) = btEncloserByte Then
                    lEncloserPos = 0
                End If
        
                If iColumns > 1 Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
                    ''''''''''''''''''''''''''
                    'SEPARATORS AND ENCLOSERS'
                    ''''''''''''''''''''''''''
                    For i = 1 To lUB
                        Select Case arrBytes(i)
                            Case btSeparatorByte
                                If c < iColumns And lEncloserPos = -1 Then
                                    If lSeparatorPos = -1 Then
                                        If lChr10Pos = -1 Then
                                            'this will be a separator after a closing encloser!
                                            '--------------------------------------------------
                                            lSeparatorPos = i
                                        Else
                                            If i - lChr10Pos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - 1) - lChr10Pos, "UTF-8")
                                            End If    'If i - lChr10Pos > 1
                                            c = c + 1
                                        End If
                                        lChr10Pos = -1
                                    Else    'If lSeparatorPos = -1
                                        If i - lSeparatorPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                        End If    'If (i - 1) - (lSeparatorPos + 1) > 1
                                        c = c + 1
                                        lChr10Pos = -1
                                    End If    'If lSeparatorPos = -1
                                    lSeparatorPos = i
                                End If    'If c < lColumns and lEncloserPos = -1
                            
                                If bFinalByteBatch And i = lUB Then

                                    'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    iRows = iRows + 1
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If

                            Case btEncloserByte
                                If lEncloserPos = -1 Then
                                    lEncloserPos = i
                                Else 'If lEncloserPos = -1
                                    If c < iColumns Then
                                        If i - lEncloserPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lEncloserPos + 1, (i - 1) - lEncloserPos, "UTF-8")
                                        End If    'If i - lEncloserPos = 1
                                        c = c + 1
                                    End If    'If c < lColumns
                                    lEncloserPos = -1
                                End If 'If lEncloserPos = -1
                            
                                If bFinalByteBatch And i = lUB Then

                                    'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    iRows = iRows + 1
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                Else
                                    lSeparatorPos = -1
                                    lChr10Pos = -1
                                End If
                            
                            Case btEndOfLineByte
                                If lEncloserPos = -1 Then
                                    If c < iColumns Then
                                        If arrBytes(i - 1) = 13 Then
                                            If (i - 2) - lSeparatorPos > 0 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 2) - lSeparatorPos, "UTF-8")
                                            End If 'If (i - 2) - lSeparatorPos > 0
                                            c = c + 1
                                        Else    'If arrBytes(i - 1) = 13
                                            If i - lSeparatorPos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                            End If
                                            c = c + 1
                                        End If    'If arrBytes(i - 1) = 13
                                    End If

                                    iRows = iRows + 1
                                    lSeparatorPos = -1
                                    lChr10Pos = i
                                    lChr10PosLast = i
                                    c = 0
                                
                                    'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                            
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    
                                    If iRows = iRowsToInsert Then
                                        bExit = True
                                        Exit
                                    End If
                                End If    'If lEncloserPos = -1
                            
                                If bFinalByteBatch And i = lUB Then
                                    bExit = True 'row already processed, so no need to do this again here
                                    Exit
                                End If
                            
                            Case Else
                            
                                If bFinalByteBatch And i = lUB Then
                                    If c < iColumns Then
                                        If i - lSeparatorPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")

                                            'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                            General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                            iRows = iRows + 1
                                            If bShowProgress Then
                                                If iRows Mod iMod = 0 Then
                                                    B4XProgressBar1.Progress = iRows
                                                    Sleep(0)
                                                End If
                                            End If
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If 'If bFinalByteBatch And i = lUB
                            
                        End Select
                    
                        If bExit Then Exit
            
                    Next
                
                Else 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                    '''''''''''''''''''''''''''''
                    'NO SEPARATORS BUT ENCLOSERS'
                    '''''''''''''''''''''''''''''
                    For i = 1 To lUB
                        Select Case arrBytes(i)
                            Case btEncloserByte
                                If lEncloserPos = -1 Then
                                    lEncloserPos = i
                                Else
                                    If c < iColumns Then
                                        If i - lEncloserPos = 1 Then
                                            c = c + 1
                                        Else    'If i - lEncloserPos = 1
                                            arrRowData(c) = BytesToString(arrBytes, lEncloserPos + 1, (i - 1) - lEncloserPos, "UTF-8")
                                            c = c + 1
                                        End If    'If i - lEncloserPos = 1
                                    End If    'If c < lColumns
                                    lEncloserPos = -1
                                End If
                            
                                If bFinalByteBatch And i = lUB Then

                                    'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    iRows = iRows + 1
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                Else
                                    lChr10Pos = -1
                                End If
                            
                            Case btEndOfLineByte
                                If lEncloserPos = -1 Then
                                    If c < iColumns Then
                                        If arrBytes(i - 1) = 13 Then
                                            If (i - lChr10Pos) -1 > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8")
                                            End If
                                            c = c + 1
                                        Else    'If arrBytes(i - 1) = 13
                                            If (i - lChr10Pos) > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                            End If
                                            c = c + 1
                                        End If    'If i - lSeparatorPos > 1
                                    End If    'If c < lColumns
                    
                                    iRows = iRows + 1
                                    lSeparatorPos = -1
                                    lChr10Pos = i
                                    lChr10PosLast = i
                                    c = 0
                                
                                   'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                
                                    If iRows = iRowsToInsert Then
                                        bExit = True
                                        Exit
                                    End If
                                
                                End If    'If lEncloserPos = -1
                            
                                If bFinalByteBatch And i = lUB Then
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If
                            
                            Case Else
                                If bFinalByteBatch Then
                                    If i = lUB Then
                                        'If lEncloserPos = -1 Then
                                        If c < iColumns Then
                                            If (i - lChr10Pos) > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")

                                                'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                                General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                                iRows = iRows + 1
                                                If bShowProgress Then
                                                    If iRows Mod iMod = 0 Then
                                                        B4XProgressBar1.Progress = iRows
                                                        Sleep(0)
                                                    End If
                                                End If
                                            End If
                                        End If    'If c < lColumns
                                        bExit = True
                                        Exit
                                    End If 'If lPosition + i + 1 = lRAFFileSize Then 'last file byte
                                End If 'If bFinalByteBatch
                            
                        End Select
                    
                        If bExit Then Exit
                    
                    Next
                    
                End If 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
            Else  'If bCSVHasEncloserBytes<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                '''''''''''''''''''''''''''''
                'SEPARATORS BUT NO ENCLOSERS'
                '''''''''''''''''''''''''''''
                If iColumns > 1 Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                    For i = 1 To lUB
                        Select Case arrBytes(i)
                            Case btSeparatorByte
                                If c < iColumns Then
                                    If lSeparatorPos = -1 Then
                                        If lChr10Pos = -1 Then
                                            'this will be a separator after a closing encloser!
                                            '--------------------------------------------------
                                            lSeparatorPos = i
                                        Else
                                            If i - lChr10Pos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - 1) - lChr10Pos, "UTF-8")
                                            End If    'If i - lChr10Pos > 1
                                            c = c + 1
                                        End If
                                        lChr10Pos = -1
                                    Else    'If lSeparatorPos = -1
                                        If i - lSeparatorPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                        End If    'If (i - 1) - (lSeparatorPos + 1) > 1
                                        c = c + 1
                                        lChr10Pos = -1
                                    End If    'If lSeparatorPos = -1
                                    lSeparatorPos = i
                                End If    'If c < lColumns
                            
                                If bFinalByteBatch And i = lUB Then

                                    'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    iRows = iRows + 1
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If

                            Case btEndOfLineByte
                                If c < iColumns Then
                                    If lSeparatorPos > -1 Then
                                        If arrBytes(i - 1) = 13 Then
                                            If (i - 2) - lSeparatorPos > 0 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 2) - lSeparatorPos, "UTF-8")
                                            End If
                                            c = c + 1
                                        Else    'If arrBytes(i - 1) = 13
                                            If i - lSeparatorPos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                            End If 'If i - lSeparatorPos > 1
                                            c = c + 1
                                        End If    'If arrBytes(i - 1) = 13
                                    Else    'If lSeparatorPos > -1
                                        If arrBytes(i - 1) = 13 Then
                                            If (i - lChr10Pos) - 1 > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8")
                                            End If 'If (i - lChr10Pos) - 1 > 1
                                            c = c + 1
                                        Else    'If arrBytes(i - 1) = 13
                                            If (i - lChr10Pos) > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                            End If 'If (i - lChr10Pos) > 1
                                            c = c + 1
                                        End If    'If i - lSeparatorPos > 1
                                    End If    'If lSeparatorPos > -1
                    
                                    iRows = iRows + 1
                                    lSeparatorPos = -1
                                    lChr10Pos = i
                                    lChr10PosLast = i
                                    c = 0
                                
                                   'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    
                                    If iRows = iRowsToInsert Then
                                        bExit = True
                                        Exit
                                    End If
                            
                                End If    'If c < iColumns
                            
                                If bFinalByteBatch And i = lUB Then
                                    bExit = True
                                    Exit
                                End If
                            
                            Case Else
                                If bFinalByteBatch And i = lUB Then
                                    If c < iColumns Then
                                        If lSeparatorPos > -1 Then
                                            If i - lSeparatorPos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, i - lSeparatorPos, "UTF-8")
                                            End If 'If i - lSeparatorPos > 1
                                        Else    'If lSeparatorPos > -1
                                            If (i - lChr10Pos) > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) + 1 , "UTF-8")
                                            End If 'If (i - lChr10Pos) > 1
                                        End If    'If lSeparatorPos > -1
                                    
                                        'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                        General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                        iRows = iRows + 1
                                        Log("Got to final file byte, iRows: " & iRows)
                                        If bShowProgress Then
                                            If iRows Mod iMod = 0 Then
                                                B4XProgressBar1.Progress = iRows
                                                Sleep(0)
                                            End If
                                        End If
                                    End If    'If c < iColumns

                                    bExit = True
                                    Exit
                                End If 'If bFinalByteBatch And i = lUB
                            
                        End Select
                    
                        If bExit Then Exit

                    Next
                
                Else 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                    ''''''''''''''''''''''''''''''''
                    'NO SEPARATORS AND NO ENCLOSERS'
                    ''''''''''''''''''''''''''''''''
                    For i = 1 To lUB
                        Select Case arrBytes(i)
                            Case btEndOfLineByte
                                If c < iColumns Then
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - lChr10Pos) -1 > 0 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8")
                                        End If
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If (i - lChr10Pos) > 0 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                        End If
                                        c = c + 1
                                    End If    'If i - lSeparatorPos > 1
                                End If    'If c < lColumns
                    
                                iRows = iRows + 1
                                lSeparatorPos = -1
                                lChr10Pos = i
                                lChr10PosLast = i
                                c = 0
                            
                                'General.cConn.SQLMain.ExecNonQuery2(strSQL, arrRowData)
                                General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                            
                                If bShowProgress Then
                                    If iRows Mod iMod = 0 Then
                                        B4XProgressBar1.Progress = iRows
                                        Sleep(0)
                                    End If
                                End If

                                If iRows = iRowsToInsert Then
                                    bExit = True
                                    Exit
                                End If

                                If bFinalByteBatch And i = lUB Then
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If

                        End Select
                    
                        If bExit Then Exit
                    
                    Next
                
                End If 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
            End If 'If bCSVHasEncloserBytes<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
            bReadyForNextBytes = False
            Dim SenderFilter As Object = General.cConn.SQLMain.ExecNonQueryBatch("SQL")
            Wait For (SenderFilter) SQL_NonQueryComplete (bSuccess As Boolean)
            'Log(iRows & ", success: " & bSuccess)
            bReadyForNextBytes = bSuccess
            iCommittedAtRow = iRows
            
        End If 'If bReadyForNextBytes
    
    Loop
    
    If iRows > iCommittedAtRow Then
        Dim SenderFilter As Object = General.cConn.SQLMain.ExecNonQueryBatch("SQL")
        Wait For (SenderFilter) SQL_NonQueryComplete (bSuccess As Boolean)
        Log(iRows & " > " & iCommittedAtRow)
    Else
        bSuccess = True
    End If
    
    If bSuccess Then
        If bShowProgress Then
            B4XProgressBar1.Reset
        End If
    
        Return iRows
    End If

End Sub

I will do some testing and compare speed with the suggested, much simpler approach.
Not sure though why there was no information regarding the cause of this crash.

RBS
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Actually the code as posted has a major bug and that was that arrRowData was only declared once. There needs to be a newly declared array for every new row, so that
is after doing:

B4X:
General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)

So, the full code should be:

B4X:
Sub CSV2Table3(strFolder As String, _
               strFile As String, _
               bFileHasFieldNames As Boolean, _
               arrFieldNames() As String, _
               btSeparatorByte As Byte, _
               btEncloserByte As Byte, _
               btEndOfLineByte As Byte, _
               lMaxBytes As Long, _
               iRowsToInsert As Int, _
               iRowsInCSV As Int, _
               strTable As String, _
               bShowProgress As Boolean) As ResumableSub
             
    Dim i As Long
    Dim c As Int
    Dim x As Int
    Dim lRAFFileSize As Long
    Dim lPosition As Long
    Dim lBytes As Long
    Dim lUB As Long
    Dim arrBytes() As Byte
    Dim bFinalByteBatch As Boolean
    Dim iColumns As Int
    Dim iRows As Int
    Dim lSeparatorPos As Long
    Dim lEncloserPos As Long
    Dim lChr10Pos As Long
    Dim lChr10PosLast As Long
    Dim lFirstDataByte As Int
    Dim bCSVHasEncloserBytes As Boolean
    Dim bExit As Boolean
    Dim strSQL As String
    Dim iMod As Int
    Dim iRowsToDo As Int
    Dim iTableCols As Int
    Dim iAddNullColumns As Int
    Dim iCommittedAtRow As Int
    
    If iRowsToInsert = 0 Then
        iRowsToDo = iRowsInCSV
    Else
        iRowsToDo = iRowsToInsert
    End If

    'this will be a zero-based UTF8 byte array
    '-----------------------------------------
    RAF.Initialize(strFolder, strFile, True)
    lRAFFileSize = RAF.Size
    If iRowsToInsert = 0 Then iRowsToInsert = lRAFFileSize / 2
    
    'declare here as it also used to get the position of the first data byte (after the field names)
    Dim arrBytes(1000) As Byte 'this should be enough to get the first row
    RAF.ReadBytes(arrBytes, 0, 1000, 0)
    
    If bFileHasFieldNames = False Then
        iColumns = GetCSVHeaders(arrBytes, btSeparatorByte, btEncloserByte, btEndOfLineByte).Length
    Else
        iColumns = arrFieldNames.Length
    End If
    
    Dim arrRowData(iColumns) As String
    
    strSQL = "insert into " & strTable & " values(" & MakePlaceHolders(iColumns) & ")"
    
    If bFileHasFieldNames Then
        'as we are skipping the the column names
        '---------------------------------------
        lFirstDataByte = GetCSVFirstDataByte(arrBytes, btEndOfLineByte)
        lPosition = lFirstDataByte - 1 'always start the batches of bytes with the previous last Chr(10)
    Else
        lFirstDataByte = 0
        lPosition = 0
    End If
    
    lEncloserPos = -1
    lSeparatorPos = -1
    
    If bShowProgress Then
        If B4XProgressBar1.IsInitialized = False Then
            Activity.LoadLayout("ProgressBar")
        End If
        If iRowsInCSV > 250000 Then
            iMod = CInt((iRowsToDo / 100) * (250000 / iRowsInCSV))
        Else
            iMod = CInt(iRowsToDo / 100)
        End If
        If iMod = 0 Then iMod = 1
        B4XProgressBar1.Show(Activity, iRowsToDo, False, 1, "CSV > Table, rows: ", "/", "")
        Sleep(0)
    End If
    
    Do While True
        
        If bExit Then
            Exit
        End If
        
        If lPosition + lBytes >= lRAFFileSize Then
            Exit
        End If
        
        If iRows > 0 Then
            Dim SenderFilter As Object = General.cConn.SQLMain.ExecNonQueryBatch("SQL")
            Wait For (SenderFilter) SQL_NonQueryComplete (bSuccess As Boolean)
            If bSuccess = False Then
                If bShowProgress Then
                    B4XProgressBar1.Reset
                End If
                Log("ExecNonQueryBatch failed at " & iRows & " rows!")
                Return 0
            Else
                iCommittedAtRow = iRows
            End If
        End If
        
        If bSuccess Or iRows = 0 Then
        
            lPosition = lPosition + lChr10PosLast
        
            If lPosition + lMaxBytes > lRAFFileSize Then
                lBytes = (lRAFFileSize - lPosition) - 1
                bFinalByteBatch = True
            Else
                lBytes = lMaxBytes
            End If
        
            Dim arrBytes(lBytes) As Byte
            lBytes = RAF.ReadBytes(arrBytes, 0, lBytes, lPosition)
        
            'get the index of the last btEndOfLineByte in arrBytes
            'so we don't go past that in the next bit byte loop
            '-----------------------------------------------------
            If bFinalByteBatch = False Then
                lUB = lBytes - 1
                Do While arrBytes(lUB) <> btEndOfLineByte
                    lUB = lUB - 1
                Loop
            Else
                lUB = lBytes - 1
            End If
        
            lEncloserPos = -1
            lSeparatorPos = -1
            lChr10Pos = 0 'as all the arrBytes will start with byte 10
            bExit = False
        
            'so we ignore enclosers in the fields row!
            '-----------------------------------------
            If btEncloserByte > -1 Then
                bCSVHasEncloserBytes = CSVHasEncloserBytes(arrBytes, 0, 0, btEncloserByte)
            End If
    
            If bCSVHasEncloserBytes Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
                If arrBytes(0) = btEncloserByte Then
                    lEncloserPos = 0
                End If
        
                If iColumns > 1 Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
                    ''''''''''''''''''''''''''
                    'SEPARATORS AND ENCLOSERS'
                    ''''''''''''''''''''''''''
                    For i = 1 To lUB
                        Select Case arrBytes(i)
                            Case btSeparatorByte
                                If c < iColumns And lEncloserPos = -1 Then
                                    If lSeparatorPos = -1 Then
                                        If lChr10Pos = -1 Then
                                            'this will be a separator after a closing encloser!
                                            '--------------------------------------------------
                                            lSeparatorPos = i
                                        Else
                                            If i - lChr10Pos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - 1) - lChr10Pos, "UTF-8")
                                                End If    'If i - lChr10Pos > 1
                                            c = c + 1
                                        End If
                                        lChr10Pos = -1
                                    Else    'If lSeparatorPos = -1
                                        If i - lSeparatorPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                        End If    'If (i - 1) - (lSeparatorPos + 1) > 1
                                        c = c + 1
                                        lChr10Pos = -1
                                    End If    'If lSeparatorPos = -1
                                    lSeparatorPos = i
                                End If    'If c < lColumns and lEncloserPos = -1
                            
                                If bFinalByteBatch And i = lUB Then
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                            
                                    iRows = iRows + 1
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If

                            Case btEncloserByte
                                If lEncloserPos = -1 Then
                                    lEncloserPos = i
                                Else 'If lEncloserPos = -1
                                    If c < iColumns Then
                                        If i - lEncloserPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lEncloserPos + 1, (i - 1) - lEncloserPos, "UTF-8")
                                        End If    'If i - lEncloserPos = 1
                                        c = c + 1
                                    End If    'If c < lColumns
                                    lEncloserPos = -1
                                End If 'If lEncloserPos = -1
                            
                                If bFinalByteBatch And i = lUB Then
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    iRows = iRows + 1
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                Else
                                    lSeparatorPos = -1
                                    lChr10Pos = -1
                                End If
                            
                            Case btEndOfLineByte
                                If lEncloserPos = -1 Then
                                    If c < iColumns Then
                                        If arrBytes(i - 1) = 13 Then
                                            If (i - 2) - lSeparatorPos > 0 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 2) - lSeparatorPos, "UTF-8")
                                            End If 'If (i - 2) - lSeparatorPos > 0
                                            c = c + 1
                                        Else    'If arrBytes(i - 1) = 13
                                            If i - lSeparatorPos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                            End If
                                            c = c + 1
                                        End If    'If arrBytes(i - 1) = 13
                                    End If

                                    iRows = iRows + 1
                                    lSeparatorPos = -1
                                    lChr10Pos = i
                                    lChr10PosLast = i
                                    c = 0
                                
                                     General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    Dim arrRowData(iColumns) As String '1 need to declare a new array for every new row!!
                            
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    
                                    If iRows = iRowsToInsert Then
                                        bExit = True
                                        Exit
                                    End If
                                End If    'If lEncloserPos = -1
                            
                                If bFinalByteBatch And i = lUB Then
                                    bExit = True 'row already processed, so no need to do this again here
                                    Exit
                                End If
                            
                            Case Else
                            
                                If bFinalByteBatch And i = lUB Then
                                    If c < iColumns Then
                                        If i - lSeparatorPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                            General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                            iRows = iRows + 1
                                            If bShowProgress Then
                                                If iRows Mod iMod = 0 Then
                                                    B4XProgressBar1.Progress = iRows
                                                    Sleep(0)
                                                End If
                                            End If
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If 'If bFinalByteBatch And i = lUB
                            
                        End Select
                    
                        If bExit Then Exit
            
                    Next
                
                Else 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                    '''''''''''''''''''''''''''''
                    'NO SEPARATORS BUT ENCLOSERS'
                    '''''''''''''''''''''''''''''
                    For i = 1 To lUB
                        Select Case arrBytes(i)
                            Case btEncloserByte
                                If lEncloserPos = -1 Then
                                    lEncloserPos = i
                                Else
                                    If c < iColumns Then
                                        If i - lEncloserPos = 1 Then
                                            c = c + 1
                                        Else    'If i - lEncloserPos = 1
                                            arrRowData(c) = BytesToString(arrBytes, lEncloserPos + 1, (i - 1) - lEncloserPos, "UTF-8")
                                            c = c + 1
                                        End If    'If i - lEncloserPos = 1
                                    End If    'If c < lColumns
                                    lEncloserPos = -1
                                End If
                            
                                If bFinalByteBatch And i = lUB Then
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    iRows = iRows + 1
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                Else
                                    lChr10Pos = -1
                                End If
                            
                            Case btEndOfLineByte
                                If lEncloserPos = -1 Then
                                    If c < iColumns Then
                                        If arrBytes(i - 1) = 13 Then
                                            If (i - lChr10Pos) -1 > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8")
                                            End If
                                            c = c + 1
                                        Else    'If arrBytes(i - 1) = 13
                                            If (i - lChr10Pos) > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                            End If
                                            c = c + 1
                                        End If    'If i - lSeparatorPos > 1
                                    End If    'If c < lColumns
                    
                                    iRows = iRows + 1
                                    lSeparatorPos = -1
                                    lChr10Pos = i
                                    lChr10PosLast = i
                                    c = 0
                                
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    Dim arrRowData(iColumns) As String  '2 need to declare a new array for every new row!!
                                    
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                
                                    If iRows = iRowsToInsert Then
                                        bExit = True
                                        Exit
                                    End If
                                
                                End If    'If lEncloserPos = -1
                            
                                If bFinalByteBatch And i = lUB Then
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If
                            
                            Case Else
                                If bFinalByteBatch Then
                                    If i = lUB Then
                                        'If lEncloserPos = -1 Then
                                        If c < iColumns Then
                                            If (i - lChr10Pos) > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                                General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                                iRows = iRows + 1
                                                If bShowProgress Then
                                                    If iRows Mod iMod = 0 Then
                                                        B4XProgressBar1.Progress = iRows
                                                        Sleep(0)
                                                    End If
                                                End If
                                            End If
                                        End If    'If c < lColumns
                                        bExit = True
                                        Exit
                                    End If 'If lPosition + i + 1 = lRAFFileSize Then 'last file byte
                                End If 'If bFinalByteBatch
                            
                        End Select
                    
                        If bExit Then Exit
                    
                    Next
                    
                End If 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
            Else  'If bCSVHasEncloserBytes<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                '''''''''''''''''''''''''''''
                'SEPARATORS BUT NO ENCLOSERS'
                '''''''''''''''''''''''''''''
                If iColumns > 1 Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                    For i = 1 To lUB
                        Select Case arrBytes(i)
                            Case btSeparatorByte
                                If c < iColumns Then
                                    If lSeparatorPos = -1 Then
                                        If lChr10Pos = -1 Then
                                            'this will be a separator after a closing encloser!
                                            '--------------------------------------------------
                                            lSeparatorPos = i
                                        Else
                                            If i - lChr10Pos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - 1) - lChr10Pos, "UTF-8")
                                            End If    'If i - lChr10Pos > 1
                                            c = c + 1
                                        End If
                                        lChr10Pos = -1
                                    Else    'If lSeparatorPos = -1
                                        If i - lSeparatorPos > 1 Then
                                            arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                        End If    'If (i - 1) - (lSeparatorPos + 1) > 1
                                        c = c + 1
                                        lChr10Pos = -1
                                    End If    'If lSeparatorPos = -1
                                    lSeparatorPos = i
                                End If    'If c < lColumns
                            
                                If bFinalByteBatch And i = lUB Then
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    iRows = iRows + 1
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If

                            Case btEndOfLineByte
                                If c < iColumns Then
                                    If lSeparatorPos > -1 Then
                                        If arrBytes(i - 1) = 13 Then
                                            If (i - 2) - lSeparatorPos > 0 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 2) - lSeparatorPos, "UTF-8")
                                            End If
                                            c = c + 1
                                        Else    'If arrBytes(i - 1) = 13
                                            If i - lSeparatorPos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")
                                                'Log(arrRowData(c))
                                            End If 'If i - lSeparatorPos > 1
                                            c = c + 1
                                        End If    'If arrBytes(i - 1) = 13
                                    Else    'If lSeparatorPos > -1
                                        If arrBytes(i - 1) = 13 Then
                                            If (i - lChr10Pos) - 1 > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8")
                                            End If 'If (i - lChr10Pos) - 1 > 1
                                            c = c + 1
                                        Else    'If arrBytes(i - 1) = 13
                                            If (i - lChr10Pos) > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                            End If 'If (i - lChr10Pos) > 1
                                            c = c + 1
                                        End If    'If i - lSeparatorPos > 1
                                    End If    'If lSeparatorPos > -1
                    
                                    iRows = iRows + 1
                                    lSeparatorPos = -1
                                    lChr10Pos = i
                                    lChr10PosLast = i
                                    c = 0
                                
                                    General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                    Dim arrRowData(iColumns) As String '3 need to declare a new array for every new row!!
                                    
                                    If bShowProgress Then
                                        If iRows Mod iMod = 0 Then
                                            B4XProgressBar1.Progress = iRows
                                            Sleep(0)
                                        End If
                                    End If
                                    
                                    If iRows = iRowsToInsert Then
                                        bExit = True
                                        Exit
                                    End If
                            
                                End If    'If c < iColumns
                            
                                If bFinalByteBatch And i = lUB Then
                                    bExit = True
                                    Exit
                                End If
                            
                            Case Else
                                If bFinalByteBatch And i = lUB Then
                                    If c < iColumns Then
                                        If lSeparatorPos > -1 Then
                                            If i - lSeparatorPos > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lSeparatorPos + 1, i - lSeparatorPos, "UTF-8")
                                            End If 'If i - lSeparatorPos > 1
                                        Else    'If lSeparatorPos > -1
                                            If (i - lChr10Pos) > 1 Then
                                                arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) + 1 , "UTF-8")
                                            End If 'If (i - lChr10Pos) > 1
                                        End If    'If lSeparatorPos > -1
                                    
                                        General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                        iRows = iRows + 1
                                        If bShowProgress Then
                                            If iRows Mod iMod = 0 Then
                                                B4XProgressBar1.Progress = iRows
                                                Sleep(0)
                                            End If
                                        End If
                                    End If    'If c < iColumns

                                    bExit = True
                                    Exit
                                End If 'If bFinalByteBatch And i = lUB
                            
                        End Select
                    
                        If bExit Then Exit

                    Next
                
                Else 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                    ''''''''''''''''''''''''''''''''
                    'NO SEPARATORS AND NO ENCLOSERS'
                    ''''''''''''''''''''''''''''''''
                    For i = 1 To lUB
                        Select Case arrBytes(i)
                            Case btEndOfLineByte
                                If c < iColumns Then
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - lChr10Pos) -1 > 0 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8")
                                        End If
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If (i - lChr10Pos) > 0 Then
                                            arrRowData(c) = BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8")
                                        End If
                                        c = c + 1
                                    End If    'If i - lSeparatorPos > 1
                                End If    'If c < lColumns
                    
                                iRows = iRows + 1
                                lSeparatorPos = -1
                                lChr10Pos = i
                                lChr10PosLast = i
                                c = 0
                            
                                General.cConn.SQLMain.AddNonQueryToBatch(strSQL, arrRowData)
                                Dim arrRowData(iColumns) As String '4 need to declare a new array for every new row!!
                            
                                If bShowProgress Then
                                    If iRows Mod iMod = 0 Then
                                        B4XProgressBar1.Progress = iRows
                                        Sleep(0)
                                    End If
                                End If

                                If iRows = iRowsToInsert Then
                                    bExit = True
                                    Exit
                                End If

                                If bFinalByteBatch And i = lUB Then
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If

                        End Select
                    
                        If bExit Then Exit
                    
                    Next
                
                End If 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
            End If 'If bCSVHasEncloserBytes<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

        End If 'If bSuccess
    
    Loop
    
    If bShowProgress Then
        B4XProgressBar1.Reset
    End If
    
    If iRows > iCommittedAtRow Then
        Dim SenderFilter As Object = General.cConn.SQLMain.ExecNonQueryBatch("SQL")
        Wait For (SenderFilter) SQL_NonQueryComplete (bSuccess As Boolean)
        If bSuccess = False Then
            Log("Final ExecNonQueryBatch failed at " & iRows & " rows!")
            Return 0
        Else
            Return iRows
        End If
    Else
        Return iRows
    End If

End Sub

RBS
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Still not solved this problem. I thought that the SQLite commits were too large and reducing that seemed to fix the crashes, but I have done much larger transactions in the past, so this may not be the problem. To prove/disprove that I have a similar routine that checks a .csv file for various properties, mainly data types and number of rows.
This routine has no SQLite related to code and found that this routine has the same problem, so random crashes, without any useful message in the logs (including unfiltered logs). These crashes happen at random spots, so after processing a variable number of rows. Reducing the byte arrays helps partially, so smaller arrays means it gets a bit further, but it will still crash. I am at a loss now to get to the bottom of this problem and I am hoping somebody might spot an error in my code:

B4X:
Sub GetCSVProperties(strFolder As String, _
                      strFile As String, _
                      bFileHasFieldNames As Boolean, _
                      btSeparatorByte As Byte, _
                      btEncloserByte As Byte, _
                      btEndOfLineByte As Byte, _
                      lMaxBytes As Long, _
                     iCSVRows As Int, _
                     bShowProgress As Boolean) As ResumableSub
             
    Dim i As Long
    Dim c As Int
    Dim x As Int
    Dim iMaxX As Int
    Dim lOldI As Long
    Dim iMaxRowBytes As Int
    Dim lRAFFileSize As Long
    Dim lPosition As Long
    Dim lBytes As Long
    Dim arrFields() As String
    Dim iColumns As Int
    Dim iRows As Int
    Dim lSeparatorPos As Long
    Dim lEncloserPos As Long
    Dim lChr10Pos As Long
    Dim lChr10PosLast As Long
    Dim lFirstDataByte As Int
    Dim bCSVHasEncloserBytes As Boolean
    Dim strType As String
    Dim bExit As Boolean
    Dim lUB As Long
    Dim bFinalByteBatch As Boolean
    Dim CSVProps As CSVProperties
    
    'this will be a zero-based UTF8 byte array
    '-----------------------------------------
    RAF.Initialize(strFolder, strFile, True)
    lRAFFileSize = RAF.Size
    If iCSVRows = 0 Then iCSVRows = lRAFFileSize / 2
    Dim arrBytes(1000) As Byte 'this should be enough to get the first row
    RAF.ReadBytes(arrBytes, 0, 1000, 0)
    
    If bShowProgress Then
        iMaxX = (lRAFFileSize / lMaxBytes) / 100
        Log("iMaxX: " & iMaxX)
        bShowProgress = lRAFFileSize > 1000000
    End If
    
    'this can be just the first row, needed to count the columns
    '-----------------------------------------------------------
    arrFields = GetCSVHeaders(arrBytes, btSeparatorByte, btEncloserByte, btEndOfLineByte)
    
    iColumns = arrFields.Length
    Dim arrDataTypes(iColumns) As String
    Dim arrColHasReal(iColumns) As Boolean
    Dim arrColHasInt(iColumns) As Boolean
    
    If bFileHasFieldNames Then
        'as we are skipping the the column names
        '---------------------------------------
        lFirstDataByte = GetCSVFirstDataByte(arrBytes, btEndOfLineByte)
        lPosition = lFirstDataByte - 1
    Else
        lFirstDataByte = 0
        lPosition = 0
    End If
    
    If bShowProgress Then
        If B4XProgressBar1.IsInitialized = False Then
            Activity.LoadLayout("ProgressBar")
        End If
        'this class has been customized, hence the extra arguments
        B4XProgressBar1.Show(Activity, lRAFFileSize - 1, False, 1, "", "", "")
        Sleep(0)
    End If
    
    Do While True
        
        If bExit Then
            Exit
        End If
        
        If lPosition + lBytes >= lRAFFileSize Then
            Exit
        End If
        
        lPosition = lPosition + lChr10PosLast
            
        If lPosition + lMaxBytes > lRAFFileSize Then
            lBytes = (lRAFFileSize - lPosition) - 1
            bFinalByteBatch = True
        Else
            lBytes = lMaxBytes
        End If
        
        Dim arrBytes(lBytes) As Byte
        lBytes = RAF.ReadBytes(arrBytes, 0, lBytes, lPosition)
        
        'Log(lPosition & " - " & RAF.CurrentPosition)
        
        'get the index of the last btEndOfLineByte in arrBytes
        'so we don't go past that in the next bit byte loop
        '-----------------------------------------------------
        If bFinalByteBatch = False Then
            lUB = lBytes - 1
            Do While arrBytes(lUB) <> btEndOfLineByte
                lUB = lUB - 1
            Loop
        Else
            lUB = lBytes - 1
            'Log("lUB (final batch): " & lUB)
        End If
        
        'ShowMemory(True)
        
        lEncloserPos = -1
        lSeparatorPos = -1
        lChr10Pos = 0 'as all the arrBytes will start with byte 10
        bExit = False
        
        If bShowProgress Then
            If x = 0 Or x = iMaxX Then
                B4XProgressBar1.mstrPreValues = "Checked " & iRows & " rows"
                B4XProgressBar1.Progress = lPosition
                'Log(iRows)
                x = 1
                Sleep(0)
            End If
        End If
        
        x = x + 1
        
        'so we ignore enclosers in the fields row!
        '-----------------------------------------
        If btEncloserByte > -1 Then
            bCSVHasEncloserBytes = CSVHasEncloserBytes(arrBytes, 0, 0, btEncloserByte)
        End If
    
        If bCSVHasEncloserBytes Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
            If arrBytes(0) = btEncloserByte Then
                lEncloserPos = 0
            End If
        
            If iColumns > 1 Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
                ''''''''''''''''''''''''''
                'SEPARATORS AND ENCLOSERS'
                ''''''''''''''''''''''''''
                For i = 1 To lUB

                    Select Case arrBytes(i)
                        Case btSeparatorByte
                            If c < iColumns And lEncloserPos = -1 Then
                                If lSeparatorPos = -1 Then
                                    If lChr10Pos = -1 Then
                                        'this will be a separator after a closing encloser!
                                        '--------------------------------------------------
                                        lSeparatorPos = i
                                    Else
                                        If i - lChr10Pos > 1 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - 1) - lChr10Pos, "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If    'If i - lChr10Pos > 1
                                        c = c + 1
                                    End If
                                    lChr10Pos = -1
                                Else    'If lSeparatorPos = -1
                                    If i - lSeparatorPos > 1 Then
                                        If arrDataTypes(c) <> "T" Then
                                            strType = GetDataTypeFromString(BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8"), True, "T")
                                            If strType = "T" Then
                                                arrDataTypes(c) = "T" 'text
                                            Else
                                                If strType = "R" Then
                                                    arrColHasReal(c) = True
                                                End If
                                                If strType = "I" Then
                                                    arrColHasInt(c) = True
                                                End If
                                            End If
                                        End If
                                    End If    'If (i - 1) - (lSeparatorPos + 1) > 1
                                    c = c + 1
                                    lChr10Pos = -1
                                End If    'If lSeparatorPos = -1
                                lSeparatorPos = i
                            End If    'If c < lColumns and lEncloserPos = -1
                            
                            If bFinalByteBatch And i = lUB Then
                                iRows = iRows + 1
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If

                        Case btEncloserByte
                            If lEncloserPos = -1 Then
                                lEncloserPos = i
                            Else 'If lEncloserPos = -1
                                If c < iColumns Then
                                    If i - lEncloserPos > 1 Then
                                        If arrDataTypes(c) <> "T" Then
                                            strType = GetDataTypeFromString(BytesToString(arrBytes, lEncloserPos + 1, (i - 1) - lEncloserPos, "UTF-8"), True, "T")
                                            If strType = "T" Then
                                                arrDataTypes(c) = "T" 'text
                                            Else
                                                If strType = "R" Then
                                                    arrColHasReal(c) = True
                                                End If
                                                If strType = "I" Then
                                                    arrColHasInt(c) = True
                                                End If
                                            End If
                                        End If
                                    End If    'If i - lEncloserPos = 1
                                    c = c + 1
                                End If    'If c < lColumns
                                lEncloserPos = -1
                            End If 'If lEncloserPos = -1
                            
                            If bFinalByteBatch And i = lUB Then
                                iRows = iRows + 1
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            Else
                                lSeparatorPos = -1
                                lChr10Pos = -1
                            End If
                            
                        Case btEndOfLineByte
                            If lEncloserPos = -1 Then
                                If c < iColumns Then
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - 2) - lSeparatorPos > 0 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lSeparatorPos + 1, (i - 2) - lSeparatorPos, "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If 'If (i - 2) - lSeparatorPos > 0
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If i - lSeparatorPos > 1 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If
                                        c = c + 1
                                    End If    'If arrBytes(i - 1) = 13
                                End If

                                iRows = iRows + 1
                                
                                lEncloserPos = - 1
                                lSeparatorPos = -1
                                lChr10Pos = i
                                lChr10PosLast = i
                                c = 0
                                
                                If i - lOldI > iMaxRowBytes Then
                                    iMaxRowBytes = i - lOldI
                                End If
                                lOldI = i

                                If iRows = iCSVRows Then
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If
                                
                            End If    'If lEncloserPos = -1
                            
                            If bFinalByteBatch And i = lUB Then
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If
                            
                        Case Else
                            
                            If bFinalByteBatch Then
                                If i = lUB Then
                                    'If lEncloserPos = -1 Then
                                    If c < iColumns Then
                                        If i - lSeparatorPos > 1 Then
                                            Log("Last csv item: " & BytesToString(arrBytes, lSeparatorPos + 1, i - lSeparatorPos, "UTF-8"))
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lSeparatorPos + 1, i - lSeparatorPos, "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If
                                    End If

                                    iRows = iRows + 1
                                    'Log("end of file, iRows: " & iRows)
                                
                                    If i - lOldI > iMaxRowBytes Then
                                        iMaxRowBytes = i - lOldI
                                    End If
                                    lOldI = i
                                
                                    'End If    'If lEncloserPos = -1

                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If 'If lPosition + i + 1 = lRAFFileSize Then 'last file byte
                            End If 'If bFinalByteBatch
                                
                    End Select
                    
                    If bExit Then
                        Exit 'exit due to iRows = iCSVRows or dealt with last file byte
                    End If
                    
                Next
                
            Else 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                '''''''''''''''''''''''''''''
                'NO SEPARATORS BUT ENCLOSERS'
                '''''''''''''''''''''''''''''
                For i = 1 To lUB
                    Select Case arrBytes(i)
                        Case btEncloserByte
                            If lEncloserPos = -1 Then
                                lEncloserPos = i
                            Else
                                If c < iColumns Then
                                    If i - lEncloserPos = 1 Then
                                        c = c + 1
                                    Else    'If i - lEncloserPos = 1
                                        If arrDataTypes(c) <> "T" Then
                                            strType = GetDataTypeFromString(BytesToString(arrBytes, lEncloserPos + 1, (i - 1) - lEncloserPos, "UTF-8"), True, "T")
                                            If strType = "T" Then
                                                arrDataTypes(c) = "T"
                                            Else
                                                If strType = "R" Then
                                                    arrColHasReal(c) = True
                                                End If
                                                If strType = "I" Then
                                                    arrColHasInt(c) = True
                                                End If
                                            End If
                                        End If
                                        c = c + 1
                                    End If    'If i - lEncloserPos = 1
                                End If    'If c < lColumns
                                lEncloserPos = -1
                            End If
                            
                            If bFinalByteBatch And i = lUB Then
                                iRows = iRows + 1
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            Else
                                lChr10Pos = -1
                            End If
                            
                        Case btEndOfLineByte
                            If lEncloserPos = -1 Then
                                If c < iColumns Then
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - lChr10Pos) -1 > 1 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If (i - lChr10Pos) > 1 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If
                                        c = c + 1
                                    End If    'If i - lSeparatorPos > 1
                                End If    'If c < lColumns
                    
                                iRows = iRows + 1
                                lEncloserPos = - 1
                                lSeparatorPos = -1
                                lChr10Pos = i
                                lChr10PosLast = i
                                c = 0
                                
                                If i - lOldI > iMaxRowBytes Then
                                    iMaxRowBytes = i - lOldI
                                End If
                                lOldI = i
                                
                                If iRows = iCSVRows Then
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If
                        
                            End If    'If lEncloserPos = -1
                            
                            If bFinalByteBatch And i = lUB Then
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If
                            
                        Case Else
                            If bFinalByteBatch Then
                                If i = lUB Then
                                    'If lEncloserPos = -1 Then
                                    If c < iColumns Then
                                        If (i - lChr10Pos) > 1 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If
                                        c = c + 1
                                    End If    'If c < lColumns
                    
                                    iRows = iRows + 1
                                
                                    If i - lOldI > iMaxRowBytes Then
                                        iMaxRowBytes = i - lOldI
                                    End If
                                    lOldI = i
                                
                                    'End If    'If lEncloserPos = -1
                                    
                                    'Log("Final byte exit")
                                
                                    bExit = True
                                    Exit
                                End If 'If lPosition + i + 1 = lRAFFileSize Then 'last file byte
                            End If 'If bFinalByteBatch
                            
                    End Select
                    
                    If bExit Then Exit
                    
                Next
                
            End If 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
        Else  'If bCSVHasEncloserBytes<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
            '''''''''''''''''''''''''''''
            'SEPARATORS BUT NO ENCLOSERS'
            '''''''''''''''''''''''''''''
            If iColumns > 1 Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                For i = 1 To lUB
                    Select Case arrBytes(i)
                        Case btSeparatorByte
                            If c < iColumns Then
                                If lSeparatorPos = -1 Then
                                    If lChr10Pos = -1 Then
                                        'this will be a separator after a closing encloser!
                                        '--------------------------------------------------
                                        lSeparatorPos = i
                                    Else
                                        If i - lChr10Pos > 1 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - 1) - lChr10Pos, "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If    'If i - lChr10Pos > 1
                                        c = c + 1
                                    End If
                                    lChr10Pos = -1
                                Else    'If lSeparatorPos = -1
                                    If i - lSeparatorPos > 1 Then
                                        If arrDataTypes(c) <> "T" Then
                                            strType = GetDataTypeFromString(BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8"), True, "T")
                                            If strType = "T" Then
                                                arrDataTypes(c) = "T" 'text
                                            Else
                                                If strType = "R" Then
                                                    arrColHasReal(c) = True
                                                End If
                                                If strType = "I" Then
                                                    arrColHasInt(c) = True
                                                End If
                                            End If
                                        End If
                                    End If    'If (i - 1) - (lSeparatorPos + 1) > 1
                                    c = c + 1
                                    lChr10Pos = -1
                                End If    'If lSeparatorPos = -1
                                lSeparatorPos = i
                            End If    'If c < lColumns
                            
                            If bFinalByteBatch And i = lUB Then
                                iRows = iRows + 1
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If

                        Case btEndOfLineByte
                            If c < iColumns Then
                                If lSeparatorPos > -1 Then
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - 2) - lSeparatorPos > 0 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lSeparatorPos + 1, (i - 2) - lSeparatorPos, "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If i - lSeparatorPos > 1 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If 'If i - lSeparatorPos > 1
                                        c = c + 1
                                    End If    'If arrBytes(i - 1) = 13
                                Else    'If lSeparatorPos > -1
                                    If arrBytes(i - 1) = 13 Then
                                        If (i - lChr10Pos) - 1 > 1 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If 'If (i - lChr10Pos) - 1 > 1
                                        c = c + 1
                                    Else    'If arrBytes(i - 1) = 13
                                        If (i - lChr10Pos) > 1 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If 'If (i - lChr10Pos) > 1
                                        c = c + 1
                                    End If    'If i - lSeparatorPos > 1
                                End If    'If lSeparatorPos > -1
                    
                                iRows = iRows + 1
                                lSeparatorPos = -1
                                lChr10Pos = i
                                lChr10PosLast = i
                                c = 0
                                
                                If i - lOldI > iMaxRowBytes Then
                                    iMaxRowBytes = i - lOldI
                                End If
                                lOldI = i
                                
                                If iRows = iCSVRows Then
                                    bExit = True 'as this exit will only exit the Select Case
                                    Exit
                                End If
                        
                            End If    'If c < iColumns
                            
                            If bFinalByteBatch And i = lUB Then
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If
                        
                            'this code block is only for in case the last item of the last row bumps up the iMaxRowBytes,
                            'and we could leave this out and increase the found rows by looking at the last byte outside this loop
                            '-----------------------------------------------------------------------------------------------------
                        Case Else
                            If bFinalByteBatch Then
                                If i = lUB Then
                                    If c < iColumns Then
                                        If lSeparatorPos > -1 Then
                                            If i - lSeparatorPos > 1 Then
                                                If arrDataTypes(c) <> "T" Then
                                                    'Log(BytesToString(arrBytes, lSeparatorPos + 1, i - lSeparatorPos, "UTF-8"))
                                                    strType = GetDataTypeFromString(BytesToString(arrBytes, lSeparatorPos + 1, i - lSeparatorPos, "UTF-8"), True, "T")
                                                    If strType = "T" Then
                                                        arrDataTypes(c) = "T" 'text
                                                    Else
                                                        If strType = "R" Then
                                                            arrColHasReal(c) = True
                                                        End If
                                                        If strType = "I" Then
                                                            arrColHasInt(c) = True
                                                        End If
                                                    End If
                                                End If
                                            End If 'If i - lSeparatorPos > 1
                                        Else    'If lSeparatorPos > -1
                                            If (i - lChr10Pos) > 1 Then
                                                If arrDataTypes(c) <> "T" Then
                                                    strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) + 1 , "UTF-8"), True, "T")
                                                    If strType = "T" Then
                                                        arrDataTypes(c) = "T" 'text
                                                    Else
                                                        If strType = "R" Then
                                                            arrColHasReal(c) = True
                                                        End If
                                                        If strType = "I" Then
                                                            arrColHasInt(c) = True
                                                        End If
                                                    End If
                                                End If
                                            End If 'If (i - lChr10Pos) > 1
                                        End If    'If lSeparatorPos > -1
                    
                                        iRows = iRows + 1
                                                
                                        If i - lOldI > iMaxRowBytes Then
                                            iMaxRowBytes = i - lOldI
                                        End If
                
                                    End If    'If c < iColumns
                                    'Log("Final byte exit")
                                    bExit = True
                                    Exit
                                End If 'If i = lUB
                            End If 'If bFinalByteBatch
        
                    End Select
                    
                    If bExit Then Exit
                    
                Next
                
            Else 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
                ''''''''''''''''''''''''''''''''
                'NO SEPARATORS AND NO ENCLOSERS'
                ''''''''''''''''''''''''''''''''
                For i = 1 To lBytes - 1
                    Select Case arrBytes(i)
                        Case btEndOfLineByte
                            If c < iColumns Then
                                If arrBytes(i - 1) = 13 Then
                                    If (i - lChr10Pos) -1 > 0 Then
                                        If arrDataTypes(c) <> "T" Then
                                            strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) -1 , "UTF-8"), True, "T")
                                            If strType = "T" Then
                                                arrDataTypes(c) = "T" 'text
                                            Else
                                                If strType = "R" Then
                                                    arrColHasReal(c) = True
                                                End If
                                                If strType = "I" Then
                                                    arrColHasInt(c) = True
                                                End If
                                            End If
                                        End If
                                    End If
                                Else    'If arrBytes(i - 1) = 13
                                    If (i - lChr10Pos) > 0 Then
                                        If arrDataTypes(c) <> "T" Then
                                            strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8"), True, "T")
                                            If strType = "T" Then
                                                arrDataTypes(c) = "T" 'text
                                            Else
                                                If strType = "R" Then
                                                    arrColHasReal(c) = True
                                                End If
                                                If strType = "I" Then
                                                    arrColHasInt(c) = True
                                                End If
                                            End If
                                        End If
                                    End If
                                End If    'If i - lSeparatorPos > 1
                                c = c + 1
                            End If    'If c < lColumns
                    
                            iRows = iRows + 1
                            lSeparatorPos = -1
                            lChr10Pos = i
                            lChr10PosLast = i
                            c = 0
                            
                            If i - lOldI > iMaxRowBytes Then
                                iMaxRowBytes = i - lOldI
                            End If
                            lOldI = i
                            
                            If iRows = iCSVRows Then
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If
                            
                            If bFinalByteBatch And i = lUB Then
                                bExit = True 'as this exit will only exit the Select Case
                                Exit
                            End If
                            
                        Case Else
                            If bFinalByteBatch Then
                                If i = lUB Then
                                    If c < iColumns Then
                                        If (i - lChr10Pos) > 0 Then
                                            If arrDataTypes(c) <> "T" Then
                                                strType = GetDataTypeFromString(BytesToString(arrBytes, lChr10Pos + 1, (i - lChr10Pos) , "UTF-8"), True, "T")
                                                If strType = "T" Then
                                                    arrDataTypes(c) = "T" 'text
                                                Else
                                                    If strType = "R" Then
                                                        arrColHasReal(c) = True
                                                    End If
                                                    If strType = "I" Then
                                                        arrColHasInt(c) = True
                                                    End If
                                                End If
                                            End If
                                        End If
                                        c = c + 1
                                    End If    'If c < lColumns
                                
                                    iRows = iRows + 1
                            
                                    If i - lOldI > iMaxRowBytes Then
                                        iMaxRowBytes = i - lOldI
                                    End If
                                
                                    bExit = True
                                    Exit
                                End If  'If i = lUB
                            End If  'If bFinalByteBatc
                            
                    End Select
                    
                    If bExit Then Exit
                    
                Next
                
            End If 'If lColumns > 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
        End If 'If bCSVHasEncloserBytes<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    
    Loop
    
    For c = 0 To iColumns - 1
        If arrDataTypes(c) <> "T" Then
            If arrColHasReal(c) Then
                arrDataTypes(c) = "R" 'real
            Else
                If arrColHasInt(c) Then
                    arrDataTypes(c) = "I" 'integer
                Else
                    arrDataTypes(c) = "N" 'null
                End If
            End If
        End If
    Next
    
    CSVProps.iFields = iColumns
    CSVProps.iRows = iRows
    CSVProps.iMaxRowBytes = iMaxRowBytes
    CSVProps.arrFoundFieldNames = arrFields
    CSVProps.arrFoundDataTypes = arrDataTypes
    
    If bShowProgress Then
        B4XProgressBar1.Reset
        Sleep(0)
    End If
    
    Return CSVProps

End Sub


RBS
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Some more insight in this crash:

1. I can see that the crashes happen somewhere in the i loops
2. commenting out the lines starting with: strType = GetDataTypeFromString(BytesToString( doesn't stop the crashes
3. commenting out the whole blocks starting with: If arrDataTypes(c) <> "T" Then does stop the crashes

Can't understand this, but at least it does narrow things down.

RBS
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
I simplified code (leaving many file options out, leaving only files with no encloser, tabs as separators and the data types already supplied to the sub) giving me this code:

B4X:
'Just for testing
'-----------------------------------------------------------------------------------
'this one is only for files with no enclosers and tabs as separators
'also the data types need to be supplied as this Sub won't check the file data types
'-----------------------------------------------------------------------------------
Sub GetCSVProperties2(strFolder As String, _
                      strFile As String, _
                      bFileHasFieldNames As Boolean, _
                      btSeparatorByte As Byte, _
                      btEncloserByte As Byte, _
                      btEndOfLineByte As Byte, _
                      lMaxBytes As Long, _
                     iCSVRows As Int, _
                     arrDataTypes() As String, _
                     bShowProgress As Boolean) As ResumableSub
             
    Dim i As Long
    Dim c As Int
    Dim x As Int
    Dim iMaxX As Int
    Dim lOldI As Long
    Dim iMaxRowBytes As Int
    Dim lRAFFileSize As Long
    Dim lPosition As Long
    Dim lBytes As Long
    Dim arrFields() As String
    Dim iColumns As Int
    Dim iRows As Int
    Dim lSeparatorPos As Long
    Dim lChr10Pos As Long
    Dim lChr10PosLast As Long
    Dim lFirstDataByte As Int
    Dim bExit As Boolean
    Dim lUB As Long
    Dim bFinalByteBatch As Boolean
    Dim CSVProps As CSVProperties
    
    'this will be a zero-based UTF8 byte array
    '-----------------------------------------
    RAF.Initialize(strFolder, strFile, True)
    lRAFFileSize = RAF.Size
    If iCSVRows = 0 Then iCSVRows = lRAFFileSize / 2
    
    If bShowProgress Then
        iMaxX = (lRAFFileSize / lMaxBytes) / 100
        Log("iMaxX: " & iMaxX)
        bShowProgress = lRAFFileSize > 1000000
    End If
    
    'this can be just the first row (not field names), needed to count the columns
    '-----------------------------------------------------------------------------
    Dim arrBytes(1000) As Byte 'this should be enough to get the first row
    RAF.ReadBytes(arrBytes, 0, 1000, 0)
    arrFields = GetCSVHeaders(arrBytes, btSeparatorByte, btEncloserByte, btEndOfLineByte)
    
    iColumns = arrFields.Length
    
    If bFileHasFieldNames Then
        'as we are skipping the the column names
        '---------------------------------------
        lFirstDataByte = GetCSVFirstDataByte(arrBytes, btEndOfLineByte)
        lPosition = lFirstDataByte - 1
    Else
        lFirstDataByte = 0
        lPosition = 0
    End If
    
    If bShowProgress Then
        If B4XProgressBar1.IsInitialized = False Then
            Activity.LoadLayout("ProgressBar")
        End If
        'this class has been customized, hence the extra arguments
        B4XProgressBar1.Show(Activity, lRAFFileSize - 1, False, 1, "", "", "")
        Sleep(0)
    End If
    
    Do While True
        
        If bExit Then
            Exit
        End If
        
        If lPosition + lBytes >= lRAFFileSize Then
            Exit
        End If
        
        lPosition = lPosition + lChr10PosLast
            
        If lPosition + lMaxBytes > lRAFFileSize Then
            lBytes = (lRAFFileSize - lPosition) - 1
            bFinalByteBatch = True
        Else
            lBytes = lMaxBytes
        End If
        
        Dim arrBytes(lBytes) As Byte
        lBytes = RAF.ReadBytes(arrBytes, 0, lBytes, lPosition)
        
        'Log(lPosition & " - " & RAF.CurrentPosition)
        
        'get the index of the last btEndOfLineByte in arrBytes
        'so we don't go past that in the next bit byte loop
        '-----------------------------------------------------
        If bFinalByteBatch = False Then
            lUB = lBytes - 1
            Do While arrBytes(lUB) <> btEndOfLineByte
                lUB = lUB - 1
            Loop
        Else
            lUB = lBytes - 1
            'Log("lUB (final batch): " & lUB)
        End If
        
        'ShowMemory(True)
        
        lSeparatorPos = -1
        lChr10Pos = 0 'as all the arrBytes will start with byte 10
        bExit = False
        
        If bShowProgress Then
            If x = 0 Or x = iMaxX Then
                B4XProgressBar1.mstrPreValues = "Checked " & iRows & " rows"
                B4XProgressBar1.Progress = lPosition
                'Log(iRows)
                x = 1
                Sleep(0)
            End If
        End If
        
        x = x + 1
        
        'start byte loop
        '--------------------------
        For i = 1 To lUB
            Select Case arrBytes(i)
                Case btSeparatorByte
                    If c < iColumns Then
                        If lSeparatorPos = -1 Then
                            If lChr10Pos = -1 Then
                                'this will be a separator after a closing encloser!
                                '--------------------------------------------------
                                lSeparatorPos = i
                            Else
                                c = c + 1
                            End If
                            lChr10Pos = -1
                        Else    'If lSeparatorPos = -1
                            c = c + 1
                            lChr10Pos = -1
                        End If    'If lSeparatorPos = -1
                        lSeparatorPos = i
                    End If    'If c < lColumns
                            
                    If bFinalByteBatch And i = lUB Then
                        iRows = iRows + 1
                        bExit = True 'as this exit will only exit the Select Case
                        Exit
                    End If

                Case btEndOfLineByte
                    If c < iColumns Then
                        If arrBytes(i - 1) = 13 Then
                            c = c + 1
                        End If    'If arrBytes(i - 1) = 13
        
                        iRows = iRows + 1
                        lSeparatorPos = -1
                        lChr10Pos = i
                        lChr10PosLast = i
                        c = 0
                                
                        If i - lOldI > iMaxRowBytes Then
                            iMaxRowBytes = i - lOldI
                        End If
                        lOldI = i
                                
                        If iRows = iCSVRows Then
                            bExit = True 'as this exit will only exit the Select Case
                            Exit
                        End If
                        
                    End If    'If c < iColumns
                            
                    If bFinalByteBatch And i = lUB Then
                        bExit = True 'as this exit will only exit the Select Case
                        Exit
                    End If
                        
                    'this code block is only for in case the last item of the last row bumps up the iMaxRowBytes,
                    'and we could leave this out and increase the found rows by looking at the last byte outside this loop
                    '-----------------------------------------------------------------------------------------------------
                Case Else
                    If bFinalByteBatch Then
                        If i = lUB Then
                            If c < iColumns Then
                                iRows = iRows + 1
                                                
                                If i - lOldI > iMaxRowBytes Then
                                    iMaxRowBytes = i - lOldI
                                End If
                
                            End If    'If c < iColumns
                                    
                            'Log("Final byte exit")
                            bExit = True
                            Exit
                        End If 'If i = lUB
                    End If 'If bFinalByteBatch
        
            End Select
                    
            If bExit Then Exit
                    
        Next
    
    Loop
    
    CSVProps.iFields = iColumns
    CSVProps.iRows = iRows
    CSVProps.iMaxRowBytes = iMaxRowBytes
    CSVProps.arrFoundFieldNames = arrFields
    CSVProps.arrFoundDataTypes = arrDataTypes
    
    If bShowProgress Then
        B4XProgressBar1.Reset
        Sleep(0)
    End If
    
    Return CSVProps

End Sub

Now this runs perfectly fine, no crashes. However if I the run the same as above (that is same file options as above and with data types already supplied up front) in a much larger Sub (because it caters for all file options and has the option to supply the data types at front or not) then I get these crashes.

This makes me think that maybe it somehow has to do with the amount of code in a Sub. I guess this is unlikely but is there some maximum in B4A regarding the amount of code in one Sub?

RBS
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
I guess this is unlikely but is there some maximum in B4A regarding the amount of code in one Sub?
In debug mode you can reach a compiler limit with very large subs. It will throw an error during compilation in that case. If it compiles then this is not the problem.
 
Upvote 0

edgar_ortiz

Active Member
Licensed User
Longtime User
Hi RB,

I use this to get run-time errors.

In Starter Service:
'Sub Process_Globals...'

    Private logs  As StringBuilder
    Private logcat As LogCat
    Private const emailAddress As String = "[email protected]"
    
Sub Service_Create
    'This is the program entry point.
    'This is a good place to load resources that are not specific to a single activity.
    '
    logs.Initialize
#if RELEASE
    logcat.LogCatStart(Array As String("-v","raw","*:F","B4A:v"), "logcat")
#end if
    '
    Provider.Initialize

End Sub


Private Sub logcat_LogCatData (Buffer() As Byte, Length As Int)
    logs.Append(BytesToString(Buffer, 0, Length, "utf8"))
    If logs.Length > 5000 Then
        logs.Remove(0, logs.Length - 4000)
    End If
End Sub

'Return true to allow the OS default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    'wait for 500ms to allow the logs to be updated.
    Dim jo As JavaObject
    Dim l As Long = 500
    jo.InitializeStatic("java.lang.Thread").RunMethod("sleep", Array(l))
    logcat.LogCatStop
    logs.Append(StackTrace)
    Dim email As Email
    email.To.Add(emailAddress)
    email.Subject = "Program crashed"
    email.Body = logs
    StartActivity(email.GetIntent)
    Return True
End Sub
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Hi RB,

I use this to get run-time errors.

In Starter Service:
'Sub Process_Globals...'

    Private logs  As StringBuilder
    Private logcat As LogCat
    Private const emailAddress As String = "[email protected]"
   
Sub Service_Create
    'This is the program entry point.
    'This is a good place to load resources that are not specific to a single activity.
    '
    logs.Initialize
#if RELEASE
    logcat.LogCatStart(Array As String("-v","raw","*:F","B4A:v"), "logcat")
#end if
    '
    Provider.Initialize

End Sub


Private Sub logcat_LogCatData (Buffer() As Byte, Length As Int)
    logs.Append(BytesToString(Buffer, 0, Length, "utf8"))
    If logs.Length > 5000 Then
        logs.Remove(0, logs.Length - 4000)
    End If
End Sub

'Return true to allow the OS default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    'wait for 500ms to allow the logs to be updated.
    Dim jo As JavaObject
    Dim l As Long = 500
    jo.InitializeStatic("java.lang.Thread").RunMethod("sleep", Array(l))
    logcat.LogCatStop
    logs.Append(StackTrace)
    Dim email As Email
    email.To.Add(emailAddress)
    email.Subject = "Program crashed"
    email.Body = logs
    StartActivity(email.GetIntent)
    Return True
End Sub

Thanks, will try that.

RBS
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Hi RB,

I use this to get run-time errors.

In Starter Service:
'Sub Process_Globals...'

    Private logs  As StringBuilder
    Private logcat As LogCat
    Private const emailAddress As String = "[email protected]"
   
Sub Service_Create
    'This is the program entry point.
    'This is a good place to load resources that are not specific to a single activity.
    '
    logs.Initialize
#if RELEASE
    logcat.LogCatStart(Array As String("-v","raw","*:F","B4A:v"), "logcat")
#end if
    '
    Provider.Initialize

End Sub


Private Sub logcat_LogCatData (Buffer() As Byte, Length As Int)
    logs.Append(BytesToString(Buffer, 0, Length, "utf8"))
    If logs.Length > 5000 Then
        logs.Remove(0, logs.Length - 4000)
    End If
End Sub

'Return true to allow the OS default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    'wait for 500ms to allow the logs to be updated.
    Dim jo As JavaObject
    Dim l As Long = 500
    jo.InitializeStatic("java.lang.Thread").RunMethod("sleep", Array(l))
    logcat.LogCatStop
    logs.Append(StackTrace)
    Dim email As Email
    email.To.Add(emailAddress)
    email.Subject = "Program crashed"
    email.Body = logs
    StartActivity(email.GetIntent)
    Return True
End Sub

I had this already in my app in a slightly simpler way:

B4X:
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    
    LogError(Error.Message, StackTrace)
    Return True
    
End Sub

Sub LogError(strErrorMsg As String, strStackTrace As String)
    
    Dim str As String
    Dim strCurrentDateTime As String
    Dim strDateFormat As String
    Dim strSub As String
    
    'keep the current date format
    strDateFormat = DateTime.DateFormat
    DateTime.DateFormat = "EEE, d/MMM/yyyy HH:mm:ss"
    strCurrentDateTime = DateTime.Date(DateTime.Now)
    
    strSub = GetErrorSubFromStackTrace(strStackTrace)
    
    str = strCurrentDateTime & CRLF & strErrorMsg & " in " & strSub
    General.lstErrors.Add(str)
    kvs.Put("Error list", General.lstErrors)
    
    DateTime.DateFormat = strDateFormat
    
End Sub

Sub GetErrorSubFromStackTrace(strStackTrace As String) As String
    
    Dim iPos1 As Int
    Dim iPos2 As Int
    
    iPos1 = strStackTrace.IndexOf("b4a.sqlitelight1.") + 17
    iPos2 = strStackTrace.IndexOf2("(", iPos1)
    
    Return strStackTrace.SubString2(iPos1, iPos2).Replace("_", "")
    
End Sub

Also tried your code, but it doesn't catch the error.

RBS
 
Upvote 0
Top