[solved] MySql getting error cannot be performed on an active dataset

[JimGoodley]

I spent hours reading wiki's and forum but still have problem.
Using windows 10 MySql 8.0 Lazarus 3.3.2 64 bit.
Took a copy of the procedure from main app and made a simple exe to prove the cause was not another call
to the same procedure upsetting things, still get error.
Funny thing is the main exe was working for +- 1 month then suddenly gave error, can't think of what I may have done.
I took same SQL statement (simple select) and ran in WorkBench and HeidiSQL with no problem.
In my new demo exe I changed database to world and city table, still get same error, so it's not my DB or table.
I tried with SQLQuery > options > sqoAutoCommit and error does not appear but returned dataset is empty.
I tried suggested SQLTransaction commitRetaining, CommitNone etc but error persists.
I am not a DBA or an ex Delphi programmer but Lazarus looked very interesting to me as an alternative to VS-CS.
Can someone please help me, I've got no more hairs left to tear out!!!
Jim

[egsuh]

Remove SQLQuery.Execsql. For what do you need it?

[JimGoodley]

To execute the query placed in SQLQuery.text from the query variable.
From what I read so far I put the sql code in SQLQUERY then execute then loop through the dataset.
When the execute is called, I get the error in the debugger. If I continue then the dataset is empty. Obviously since the execute failed.
Regards
Jim

[JimGoodley]

I need it to execute the sql command and read from a dB table.
Regards
Jim

[cdbc]

Hi
@egsuh is right, loose this

      showMessage('before SQLQuery.ExecSQL');
      SQLQuery.ExecSQL;
      showMessage('after SQLQuery.ExecSQL');

I took the liberty of mod'ing your code a little bit:

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

1. procedure TForm1.loadCities();
2. var
3.   query : String;
4.   Row: integer;
5. begin
6.   showMessage('loadCities');
7.   if (MySQL80Connection.Connected = False) then begin
8.      showMessage('reconnecting');
9.      MySQL80Connection.Connected := True;
10.   end;
11.   query := 'select * from city;';
12.   clipboard.asText:= query;
13.   try
14.     SQLQuery.Close;
15.     SQLQuery.SQL.Text := query;
16.     SQLQuery.Open;
17.     row := 0;
18.     while not SQLQuery.eof do begin
19.       row := row + 1;
20.       StringGrid1.RowCount := row+1;
21.       StringGrid1.cells[0, row] := SQLQuery.FieldByName('Name').asString;
22.       StringGrid1.cells[1, row] := SQLQuery.FieldByName('CountryCodes').asString;
23.       SQLQuery.next;
24.     end;
25.   except
26.     ShowMessage('Error getting data from DB '+LineEnding+'query='+query);
27.   end;
28.   SQLTransaction.Commit; // the trx should close the query, when it's committed
29. //  SQLQuery.Close; // <--- so this is not necessary
30.   showMessage('end of loadShifts');
31. end;
32.  

I think this should compile and produce results 
...if the table ain't empty 
HTH
edit: SQLQuery.Open does exactly for reading, what you think ExecSql does.
edit2: Removed the 'showmessage' in the loop.
Regards Benny

[JimGoodley]

Thanks, I got the wrong idea of open and execute.
I will change my code tomorrow and let you know.
Regards Jim

[Zvoni]

Thanks, I got the wrong idea of open and execute.
I will change my code tomorrow and let you know.
Regards Jim

Nutshell: execsql should only be used (if even then), if no result is expected

[JimGoodley]

Solved
Thanks so much guys. I must learn to read what is actually written in the wiki, not what I expect to happen.