SQLdb; where are the records.
lazarus / 16-Databases

----------------------------------------------------------------------
mangakissa · 2014-01-23 21:18

I can't figure it why.

I have a form. In this form I can create a new batchnumber. With this
number I get with a query
the results I need. This founded results are put in a TBufdataset on a
new form.
When closing the new form, the TBufdataset will be saved to a table
(embedded Firebird).
Code: [Select]function TDMBatches.DBBatchOpslaan(aDataset : TDataset)
: boolean;
var MyQuery        : TSQLQuery;
    SQLTransaction : TSQLTransaction;
begin
  //opslaan van de batch
  result  := true;
  MyQuery := TSQLQuery.create(nil);
  SQLTransaction := TSQLTransaction.Create(nil);
  try
    try
      MyQuery.DataBase    := DBConnection;
      MyQuery.Transaction := SQLTransaction;
      SQLTransaction.DataBase := DBConnection;
      SQLTransaction.StartTransaction;
      MyQuery.SQL.Add('INSERT INTO transacties (lidnummer,adoptieid,
batchnummer,aniwaybedrag,donatiebedrag,usernr_create,
usernr_update,date_create,date_update)');
      MyQuery.SQL.Add('VALUES');
      MyQuery.SQL.Add('(:lidnummer,:adoptieid, :batchnummer,:aniwaybed
rag,:donatiebedrag,:usernr_create,:usernr_update,:date_create,:date_up
date)');
      aDataset.DisableControls;
      aDataset.First;
      while not aDataset.EOF do
      begin
        MyQuery.parambyname('lidnummer').AsString :=
aDataset.FieldByName('lidnummer').AsString;
        MyQuery.parambyname('batchnummer').AsString :=
aDataset.FieldByName('batchnummer').AsString;
        MyQuery.parambyname('adoptieid').AsInteger :=
aDataset.FieldByName('realadoptid').AsInteger;
        MyQuery.parambyname('aniwaybedrag').AsFloat :=
aDataset.FieldByName('aniwaybedrag').AsFloat;
        MyQuery.parambyname('donatiebedrag').AsFloat :=
aDataset.FieldByName('adoptiebedrag').AsFloat;
        MyQuery.parambyname('usernr_create').AsInteger :=
uGebruiker.ID;
        MyQuery.parambyname('usernr_update').AsInteger :=
uGebruiker.ID;
        MyQuery.parambyname('date_create').AsDate      := now;
        MyQuery.parambyname('date_update').AsDate      := now;
        MyQuery.ExecSQL;
        if MyQuery.RowsAffected = -1 then
        begin
          result := false;
          break;
        end else
          aDataset.Next;
      end;
      if result then
        SQLTransaction.Commit
      else
        SQLTransaction.Rollback;
    except
      result := false;
      SQLTransaction.Rollback;
    end;
  finally
    SQLTransaction.free;
    MyQuery.Free;
    aDataset.EnableControls;
  end;
end;
The records are saved to the database (I can see it in my tool
"database workbench lite, firebird edition).

My first form shows again. In this form I select the just created
batchnumber. With this batchnumer
I can create a query with the records I need.
Code: [Select]function TDMBatches.GetBatch(const aNewBatch: string;
const aDate: TDate): boolean;
var MyQuery        : TSQLQuery;
    SQLTransaction : TSQLTransaction;
begin
  result         := true;
  MyQuery        := TSQLQuery.Create(nil);
  SQLTransaction := TSQLTransaction.create(nil);
  try
    try
      MyQuery.DataBase        := DBConnection;
      SQLTransaction.DataBase := DBConnection;
      SQLTransaction.StartTransaction;
      MyQuery.ReadOnly        := true;
      MyQuery.PacketRecords   := -1;
      MyQuery.SQL.Add('SELECT
t.adoptieid,t.aniwaybedrag,t.donatiebedrag, t.batchnummer,');
      MyQuery.SQL.Add('  t.lidnummer,t.teruggeboekt, a.mandaatid,
a.startdatum,');
      MyQuery.SQL.Add('  a.einddatum, a.batchdatum, a.iban,
a.transactieid,');
      MyQuery.SQL.Add('  b.naam AS banknaam, b.biccode,
c.omschrijving,c.aantalperiodes FROM transacties t');
      MyQuery.SQL.Add('  LEFT OUTER JOIN adopties a ON a.adoptid =
t.adoptieid');
      MyQuery.SQL.Add('  LEFT OUTER JOIN banken b   ON b.id =
a.bankid');
      MyQuery.SQL.Add('  LEFT OUTER JOIN periodes c on a.periodeID =
c.id');
      MyQuery.SQL.Add('WHERE t.batchnummer = :batchnummer');
      MyQuery.Params[0].AsString := aNewBatch;
      MyQuery.Active := true;
      showmessage(inttostr(MyQuery.RecordCount));
      while not MyQuery.EOF do
      begin
        fBatchDataSet.AppendRecord([......]);
        MyQuery.Next;
      end;
      MyQuery.Active := false;
    except
      result := false
    end;
  finally
    SQLTransaction.Commit;
    MyQuery.free;
  end;
end;
Showmessage tells my RecordCount is 0. But I'm sure there are records.
I can see them in my tool.
If I close the application and runs it again, I see my selected
records (but not all my
information, but that's another problem).

Why can't I see my saved records if I want to see my records of the
batch again?
As far As I know, everything is going well on de SQLdb book.

Hopefully this story above is understandable.

----------------------------------------------------------------------
kpeters58 · 2014-01-23 22:08

You seem to know that you need to assign a transaction to your query
since you are doing it in your first snippet ....

----------------------------------------------------------------------
taazz · 2014-01-23 22:18

don't forget to call applyupdates after you finish appending records
and before you deactivate your query.

----------------------------------------------------------------------
mangakissa · 2014-01-24 09:03

ExecSQL and ApplyUpdates?
Isn't ExecSQL writes directly to the database? There's no dataset.
Using Applyupdates results an (invisible ) error and goes to
try..except.
ApplyUpdates will create queries to insert/update/delete records from
TBufdataset to the database.

----------------------------------------------------------------------
mangakissa · 2014-01-24 09:20

Solved.

To read the table again I forgot the most important rule when using a
separate transaction
Code: [Select]MyQuery.Transaction := SQLTransaction;