SQL Statement not set error
lazarus / 16-Databases

----------------------------------------------------------------------
Paulnewyork · 2020-06-10 12:00

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   unit
test; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, Forms,
Controls, Graphics, Dialogs, ComCtrls,connect, StdCtrls; type   {
TForm8 }   TForm8 = class(TForm)    Label1: TLabel;    Label2:
TLabel;    ListViewSomething: TListView;    ListViewPeople:
TListView;    procedure FormShow(Sender: TObject);  private   public  
end; var  Form8: TForm8;   implementation {$R *.lfm}    { TForm8 }   
 varstringHouse : string;stringCar : string;stringName :
string;stringName : string;stringPrice : string; procedure
addDatatoSomething(house, car :string);  var    item :
TListItem; begin item :=
form8.ListViewSomething.Items.Add; item.caption :=
ID; item.Subitems.Add(house); item.Subitems.Add(car);  end;  procedure
addDatatoPeople(name,price  :string);  var    item :
TListItem; begin item := form8.ListViewPeople.Items.Add; item.caption
:= ID; item.Subitems.Add(name); item.Subitems.Add(price);  end;      
    procedure TForm8.FormShow(Sender: TObject);var  begin 
 Form2.connectDB;   Form2.SQLQuery1.SQL.Text := 'SELECT * FROM
test1';   Form2.SQLQuery4.SQL.Text := 'SELECT * FROM test2'; 
 Form2.SQLQuery1.Open;   Form2.SQLQuery4.Open;    while not
Form2.SQLQuery1.Eof do   begin     stringname   :=
Form2.SQLQuery1.FieldByName('name').AsString;     stringprice  :=
Form2.SQLQuery1.FieldByName('price').AsString;       addDatatoPeople
(stringname,stringprice);      Form2.SQLQuery1.next;     end;     
 while not Form2.SQLQuery4.Eof do     begin     stringCar:=
Form2.SQLQuery4.FieldByName('car').AsString;     stringHouse:=
Form2.SQLQuery4.FieldByName('house').AsString;      addDatatoSomething
(stringCar, stringHouse);     Form2.SQLQuery4.next;      end; 
 Form2.closeDB;  end.

----------------------------------------------------------------------
rvk · 2020-06-10 12:40

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 · 2020-06-10 12:50

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  unit
connect; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
mysql57conn, sqldb, Forms, Controls, Graphics, Dialogs; type   { TForm
}   TForm = class(TForm)    MySQL57Connection1: TMySQL57Connection;  
SQLQuery1: TSQLQuery;       SQLTransaction1: TSQLTransaction; 
private    public      procedure connectDB;     procedure closeDB;  
end; var  Form: TForm; implementation {$R *.lfm} { TForm }  procedure
TForm.connectDB;begin  MySQL57Connection1.Hostname :='localhost'; 
MySQL57Connection1.UserName :='admin';  MySQL57Connection1.Password
:='xyz';  MySQL57Connection1.DatabaseName :='test'; 
MySQL57Connection1.Transaction := SQLTransaction1; 
MySQL57Connection1.Connected := true;  SQLQuery1.Database :=
MySQL57Connection1; end; procedure TForm.closeDB;begin 
SQLQuery1.ExecSQL;  SQLTransaction1.Commit;end; end.

----------------------------------------------------------------------
rvk · 2020-06-10 13:11

Quote from: Paulnewyork on June 10, 2020, 02:50:24 pmAnd 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 · 2020-06-10 13:35

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 · 2020-06-10 13:35

pic 2

----------------------------------------------------------------------
rvk · 2020-06-10 13:42

Quote from: Paulnewyork on June 10, 2020, 03:35:35 pmI 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 · 2020-06-10 13:43

Now something happen. The program stops here:

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
TCustomForm.CMShowingChanged(var Message: TLMessage);begin  try    if
Showing then      DoShow    else      DoHide;  except    if not
HandleShowHideException then      raise;  end;  inherited
CMShowingChanged(Message);end;

----------------------------------------------------------------------
rvk · 2020-06-10 13:49

Quote from: Paulnewyork on June 10, 2020, 03:43:10 pmNow 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Showmessage
('log1');Form2.connectDB;Showmessage('log2');Form2.SQLQuery1.SQL.Text
:= 'SELECT * FROM test1';Form2.SQLQuery4.SQL.Text := 'SELECT * FROM te
st2';Showmessage('log3');Form2.SQLQuery1.Open;Form2.SQLQuery4.Open;Sho
wmessage('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 · 2020-06-10 13:56

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

----------------------------------------------------------------------
rvk · 2020-06-10 14:01

Quote from: Paulnewyork on June 10, 2020, 03:56:35 pmI 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //
Form2.closeDB; // remove this line.Form2.SQLQuery1.Open; // add this
oneForm2.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 · 2020-06-10 14:09

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 · 2020-06-10 14:14

Quote from: Paulnewyork on June 10, 2020, 04:09:33 pmSo 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.

QuoteSo 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.

QuoteBut 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //
Form2.closeDB; // remove this line.Form2.SQLQuery1.Close; // add this
oneForm2.SQLQuery4.Close; // and this one