[Gambas-user] gb3 database error handling

[Bruce Bruen]

Hi Benoît,

I don't raise these as bugs because they are only annoyances and don't
(specifically) stop things from working.

I have this code (which works fine when things are fine) but has a few
quirks when a database error occurs.  I made a simple test that just
calls this and tries to add the same record to the database twice.


Public Function AddDB() As Boolean

  Dim rwcursor As Result

  DBMod.Conn.Begin		' DBMod.Conn is just the current open database connection
  rwcursor = DBMod.Conn.Create($tname)
  Marshall(rwcursor, $colmap)   ' This just moves the current values into the cursor
  Try rwcursor.Update
  If Error Then
    Debug db.Error, Error.code, Error.Text, IsNull(Error.Backtrace)
    Print Subst("&1\n[phDB9_gb3: Base persistor database error (&2)] &3\n&4\n&1\n\n", String$(60, "="), Error.Code, Error.Text, Error.Where)
    DBMod.Conn.Rollback
    Return False
  Endif

  DBMod.Conn.Commit
  Return True

Catch
  Print Subst("&1\n[phDB9_gb3: Base persistor general error trap (&2)] &3\n&4", String$(60, "="), Error.Code, Error.Text, Error.Backtrace.Join("\n"))

End

As I said, when the insert works all is fine, but on the duplicate key
insert, I get the following output from the two trace lines in the "If
Error" block


Persistor.AddDB.404: 0  -1      Cannot create record: Duplicate entry '' for key 'PRIMARY'      True
============================================================
[phDB9_gb3: Base persistor database error (-1)] Cannot create record: Duplicate entry '' for key 'PRIMARY'
Persistor.AddDB.402
============================================================


In the first line we see that db.Error=0 instead of the driver error
code, I would have expected 1022 (mysql error: "Error: 1022 SQLSTATE:
23000 (ER_DUP_KEY) Message: Can't write; duplicate key in table '%s'). 
 
Secondly, and this is more of an issue, the Error.Backtrace is null (as
evidenced by "True" at the end of the line).  So I can't get a full
backtrace to the caller that is trying the duplicate insert.

Thirdly, the gambas error code is -1?  I thought that code was for
errors raised within the project i.e. Error.Raise(Message) I have used
that code (-1) to discern local vs system errors previously, is this no
longer safe?

Finally, the error message itself seems to be not populated properly,
"Duplicate entry '' for key.."  In my test case the key is multicolumn,
so this may be the reason.  However, "Cannot create record: Duplicate
key for '<actual key name>' on table '<table name>'" would (IMO) be a
better message.

regards
Bruce