Recent

Author Topic: how to refresh FIBL after insert or update?  (Read 11192 times)

researcher

  • New member
  • *
  • Posts: 8
    • http://www.habarbadi.com
how to refresh FIBL after insert or update?
« on: May 22, 2009, 11:55:30 pm »
We're moving everything in our research project nicely from Windows over to Linux with Lazarus.
SQLdb had a few glitches with Locate, so we tried FIBL (Sergey Smirnov's port of FreeIB by Greg Deatz) which didn't have those glitches, but it's not obvious how to refresh the dataset after inserts or updates (which are successful).
Closing and opening the FIBDataSet does not display the new data.
Closing FIBTransaction and reopening it just crashes the app consistently.   :-[

What is the correct way to do that with FIBL?
Thanks!

clauslack

  • Sr. Member
  • ****
  • Posts: 275
Re: how to refresh FIBL after insert or update?
« Reply #1 on: May 23, 2009, 08:00:19 pm »
As Firebird use transactions, to "refresh" a DBGrid.

In the query of the DBGrid.

query.commit
query.StartTransaction      //appear the new transactions of the insert-update


Works fine with TIBConnection and Flamerobin (using IBPP library)

      
« Last Edit: May 24, 2009, 03:37:04 am by clauslack »

researcher

  • New member
  • *
  • Posts: 8
    • http://www.habarbadi.com
Re: how to refresh FIBL after insert or update?
« Reply #2 on: May 24, 2009, 02:31:07 pm »
Thanks Clauslack,
We are using FIBDataSet to display the entire data and to allow scrolling.
The insert and update are done with a separate FIBQuery.
I tried setting autocommit and autostarttransaction but this did not change the behaviour.
The update does work, but we have to close the app and restart it to see the new data.

The code below shows how we disengaged the DBGrid.DataSource and added a MessageDlg.
This seems to successfully post, commit and then allow display the new data - but this is clearly not the correct way to do it... Even adding a long sleep without the messagedlg seems to work. Is this a glitch of the DBGrid?

Has anyone used FIBL to update and view the new data immediately?

  DBGrid1.DataSource := nil;
  if messagedlg('continue',mtconfirmation,[mbok],0) = mrok then
    begin
      sleep(800);
      FIBDataSet1.Active := False;
      FIBTransaction1.Active := False;
      FIBTransaction1.Active := True;
      FIBDataSet1.Active := True;
      FIBDataSet1.First;
      FIBDataSet1.Last;
      FIBDataSet1.First;
      FIBDataSet1.Locate('idnum',currentidnum,[]);
      sleep(800);
    end;
  DBGrid1.DataSource := DataSource1;

-----------------------------

We are also trying another method from the FIBL documentation that comes with the source.
This method uses the same FIBDataSet for select, update and insert. This method seems to refresh the dataset without problems, but it does not update. Anyone have similar experience?

  FIBDataSet1.QUpdate.Params.ByName['notes'].AsString := EditNoteComment1.text;
  FIBDataSet1.QUpdate.ExecQuery;
  FIBDataSet1.ApplyUpdates;
  FIBDataSet1.QRefresh.Params.ByName['idnum'].asInteger := currentidnum;
  FIBDataSet1.Refresh;

--------------------------


clauslack

  • Sr. Member
  • ****
  • Posts: 275
Re: how to refresh FIBL after insert or update?
« Reply #3 on: May 24, 2009, 05:51:59 pm »
Well
Here you have an example for refresh after "update-insert". Please give me your email to send you the full source.
Works fine for me.

procedure TForm1.Button1Click(Sender: TObject);
begin            //select with FIBDataSet1 in DBGrid
   if not FIBDatabase1.Connected then FIBDatabase1.Open;
    if FIBTransaction1.Active then FIBTransaction1.Commit;
    FIBDataSet1.Close;
    FIBDataSet1.SelectSQL.clear;
    FIBDataSet1.SelectSQL.AddStrings(memo1.lines);
    FIBTransaction1.StartTransaction;
    FIBDataSet1.Open;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin    //insert-update with FIBQuery1
    if not FIBDatabase1.Connected then FIBDatabase1.Open;
    FIBQuery1.close;
    FIBQuery1.SQL.clear;
    FIBQuery1.SQL.AddStrings(memo2.lines);
    FIBTransaction2.StartTransaction;
    FIBQuery1.ExecQuery;
    FIBTransaction2.Commit;
    Button1Click(Sender);   //refresh the DBGrid
end;                   

Regards
clauslack@gmail.com
« Last Edit: May 24, 2009, 05:55:02 pm by clauslack »

Loesje

  • Full Member
  • ***
  • Posts: 157
    • Lazarus Support website
Re: how to refresh FIBL after insert or update?
« Reply #4 on: May 25, 2009, 10:14:11 pm »
SQLdb had a few glitches with Locate, so we tried FIBL (Sergey Smirnov's port of FreeIB by Greg Deatz) which didn't have those glitches, but it's not obvious how to refresh the dataset after inserts or updates (which are successful).

What glitches? I don't know of any?

researcher

  • New member
  • *
  • Posts: 8
    • http://www.habarbadi.com
Re: how to refresh FIBL after insert or update?
« Reply #5 on: May 27, 2009, 04:01:27 pm »
The SQLdb glitch was with Locate.
After inserting or updating a record, we wanted to go to that record using a key field, but the DBGrid just froze up, then after a click, allowed some movement, but to a different record, then froze again. Tried to make it work many times before we installed FIBL.

Have you done that successfully? What is the standard procedure?

Can we add these standard procedures as beginning examples "for getting started " to the Lazarus Documentation?


researcher

  • New member
  • *
  • Posts: 8
    • http://www.habarbadi.com
Re: how to refresh FIBL after insert or update?
« Reply #6 on: June 01, 2009, 08:44:45 pm »
Thanks Clauslack,
That code sample helped find the right sequence.
Now the DBGrid can refresh very nicely and the locate works, so it all is in good usable shape,
.... except ...
the app seems to crash when -
the app is open,
and we switch to a text editor to pick up data,
then switch back to the application
then input the data -
then it inserts correctly, then crashes!
  :'(
upon restarting, the data is there!
(is this a DBGrid thing, a DataSource change thing?)

I'm still exploring this funny behaviour, but the grid does refresh and the app is accepting data, so Thanks!


clauslack

  • Sr. Member
  • ****
  • Posts: 275
Re: how to refresh FIBL after insert or update?
« Reply #7 on: June 01, 2009, 09:35:52 pm »
I have this code in my home :)

I will send you for e-mail the complete project, because is not normal so many crash.

But be careful, Version 0.7.3 released in 2007-10-23, is too old, may be it have some bugs.

clauslack

  • Sr. Member
  • ****
  • Posts: 275
Re: how to refresh FIBL after insert or update?
« Reply #8 on: June 03, 2009, 12:56:05 am »
I upload the full project example_fib_refresh.zip

Tested with
Lazarus 0.9.27 svn 20212 fpc 2.2.4
Windows XP SP3
requires
fib 0.7.3.
Firebird database for test.

Regards

Loesje

  • Full Member
  • ***
  • Posts: 157
    • Lazarus Support website
Re: how to refresh FIBL after insert or update?
« Reply #9 on: June 07, 2009, 03:13:00 pm »
Can you create a bug-report for the problems you have with TSQLQuery.Locate?

clauslack

  • Sr. Member
  • ****
  • Posts: 275
Re: how to refresh FIBL after insert or update?
« Reply #10 on: June 09, 2009, 05:29:50 am »
Loesje
I created a bug-report about TDataset.Locate
******
Locate don't raise an error in a closed TDataset, just return false.

http://bugs.freepascal.org/view.php?id=13938

Regards