SQLite Insert with ON CONFLICT

Targets macOS
1

Hi

Can anyone show me the syntax to use the ON CONFLICT clause on an SQL insert please (I’m going mad here).

I’ve tried

try
  db.ExecuteSQL("Insert into menuEntries " _
  + "(menuId, Id) " _
  + "values(" + str(menuId) _
  + ", '" + me.CellTextAt(i, 0) _
  + "') on conflict(menuId, Id) ignore")
  
Catch error as DatabaseException
  abend("Insert into menu from contextual update in recipe list", error)
end try

That code returned an error near Ignore.

I’ve tried it with and without (menuId, id), which is the syntax to make UPSERT work. Both options failed.

The SQLite documentation says for Inserts and Updates replace ON CONFLICT with OR. Doing that resulted in an error near OR.

I’m running Xojo 2025 R2.1.

I’m bringing a program up to date and converting it to API2. In the old version I tried reading the row first and if it wasn’t there did an insert. ON CONFLICT should be a bit more elegant and save an SQL statement.

Help, please

Jack

2

I’ve found the answer.

The action keyword is not Ignore … it’s Do Nothing.

The syntax is

try
  db.ExecuteSQL("Insert into menuEntries " _
  + "(menuId, Id) " _
  + "values(" + str(menuId) _
  + ", '" + me.CellTextAt(i, 0) _
  + "') on conflict do nothing")
  
Catch error as DatabaseException
  abend("Insert into menu from contextual update in recipe list", error)
end try
4

It’s a global method I wrote to handle SQL failing. It displays the error, issues a Rollback and terminates the program.