[SOLVED] SQLScript syntax error when executed for second time
lazarus / 16-Databases

----------------------------------------------------------------------
SassyPenguin · 2021-11-14 08:56

I use script to create a default database file, script ran without
error for the 1st time, but not the succeeding call.

Can someone please point out why the error occurred when
SQLScript.Execute is called on the 2nd time.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);var  s: string;begin 
SaveDialog1.FileName := '';  if SaveDialog1.Execute then    begin    
s := SaveDialog1.FileName;      if FileExists(s) then       
DeleteFile(s);       SQLite3Connection1.DatabaseName := s;     
SQLite3Connection1.Open;      SQLTransaction1.Active := TRUE;      
SQLScript1.Execute;             //<========= Caused "Syntax error at
..." when executed for the 2nd time       SQLTransaction1.Commit;    
 SQLTransaction1.Active := FALSE ;      SQLite3Connection1.Close;    
 Application.MessageBox('Database has been created
successfully.','Done',0);    end;end;

----------------------------------------------------------------------
bytebites · 2021-11-14 12:20

Reassign seems to help?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
SQLScript1.Script.Text:=SQLScript1.Script.Text;

----------------------------------------------------------------------
wp · 2021-11-14 12:41

I am aware that this post is about SQLScript, but maybe I should note
that direct execution of the query by the Sqlite3Connection does not
have this issue:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
SQLite3Connection1.ExecuteDirect(        'CREATE TABLE IF NOT EXISTS
"Company" ('+        '"Name"     VARCHAR(20) NOT NULL, '+       
'"Address"  VARCHAR(200), '+        '"Phone"    VARCHAR(20), ' +     
 '"Email"    VARCHAR(30), ' +        '"Website"  VARCHAR(50)'+     
');');

----------------------------------------------------------------------
SassyPenguin · 2021-11-15 04:40

Thanks for all the replies and suggestions.

The script included in the sample project is just an example, in real
program I have more lines to execute in order to create a complete
database.
If this is a SQLScript issue, then hope it will be addressed in the
future release.

I will take ExecuteDirect method as suggested by wp as a workaround
for now.

Quote from: wp on November 14, 2021, 01:41:59 pmI am aware that this
post is about SQLScript, but maybe I should note that direct execution
of the query by the Sqlite3Connection does not have this issue:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
SQLite3Connection1.ExecuteDirect(        'CREATE TABLE IF NOT EXISTS
"Company" ('+        '"Name"     VARCHAR(20) NOT NULL, '+       
'"Address"  VARCHAR(200), '+        '"Phone"    VARCHAR(20), ' +     
 '"Email"    VARCHAR(30), ' +        '"Website"  VARCHAR(50)'+     
');');

----------------------------------------------------------------------
af0815 · 2021-11-15 06:25

Quote from: jamestien on November 15, 2021, 05:40:39 amIf this is a
SQLScript issue, then hope it will be addressed in the future
release.But only if a Bug is filed. If nobody knows it wont fixed.

----------------------------------------------------------------------
egsuh · 2021-11-15 07:29

If you are trying to create a TABLE whose name is already exists then
it wll cause errors.

----------------------------------------------------------------------
SassyPenguin · 2021-11-15 08:59

No friend, if you read the code, it creates a new file every time when
the button is clicked. so the existing TABLE isn't issue, besides the
SQL script also using "IF NOT EXISTS..." check method to create table.

Quote from: egsuh on November 15, 2021, 08:29:56 amIf you are trying
to create a TABLE whose name is already exists then it wll cause
errors.

----------------------------------------------------------------------
SassyPenguin · 2021-11-15 09:31

Thanks, bytebites. Your suggestion also worked, what a magic fix
hahaha.

Quote from: bytebites on November 14, 2021, 01:20:47 pmReassign seems
to help?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
SQLScript1.Script.Text:=SQLScript1.Script.Text;

----------------------------------------------------------------------
wp · 2021-11-15 14:26

Quote from: jamestien on November 15, 2021, 10:31:19 amThanks,
bytebites. Your suggestion also worked, what a magic fix hahaha.

Quote from: bytebites on November 14, 2021, 01:20:47 pmReassign seems
to help?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
SQLScript1.Script.Text:=SQLScript1.Script.Text;The fact that this
nonsense instruction is required is clearly an indication of a bug,
and the issue should be reported.

Testing the demo project with Laz 1.8.4, 2.0.12 does not show the
issue. Even Laz/main does not show it when it is combined with FPC
3.2.0. But in combination with FPC 3.2.2 the issue comes up, like with
FPC/main. Therefore, the issue even is a regression.

Because FCL/RTL are hard to debug I did not fully dive into the issue
but when TCustomSQLScript.ProcessStatement is entered at the second
call the sql statement is lacking the "CREATE" keyword which has been
dropped somewhere along the way. No idea whether this really is
related, at least it looks suspicious.

When reporting the issue please add your demo project (or even better:
rewrite the demo project as console application so that Lazarus is not
needed).