B4J Question [Solved] MySql INSERT IGNORE

udg

Expert
Licensed User
Longtime User
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:
B4X: 
   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.
 
Sort by date Sort by votes
M

Mahares

Expert
Licensed User
Longtime User
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()
 
Upvote 0

udg

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

udg

Expert
Licensed User
Longtime User
I solved with the following code (simplified for the purpose of showing here a solution to the original problem):
B4X: 
    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.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
udg said:
solved with the following code
Click to expand...
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:
B4X: 
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
 
Upvote 0

udg

Expert
Licensed User
Longtime User
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
B4X: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
B4J Question [SOLVED] SQL questions re: INSERT IGNORE, and EndTransaction
Replies
3
Views
3K
HowardTheDuck
  • Question
B4J Question B4Xtable sql.exception error SOLVED
Replies
5
Views
1K
ilan
  • Question
B4J Question error WriteB4XObject
Replies
2
Views
1K
Tayfur
  • Question
B4J Question AddNonQueryToBatch
Replies
2
Views
2K
johnB
M
  • Question
B4J Question sql.AddNonQueryToBatch Error
Replies
6
Views
2K
inakigarm
I
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…