B4J Question rdc2 & SELECT LAST_INSERT_ID()

[madru]

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.

request: INSERT INTO books (title) VALUES ('test');

followed by:

request: SELECT LAST_INSERT_ID();

will return always 0 (zero)


request everything at once returns the correct ROW number:

request: INSERT INTO books (title) VALUES ('test');SELECT LAST_INSERT_ID();

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

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

 

[aeric]

First query you need ExecuteNonQuery and second you need ExecuteQuery (or ExecQuerySingleResult) so I think you need call 2 rounds.

 

[MarcoRome]

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

File 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:

....
        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
....

 

[aeric]

If you want to try to use my User Login + MySQL B4J API (API Key + Token) example,
check Register sub inside UserHandler.

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)

 

[aeric]

sql.insert_book=INSERT INTO books (title) VALUES (?);
sql.select_last_id=SELECT IFNULL(LAST_INSERT_ID(), 0) AS id;

B4A client

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

 

[josejad]

Maybe this could be helpful?

jRDC2 Add MySql Master / Detail

Hi: Searching several threads in the forum, I've used this code to add a row in a "master" table, and then the "children rows" in a "detail" table. May be useful to someone. Improvements are welcome. Table Master: id (primary, autoincrement), field 1, field 2... Table Detail: id, master_id...

www.b4x.com

 

[madru]

Hi aeric,

you saved my day

 

[Erel]

Note that if multiple clients insert data at the same time then you might receive the wrong last id.

 

[udg]

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?

 

[Erel]

I think that a better solution is to avoid using LAST_INSERT_ID. Use the primary key of the last inserted item instead.

 

[KMatle]

What about Begin... EndTransaction? This should ensure the integrity.

 

[madru]

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

will try

SELECT my_row_with_prim_key FROM Books ORDER BY BookId DESC LIMIT 1;

to get the primary key value

 

[Erel]

Thought that LAST_INSERT_ID is Thread safe and can handle multiple e.g. inserts even via the same connection

That is of course true, however you cannot assume that two requests will be executed using the same connection.

 

[OliverA]

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.

 

[OliverA]

As promised: https://www.b4x.com/android/forum/t...uto-generated-key-values-after-insert.126407/

 

[j_o_h_n]

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.

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

 

[Erel]

You should never post questions in existing threads.

The answer is yes.

 

[j_o_h_n]

Thanks Erel, it must feel like herding cats sometimes!