Android Question [SOLVED] jRDC2, how to update master child table in Server while keeping data integrity?

[incendio]

Hi guys,

I have a master and child table in server. When insert/update data, to keeps data integrity usually I sent commands to insert/update master child table in a list of commands with ExecuteBatch.

The codes, something look like these

Sub SaveData   
    Private commands As List
    commands.Initialize
    
    Private cmd As DBCommand
    cmd.Initialize
    cmd.Name = "ins_master"
    commands.Add(cmd)

    Private Cur As Cursor
    Cur = Main.SQLMem.ExecQuery("select * from Child")

    For i = 0 To Cur.RowCount - 1
        Cur.Position = i
                
        Private cmd As DBCommand
        cmd.Initialize
        cmd.Name = "ins_child"
        commands.Add(cmd)
    Next
    Cur.Close
    wait for (reqM.ExecuteBatch(commands,"insert")) JobDone(job As HttpJob)
End Sub

The command ins_master and ins_child are a stored procedure in server without returning any values.

But the problem is, now I need a return values for every succesful ins_child operation.

I can do this by separated ins_master and ins_child operation, ins_master will execute using ExecuteCommand, while ins_child will execute with ExecuteQuery, but this will break data integrity if something goes wrong in one of ins_master/ins_child operations.

Is there a way to handle this situation?

 

[josejad]

Hi incendio:

Not sure if I'm wrong, but as far as I know, ExecuteBatch (the way you do it now) is executed in a single transaction and is rolled back if there is some error. So, if you get the success message, you could assume all your ins_child are inserted properly.

Or maybe I'm not understanding rigth this sencence?

I need a return values for every succesful ins_child operation

 

[incendio]

Hi José J. Aguilar,

The stored procedure in ins_child returns an integer value that needed for further processing in Android side.

This integer value is generated on server side after records inserted in child table.

So, you see, it was not enough if only to know whether the operation was a success or a failure.

Currently, to achieve this purpose, I run ExecuteBatch, and if it was successful, run again ExecuteQuery to retrieve values from child table.

This method has a cost, it will increase traffic to the server.

Since the application expected to have lots of users, I was hoping, perhaps there's a single command that could achieve that goal.

But it seems, there is no way to do that.

Thanks anyway.

 

[aeric]

I don’t understand why you need to select from Child table. Is Child table already have records before you insert into Master?

What do you mean data integrity? What are the Primary and Foreign keys between both tables?

 

[incendio]

I don’t understand why you need to select from Child table. Is Child table already have records before you insert into Master?

For further processing in Android side.

 

[josejad]

Have you tried after your "ins" commands add another cmd command with a select to get the inserted id's and execute it in the batch command?
And then add after your j.success

If j.Success Then
    Dim req As DBRequestManager = CreateRequest
    req.HandleJobAsync(j, "req")
    Wait For (req) req_Result(res As DBResult) 'get the new id's
    'work with result
    req.PrintTable(res)
Else
    Log("ERROR: " & j.ErrorMessage)
End If

 

[incendio]

Have you tried after your "ins" commands add another cmd command with a select to get the inserted id's and execute it in the batch command?
And then add after your j.success

If j.Success Then
    Dim req As DBRequestManager = CreateRequest
    req.HandleJobAsync(j, "req")
    Wait For (req) req_Result(res As DBResult) 'get the new id's
    'work with result
    req.PrintTable(res)
Else
    Log("ERROR: " & j.ErrorMessage)
End If

Do you mean, after a successful ExecuteBatch(with contain only inser/update command), then issue another command but this time with ExecQuery command to retrieve id form child table?

 

[josejad]

No, I mean add commands to the executebatch after the insert, to get the inserted id’s in the same transaction

 

[incendio]

No, I mean add commands to the executebatch after the insert, to get the inserted id’s in the same transaction

I see, but unfortunately, I have already implemented other way to handle the limitation of ExecuteBatch.

Since I need an unique id on child table, I created this unique id on Android side and sent it to server, so that, no need again to retrieved these ids from server.

This can achieve with a change in program flow and add a column in child table on server. These are the steps

1. add LineNo column on Child Table in server
2. For every records on Android side, generate unique LineNo
3. run ExecuteQuery to get Id for Primary key in master from server
4. This LineNo + Primary key of master, became a unique id in child table
5. run ExecuteBatch to insert master and child table
   

Thank anyway for your help.

 

[MrKim]

Change your stored procedure on the server to return a value(s), then instead of execute batch just run an ordinary ExecQueryAsync. I would also suggest that if possible, write one server side procedure that you can use to insert both the master and child data. If you pass both then it will insert both. If you pass only new child data, then it will insert just that. Put all your validation rules on the server. I write all my server side procedures so they always return at least one field which I call 'Status' and it returns either "SUCCESS", or the error message. If it returns SUCCESS then I can retrieve the data for any other fields I might be returning.

Experience has taught me - put as much as you can on the server side and make as few server calls as possible. I didn't like doing this at first because for me it is easier to write VB code and it has meant learning more SQL than I wanted to but it has saved me a LOT of time in the long run.

Edit: Just read your last post. I would still recommend moving those steps to the server and have it return your unique id or whatever else you need.

 

[Vikjh]

I see, but unfortunately, I have already implemented other way to handle the limitation of ExecuteBatch.

Since I need an unique id on child table, I created this unique id on Android side and sent it to server, so that, no need again to retrieved these ids from server.

This can achieve with a change in program flow and add a column in child table on server. These are the steps

1. add LineNo column on Child Table in server
2. For every records on Android side, generate unique LineNo
3. run ExecuteQuery to get Id for Primary key in master from server
4. This LineNo + Primary key of master, became a unique id in child table
5. run ExecuteBatch to insert master and child table

Thank anyway for your help.

For MS SQL you can use: "INSERT INTO tbl (Fild1, Fild1) OUTPUT INSERTED.ID Values (Value1,Value2)" it return ID.

However, it need much more traffic. Best way use stored procedures with input and output parameters

 

[incendio]

Edit: Just read your last post. I would still recommend moving those steps to the server and have it return your unique id or whatever else you need.

Thanks for your hint.

Your method need to download again the child's table unique id from server to Android device,
my method need to add a column in child table to store unique id generated on Android side, but there's no need to download this child's id from server to Android device.

In this case, I prefer mine.