Read data changes from Firebird database [SOLVED]

[mig-31]

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]

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]

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]

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]

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]

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]

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?

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

I

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.

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]

Could 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]

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]

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]

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]

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]

I added this line
SQLTransaction.CommitRetaining;

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

[ludob]

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]

Thank you.
With this parametr Refresh of dataset works.

Code: [Select]

SQLTransaction1.Params.text:='isc_tpb_read_committed';