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

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?

incendio said:

I need a return values for every succesful ins_child operation

Click to expand...

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.

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?

aeric said:

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

Click to expand...

For further processing in Android side.

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

José J. Aguilar said:

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

B4X:

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

Click to expand...

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?

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

José J. Aguilar said:

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

Click to expand...

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.

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.

incendio said:

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.

Click to expand...

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

MrKim said:

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.

Click to expand...

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.