Android Question To catch DB Errors within DB Transactions

[sivakrith]

Hello,
Using B4A 10.2 with DBUtils v 2.09
To update a table

Dim l as list
l.initialize
dim m as map
m.initialize
m.put("field1", "value1")
m.put("field2", "value2")
l.add(m)

sql.begintransaction
dbutils.insertmaps(sql, "table1", l)
sql.transactionsuccessful
sql.endtransaction

This works fine, till there is no error returned by database. Say if some error happens (due to constraint or FK constraint), database might return an error. I want to catch that error and proceed accordingly. How to implement that in B4A ?

Happiness Always
BKR Sivaprakash

 

[Erel]

Using B4A 10.2.

Not good. Always use the latest version.

Remove the transaction code as DBUtils.InsertMaps already creates a transaction.

You can catch errors with Try / Catch.

 

[sivakrith]

Thanks Erel. I'vent noticed the new release. I'll update soon. I'll remove the transaction code as well.

 

[sivakrith]

Updated to v10.5

Could not catch DBErrors while using InsertMap. My code is

        Dim lb_saved = False As Boolean
        Try
            Dim ls_result As String = DBUtils.InsertMaps(Main.gs_sql, "co_mobile_registration", co_mobile_registration)
            Log("ls_result : " & ls_result)
            lb_saved = True
        Catch
            Log("Exception : " & LastException)
            lb_saved = False
            MsgboxAsync("Error While Saving Data. Retry...","Error")
        End Try
       
        If lb_saved = True Then
            ToastMessageShow("Registration Information saved",False)
            is_fullname.Text = ""
            is_mobilenumber.Text = ""
            is_password.Text = ""
            is_confirmpassword.Text = ""
       
            StartActivity(login_choice)
            Activity.Finish
        Else
            is_fullname.RequestFocus
        End If

Code executed [ as in the log]
InsertMaps (first query out of 1): INSERT INTO [co_mobile_registration] ([mobileregistrationpk], [fullname], [mobilenumber], [password], [regstatus], [sentstatus], [updateon]) VALUES (?, ?, ?, ?, ?, ?, ?)

And returns
(SQLiteConstraintException) android.database.sqlite.SQLiteConstraintException: column mobileregistrationpk is not unique (code 19)

I expect the cursor to move to field fullname, but it displays 'Registration Information saved' message and closes that activity, as if no error has happened.
Am I doing anything wrong in capturing the DB Error ?

Thanks.

 

[Erel]

You are correct. The problem is in InsertMaps. It catches the error internally, rollbacks the transaction and doesn't provide a simple way to check the result.

You have several options:

1. Don't use InsertMaps.
2. Modify the source code (b4xlibs = zips) and return a Boolean value from this sub that will tell you whether it succeeded or not.
3. Check the table size with SQL.ExecQuerySingleResult before and after calling InsertMaps.

 

[sivakrith]

Thanks Erel,

I feel insertmaps is better than manually constructing sql queries.
Modifying the source, well I need to learn that [ I'm new to B4X].
Can check the table. hmmm..

Well, is there any idea to implement this in the next version of InsertMaps?

 

[Erel]

It was added in v2.10: https://www.b4x.com/android/forum/threads/b4x-dbutils-2.81280

 

[sivakrith]

Wow. Superfast Erel. I didn't expect such an enhancement so soon...
Thanks Erel.