B4J Question rdc2 & SELECT LAST_INSERT_ID()

madru

Active Member
Licensed User
Longtime User
Hi,

how can we get the last inserted ID from MariaDB/MySQL ?

sending a query one by one does not work for LAST_INSERT_ID
e.g.

B4X:
request: INSERT INTO books (title) VALUES ('test');
followed by:
B4X:
request: SELECT LAST_INSERT_ID();
will return always 0 (zero)


request everything at once returns the correct ROW number:
B4X:
request: INSERT INTO books (title) VALUES ('test');SELECT LAST_INSERT_ID();

question is how to write the SQL statement in config.properties?

B4X:
sql.insert_books = INSERT INTO books (title) VALUES (?);SELECT LAST_INSERT_ID();
 

MarcoRome

Expert
Licensed User
Longtime User
If you have php file you can use mysqli_insert_id. In this mode the return from Server is last_insert_id()

File PHP
PHP:
.....
            case "2":           
                $query = "INSERT INTO books (title) VALUES ('test')";
            
                $sth = mysqli_query($conn_DB, $query);


                if (mysqli_errno($conn_DB)) {
                    header("HTTP/1.1 500 Internal Server Error");
                    echo $query.'n';
                    echo mysqli_error($conn_DB);
                }
                else
                {                                       
                        $rows = mysqli_insert_id($conn_DB);
                        $gzdata = json_encode($rows);
                        echo $gzdata;  
                }
                 break;
                 .....

Code B4X:
B4X:
....
        Dim VERIFICAPASSWORD As HttpJob
        VERIFICAPASSWORD.Initialize("", Me)
        VERIFICAPASSWORD.Download2(linkwebsitefilephp, Array As String(......))
        Wait For (VERIFICAPASSWORD) JobDone(VERIFICAPASSWORD As HttpJob)
        If VERIFICAPASSWORD.Success Then
            Dim res As String
            res = VERIFICAPASSWORD.GetString
           Log("LastRow ID: " & res)
        Else
            Log("Error: " & VERIFICAPASSWORD.ErrorMessage)
        End If
        VERIFICAPASSWORD.Release
....
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
If you want to try to use my User Login + MySQL B4J API (API Key + Token) example,
check Register sub inside UserHandler.
B4X:
strSQL = $"INSERT INTO tbl_users 
(user_email, 
user_name, 
user_hash, 
user_salt, 
user_api_key, 
user_activation_code, 
user_activation_flag) 
VALUES (?, ?, ?, ?, ?, ?, ?)"$
con.ExecNonQuery2(strSQL, Array As String(eml, name, hash, salt, key, code, flag)) 
Dim user_id As Int = con.ExecQuerySingleResult("SELECT LAST_INSERT_ID()") 
msg_text = "new user" 
WriteUserLog("user/register", "success", msg_text, user_id)
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
config (config.properties):
sql.insert_book=INSERT INTO books (title) VALUES (?);
sql.select_last_id=SELECT IFNULL(LAST_INSERT_ID(), 0) AS id;

B4A client
B4X:
Sub Activity_Create(FirstTime As Boolean)
    AddBook("Test")
End Sub

Sub AddBook (Name As String)
    Dim cmd As DBCommand = CreateCommand("insert_book", Array(Name))
    Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted successfully!")
    End If
    j.Release
    GetLastId
End Sub

Sub GetLastId
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("select_last_id", Null)
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        'work with result
        'req.PrintTable(res)
        ' just get first row and first column
        'Dim row() As Object = res.Rows.Get(0)
        'Log(row(0))
        ' or loop for any row
        For Each row() As Object In res.Rows
            Log(row(res.Columns.Get("id")))
        Next
    Else
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release
End Sub
 
Last edited:
Upvote 0

josejad

Expert
Licensed User
Longtime User
Maybe this could be helpful?

 
Upvote 0

udg

Expert
Licensed User
Longtime User
What about a stored procedure that executes one after the other the INSERT statement and the SELECT for last_insert_id() ?
Same problem when multiple clients insert data at the same time or we can count on an internal locking mechanism that assures each one will receive the correct last_id?
 
Upvote 0

madru

Active Member
Licensed User
Longtime User
mmh, interesting.
Thought that LAST_INSERT_ID is Thread safe and can handle multiple e.g. inserts even via the same connection

will try
B4X:
SELECT my_row_with_prim_key FROM Books ORDER BY BookId DESC LIMIT 1;
to get the primary key value
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
JDBC can actually return the values of auto-increment keys. I've done a test modification of jRDC2, but I just have not published it yet (it requires a modified DBRequestManager). I'll post my modifications in another thread and post a link here. Any questions/issues with the modification can then be addressed in the new thread.
 
Upvote 0

j_o_h_n

Active Member
Licensed User
Note that if multiple clients insert data at the same time then you might receive the wrong last id.
Would you be guaranteed to get the right id with the code below because it's the same connection?
I'm fairly certain the answer is yes but I'm using mysql for the first time.
B4X:
Dim sql1 As SQL = Main.pool.GetConnection
sql1.ExecNonQuery2("INSERT INTO etc", Array As Object(etc))
Dim lastID As Int = sql1.ExecQuerySingleResult("SELECT LAST_INSERT_ID()")
sql1.Close
 
Upvote 0
Top