Android Code Snippet jRDC2 Add MySql Master / Detail

Hi:

Searching several threads in the forum, I've used this code to add a row in a "master" table, and then the "children rows" in a "detail" table. May be useful to someone. Improvements are welcome.

Table Master:
id (primary, autoincrement), field 1, field 2...

Table Detail:
id, master_id, detail1, detail2...

B4X:
Sub InsertMasterDetail
    Dim Commands As List
    Commands.Initialize
    Dim cmd0 As DBCommand = CreateCommand("addMasterField", Array(params)) 'Add master field
    Dim cmd1 As DBCommand = CreateCommand("getIDMaster", Null) 'Get the id of the added field
    Commands.Add(cmd0)
    Commands.Add(cmd1)
  
    For i = 0 To ListOfDetailFields.Size - 1
        Dim CurrentField As FieldType
        CurrentField= ListOfDetailFields.Get(i)
        Dim cmd As DBCommand
        cmd.Initialize
        cmd.Name = "addDetailField"
        cmd.Parameters = Array As Object(params)
        Commands.Add(cmd)
    Next

    Dim j As HttpJob = CreateRequest.ExecuteBatch(Commands, Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted successfully!")
    End If
    j.Release
End Sub

The config.properties for the B4J jrdc2 server would be something like:
B4X:
sql.addMasterField= INSERT INTO `master_table` (`field1`, `field2`, ...) VALUES (?,?,...);
sql.getIDMaster = SET @last_id := (SELECT LAST_INSERT_ID());
sql.addDetailField= INSERT INTO `detail_table` (`master_id`, `field1`, `field2`, ...) VALUES (@last_id, ?, ?, ...);
 
Last edited:
Top