B4J Question My Batch Insert Qurey insn't working.

rgarnett1955 · Oct 25, 2020

Hi,

If I use the following code my table gets populated:

Non-Batch Insert:

'============================================================================================
private Sub insertRecsToTempBuffer(recList As List, tblTempMetaDataArg As tblTemp_Meta_t, N5Arg As Int)
    
    Dim n As Int = 1
    Dim listN As Int
    Dim ArgArray As List
    ArgArray.Initialize
    listN = recList.Size
    
    
    Dim qryStr     As String
    Dim rSet As ResultSet

    'test the data set to ensure all records for the five minute time windows will fit in memory
    qryStr = _
    $"INSERT INTO tblAgg2SecTemp(
        julianDateUTC,
        jDate,
        JdateTime5Min,
        dataTicks,
        frequency,
        Vrms,
        P1,
        P2,
        P3,
        ProcessorTemp,
        mainBatteryVolts,
        Quality,
        serverTicksStart,
        serverTicksEnd,
        N5,
        dataTicksString,
        JdateTime5MinStr)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"$
    

    For Each row() As String In recList
        ArgArray.Clear
        ArgArray.Add(row(0))
        ArgArray.Add(row(1))
        ArgArray.Add(row(2))
        ArgArray.Add(row(3))
        ArgArray.Add(row(4))
        ArgArray.Add(row(5))
        ArgArray.Add(row(6))
        ArgArray.Add(row(7))
        ArgArray.Add(row(8))
        ArgArray.Add(row(9))
        ArgArray.Add(row(10))
        ArgArray.Add(row(11))
        ArgArray.Add(row(12))
        
                If n = listN Then
            'Add the time to the last record
            ArgArray.Add(DateTime.Now)  '13
        Else
            ArgArray.Add(0)                '13
        End If
        ArgArray.Add(N5Arg) '14
        ArgArray.Add(DateTime.Date(row(3)) & " " & DateTime.Time(row(3))) '15
        ArgArray.Add(jdt.jdDateTimeToDateTimeStr(row(2))) '16
        
        Try
            sqlDbAggTemp.BeginTransaction
            sqlDbAggTemp.ExecNonQuery2(qryStr, ArgArray)
            sqlDbAggTemp.TransactionSuccessful
            Log(jdt.jdDateTimeToDateTimeStr(row(0)))

            n = n + 1
        Catch
            Log(LastException)
        End Try
    Next
    
    qryStr = _
    $"SELECT count(rowid) AS noRecsAdded FROM tblAgg2SecTemp"$
    
    rSet = sqlDbAggTemp.ExecQuery(qryStr)
    If rSet.NextRow = False Then
        tblTempMetaData.errorFlag = True
    End If   
    
    tblTempMetaData.noRecsInserted =  rSet.GetLong("noRecsAdded")
    
    If tblTempMetaDataArg.noRecsInserted <> listN Then
        Log("Insert into tblAgg2SecTemp NList = " & listN & " <> " & " No Inserted = " & tblTempMetaData.noRecsInserted)
        tblTempMetaData.errorFlag = True
    End If
    tblTempMetaData.errorFlag = False
End Sub

If I execute the batch version it does not insert records, but doesn't thrown any errors.

Batch Insert:

'============================================================================================
private Sub insertRecsToTempBuffer(recList As List, tblTempMetaDataArg As tblTemp_Meta_t, N5Arg As Int)
    
    Dim n As Int = 1
    Dim listN As Int
    Dim ArgArray As List
    ArgArray.Initialize
    listN = recList.Size
    
    
    Dim qryStr     As String
    Dim rSet As ResultSet

    'test the data set to ensure all records for the five minute time windows will fit in memory
    qryStr = _
    $"INSERT INTO tblAgg2SecTemp(
        julianDateUTC,
        jDate,
        JdateTime5Min,
        dataTicks,
        frequency,
        Vrms,
        P1,
        P2,
        P3,
        ProcessorTemp,
        mainBatteryVolts,
        Quality,
        serverTicksStart,
        serverTicksEnd,
        N5,
        dataTicksString,
        JdateTime5MinStr)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"$
    

    For Each row() As String In recList
        ArgArray.Clear
        ArgArray.Add(row(0))
        ArgArray.Add(row(1))
        ArgArray.Add(row(2))
        ArgArray.Add(row(3))
        ArgArray.Add(row(4))
        ArgArray.Add(row(5))
        ArgArray.Add(row(6))
        ArgArray.Add(row(7))
        ArgArray.Add(row(8))
        ArgArray.Add(row(9))
        ArgArray.Add(row(10))
        ArgArray.Add(row(11))
        ArgArray.Add(row(12))
        
                If n = listN Then
            'Add the time to the last record
            ArgArray.Add(DateTime.Now)  '13
        Else
            ArgArray.Add(0)                '13
        End If
        ArgArray.Add(N5Arg) '14
        ArgArray.Add(DateTime.Date(row(3)) & " " & DateTime.Time(row(3))) '15
        ArgArray.Add(jdt.jdDateTimeToDateTimeStr(row(2))) '16
        
        Try
            Log(jdt.jdDateTimeToDateTimeStr(row(0)))
            
            sqlDbAggTemp.AddNonQueryToBatch(qryStr, ArgArray)

            n = n + 1
        Catch
            Log(LastException)
        End Try
    Next

    sqlDbAggTemp.ExecNonQueryBatch("sqlDbAggTemp")
    Wait for CanContinueInsertRecsToTemp(Success As Boolean)
    
    
    qryStr = _
    $"SELECT count(rowid) AS noRecsAdded FROM tblAgg2SecTemp"$
    
    rSet = sqlDbAggTemp.ExecQuery(qryStr)
    If rSet.NextRow = False Then
        tblTempMetaData.errorFlag = True
    End If   
    
    tblTempMetaData.noRecsInserted =  rSet.GetLong("noRecsAdded")
    
    If tblTempMetaDataArg.noRecsInserted <> listN Then
        Log("Insert into tblAgg2SecTemp NList = " & listN & " <> " & " No Inserted = " & tblTempMetaData.noRecsInserted)
        tblTempMetaData.errorFlag = True
    End If
    tblTempMetaData.errorFlag = False
End Sub

'======================================================================================================
private Sub sqlDbAggTemp_NonQueryComplete(Success As Boolean)
    CanContinueInsertRecsToTemp(Success)
End Sub

'======================================================================================================
Sub CanContinueInsertRecsToTemp(Success As Boolean) As ResumableSub
    Sleep(0)
    #If LOGGING_ON
    Log("Can Continue  - Main " & DateTime.Date(DateTime.now))
    #End If
End Sub

Why would this be, I wonder.

I want to use batch as the queries take a long time to run and I want to be able to interact with the process.

Best regards
Rob

Erel · Oct 25, 2020

This is one of the most common mistakes related to lists.
If I remember correctly it is explained in the Objects & Custom Types video tutorial: https://www.b4x.com/etp.html (if not then it is in the Collections tutorial).

You are adding single list (ArgArray) multiple times. Move the Dim + Initialize lines inside the loop.

rgarnett1955 · Oct 26, 2020

Hi Erel,

I don't think that this is the problem.

For the non-asynchronous version I tried with the following two code versions:

Dim Arg, Init Arg Outside of loop:

private Sub insertRecsToTempBuffer(recList As List, tblTempMetaDataArg As tblTemp_Meta_t, N5Arg As Int)
    Dim n As Int = 1
    Dim listN As Int

    Dim ArgArray As List
    ArgArray.Initialize

    listN = recList.Size

    Dim qryStr     As String

    'test the data set to ensure all records for the five minute time windows will fit in memory
    qryStr = _
    $"INSERT INTO tblAgg2SecTemp(
        julianDateUTC, ...

    ...    JdateTime5MinStr)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"$

    'Set up the arguments - values to be inserted
    For Each row() As String In recList
        ArgArray.Clear
        ArgArray.Add(row(0)) ...
        ...ArgArray.Add(jdt.jdDateTimeToDateTimeStr(row(2))) '16
    
        'Insert transaction
        Try
            sqlDbAggTemp.BeginTransaction
            sqlDbAggTemp.ExecNonQuery2(qryStr, ArgArray)
            sqlDbAggTemp.TransactionSuccessful
            n = n + 1
        Catch
            sqlDbPm.Close
            sqlDbAgg.Close
            sqlDbAggTemp.Close
            Log(LastException)
        End Try
    Next

Dim Arg, Init Arg Inside of loop:

private Sub insertRecsToTempBuffer(recList As List, tblTempMetaDataArg As tblTemp_Meta_t, N5Arg As Int)

    Dim n As Int = 1
    Dim listN As Int

    listN = recList.Size

    Dim qryStr     As String

    tblTempMetaData.startTimeProcess = DateTime.Now
    Log("Start Time Process = " & DateTime.Date(tblTempMetaData.startTimeProcess) & " " & DateTime.Time(tblTempMetaData.startTimeProcess))
    Log(" ")

    'test the data set to ensure all records for the five minute time windows will fit in memory
    qryStr = _
    $"INSERT INTO tblAgg2SecTemp(
        julianDateUTC, ...
     ...JdateTime5MinStr)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"$

    'Set up the arguments - values to be inserted
    For Each row() As String In recList
        Dim ArgArray As List
        ArgArray.Initialize
        ArgArray.Add(row(0))...
     ...ArgArray.Add(jdt.jdDateTimeToDateTimeStr(row(2))) '16
    
        'Insert transaction
        Try
            sqlDbAggTemp.BeginTransaction
            sqlDbAggTemp.ExecNonQuery2(qryStr, ArgArray)
            sqlDbAggTemp.TransactionSuccessful
'            Log(jdt.jdDateTimeToDateTimeStr(row(0)))
        
'            sqlDbAggTemp.AddNonQueryToBatch(qryStr, ArgArray)

            n = n + 1
        Catch
            sqlDbPm.Close
            sqlDbAgg.Close
            sqlDbAggTemp.Close
            Log(LastException)
        End Try
    Next

These two versions ran and inserted identical records into the table. The first took 1m:37s to complete the second took about the same 1m:38s

I checked the first 1000 records of each run making sure I deleted the records prior to each run. They were identical with the exception of the server ticks which are set at run time and not part of the source data. I also checked the out put of an aggregate query that groups the data over five minute intervals for the entire table of 1785 records.

However when I run the following code for the async batch insert I get no inserted records, but the following error:

Batch Insert:

private Sub insertRecsToTempBuffer(recList As List, tblTempMetaDataArg As tblTemp_Meta_t, N5Arg As Int)

    Dim n As Int = 1
    Dim listN As Int

    listN = recList.Size

    Dim qryStr     As String

    tblTempMetaData.startTimeProcess = DateTime.Now
    Log("Start Time Process = " & DateTime.Date(tblTempMetaData.startTimeProcess) & " " & DateTime.Time(tblTempMetaData.startTimeProcess))
    Log(" ")

    'test the data set to ensure all records for the five minute time windows will fit in memory
    qryStr = _
    $"INSERT INTO tblAgg2SecTemp(
        julianDateUTC, ...
    ...    JdateTime5MinStr)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"$

    'Set up the arguments - values to be inserted
    For Each row() As String In recList
        Dim ArgArray As List
        ArgArray.Initialize
        ArgArray.Add(row(0)) ...
    ...    ArgArray.Add(jdt.jdDateTimeToDateTimeStr(row(2))) '16
    
        'Insert transaction
        Try
            sqlDbAggTemp.AddNonQueryToBatch(qryStr, ArgArray)
            n = n + 1
        Catch
            sqlDbPm.Close
            sqlDbAgg.Close
            sqlDbAggTemp.Close
            Log(LastException)
        End Try
    Next

    'Execute batch insert query
    Dim SenderFilter As Object = sqlDbAggTemp.ExecNonQueryBatch("sqlDbAggTemp")
    Wait For (SenderFilter) sqlDbAggTemp_NonQueryComplete (Success As Boolean)

    Log("NonQuery: " & Success)
    tblTempMetaData.errorFlag = Not(Success)

    tblTempMetaData.endTimeProcess = DateTime.Now
    Log("End Time Process = " & DateTime.Date(tblTempMetaData.endTimeProcess) & " " & DateTime.Time(tblTempMetaData.endTimeProcess))
    Log("Duration Process: "  &  DateTime.GetMinute(tblTempMetaData.endTimeProcess - tblTempMetaData.startTimeProcess) & ":" & DateTime.GetSecond(tblTempMetaData.endTimeProcess - tblTempMetaData.startTimeProcess))

Error - Batch Insert:

Start Time Process = 2020-10-26 12:51:10

No Of Rows Inserted Into tblAgg2SecTemp = 0
#
# A fatal error has been detected by the Java Runtime Environment:
#
#  EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x00000000677d7356, pid=10004, tid=0x0000000000001f48
#
# JRE version: Java(TM) SE Runtime Environment (8.0_261-b12) (build 1.8.0_261-b12)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.261-b12 mixed mode windows-amd64 compressed oops)
# Problematic frame:
# C  [sqlite-3.32.3.2-be79fc73-d518-4f5f-b791-a6d2da70dab4-sqlitejdbc.dll+0x57356]
#
# Failed to write core dump. Minidumps are not enabled by default on client versions of Windows
#
# An error report file with more information is saved as:
# W:\MyProgramFiles_W\PowerMonProgsDev\Pmon_Agg_Server\Objects\hs_err_pid10004.log
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.java.com/bugreport/crash.jsp
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#

This looks like a memory issue to me with the queued queries taking too much memory. I don't know why I didn't get the error before when I was using the CanContinue callback method instead of this version of the code, but at least we now know what we are looking for.

I couldn't work out how to turn on the mini-dump if that's required. I looked up stack exchange and they said you need to use:

-XX:+CreateMinidumpOnCrash,

but I don't know where to use this.

Additional: I tried the following sql Versions:

SQL Versions:

    #AdditionalJar: sqlite-jdbc-3.32.3.2
'    #AdditionalJar: sqlite-jdbc-3.31.1
'    #AdditionalJar: sqlite-jdbc-3.15.1
'    #AdditionalJar: sqlite-jdbc-3.21.0.1

None of them fixed the problem.

Ohh! Here is the log file attached:

Additional 2: I restricted the data to 71 records, still got the same error.

Any suggestions

Best regards
Rob

Erel · Oct 26, 2020

Create a single transaction for all the inserts.

rgarnett1955 · Oct 26, 2020

B4J Question My Batch Insert Qurey insn't working.

rgarnett1955

Active Member

Erel

B4X founder

rgarnett1955

Active Member

Attachments

Erel

B4X founder

rgarnett1955

Active Member

Similar Threads