B4J Question problems database rollback sql

[Roberto P.]

I created a procedure that calls the begin transaction before 3 procedures that make data entry in tables 3. If one of the three procedures is in exception, the rollback does not work.
what am I doing wrong?

See example

Sub InsertTicketInterventi(aBO As BusinessObject, req As ServletRequest, resp As ServletResponse)
      
        mDB.BeginTransaction
      
        aBO.Master.OkUpdate     = True
        aBO.Detail.OkUpdate     = True
        aBO.Summary.OkUpdate     = True
      
       '1
        If aBO.Master.Fields.Size > 0 And aBO.Master.Name <> "" Then
          
           aBO.Master.OkUpdate = DBUtils.InsertMapsNTransaction(Main.mDB, aBO.Master.Name, aBO.Master.Fields)
           ' it's ok AND SAVE THE DATA
          
        End If
       
            ' 2
          If aBO.Master.OkUpdate And aBO.Summary.Fields.Size > 0 And aBO.Summary.Name <> "" Then
          
            Dim aFieldsIntervento As Map
      
            If aBO.Summary.Fields.Size > 0 Then
                aFieldsIntervento    = aBO.Summary.Fields.Get(0)
            End If
              
            ' leggo il nuovo
            aBO.Summary.IDDocServer     = m_IDManager.GetIdNumber(m_IDManager.ID_NumService)
                  
            aFieldsIntervento.Put("IDRigaIntervento", aBO.Summary.IDDocServer)
          
            aBO.Summary.OkUpdate         = DBUtils.InsertMapsNTransaction(Main.mDB, aBO.Summary.Name, aBO.Summary.Fields)
            ' it's ok AND SAVE THE DATA
              
        End If
      
        '2
        If aBO.Master.OkUpdate And aBO.Detail.Fields.Size > 0 And aBO.Detail.Name <> "" Then
          
             DBUtils.DeleteRecord( Main.mDB, aBO.Detail.Name, aBO.Detail.WhereUpdateFields)
                                 
            For i = 0 To aBO.Detail.Fields.Size - 1
              
                Dim aRecord As Map    =    aBO.Detail.Fields.Get(i)
                aRecord.Put("IDRigaIntervento", aBO.Summary.IDDocServer)
            Next
          
            ' it's NOT OK
            aBO.Detail.OkUpdate = DBUtils.InsertMapsNTransaction(Main.mDB, aBO.Detail.Name, aBO.Detail.Fields)
      
        End If
      
        ' CHECK FALSE
        If aBO.Master.OkUpdate And aBO.Detail.OkUpdate And aBO.Summary.OkUpdate  Then
            Main.mDB.TransactionSuccessful
                    
        Else
           ' CALL ROLLBACK - BUT THE DATA SAVED IN 1 AND 2 POINT REMAIND SAVED IN THE TABLET
           ' I EXPECT THAT IS MADE THE ROLLING BACK THE DATABASE AND REMOVE THE DATA...
            Main.mDB.Rollback
  
  
        End If
  
    Main.SendObjectReturnToSend(aBO, resp.OutputStream)

End Sub

where am I wrong? or the system has limits?
thansk

 

[Erel]

Can you post the code in:
DBUtils.InsertMapsNTransaction

 

[Roberto P.]

here is the code

Public Sub InsertMapsNTransaction(SQL As SQL, TableName As String, ListOfMaps As List) 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
        Main.Log2("Same Map found twice in list. Each item in the list should include a different map object.")
        Return False
    End If
  
    Dim aOk As Boolean = True
   
    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)
            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 Main.Log2("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
            SQL.ExecNonQuery2(sb.ToString, listOfValues)
        Next
  
    Catch
        Main.Log2("Errore inserimento dati tabella " & TableName & " = "  & LastException)
  
        aOk = False
    End Try
   
    Return aOk
   
End Sub

 

[Erel]

Make sure that none of the DBUtils methods that you use explicitly creates a transaction.

 

[Roberto P.]

I, have done that, and I do not see anything strange, and I do not know where to look!
Help me.
Thank you

 

[Erel]

Try to reproduce it in a small project and upload it.

 

[FabioRome]

have you table innodb into database ?

 

[Roberto P.]

no, they are different tables. I repeat that the problem is not the table but the transaction does not roolback when you create an exception.