ExecuteSQL to create trigger

I am trying to create a database called Mouse with a table called Help that is fairly simple: it has 5 fields (columns)
ID; CreateGMP; ModifyLocal; winTitle; ContentHTML

I want to create a Trigger that will Update the ModifyLocal column with the current datetime when a record is modified

Here is the code that I am using to create this database

[code]Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem(“Mouse.sqlite”, FolderItem.PathModes.Native)

Try
db.CreateDatabase
Catch Error As IOException
MessageBox("The database file could not be created: " + Error.Message)
End Try

db.ExecuteSQL(“CREATE TABLE IF NOT EXISTS” + DQ + “Help” + DQ + “(ID Integer Not NULL PRIMARY KEY, CreateGMT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ModifyLocal DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP,‘localtime’)), WinTitle TEXT, ContentHTML Text NOT NULL DEFAULT ‘NewContent’)”)

db.ExecuteSQL("?CREATE TRIGGER when_change AFTER UPDATE ON Help BEGIN UPDATE Help SET ModifyLocal = datetime(CURRENT_TIMESTAMP,‘localtime’) WHERE ID = NEW.id; END;")[/code]

I can follow the code in the debugger and it works fine until I get to the db.ExecuteSQL("?CREATE TRIGGER… line.

The database gets created.
The Table Help gets created.
But when I get to ExecuteSQL the line to create the Trigger, I get a database exception error.


I can open the database that is created in another application (I am using DB Browser for SQLite) that deals with SQLite and manually execute the code (SQL command):

CREATE TRIGGER when_change AFTER UPDATE ON Help BEGIN UPDATE Help SET ModifyLocal = datetime(CURRENT_TIMESTAMP,'localtime') WHERE ID = NEW.id; END;

That is accepted without complaint and it works in the way that I want.

But somehow when I try to execute this command within Xojo using the ExecuteSQL command, it fails.

Can someone explain this to me? Thanks for any help.

Which error? :slight_smile:

Without more context, I can only guess.: It seems you cannot run (all) DDL in an prepared statements, but 2019r2 is always using one for ExecuteSQL. This seems to be fixed by <https://xojo.com/issue/57974> for 2019r2.1

CREATE TRIGGER when_change AFTER UPDATE ON Help BEGIN UPDATE Help SET ModifyLocal = datetime(CURRENT_TIMESTAMP,'localtime') WHERE ID = NEW.id; END;

What’s the purpose of this “End;”? Try without it. And (always) check the errormessage of the exception, it’ll probably say something along the lines of “can’t execute multiple commands in a prepared statement”. This is fixed in the FB mentioned by Tobias, but if you just omit the “End;” you shouldn’t run into this issue…

Sorry for the delay getting back to this issue. Computer context is osMac Mojave & Xojo 2019 R2

Error Message: An exception of class DatabaseException was not handled. The application must shut down.
Exception Message: near “Create”: syntax error Exception Error Number 1

As far as End; is concerned I have tried

…WHERE ID = NEW.id; END;")
…WHERE ID = NEW.id;")
…WHERE ID = NEW.id")

And since the purpose of the End is, I have always assumed, to close out the BEGIN that occurs a little earlier so I tried this getting rid of the BEGIN as well.

…ON Help BEGIN UPDATE Help SET ModifyLocal = datetime(CURRENT_TIMESTAMP,'localtime') WHERE ID = NEW.id")
…ON Help UPDATE Help SET ModifyLocal = datetime(CURRENT_TIMESTAMP,'localtime') WHERE ID = NEW.id")

All to no avail.

Now I am using Xojo 2019r2. Somebody mentioned 2019r2.1 which intrigued me, but that does not seem to be available at the moment.

According to Docs your syntax seem to be correct.

I cannot talk about unreleased versions but the Feedback ticket I mentioned, indicates a fix for a version with this name. Yes, it is not yet released.

When testing with your query, it seems there is a bug in the API2 framework. As a workaround, just use the old db.SQLExecute method. I tested your query with old and new framework, and in both cases the error appears. I does not with the same queries on the sqlite3 command line interface. I’m out of ideas, but not very experienced with SQLite.

Test-Code:

[code]Dim db As New SQLiteDatabase
db.Connect

db.ExecuteSQL(“CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, modified DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP,‘localtime’)));”)

Try
db.ExecuteSQL(“?CREATE TRIGGER test_change AFTER UPDATE ON test BEGIN UPDATE test SET modified = datetime(CURRENT_TIMESTAMP,‘localtime’) WHERE id = NEW.id; END;”)
Catch e As DatabaseException
//raises DatabaseException: “near “?CREATE”: syntax error”
MessageBox e.Message
End Try

db.SQLExecute(“?CREATE TRIGGER test_change AFTER UPDATE ON test BEGIN UPDATE test SET modified = datetime(CURRENT_TIMESTAMP,‘localtime’) WHERE id = NEW.id; END;”)
If db.error Then MessageBox db.errormessage
[/code]

Thanks for the replies and to Tobias for taking the time to independently test this code.
As best I can tell, you cannot create a Trigger for a Table using the Xojo (Xojo 2019r2) command:
ExecuteSQL(“CREATE TRIGGER…”)

I have tried creating triggers of various types and have always hit an error.

If anyone has successfully created a Trigger using ExecuteSQL in Xojo 2019r2 and beyond, I would be interested in seeing the code that made this work.

Ah, that’s odd. The SQL itself is perfectly valid (I somehow missed that of course the “End;” is necessary), looks like Xojo still somehow alters the SQL before it hands it down to the db engine. You may want to try the MBS plugin to talk to SQLite if you’re desperate enough.

<https://xojo.com/issue/58324>

The SQL statement is perfectly valid, what’s not valid is that you have some extra encoding in front of “CREATE TRIGGER” (as can be seen through a hex editor). Try deleting and retyping “CREATE”

Oh Lord. Looks like that little gremlin made it all the way into the FR and finally on Robin’s machine as he was able to reproduce the problem.

select all
right click
clean invisible ascii characters
:stuck_out_tongue:

lol. So at least 4 pople were happily copy & pasting the problem around while createing and validating a testcase. Bloody Gremlins!
Not the first time I was hit by such a stupid problem, but obviously not able to learn :wink: Sorry for the noise, I promise to use my keyboard when I’m creating a testcase next time.
Thanks William!

WOW!

First of all, this is a great community and I am very grateful. For Yu who finally identified the problem and for the others who “played with it” and managed to copy the gremlin into their code testing. I would have never figured this out. I would have only fixed things “by mistake” if and when I happened to retype from scratch. Instead I copied the invisible gremlin all over my testing code trying to figure out the problem. When I copied the line of code to try out in DB Browser for SQLite, it is worked fine because I grabbed the text starting with the word CREATE – just missing the gremlin lurking in front of the word.

The exception that Xojo threw did say that the syntax error was in the vicinity of CREATE. That should have been a clue. But I looked there and could see nothing.

To my knowledge, I have never run into this issue before. The bug cost me about 7 hours of time screwing around before I came to the forum and eventually got the help that puts this to rest.

The gremlin is U+FEFF ( in UTF-8 it is more commonly known as 0xEF) which is a ZERO WIDTH NO-BREAK SPACE. The common use for U+FEFF is apparently as Byte order mark or BOM. Since it is zero-width, it is generally invisible. However, when I pasted the problematic line of code into BBEdit using a monospace font, it is visible but just looks like any other space. But in the Xojo code editor, it is invisible and in the Xojo forum it is invisible.

But I presume that others have been bitten by this. Palardy pointed out the feature to Clean Invisible Ascii Characters which appears with a Right-Click if you have put code on the Clipboard as he describes. I had never noticed this option and would not have understood what it was for if I had. But now it is my favorite tool. :slight_smile:

I assume that I somehow grabbed this gremlin when I copied code from the web to manipulate further in Xojo. I had had some legitimate problems with CREATE TRIGGER that I was trying to figure out. While eventually I got my CREATE TRIGGER code to be properly formatted I had, in the meantime, sucked in this gremlin so it was never going to work.

Playing with Clean Invisible Ascii Characters I can see that I have managed to copy other invisible ascii characters into my code in the past, but the ones I found are lurking in my comments where they do no immediate harm. They were generally the HTML non-breaking space. (\x{A0}) which at least you can see looking like a space.

Live and learn. And thanks again to Tobias, William, Norman…

Just to learn: do you ExecuteSQL TRIGGER once for all ? I mean: when you execute a trigger it will be ‘stored/saved’ into SQL database ? Or you need to ExecuteSQL the trigger every time ?

The ZERO WIDTH NO-BREAK SPACE is actually 0xFE 0xBB 0xBF in UTF-8.

The ZERO WIDTH NO-BREAK SPACE is actually 0xFE 0xBB 0xBF in UTF-8.

Are you sure? I see references, where it is 0xEF, not 0xFE

(Unicode Character 'ZERO WIDTH NO-BREAK SPACE' (U+FEFF))

People might also check out the Wikipedia entry: Word joiner which is a different “character” and apparently is preferred in many instances where the ZERO WIDTH NO-BREAK SPACE was used in the past. That article says that (Character U+FEFF) should be used as a BOM (Byte Order Mark) at the start of a file. I confess most of this stuff is just beyond my comfort zone.

Word joiner - Wikipedia

run TRIGGER once on the SQLiteManager for me until you make changes to the TRIGER then DROP it first and then run the new TRIGGER

Is it necessary to DROP it first ?
Do you think that the lack of DROP first could influence the WAL if enabled (locking the database) ?

Ah yes, sorry for the typo, it’s 0xEFBBBF (a three-byte character). See:

https://www.utf8-chartable.de/unicode-utf8-table.pl

and look for the page with code points U+FE70 to U+1026F. The ZERO WIDTH NO-BREAK SPACE is about half way down.

In fact your reference shows that too.

The BOM “character” is needed at the start of a UTF-16 string to indicate whether the string is big- or little-endian, since UTF-16 is made from 16-bit items. UTF-8 doesn’t actually need a BOM since it’s made from 8-bit items (bytes).

See also: