B4J Library Alpha/Beta testing if jRDC2 with returning auto-generated key values after INSERT

I've had this mod sitting on the sidelines since last fall. Looks like there is a need for this as shown in this post: https://www.b4x.com/android/forum/threads/rdc2-select-last_insert_id.126310/

In order to retrieve the auto generated key values for an INSERT statement, I've added the ExecuteInsert method to the DBRequestManager shared module. Going with the sample code posted by @aeric in the above mentioned thread

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
End Sub

to retrieve the auto-generated key values, the new method can be used

Sub AddBook (Name As String)
    Dim cmd As DBCommand = CreateCommand("insert_book", Array(Name))
    Dim j As HttpJob = CreateRequest.ExecuteInsert(Array(cmd), Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted successfully!")
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        'Let's print out the returned auto-generated key(s)
        req.PrintTable(res)
    End If
    j.Release
End Sub

Notes:
1) By default, the JDBC returned value is a BIGINT. The jRDC2 code uses getLong to retrieve the value. So for very large DB's with auto-increment values that are greater than Java's Long value, this will fail. If that is an issue, the code could be modified to return a BIGINT value. Note that your code on the client file will fail unless you also provide BIGINT support in your client. See post#2 for changes
2) Most DB's (at least the ones I have access to) only return one auto-generated key value. The default column name for this value seems to be GENERATED_KEY
3) The above code just prints the complete DBResults out in order to show what is returned. You can use that information to actually create the retrieval code of the values
4) Don't forget that this requires an update (attached) to your DBRequestManager.bas

 

[OliverA]

After some more testing, I realized B4XSerializator cannot handle BigInteger, so I decided to convert the auto-increment value to a string. It is then up to the client to assign this string to the properly sized integer type. For small DB's, that can just be a long. For larger DB's, B4A/B4J applications can include @agraham's excellent BigNumbers library (https://www.b4x.com/android/forum/threads/bignumbers-library.9540/#content) to handle the auto-increment values. The change/update will be attached to post #1 as jRDCv2_mod.2021.01.11.02a_published.zip

 

[Chris2]

I'm a bit late to this particular party but useful work again @OliverA, many thanks.

Correct me if I'm wrong, but I think the example code has a couple of errors though:

Sub AddBook (Name As String)
    Dim req As DBRequestManager = CreateRequest      '<--------- we need a req to use it for the insert & result'

    Dim cmd As DBCommand = CreateCommand("insert_book", Array(Name))
    Dim j As HttpJob = req.ExecuteInsert(cmd, Null)     'use the req already declared. Just a single cmd, not Array(cmd)'
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted successfully!")
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        'Let's print out the returned auto-generated key(s)
        req.PrintTable(res)
    End If
    j.Release
End Sub

 

[OliverA]

Correct me if I'm wrong, but I think the example code has a couple of errors though:

Actually, the one mistake I made is using array(cmd) instead of just cmd as the first parameter in the ExecuteInsert method. But otherwise,

Dim j As HttpJob = CreateRequest.ExecuteInsert(cmd, Null)

would be correct and you would not need to have a separate request declaration of

Dim req As DBRequestManager = CreateRequest      '<--------- we need a req to use it for the insert & result'
'The above is not really needed if you go with CreateRequest.[any method exposed by DBRequestManager] calling convention

as you have it. But if it makes your code cleaner to read, then there is also nothing wrong with the way you implement this.

Thank you for debugging my ancient code sample!!!!