Firebird.
I'm trying to iterate through a view (AdresNotEnough) (that has names in it), get the primary key (indicating a person).
Then get the last modified record in a linked table with addresses (AdresLatest) (this has some addresses, none of which have the StandaardAdres field set to 1).
Finally I want to update the linked table, and set an integer field to 1 (AdresDefault) for that latest field.
This latest operation fails with
Operation cannot be performed on an active dataset.
(marked failure here below)
The .executedirect command does modify the data so that one record in the original view would disappear. Is that the problem I'm facing or is it something else?
If it's the former, I can redo the thing with 2 transaction, something like a snapshot read only transaction for the read iteration and a normal transaction for the write transaction... just wanted to make sure that is really needed.
procedure TAddressScrubber.MainProgram;
const
AdresDefault='UPDATE ADRESSEN a SET a.STANDAARDADRES = 1 WHERE a.ADRESID = ';
AdresLatest='select a.adresid from adressen a where a.naamid=%s order by changedate desc rows 1 to 1';
AdresNonDefault='UPDATE ADRESSEN a SET a.STANDAARDADRES = 0 WHERE a.ADRESID <> ';
AdresNotEnough='select naamid from vwadres_std_notenough';
var
Conn: TIBConnection;
DetailDefault, NameID: integer;
Q, QDetail: TSQLQuery;
Rec: integer;
Tran: TSQLTransaction;
begin
Conn:=TIBConnection.Create(nil);
Q:=TSQLQuery.Create(nil);
QDetail:=TSQLQuery.Create(nil);
Tran:=TSQLTransaction.create(nil);
try
//todo fix static assignments
Conn.HostName:='someserver';
Conn.DatabaseName:='thedatabase';
Conn.UserName:='SYSDBA';
Conn.Password:='masterkey';
Conn.CharSet:='UTF8';
Conn.Open;
if not(Conn.Connected) then
begin
writeln('Connection error.');
halt(2);
end;
Conn.Transaction:=Tran;
Tran.StartTransaction;
Q.DataBase:=Conn;
Q.SQL.Text:=AdresNotenough;
Q.Open;
QDetail.Database:=Conn;
while not(q.EOF) do
begin
NameID:=q.Fields[0].AsInteger;
// Get detail record that was modified last, a suitable default for the standard detail record:
QDetail.SQL.Text:=Format(AdresLatest, [inttostr(NameID)]);
QDetail.Open;
writeln('NameID: '+inttostr(NameID));
DetailDefault:=QDetail.Fields[0].AsInteger;
writeln('DetailDefault '+inttostr(DetailDefault));
writeln('going to run:');
writeln(AdresDefault+inttostr(DetailDefault));
Conn.ExecuteDirect(AdresDefault+inttostr(DetailDefault)); //<===== failure here
q.next;
end;
Tran.Commit;
QDetail.Close;
q.close;
conn.close;
finally
Q.Free;
QDetail.Free;
Tran.Free;
Conn.Free;
end;
end;
Lazarus 1.0, FPC 2.6.0, x86, Windows, also occurs with FPC trunk x86
Thanks,
BigChimp