SQL syntax error
lazarus / 16-Databases

----------------------------------------------------------------------
jablalooz · 2018-02-15 16:23

Hello
I want to update the values of column values in a table when the
condition is met in the other column, but the problem is that when
using this term

TSQLQuery.SQL.Text:= 'SET @num: = 0; UPDATE MyTable SET ID = @num: =
(@ num + 1) where ID_Name = 1;';
TSQLQuery.ExecSQL;
Trans.Commit;

This is due to an error in the SQL statement

win10 64
lazarus 1.8.0
mysql5.5

----------------------------------------------------------------------
GAN · 2018-02-15 20:19

Use ExecuteDirect from your connection instead TSQLQuery.ExecSQL.
Check the wiki: http://wiki.lazarus.freepascal.org/Working_With_TSQLQu
ery#Query_with_Format_function

----------------------------------------------------------------------
jablalooz · 2018-02-15 21:22

Thanks for the answer
I used it, but the problem is still the same
This statement

SET  @num := 0;
UPDATE MyTable SET ID = @num := (@num+1) where ID_Name =1;

 works in MySQL effectively, but when in Lazarus the error is due to
not recognizing  tokin :@ num

----------------------------------------------------------------------
GAN · 2018-02-15 21:36

When I use Params in SQLite I don't use @ I use : like this:
https://www.freepascal.org/docs-html/fcl/sqldb/usingparams.html

Check also this: https://www.freepascal.org/docs-
html/fcl/sqldb/tsqlquery.params.html

----------------------------------------------------------------------
egsuh · 2018-02-17 23:05

I don't know about MS-SQL, but TSQLQuery seems to take only one
command. Try TSQLScript instead.