[Gambas-user] gb3 database error handling

[Benoît Minisini]

> 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').

I don't know why. The gb.db.mysql driver set the connection error code just 
after the query has been run by using a specific API of the mysql library.

> 
> 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.

You have no Error.Backtrace because you are not inside an error handler, but 
inside normal code. Use System.Backtrace instead.

> 
> 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?

Gambas has always used "-1" error code for error raised by components and for 
Error.Raise() method.

> 
> 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.

This message comes from mysql. So I guess I can't fix it.

Regards,

-- 
Benoît Minisini