Syntax error at UPDATE - works in SQLiteStudio, not in my code
(1) By Rocky (Rocky_Hu) on 2022-01-28 20:45:51 [link] [source]
my problem is, that I wrote an update, checked it in SQLiteStudio, it works fine. When I execute the same in my program, it throws a syntax error. ('near "FROM": syntax error')
There is a difference in the sqlite3.dlls, because SQLiteStudio uses a 64bit one, my code is a 32bit Windows application, so it uses a 32bit dll. I found already some little differences (e.g. parenthesis is accepted or not) earlier in the function of the two versions, but there was always a solution to avoid the problem. (Unfortunately I don't know any exact version number, but the 32bit version is digitally signed by Idera Inc. in 20. February 2021.)
But this time I have no idea, what can be the problem.
The original form of my UPDATE is:
UPDATE wRoutes SET tfFeedPt = fitt.tid FROM (SELECT wr.id AS wid, tf.id AS tid FROM wRoutes wr JOIN xyposFitt tf ON wr.posX = tf.posX AND wr.posY = tf.posY AND wr.drNum = tf.drNum AND wr.page = tf.page WHERE endpoint = "X") AS fitt WHERE wRoutes.id = fitt.wid
It's perferct in SQLiteStudio, but throws the mentioned exception from my code.
I searched in Google a lot, and found a tip which seemed to be promising - to use the WITH <SELECT> UPDATE form, so I tried this version too:
WITH fitt AS (SELECT wr.id AS wid, tf.id AS tid FROM wRoutes wr JOIN xyposFitt tf ON wr.posX = tf.posX AND wr.posY = tf.posY AND wr.drNum = tf.drNum AND wr.page = tf.page WHERE endpoint = "X") UPDATE wRoutes SET tfFeedPt = fitt.tid FROM fitt WHERE wRoutes.id = fitt.wid
It worked again in SQLiteStudio, but not in my code. I tried the table name aliases with and without using "AS". E.g. "FROM wRoutes wr" and "FROM wRoutes AS wr". The result is the same: SQLiteStudio OK, my code: 'near "FROM": syntax error'.
Could somebody tell, what is wrong with my UPDATE command?
Thank you in advance.
(2) By Michael A. Cleverly (cleverly) on 2022-01-28 21:47:27 in reply to 1 [link] [source]
Unfortunately I don't know any exact version number, but the 32bit version is digitally signed by Idera Inc. in 20. February 2021.
You could run:
in both your application, and in SQLiteStudio.
I imagine your application's DLL is older.
(5) By Rocky (Rocky_Hu) on 2022-01-28 23:12:13 in reply to 2 [link] [source]
The version of dll incorporated in Delphi was 220.127.116.11. (My SQLiteStudio uses 3.35.4) Meanwhile I got the info, that SQLite ver >= 3.33.0 is needed for the UPDATE...FROM syntax. I replaced the dll by the latest, and works now.
(3) By Larry Brasfield (larrybr) on 2022-01-28 22:55:21 in reply to 1 [link] [source]
There is nothing wrong with your "original form" which would produce a syntax error as long as all the columns named in it exist in the referenced table.
For convenience of others who may try to help you, here is a schema:
CREATE TABLE wRoutes(posX real, posY real, drNum int, page int, endpoint, id, tfFeedPt);
CREATE TABLE xyposFitt(posX real, posY real, drNum int, page int, wid);
There is a lingering possibility I have not covered, being too lazy to explore all the ways it could be causing you trouble. In this expression,
endpoint = "X"
, you are comparing a column, "endpoint", from one of the tables named in your SELECT, (which being impossible to guess), with another column, "X", also of mystery origin. You may imagine that your "X", (where underline is a form of quoting to avoid further confusion here), is a string literal. In SQL, it is not; rather it names a column. The SQLite library can be built with or without the option of converting double-quoted words to a string literal when no column with the given name can be found. The library you have "success"a with may have that build option differ from the one that has given you your immediate difficulty. I recommend changing that wanna-be literal to 'X' (if it was meant to be a literal), and see what happens. And going forward, learn to not write string literals with double-quoting in SQL, even when you have a SQLite instance that lets you seem to get away with it.
You would do people trying to help here a favor if you can post enough of your schema that they can replicate your problem, along with the actual error evidence. SQLite does not "throw" syntax errors. It returns error codes and, when asked for them, error messages.
a. Success when there is something wrong can be bad luck, and constitute a form of failure. Hence my scare-quotes on the word.
(6) By Rocky (Rocky_Hu) on 2022-01-28 23:33:46 in reply to 3 [link] [source]
Thank you, Larry!
Yes, "X" is a string literal. Till now in each RDBMS used by me worked "X" equivalent to 'X' - but maybe it is not allowed in each. Mostly I develop in Java, and there is better to use the 'X' format, because the strings are between double quotes, and hence this way doesn't need escaping. But now I develop in Delphi, therefore I use the "X" format, because in Pascal the strings are between single quotes, so here the double quoted part can be used within a string without escaping. This is my first SQLite project, but I used the double quoting in some queries already, and there was no problem.
As it turned out, the UPDATE...FROM syntax is quite new in SQLite, earlier versions don't support it, so a dll upgrade was needed. With the latest dll works my update.
(8) By Ryan Smith (cuz) on 2022-01-29 00:23:10 in reply to 6 [source]
You may find that in some programming languages quoting can be used interchangeably between single and double quotes. In Delphi however it is strictly single-quoted strings and likewise, in SQL it's strictly single-quotes for strings and double-quotes for Identifier names.
There is a quirk in SQLite which allowed double-quotes (since the start) in places where other engines will not allow it, using a sort-of rule of "if it can only be a string, then it must be a string, even if double quoted" which has woefully misled programmers since forever and "fixing" it might break so much historic systems that it now remains an entrenched misfeature.
There's an ongoing thread of some of the folks here pushing for a "strict" mode in which this misfeature (along with a few others) are vanquished, but there are many higher priority amendments in the pipeline.
Either way, you would do well to always use single-quotes for your SQL strings, in any SQL engine, and while double-quoted identifiers work, the best is to avoid needing quotes for identifiers at all by using proper names without spaces, special characters and whatnot.
PS: In Delphi using single-quotes in a string is perfectly easy, you simply need to double it, same as in SQL, so that the string "John's cat said 'pffff'." becomes:
saying := 'John''s cat said ''pffff''. ';
To insert that into a query is even easier, just use the function "QuotedStr()" or "QuoteStr()" (I forget which exactly, and if it is not one of these, google it).
So for example:
query := 'UPDATE t SET value = ' + QuotedStr(saying) + ';';
which produces a string with quotes doubled for Delphi's sake and doubled again for SQL's sake (which becomes a bit hectic) equivalent to:
query := 'UPDATE t SET value = ''John''''s cat said ''''pffff''''.'';';
So yes, easier to just use the function, plus you should not really be building queries like this, you should be using prepared statements and binding values to it, which avoids the need for any complex quoting.
(4) By Keith Medcalf (kmedcalf) on 2022-01-28 23:00:58 in reply to 1 [link] [source]
That error message would indicate that the version of SQLite3 library being used is prior to the version at which the UPDATE ... FROM ... syntax was recognized.
(7) By Rocky (Rocky_Hu) on 2022-01-28 23:35:58 in reply to 4 [link] [source]
You're right. The version of dll incorporated in Delphi was 18.104.22.168. (My SQLiteStudio uses 3.35.4) It turned out, that SQLite ver >= 3.33.0 is needed for the UPDATE...FROM syntax. I replaced the dll by the latest, and works now.