[SOLVED] SQLScript syntax error when executed for second time

[SassyPenguin]

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][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   s: string;
4. begin
5.   SaveDialog1.FileName := '';
6.   if SaveDialog1.Execute then
7.     begin
8.       s := SaveDialog1.FileName;
9.       if FileExists(s) then
10.         DeleteFile(s);
11.  
12.       SQLite3Connection1.DatabaseName := s;
13.       SQLite3Connection1.Open;
14.       SQLTransaction1.Active := TRUE;
15.  
16.       SQLScript1.Execute;             //<========= Caused "Syntax error at ..." when executed for the 2nd time
17.  
18.       SQLTransaction1.Commit;
19.  
20.       SQLTransaction1.Active := FALSE ;
21.       SQLite3Connection1.Close;
22.  
23.       Application.MessageBox('Database has been created successfully.','Done',0);
24.     end;
25. end;
26.  

[bytebites]

Reassign seems to help?

Code: Pascal  [Select][+][-]

1.       SQLScript1.Script.Text:=SQLScript1.Script.Text;

[wp]

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][+][-]

1.       SQLite3Connection1.ExecuteDirect(
2.         'CREATE TABLE IF NOT EXISTS "Company" ('+
3.         '"Name"     VARCHAR(20) NOT NULL, '+
4.         '"Address"  VARCHAR(200), '+
5.         '"Phone"    VARCHAR(20), ' +
6.         '"Email"    VARCHAR(30), ' +
7.         '"Website"  VARCHAR(50)'+
8.       ');');

[SassyPenguin]

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.

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][+][-]

1.       SQLite3Connection1.ExecuteDirect(
2.         'CREATE TABLE IF NOT EXISTS "Company" ('+
3.         '"Name"     VARCHAR(20) NOT NULL, '+
4.         '"Address"  VARCHAR(200), '+
5.         '"Phone"    VARCHAR(20), ' +
6.         '"Email"    VARCHAR(30), ' +
7.         '"Website"  VARCHAR(50)'+
8.       ');');

[af0815]

If 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]

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

[SassyPenguin]

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.

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

[SassyPenguin]

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

Reassign seems to help?

Code: Pascal  [Select][+][-]

1.       SQLScript1.Script.Text:=SQLScript1.Script.Text;

[wp]

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

Reassign seems to help?

Code: Pascal  [Select][+][-]

1.       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).