B4J Question [Solved] MySql INSERT IGNORE

[udg]

Hi all,
I'd like to use the INSERT IGNORE statement format with a table having a UNIQUE index on one of its fields.
Currently my code is something like:

   Dim sql1 As SQL = pool.GetConnection
    sql1.BeginTransaction
    Try
        sql1.ExecNonQuery2($"INSERT IGNORE INTO xxxx (id,a,b,c,d,e)
                            VALUES (?,?,?,?,?,?);"$, _
                            Array As Object(Null, MData.Get("a"), MData.Get("b"),MData.Get("c"), _ 
                            MData.Get("d"), MData.Get("e")))
        risp.Put("err",0)
        sql1.TransactionSuccessful
    Catch
        risp.Put("err",-101)
        risp.Put("errmsg", LastException.Message)
        sql1.Rollback
    End Try
    sql1.Close

where risp is a map that I use to give some feedback to the caller.

Now, when duplicating the value for the a-field, correctly the DB ignores the attempt to insert the duplicate, but I'd like to put in risp("err") code 1062 to give a feedback about the attempted duplicate.
Should I use a second statementi like SHOW WARNINGS soon after the insert? If yes, how do I read the returned Level/Code/Message fields?

Another way could be not to use the IGNORE clause and let the transaction fail, but I'd like to learn how to use the IGNORE clause anyway. TIA.

 

[Mahares]

I am not too familiar with MySQL, but in SQLite, you can check with a SELECT statement for last_insert_rowid() before and after the INSERT OR IGNORE statement is run and compare the two. I think the equivalent in MySQL is: LAST_INSERT_ID()

 

[udg]

Thank you, @Mahares
I confirm that MySql uses "SELECT LAST_INSERT_ID();" to let you know about the ID assigned to the last insert on a given session.
I could do like you suggested, but that would be three operations.
Insert Ignore followed by Show Warning would count for two operations.
Letting the regular Insert fail would be just one. Anyway my goal is to learn how to properly use Insert Ignore.

 

[udg]

I solved with the following code (simplified for the purpose of showing here a solution to the original problem):

    Dim sql1 As SQL = pool.GetConnection
    sql1.BeginTransaction
    Try
        sql1.ExecNonQuery2($"INSERT IGNORE INTO xxxx (id,a,b,c,d,e)
                            VALUES (?,?,?,?,?,?);"$, _
                            Array As Object(Null, MData.Get("a"), MData.Get("b"),MData.Get("c"), _ 
                            MData.Get("d"), MData.Get("e")))
        Dim errcode As Int = 0
        Dim errmsg As String = ""
        Dim Cursor As ResultSet
        Cursor = sql1.ExecQuery("SHOW WARNINGS LIMIT 1;")     'parameters are sanitized so we could expect just 1 warning
        Do While Cursor.NextRow
            errcode = Cursor.GetInt("Code")                                       '1062 for duplicate key
            errmsg = Cursor.GetString("Message")
        Loop
        risp.Put("err", errcode)
        risp.Put("errmsg", errmsg)
        sql1.TransactionSuccessful
    Catch
        risp.Put("err",-101)
        risp.Put("errmsg", LastException.Message)
        sql1.Rollback
    End Try
    If Cursor.IsInitialized Then Cursor.Close
    sql1.Close

I didn't try yet with the alternative using the regular INSERT (w/o IGNORE clause) letting the statement fail. Anyway, the above works ok for my needs.

 

[Mahares]

solved with the following code

Glad you solved it. This is what I had in mind when I posted in post #2 which looks like it has the same number of operations as you have:

sql1.ExecNonQuery2($"INSERT IGNORE INTO xxxx (id,a,b,c,d,e)
                            VALUES (?,?,?,?,?,?);"$, _
                            Array As Object(Null, MData.Get("a"), MData.Get("b"),MData.Get("c"), _ 
                            MData.Get("d"), MData.Get("e")))                            
    Dim cursor As ResultSet = SQL1.ExecQuery("SELECT LAST_INSERT_ID() AS x FROM xxxx")
    If cursor.NextRow Then
        If cursor.GetLong("x") =0 Then
            Log($"No new rec was inserted as it would creates a duplicate"$)
        Else
            Log($"A new rec was inserted with rowid: ${ cursor.GetLong("x")}"$)
        End If
    End If
    cursor.Close

 

[udg]

Hi @Mahares , you're right; your solution needs two operations and not three as I wrongly stated. My confusion raised by your expression "last_insert_rowid() before and after" when I wrongly read it as "read last insert - do insert ignore - read last insert", which is not .
Your code can be simplified, avoiding Cursor, with a satemet like

Dim lastid As String = sql1.ExecQuerySingleResult("SELECT LAST_INSERT_ID();")

Note that lastid could be a long causing an implicit casting.

A general improvement could be an ExecNoQuery statement that returns a ResultSet which will be what is produced by Show Warnings.
So when we issue an Insert/Update/Delete/Create.. command we have a feedback in terms of errors and warnings in case we want/need to manage it.

Something continues to tell me to go the "standard" way. Issue a simple Ignore statement and let it fail in case of a duplicate.

Anyway, now I had a brief experience with INSERT IGNORE and SHOW WARNINGS so it wasn't wasted time.