B4J Question DBUtils.InsertMaps -> database locked / IllegalMonitorStateException

[Iatros]

Sorry for being stupid !

I try to save data to the database. Save behkopf or behdetail alone is running without an error. Both together I get two errors, dependent on the line

sql.TransactionSuccessful

database is locked (without sql.TransactionSuccessful)
or
java.lang.IllegalMonitorStateException (with sql.TransactionSuccessful)

Perhaps I haven't understand something fundamental ?

    Dim sBehNr As String = ""
    Dim rs As ResultSet = sql.ExecQuery("SELECT beh FROM admin")
    Dim iOld As Int
    Try
        iOld = rs.GetString("beh")
    Catch
        iOld = 0
    End Try
    sBehNr = iOld + 1
    sql.ExecNonQuery("UPDATE admin SET beh = " & sBehNr & " where beh = " & iOld)

    Dim allRecords As List
    allRecords.Initialize
    Dim oneRecord As Map
    oneRecord.Initialize
    oneRecord.Put("nr", sBehNr)
    oneRecord.Put("patnr", sPatientenNr)
    oneRecord.Put("art", "")
    oneRecord.Put("thema", "")
    oneRecord.Put("datum", DateTime.Date(DateTime.Now))
    oneRecord.Put("zeit", DateTime.Time(DateTime.Now))
    oneRecord.Put("bis", "")
    oneRecord.Put("StartZeit", "")
    oneRecord.Put("Intervall", "")
    oneRecord.Put("Dauer", "")
    allRecords.Add(oneRecord)
    DBUtils.InsertMaps(sql, "behkopf", allRecords)
    sql.TransactionSuccessful


    For Each ent As TreeItem In lstHarmonisierung.Root.Children
        Dim allRecords2 As List
        allRecords2.Initialize
        Dim oneRecord2 As Map
        oneRecord2.Initialize
        oneRecord2.Put("nr", sBehNr)
        oneRecord2.Put("db", "")
        Log(ent.Text)
        oneRecord2.Put("eintrag", ent.Text)
        oneRecord2.Put("Anatomie", "")
        allRecords2.Add(oneRecord2)
        DBUtils.InsertMaps(sql, "behdetail", allRecords2)
        sql.TransactionSuccessful

    Next

I create the tables:

Sub CreateTables
    If DBUtils.TableExists(sql, "admin") = False Then
        DBUtils.CreateTable(sql, "admin", CreateMap("nr": DBUtils.DB_INTEGER, "beh": DBUtils.DB_INTEGER, "language": DBUtils.DB_TEXT), "nr")
        'sql.ExecNonQuery("CREATE TABLE admin (nr INTEGER, beh INTEGER, language TEXT)")
        'DBUtils.InsertMaps(sql, "admin", CreateMap("nr": "0", "beh": "0", "language": "german"))

        Dim allRecords As List
        allRecords.Initialize
        Dim oneRecord As Map
        oneRecord.Initialize
        oneRecord.Put("nr", "0")
        oneRecord.Put("beh", "0")
        oneRecord.Put("language", "german")
        allRecords.Add(oneRecord)
        DBUtils.InsertMaps(sql, "admin", allRecords)
        'sql.ExecNonQuery("insert into admin (nr, beh, language) values ('0', '0', 'german'")
    End If
    If DBUtils.TableExists(sql, "patienten") = False Then
        DBUtils.CreateTable(sql, "patienten", CreateMap("nr": DBUtils.DB_TEXT, "vorname": DBUtils.DB_TEXT, "nachname": DBUtils.DB_TEXT, "strasse": DBUtils.DB_TEXT, "plz": DBUtils.DB_TEXT, "ort": DBUtils.DB_TEXT, "gebdat": DBUtils.DB_TEXT, "typ": DBUtils.DB_TEXT) ,"nr")
    End If
    
    If DBUtils.TableExists(sql, "behkopf") = False Then
        DBUtils.CreateTable(sql, "behkopf", CreateMap("nr": DBUtils.DB_TEXT, "patnr": DBUtils.DB_TEXT, "art": DBUtils.DB_TEXT, "thema": DBUtils.DB_TEXT, "datum": DBUtils.DB_TEXT, "zeit": DBUtils.DB_TEXT, "Von": DBUtils.DB_TEXT, "Bis": DBUtils.DB_TEXT, "StartZeit": DBUtils.DB_TEXT, "Intervall": DBUtils.DB_TEXT, "Dauer": DBUtils.DB_TEXT) ,"nr")
    End If

    If DBUtils.TableExists(sql, "behdetail") = False Then
        DBUtils.CreateTable(sql, "behdetail", CreateMap("nr": DBUtils.DB_TEXT, "db": DBUtils.DB_TEXT, "eintrag": DBUtils.DB_TEXT, "Anatomie": DBUtils.DB_TEXT) ,"nr")
    End If
    
End Sub

Sub FillLstPatienten
    lstSelectPatienten.Clear
    Dim rs As ResultSet = sql.ExecQuery("SELECT nr, nachname, vorname FROM patienten order by nachname, vorname")
    Do While rs.NextRow
        Dim sVorname As String
        Dim sNachname As String
        Dim iID As Int
        sVorname = rs.GetString("vorname")
        sNachname = rs.GetString("nachname")
        iID = rs.GetInt("nr")
        lstSelectPatienten.AddTextItem(sNachname & ", " & sVorname & "|" & iID, sNachname & ", " & sVorname & "|" & iID)
        'rs.NextRow
    Loop
End Sub





Fill Form Fields:
...
    sPatientenNr = Functions.Separator(sText, "|", 2, False)

    ClearStammdaten
    
    Dim lstTable As List
    Dim strFields() As String
    Dim lstRecords As List
    Dim iCountRecords As Int
    
    lstTable = DBUtils.ExecuteMemoryTable(sql, "SELECT * FROM patienten where nr = '" & sPatientenNr & "'", Null, 0)
    lstRecords.Initialize
    For iCountRecords = 0 To lstTable.Size - 1
        strFields = lstTable.Get(iCountRecords)
        txtVorname.Text = strFields(1)
        txtNachname.Text = strFields(2)
        txtStrasse.Text = strFields(3)
...

 

[Erel]

 sql.ExecNonQuery("UPDATE admin SET beh = " & sBehNr & " where beh = " & iOld)

1. This is really considered bad code. [B4X] "Code Smells" - common mistakes and other tips

2. You don't need to call SQL.TransactionSuccessful unless you have explicitly created a transaction.

3. Move the call to InsertMaps, outside of the for loop. First you create a list with all the data and then you insert it with a single call.

 

[Iatros]

Ok, I will work through your link. I did it, because I only habe one record in this table.

Now it works without an error, but I get no values:
InsertMaps (first query out of 1): INSERT INTO [behkopf] ([nr], [patnr], [art], [thema], [datum], [zeit], [bis], [StartZeit], [Intervall], [Dauer]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)