[SOLVED] Operation cannot be performed on an active dataset.
lazarus / 16-Databases

----------------------------------------------------------------------
BigChimp · 2012-09-18 15:25

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

Code: [Select]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

----------------------------------------------------------------------
BigChimp · 2012-09-18 17:18

Mmmm.... getting tired.
Throwing in a .close helped:
Quote...
      QDetail.Close;
      QDetail.SQL.Text:=Format(AdresLatest, [inttostr(NameID)]);
      QDetail.Open;
...

----------------------------------------------------------------------
Knipfty · 2012-09-18 17:23

I've found that anytime you change the SQL Text property, the dataset
needs to be closed.  The IDE does this automatically and forces you to
set active to true to see the result.  It doesn;t surprise me that you
need to do this programically as well.

----------------------------------------------------------------------
BigChimp · 2012-09-18 17:43

Nope, that seems logical. Lesson: don't program when you're tired

edit: ... and if you do, do what you tell others to do: run with the
debugger first to find out where the error message is generated
exactly