Read data changes from Firebird database [SOLVED]
lazarus / 16-Databases

----------------------------------------------------------------------
mig-31 · 2012-06-01 09:37

Hello,
How I read (refresh) the data changed (Firebird 2.5) by other user (by
other application).
I read the new data after aplication restarted.
I'm using the standard lazarus database components

Code:

procedure TMainForm.DevicesListUpdate;
begin

     //read data from table
     TableDevices.Close;
     TableDevices.SQL.Clear;
     TableDevices.SQL.Add('select name from devices');
     TableDevices.Open;

  //DbCombox ad Items
   DevicesList.Clear;
   while not(TableDevices.EOF) do
    begin
     
DevicesList.Items.Append(TableDevices.FieldByName('name').Value);
      TableDevices.Next;
    end;
end;      

Thank you

----------------------------------------------------------------------
ludob · 2012-06-01 10:14

Have you set the transaction isolation level to a particular level?

TableDevices.Close TableDevices.Open will not start a new transaction
and you could still be looking at a snapshot of the data. To be sure
that you get the latest data from the server I would add a
SQLTransaction.commitretaining before opening the table again or
explicitly set the transaction isolation level. http://wiki.freepascal
.org/Firebird_in_action#Transaction_isolation_levels

----------------------------------------------------------------------
mig-31 · 2012-06-01 14:50

I added to code DbTransaction.CommitRetaining;
But I  can't get a new data

Code:
procedure TMainForm.DevicesListUpdate;
begin
     //read data from table

     TableDevices.Close;
     TableDevices.SQL.Clear;
     TableDevices.SQL.Add('select name from devices');
     DbTransaction.CommitRetaining;
     TableDevices.Open;

   //DbCombox ad Items

   DevicesList.Clear;
   while not(TableDevices.EOF) do
    begin
     
DevicesList.Items.Append(TableDevices.FieldByName('name').Value);
      TableDevices.Next;
    end;
end;

----------------------------------------------------------------------
joseme · 2012-06-01 15:10

You should see the changes right after the other app commit them. You
do not need to close, redefine the SQL text and reopen  each time,
only perform a refresh. What are the components you are using?

----------------------------------------------------------------------
ludob · 2012-06-01 17:55

Make sure the other app committed the data. In case of doubt, use an
external tool like flamerobin to be sure the data is in the database.

----------------------------------------------------------------------
bambamns · 2012-06-01 18:13

I usualy do :

Code: [Select]with LogQ do
   begin
    Insert;
     FieldByName('ID').Value := null;
     FieldByName('VREME').Value := Now;
     FieldByName('THREAD').Value := _thread;
     FieldByName('IP').Value := _ip;
     FieldByName('LOGS').Value := logs
    Post;
    ApplyUpdates;
    DM.SQLTransaction.CommitRetaining;
  end;

If you wont to send a event to clients when database record is added
or changed, you have to write event in Firebird and Event Listener on
Client application.
When you catch event on client you have to refresh or do the
close/open procedure.

----------------------------------------------------------------------
mig-31 · 2012-06-01 18:36

Quote from: joseme on June 01, 2012, 05:10:44 pmYou should see the
changes right after the other app commit them. You do not need to
close, redefine the SQL text and reopen  each time, only perform a
refresh. What are the components you are using?

I'm using TIBConnection, TSQLTransaction, TSQLQery. Firebird 2.5.

IQuote from: ludob on June 01, 2012, 07:55:14 pmMake sure the other
app committed the data. In case of doubt, use an external tool like
flamerobin to be sure the data is in the database.


I start client application writting in Lazarus, then change or add
data in the table by the FlameRobin and read the data again by the
client application. But client application shows me data without
changing.

Could show me a code example with refresh dataset logic.

Thanks

----------------------------------------------------------------------
ludob · 2012-06-01 19:25

QuoteCould show me a code example with refresh dataset logic.Your last
code is fine. As Joseme said, closing and re-assigning is not
necessary for a refresh but I guess you use the same routine to open
the dataset the first time and then refresh it. That is perfectly OK.
Just to be sure: DbTransaction is the transaction you assigned to
TableDevices.Transaction?

Do you commit your transaction in flamerobin? Restart completely
flamerobin and see if it sees your new data.

----------------------------------------------------------------------
mig-31 · 2012-06-03 10:34

Now I setuped all needed componets directly in IDE:

SQLTransaction.Action sets to caCommitretaining,
SQLQuery.SQL:='select name from devices'
DataSource.DataSet -> SQLQuery.

Set all components to active state.

Then I draged TDBGrid and TDbNavigator on the form and conncted its to
Datasource.
Added code to
Code: [Select]procedure TForm1.DBNavigator1Click(Sender: TObject;
Button: TDBNavButtonType);
begin
   if Button=nbRefresh then
     begin
        SQLQuery.Refresh;
     end;
end;     


Then I changed data and commited chages in FlameRobin. Then I clicked
on DbNavigator Refresh Button, but I didn't read a new data.
I don't know where is a maked mistake.

FireBird 2.5 on MandrivaLinux 2011 64 bit. Lazarus 1.1 64 bit.
Database and Lazarus are on the same computer.

----------------------------------------------------------------------
ludob · 2012-06-03 10:43

If you restart the application or in the IDE disconnect and re-connect
the TIBCOnnection, do you see the new data? If this does not work
verify the database name and make sure you are looking at the same
database as flame robin.
If you restart flamerobin and reconnect, do you see the new data?

----------------------------------------------------------------------
mig-31 · 2012-06-03 10:57

If I restart application I see the new data. If I restart FlameRobin I
also see the new data.
But the Refresh Button in DbNavigator still doesn't work.

----------------------------------------------------------------------
ludob · 2012-06-03 11:21

Setting SQLTransaction.Action to caCommitretaining doesn't do a commit
action automatically.
Try this:
Code: [Select]procedure TForm1.DBNavigator1Click(Sender: TObject;
Button: TDBNavButtonType);
begin
   if Button=nbRefresh then
     begin
        SQLTransaction.CommitRetaining;
        SQLQuery.Refresh;
     end;
end;

----------------------------------------------------------------------
mig-31 · 2012-06-03 11:40

I added this line
SQLTransaction.CommitRetaining;

to the code, but it still doesn't work.

----------------------------------------------------------------------
ludob · 2012-06-03 17:01

OK. CommitRetaining on firebird hangs on to the current snapshot.
Change your code to:
Code: [Select]procedure TForm1.DBNavigator1Click(Sender: TObject;
Button: TDBNavButtonType);
begin
   if Button=nbRefresh then
     begin
        SQLTransaction.Commit;
        SQLQuery.Open;
     end;
end;  Or, as suggested earlier, change your isolation level with
Code: [Select]SQLTransaction1.Params.text:='isc_tpb_read_committed';
in the IDE or in code before you make the connection.

----------------------------------------------------------------------
mig-31 · 2012-06-03 18:18

Thank you.
With this parametr Refresh of dataset works.

Code: [Select]SQLTransaction1.Params.text:='isc_tpb_read_committed';