Catching UNIQUE error ?

Databases
1

In the LR code below, how do I catch this reported error:
Error: UNIQUE constraint failed: Team.Name

Code example from SQLiteDataBase:

Var db As New SQLiteDatabase db.DatabaseFile = New FolderItem("Employees.sqlite") Try db.Connect db.SQLExecute("BEGIN TRANSACTION;") db.SQLExecute ("INSERT INTO Employees (Name,Job,YearJoined) VALUES " _ + "('Dr.Strangelove','Advisor',1962);") db.CommitTransaction Catch error As DatabaseException MessageBox("Error: " + error.Message) db.RollbackTransaction End Try

2

You would need to look into the error.Message string. For example with an IndexOf.

3

Thank you Sasha for your answer, but I do not think so. I do not understand what your answer is for. I declared the Name field as UNIQUE. That is all I have to do.

I forget to say that the error was done in purpose to verify the Exception will be trapped and this is not the case.

Read the text in Bold. This is what error.Message returns.

In other words, there is no need to do that.

Read there: http://documentation.xojo.com/api/databases/sqlitedatabase.html

and scroll until SQLite error codes:
19 Constraint failed

in the list of errors.

Try Catch etc. Does not trap the error, only Exceptions. Aas far as I read, no more errors in API2, only Exceptions. :frowning:

4

Is this error really not causing a DatabaseException? If so, i would report it.

5 
if db.errorcode=19 then
// deal with unique error
end if
6

The error appears in the debugger (ErrorCode and ErrorMessage), but cannot be reached in the Code Editor by If db.ErrorCode or db.ErrorMessage (they do not exist there).

Nobody use the UNIQUE CONSTRAINT in its data base ?
Strange.

7

seems you’re mixing api1 and 2
the try…catch is for api 2
and sqlexecute is api1
you should use executesql ?
https://documentation.xojo.com/api/databases/database.html#database-executesql

8

Thank you Jean-Yves.

Where have you seen sqlexecute ?

Oh, I see… Ask Paul. I do nothing there excepted taking the example from the LR.

But now, I will explorate a bit more…

9

[quote=476615:@Jean-Yves Pochez]seems you’re mixing api1 and 2
the try…catch is for api 2
and sqlexecute is api1
you should use executesql ?
https://documentation.xojo.com/api/databases/database.html#database-executesql[/quote]

Goody catch! That may explain why he’s not getting a DatabaseException :slight_smile:

10

UNFORTUNATELY, the real code (based on the LR, but from the web) does not shows that error !

The case is still pending.

A global search for sqlexecute confirm that :frowning:

11

Sometimes ago I wanted to ask Xojoto remove API 1 from 2020r1 (or r2 or…). It will never been updated (as they said), so releasing it alone, removing it from the “current” version will lead to less confusion and lower the footprint (for download, in RAM, etc.).

But (read another conversation), that thing is not possible until a full “API2” rewrite, so we have to live with this kind of possible error (and searching all the time “how do I write this in API 2 ???” until the human memory will recall the new way, in some months).

My fault, I’d better stay far away from API 2… :frowning:

12

yes I would like to have a pref to choose from api 1 or api 2 or both autocomplete, and I will install r3.x immediately.

13

The Web framework has not been updated to API 2 yet. If you are trying this in a web project, you will need to stick with all API 1 functions.
Actually, API 2 database functions will work in a Web project. I was thinking about UI controls. Sorry for the noise.

14

Jay, it is a desktop project. I talked to web only for the Language Reference docs… (https://documentation.xojo.com/api/databases/sqlitedatabase.html)

talking about that page: https://documentation.xojo.com/api/databases/sqlitedatabase.html

the examples uses three different TABLE names (Foo, Team and Employees) and two .sqlite files (MyDB.sqlite and Employees.sqlite).

Nice beta, but really bad for a so old page (This page was last edited on 4 February 2020, at 14:43.)

Just in case one wonder: I am capable to do the same, eventually worst, but I take the time to (try) to remove all glitches before release.

Yes, I am a bit angry. I am so angry that I will read comics right now. What about The Amazing Spider-Man comic strips from 1998 to today ?

15

The SQLExecute was changed to ExecuteSQL on that doc page on 4 Feb 2020. So if you copied the above code from that page, it had to have been prior to that date.

I don’t understand your complaint about this, though I find no Team table mentioned anywhere.

16

The question here is about UNIQUE and it does not works.

17

Emile, here is a project that demonstrates what you’re trying to accomplish, using what Sascha hinted at in extracting the relevant information from the error message (IndexOf is not really useful in this case - I used Split).

Note that when running in the IDE (debug mode), any exceptions will cause it to break to the debugger. You have to click the Resume button to see how it will work/look in a built app. This may be why you think it’s not catching this error. Or you can use “#Pragma BreakOnExceptions False” like I did in the third button.

18

Hi Jay,
I will check your project in a minute.

I downloaded Xojo 2019r3.1 (130 minutes…) and run it / load that simple project (59KB).

Here is what I finally wrote:

[code]Try
gSQLite_db.ExecuteSQL(“BEGIN TRANSACTION”)
gSQLite_db.ExecuteSQL("INSERT INTO Team (Name, Job) VALUES " _

  • “(‘Geoff Emerick’,‘Sound Engineer’)”)
    // ********** ********** ********** **********
    // If gSQLite_db.ErrorCode = 19 Then
    // MsgBox “A UNIQUE ERROR OCCURED” + EndOfLine + EndOfLine +_
    // gSQLite_db.ErrorMessage
    //
    // Return
    // End If

gSQLite_db.CommitTransaction
Catch error As DatabaseException
If error.ErrorNumber = 19 Then
MessageBox “An error occured” + EndOfLine + EndOfLine +_
“This Row already exists.” + EndOfLine + EndOfLine +_
“UNIQUE CONSTRAINT failed.”
End If

MessageBox("Error: " + error.Message)
gSQLite_db.RollbackTransaction
End Try[/code]
The code in comments looks like API 1 code, but is not working.

The debugger line with the red bug is the INSERT line…

I went there after fooling around in the documentation and I discovered that error.Message was not alone: error.ErrorNumber exists and some other too.

19

@Jay

At first run, your example looks (behave) like another, API 1, project where I implemented this in a batch import button: I added a counter and after 5 duplicates, I report them and stop the import process (around 600 Records).

Now, I will look at your code.

20

Compared (more or less) to my code, the important line is:

#Pragma BreakOnExceptions False

and the Split line (I took that information differently in API 1 code).

In short, this API 2 code is far complex than the API 1 code I wrote last week.

Am I wrong to test running in the IDE ?
(until the project if the project is far from completion ?)
(Error above: I cannot compile, so I cannot test differently.)

Thank you.