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

Forum > Databases

(1/2) > >>

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
--- Quote --- showMessage('before SQLQuery.ExecSQL');
SQLQuery.ExecSQL;
showMessage('after SQLQuery.ExecSQL');
--- End quote ---
I took the liberty of mod'ing your code a little bit:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure TForm1.loadCities();var query : String; Row: integer;begin showMessage('loadCities'); if (MySQL80Connection.Connected = False) then begin showMessage('reconnecting'); MySQL80Connection.Connected := True; end; query := 'select * from city;'; clipboard.asText:= query; try SQLQuery.Close; SQLQuery.SQL.Text := query; SQLQuery.Open; row := 0; while not SQLQuery.eof do begin row := row + 1; StringGrid1.RowCount := row+1; StringGrid1.cells[0, row] := SQLQuery.FieldByName('Name').asString; StringGrid1.cells[1, row] := SQLQuery.FieldByName('CountryCodes').asString; SQLQuery.next; end; except ShowMessage('Error getting data from DB '+LineEnding+'query='+query); end; SQLTransaction.Commit; // the trx should close the query, when it's committed// SQLQuery.Close; // <--- so this is not necessary showMessage('end of loadShifts');end; I think this should compile and produce results 8)
...if the table ain't empty :D
HTH
edit: SQLQuery.Open does exactly for reading, what you think ExecSql does.
edit2: Removed the 'showmessage' in the loop.
Regards Benny

Navigation

[0] Message Index

[#] Next page