Recent

Author Topic: SQL Statement not set error  (Read 603 times)

Paulnewyork

  • New Member
  • *
  • Posts: 44
SQL Statement not set error
« on: June 10, 2020, 02:00:25 pm »
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.  
                         
« Last Edit: June 10, 2020, 02:13:10 pm by Paulnewyork »

rvk

  • Hero Member
  • *****
  • Posts: 4288
Re: SQL Statement not set error
« Reply #1 on: June 10, 2020, 02:40:29 pm »
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

  • New Member
  • *
  • Posts: 44
Re: SQL Statement not set error
« Reply #2 on: June 10, 2020, 02:50:24 pm »
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.  
« Last Edit: June 13, 2020, 06:06:55 pm by Paulnewyork »

rvk

  • Hero Member
  • *****
  • Posts: 4288
Re: SQL Statement not set error
« Reply #3 on: June 10, 2020, 03:11:01 pm »
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

  • New Member
  • *
  • Posts: 44
Re: SQL Statement not set error
« Reply #4 on: June 10, 2020, 03:35:35 pm »
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

  • New Member
  • *
  • Posts: 44
Re: SQL Statement not set error
« Reply #5 on: June 10, 2020, 03:35:56 pm »
pic 2

rvk

  • Hero Member
  • *****
  • Posts: 4288
Re: SQL Statement not set error
« Reply #6 on: June 10, 2020, 03:42:31 pm »
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

  • New Member
  • *
  • Posts: 44
Re: SQL Statement not set error
« Reply #7 on: June 10, 2020, 03:43:10 pm »
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

  • Hero Member
  • *****
  • Posts: 4288
Re: SQL Statement not set error
« Reply #8 on: June 10, 2020, 03:49:16 pm »
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

  • New Member
  • *
  • Posts: 44
Re: SQL Statement not set error
« Reply #9 on: June 10, 2020, 03:56:35 pm »
I saw the log1 message, log2 message, log3 message, log4 message and then the error again

rvk

  • Hero Member
  • *****
  • Posts: 4288
Re: SQL Statement not set error
« Reply #10 on: June 10, 2020, 04:01:23 pm »
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.

« Last Edit: June 10, 2020, 04:05:14 pm by rvk »

Paulnewyork

  • New Member
  • *
  • Posts: 44
Re: SQL Statement not set error
« Reply #11 on: June 10, 2020, 04:09:33 pm »
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

  • Hero Member
  • *****
  • Posts: 4288
Re: SQL Statement not set error
« Reply #12 on: June 10, 2020, 04:14:53 pm »
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.

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

Quote
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

 

TinyPortal © 2005-2018