B4J Question Is there a way around? DBUtils InsertMaps Transaction?

[Mashiane]

Ola

I'm adding multiple records to my sqlite db using DBUtils InsertMaps. This uses a transaction for the inserts. This works perfectly, however if there are records in your table that conflict with a primary key, even if there are new records in your INSERT statements, the whole transaction gets cancelled.

Is there a way around to ensure that even if there are conflicts, the unconflicted records are added to the target table still?

'Inserts the data to the table.
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
Public Sub InsertMaps(jSQL As SQL, TableName As String, ListOfMaps As List,bDebug As Boolean) As Boolean
    Dim sb, columns, values As StringBuilder
    'Small check for a common error where the same map is used in a loop
    If ListOfMaps.Size > 1 And ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
        Log("Same Map found twice in list. Each item in the list should include a different map object.")
        Return False
    End If
    jSQL.BeginTransaction
    Try
        For i1 = 0 To ListOfMaps.Size - 1
            sb.Initialize
            columns.Initialize
            values.Initialize
            Dim listOfValues As List
            listOfValues.Initialize
            sb.Append("INSERT INTO [" & TableName & "] (")
            Dim m As Map
            m = ListOfMaps.Get(i1)
            m = DeDuplicateMap(m)
            For i2 = 0 To m.Size - 1
                Dim col As String
                Dim value As Object   
                col = m.GetKeyAt(i2)
                value = m.GetValueAt(i2)
                If i2 > 0 Then
                    columns.Append(", ")
                    values.Append(", ")
                End If
                columns.Append(EscapeField(col))
                
                values.Append("?")
                listOfValues.Add(value)
            Next
            sb.Append(columns.ToString)
            sb.Append(") VALUES (")
            sb.Append(values.ToString)
            sb.Append(")")
            'If i1 = 0 Then Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
            If bDebug = True Then
                Log(sb.ToString)
                Log(listOfValues)
            End If
            jSQL.ExecNonQuery2(sb.tostring,  listOfValues)
        Next
        jSQL.TransactionSuccessful
        Return True
    Catch
        Log("InsertMaps: " & LastException)
        jSQL.Rollback
        Return False
    End Try
End Sub

Thanks in advance...

 

[BillMeyer]

Sawubona Umfowethu,

Try using this https://www.tutlane.com/tutorial/sqlite/sqlite-replace-statement - It's the INSERT/REPLACE syntax

I use it on MySQL and it works perfectly, although I had to re-jig some of the indices (indexes/keys) on the DB - but it should address your problem.

Have a lekka day !!

 

[Mashiane]

@BillMeyer , Dankie, my broer van 'n ander ma. Ohh yes!! Ta!

Updated: This deletes and replaces. Thing is my inserts affects some fields in the table and not all, so with delete, everything will be deleted and only the fields in the inserts will be affected. Is my analysis correct? If so, then i'm back to square 1, jah neh!

 

[BillMeyer]

@Mashiane

In my experience, only the affected record AND the affected fields will be updated - the rest remains intact - but it would be worth the time to test on a dummy table first.
My only experience that could have an impact (if you use it like this) is that all my tables have a primary key that is named "Serial" and auto increments. With the REPLACE, then what previously was record 212 is deleted and re-inserted now auto increments and for example now becomes record 318.

I worked around this by taking some of my fields and putting them in an index key which caused the Replace statement to check against that first and then change the record as required. I hope this makes sense.

I am currently working in B4J - I'll test and get back to you before EOB today with the results.

 

[OliverA]

This may do the trick. It just skips any failed INSERTS (currently logs them, change as appropriate). This will be way slower with a large number of inserts, since no transaction is used.
Notes:
1) I just did a code edit here, so errors may be present.
2) False is returned if any of the inserts fail (but does not mean that no inserts succeeded). As in the original code, False may be returned when the ListOfMaps is not set up properly. This may need to be changed to suit your needs.

'Inserts the data to the table, but will not batch inserts. Will return False if there is an issue with ListOfMaps
'  or when one of the INSERTs encountered an issue
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
Public Sub InsertMaps2(jSQL As SQL, TableName As String, ListOfMaps As List,bDebug As Boolean) As Boolean
    Dim sb, columns, values As StringBuilder
    'Small check for a common error where the same map is used in a loop
    If ListOfMaps.Size > 1 And ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
        Log("Same Map found twice in list. Each item in the list should include a different map object.")
        Return False
    End If
'    jSQL.BeginTransaction
'    Try
        Dim retVal as Boolean = True
        For i1 = 0 To ListOfMaps.Size - 1
            sb.Initialize
            columns.Initialize
            values.Initialize
            Dim listOfValues As List
            listOfValues.Initialize
            sb.Append("INSERT INTO [" & TableName & "] (")
            Dim m As Map
            m = ListOfMaps.Get(i1)
            m = DeDuplicateMap(m)
            For i2 = 0 To m.Size - 1
                Dim col As String
                Dim value As Object   
                col = m.GetKeyAt(i2)
                value = m.GetValueAt(i2)
                If i2 > 0 Then
                    columns.Append(", ")
                    values.Append(", ")
                End If
                columns.Append(EscapeField(col))
                
                values.Append("?")
                listOfValues.Add(value)
            Next
            sb.Append(columns.ToString)
            sb.Append(") VALUES (")
            sb.Append(values.ToString)
            sb.Append(")")
            'If i1 = 0 Then Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
            If bDebug = True Then
                Log(sb.ToString)
                Log(listOfValues)
            End If
            Try
                jSQL.ExecNonQuery2(sb.tostring,  listOfValues)
            Catch
                Log("InsertMaps2 Error:")
                Log($"SQL Statement: ${sb.ToString}"$)
                Log($"SQL Values: ${listOfValues}"$)
                Log($"Exception: ${LastException}"$)
                retVal = False
            End Try
        Next
        return retVal
'        jSQL.TransactionSuccessful
'        Return True
'    Catch
'       Log("InsertMaps: " & LastException)
'        jSQL.Rollback
'        Return False
'    End Try
End Sub

 

[XbNnX_507]

Public Sub InsertMaps(SQL As SQL, TableName As String, ListOfMaps As List)
    Dim sb, columns, values As StringBuilder
    'Small check for a common error where the same map is used in a loop
    If ListOfMaps.Size > 1 And ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
        Log("Same Map found twice in list. Each item in the list should include a different map object.")
        Return
    End If
    SQL.BeginTransaction
    Try
        For i1 = 0 To ListOfMaps.Size - 1
            sb.Initialize
            columns.Initialize
            values.Initialize
            Dim listOfValues As List
            listOfValues.Initialize
            sb.Append("INSERT INTO [" & TableName & "] (")
            Dim m As Map = ListOfMaps.Get(i1)
            For Each col As String In m.Keys
                Dim value As Object = m.Get(col)
                If listOfValues.Size > 0 Then
                    columns.Append(", ")
                    values.Append(", ")
                End If
                columns.Append(EscapeField(col))
                values.Append("?")
                listOfValues.Add(value)
            Next
            sb.Append(columns.ToString).Append(") VALUES (").Append(values.ToString).Append(")")
            If i1 = 0 Then Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
            SQL.ExecNonQuery2(sb.ToString, listOfValues)
        Next
        SQL.TransactionSuccessful
    Catch
        Log(LastException)
    End Try
    SQL.EndTransaction
End Sub