Android Question jRDC : Insert command and get last id (primary key)

[h725]

Hello Community !

I am working with B4A, JRDC2 and MS SQL Server.
I am accessing the DB with several devices (so that table insert commands can come from several devices).

I would like to know if there is a reliable way to implement the following:

With the insert command, I want to insert the LAST id (primary key in the table) in a column:
The insert command in the B4J server-config file looks like (e.g.):
INSERT INTO ORDERS (ID_PROD,PRODNAME,ID_PARENT) VALUES (?,?,?)

Now ID_PARENT should be the id of the last entry in the table (made of the the device which also makes the following entry.) Any ideas how to realize this ?

Thank you very much.

Regards
h725 (son of h724)

 

[jsanchezc]

You can get @@identity:
Best pactice is create a stored procedure but you can do it executing query like this:

INSERT INTO PARENTABLE (FIELD,FIELD2) VALUES(?,?);SELECT @@IDENTITY AS IDNEW;
this will return a single row result with the new id inserted, so execute loading a list with result.
then read result ans d load idnew to a variable and use this variable to assign last value (ID_PARENT)

INSERT INTO ORDERS (ID_PRODUCT,PRODUCTNAME,ID_PARENT) VALUES (?,?,?)

 

[h725]

Somehow I am a little bit stuck on it.
The problem is that @@identity seems to be not very reliable, as discussed in some Forums.
But, can you explain how to use this "command - extension" in the config-file ?

In the SQL-Server-Management-Studio the following command gives the correct value back (in the SQL-Server-Management Studio):
INSERT INTO ORDERS (ID_PROD,PRODNAME,ID_PARENT) OUTPUT INSERTED.ID VALUES (?,?,?)

But when I put the command into the config file, I only get 0 back (In the ReqManager_Result(result As DBResult).)

 

[sorex]

I never had problems with @@IDENTITY. I guess it will only fail on high server loads and when it all happends in the same table.

also notice that not all query trickery will work over external odbc like connections.

 

[h725]

Just to verify that I do not make a mistake in the config-file. Is it correct to put the command with the "extension" into the
jRDC config-file like this:

sql.insert_orders = INSERT INTO ORDERS (ID_PROD,PRODNAME,ID_PARENT) VALUES (?,?,?);SELECT @@IDENTITY AS IDNEW;

 

[h725]

I found a possible solution:
Using 1 command and 1 following query:

sql.insert_orders = INSERT INTO ORDERS (ID_PROD,PRODNAME,ID_PARENT) VALUES (?,?,?)
sql.get_last_id = SELECT @@IDENTITY AS IDNEW

Reaction on the insert query:

Sub ReqManager_Result(result As DBResult)
   If result.Tag <> Null Then
        Select Case result.Tag
        
            Case    "insert_orders"
Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = "get_last_id"
    reqManager.ExecuteQuery(cmd, 0, "get_last_id")
end select

The question is, if this combination is reliable enough.
Can jRDC handle multiple queries, something like:
sql.insert_orders = INSERT INTO ORDERS (ID_PROD,PRODNAME,ID_PARENT) VALUES (?,?,?);SELECT @@IDENTITY AS IDNEW;

OR is it possible to do something like that:
INSERT INTO ORDERS (ID_PROD,PRODNAME,ID_PARENT) OUTPUT INSERTED.ID VALUES (?,?,?)

Thank you very much.

 

[Erel]

The question is, if this combination is reliable enough.

The answer is no. It will only work if there is a single client.

Can jRDC handle multiple queries, something like:

No.

You need to create a stored procedure and then call it with ExecuteQuery: http://stackoverflow.com/questions/13151861/fetch-last-inserted-id-form-stored-procedure-in-mysql