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

H

h725

Active Member
Licensed User
Longtime User
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)
 
Last edited:
Sort by date Sort by votes

jsanchezc

Member
Licensed User
Longtime User
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 (?,?,?)
 
Upvote 0
H

h725

Active Member
Licensed User
Longtime User
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).)
 
Last edited:
Upvote 0
S

sorex

Expert
Licensed User
Longtime User
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.
 
Upvote 0
H

h725

Active Member
Licensed User
Longtime User
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;
 
Upvote 0
H

h725

Active Member
Licensed User
Longtime User
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:
B4X: 
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.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
Upvote 0
You must log in or register to reply here.

Similar Threads

J
  • Question
Android Question jRDC2 :An error on sql.insert command
Replies
7
Views
2K
OliverA
M
  • Question
Android Question SQLite ID INTEGER PRIMARY KEY AUTOINCREMENT Insert
Replies
2
Views
6K
Mahares
M
R
  • Question
Android Question SQLite db with Primary key as an autoincrement
Replies
19
Views
8K
Richard S
R
K
  • Question
Android Question Jrdc with ucanaccess not committing inserts (Solved)
2 3
Replies
45
Views
11K
Ryan Pigeon
  • Locked
  • Article
B4J Library [B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)
5 6 7
Replies
138
Views
165K
Erel
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…