SQL Statement not set error

[Paulnewyork]

I created a new form, which is doing someting like form1 and form2.

But If i start my program and press the button for these three forms i get the message: SQL Statement not set.
in form 1 and 2 there is no Data from the db now.
I didnt changed anything in FOrm 1 and 2.
If i press form 3 i get the same message but i can see the data from db.

Does anyone  know where this error is caused by? The internet is not the helpful.
If i restore a backup of the whole lazarus file, form 1 and 2 are working. there should be something with form 3?

I created form 3 again and get the same problems....

form 3 is below

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

1.  
2.  
3. unit test;
4.  
5. {$mode objfpc}{$H+}
6.  
7. interface
8.  
9. uses
10.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, ComCtrls,connect, StdCtrls;
11.  
12. type
13.  
14.   { TForm8 }
15.  
16.   TForm8 = class(TForm)
17.     Label1: TLabel;
18.     Label2: TLabel;
19.     ListViewSomething: TListView;
20.     ListViewPeople: TListView;
21.     procedure FormShow(Sender: TObject);
22.   private
23.  
24.   public
25.  
26.   end;
27.  
28. var
29.   Form8: TForm8;
30.  
31.  
32.  
33. implementation
34.  
35. {$R *.lfm}
36.  
37.    
38. { TForm8 }    
39.  
40. var
41. stringHouse : string;
42. stringCar : string;
43. stringName : string;
44. stringName : string;
45. stringPrice : string;
46.  
47. procedure addDatatoSomething(house, car :string);
48.   var
49.     item : TListItem;
50.  
51. begin
52.  item := form8.ListViewSomething.Items.Add;
53.  item.caption := ID;
54.  item.Subitems.Add(house);
55.  item.Subitems.Add(car);
56.  
57.  end;
58.  
59.  
60. procedure addDatatoPeople(name,price  :string);
61.   var
62.     item : TListItem;
63.  
64. begin
65.  item := form8.ListViewPeople.Items.Add;
66.  item.caption := ID;
67.  item.Subitems.Add(name);
68.  item.Subitems.Add(price);
69.  
70.  end;
71.            
72.  
73. procedure TForm8.FormShow(Sender: TObject);
74. var
75.  
76.  
77. begin
78.    Form2.connectDB;
79.    Form2.SQLQuery1.SQL.Text := 'SELECT * FROM test1';
80.    Form2.SQLQuery4.SQL.Text := 'SELECT * FROM test2';
81.    Form2.SQLQuery1.Open;
82.    Form2.SQLQuery4.Open;
83.  
84.    while not Form2.SQLQuery1.Eof do
85.    begin
86.      stringname   := Form2.SQLQuery1.FieldByName('name').AsString;
87.      stringprice  := Form2.SQLQuery1.FieldByName('price').AsString;
88.  
89.       addDatatoPeople (stringname,stringprice);
90.       Form2.SQLQuery1.next;
91.      end;
92.  
93.  
94.      while not Form2.SQLQuery4.Eof do
95.      begin
96.      stringCar:= Form2.SQLQuery4.FieldByName('car').AsString;
97.      stringHouse:= Form2.SQLQuery4.FieldByName('house').AsString;
98.  
99.      addDatatoSomething (stringCar, stringHouse);
100.      Form2.SQLQuery4.next;
101.  
102.      end;
103.    Form2.closeDB;
104.  
105.  
106. end.      
107.  
108.  

                         

[rvk]

The error is probably on the line with Form2.connectDB;

Without knowing what's in there, it's just guessing for us.
If you open queries in that procedure, the SQL is not set yet.

Otherwise, step through the code with the debugger and report the bit that gives the error.
Set a breakpoint on Form2.connectDB; with F5 and run the program with F9.
After that, step through the code (F8 = step over a instruction and F7 = step into a procedure)

[Paulnewyork]

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

1.  
2. unit connect;
3.  
4. {$mode objfpc}{$H+}
5.  
6. interface
7.  
8. uses
9.   Classes, SysUtils, mysql57conn, sqldb, Forms, Controls, Graphics, Dialogs;
10.  
11. type
12.  
13.   { TForm }
14.  
15.   TForm = class(TForm)
16.     MySQL57Connection1: TMySQL57Connection;
17.     SQLQuery1: TSQLQuery;
18.    
19.     SQLTransaction1: TSQLTransaction;
20.   private
21.  
22.  
23.   public
24.  
25.      procedure connectDB;
26.      procedure closeDB;
27.  
28.   end;
29.  
30. var
31.   Form: TForm;
32.  
33. implementation
34.  
35. {$R *.lfm}
36.  
37. { TForm }
38.  
39.  
40. procedure TForm.connectDB;
41. begin
42.   MySQL57Connection1.Hostname :='localhost';
43.   MySQL57Connection1.UserName :='admin';
44.   MySQL57Connection1.Password :='xyz';
45.   MySQL57Connection1.DatabaseName :='test';
46.   MySQL57Connection1.Transaction := SQLTransaction1;
47.   MySQL57Connection1.Connected := true;
48.   SQLQuery1.Database := MySQL57Connection1;
49.  
50. end;
51.  
52. procedure TForm.closeDB;
53. begin
54.   SQLQuery1.ExecSQL;
55.   SQLTransaction1.Commit;
56. end;
57.  
58. end.
59.  

[rvk]

And on what line does it give you that error message?

(You can also put Showmessage('log1'); etc between the lines to know which line actually results in an error.)

[Paulnewyork]

I made a breakpoint and run the programm. If i press F7 or F8 nothing happens.
And if i look under Start  the F7 and F8 options are greyed out   

do I make something wrong. teh debugger is enabled in Project settings --> Debugger -->

[Paulnewyork]

pic 2

[rvk]

I made a breakpoint and run the programm. If i press F7 or F8 nothing happens.

Did you run the program with F9 (or Run > Run) and not "Run without debugger" ?

Anyway... you can also put some Showmessage('log1'); etc between the code to know what line is really the problem.

[Paulnewyork]

Now something happen. The program stops here:

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

1. procedure TCustomForm.CMShowingChanged(var Message: TLMessage);
2. begin
3.   try
4.     if Showing then
5.       DoShow
6.     else
7.       DoHide;
8.   except
9.     if not HandleShowHideException then
10.       raise;
11.   end;
12.   inherited CMShowingChanged(Message);
13. end;  
14.  

[rvk]

Now something happen. The program stops here:

That's in the OnShow (which is logical because you have those lines in FormShow).

Try this:

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

1. Showmessage('log1');
2. Form2.connectDB;
3. Showmessage('log2');
4. Form2.SQLQuery1.SQL.Text := 'SELECT * FROM test1';
5. Form2.SQLQuery4.SQL.Text := 'SELECT * FROM test2';
6. Showmessage('log3');
7. Form2.SQLQuery1.Open;
8. Form2.SQLQuery4.Open;
9. Showmessage('log4');

In that case you probably will see the log1 message but not the log2 message before you get the exception.

In that case the problem lies in Form2.ConnectDB.

It's difficult without the full code to know exactly where the problem is.
Somehow the SQLQuery1 is opened BEFORE you assign the .Text do SQL.

[Paulnewyork]

I saw the log1 message, log2 message, log3 message, log4 message and then the error again

[rvk]

I saw the log1 message, log2 message, log3 message, log4 message and then the error again

Then your problem lies further.

And now I see it... WHY are you calling closeDB at the end?
You have ExecSQL in that procedure. But you have no INSERT or UPDATE statements.
You have SELECT.

So replace then with a simple close;

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

1. // Form2.closeDB; // remove this line.
2. Form2.SQLQuery1.Open; // add this one
3. Form2.SQLQuery4.Open; // and this one

BTW. Your procedurename TForm2.closeDB; is somewhat poorly chosen because you do an ExecSQL in there. You don't even close the DBs. I wouldn't even have ExecSQL like that in a procedure because all 4 are executed. Normally you want more control over which of the 4 you want to execute.

[Paulnewyork]

ok now the form 3 works.

but form1 und 2 not.

So if i just SELECT something i dont have to use the procedure;  closeDB?!

But in my backup from yesterday  form 1 and 2 are working. and i didn`t changed anything.
So i have to check the code of form 1 and two an delete every closeDB, when ijust select someting?!


But why i haave to add the last two lines? I have these just in the beginning.

// Form2.closeDB; // remove this line.
Form2.SQLQuery1.Open; // add this one
Form2.SQLQuery4.Open; // and this one

[rvk]

So if i just SELECT something i dont have to use the procedure;  closeDB?!

No. You have TSQLQuery.Open and TSQLQuery.Close for SELECT statements (which return a dataset)
and you have TSQLQuery.ExecSQL for other statements.

But... for simple insert you can also do a TSQLQuery.Open on a SELECT * FROM TABLE
and use TSQLQuery.Insert; to put the dataset in insert mode.
You then can put anything into the fields with TSQLQuery.FieldByName('fieldname').asString := 'abc' for example
and do a TSQLQuery.Post to post the insert/edit

So in that case TSQLQuery.ExecSQL isn't even needed.

So i have to check the code of form 1 and two an delete every closeDB, when ijust select someting?!

That depends if you are executing an INSERT INTO or UPDATE. For that you do need ExecSQL (and your CloseDB).
But if you only want one INSERT INTO for SQLQuery1, then you shouldn't have ALL 4 SQLQuery1..4.ExecSQL in CloseDB because you haven't filled in SQLQuery2..3.SQL with a correct SQL statement.

But why i haave to add the last two lines? I have these just in the beginning.
// Form2.closeDB; // remove this line.
Form2.SQLQuery1.Open; // add this one
Form2.SQLQuery4.Open; // and this one

Sorry. My (copy and paste) mistake. That should be

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

1. // Form2.closeDB; // remove this line.
2. Form2.SQLQuery1.Close; // add this one
3. Form2.SQLQuery4.Close; // and this one