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?
Thanks in advance...
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?
B4X:
'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...