Recent

Author Topic: Read data changes from Firebird database [SOLVED]  (Read 12407 times)

mig-31

  • Sr. Member
  • ****
  • Posts: 284
Read data changes from Firebird database [SOLVED]
« on: June 01, 2012, 11:37:08 am »
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
« Last Edit: June 03, 2012, 08:19:25 pm by mig-31 »
Lazarus 2.0.6 - CentOS 7.x, Mageia 7.1

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Read data changes from Firebird database
« Reply #1 on: June 01, 2012, 12:14:22 pm »
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

  • Sr. Member
  • ****
  • Posts: 284
Re: Read data changes from Firebird database
« Reply #2 on: June 01, 2012, 04:50:35 pm »
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;     
« Last Edit: June 01, 2012, 04:54:32 pm by mig-31 »
Lazarus 2.0.6 - CentOS 7.x, Mageia 7.1

joseme

  • Full Member
  • ***
  • Posts: 128
    • Logosoft sistemas
Re: Read data changes from Firebird database
« Reply #3 on: June 01, 2012, 05:10:44 pm »
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?
un aporte a la comunidad:
http://pascalylazarus.blogspot.com/

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Read data changes from Firebird database
« Reply #4 on: June 01, 2012, 07:55:14 pm »
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

  • Full Member
  • ***
  • Posts: 223
Re: Read data changes from Firebird database
« Reply #5 on: June 01, 2012, 08:13:29 pm »
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.
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

mig-31

  • Sr. Member
  • ****
  • Posts: 284
Re: Read data changes from Firebird database
« Reply #6 on: June 01, 2012, 08:36:41 pm »
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

Lazarus 2.0.6 - CentOS 7.x, Mageia 7.1

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Read data changes from Firebird database
« Reply #7 on: June 01, 2012, 09:25:54 pm »
Quote
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

  • Sr. Member
  • ****
  • Posts: 284
Re: Read data changes from Firebird database
« Reply #8 on: June 03, 2012, 12:34:32 pm »
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.
 
 
 




Lazarus 2.0.6 - CentOS 7.x, Mageia 7.1

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Read data changes from Firebird database
« Reply #9 on: June 03, 2012, 12:43:20 pm »
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

  • Sr. Member
  • ****
  • Posts: 284
Re: Read data changes from Firebird database
« Reply #10 on: June 03, 2012, 12:57:11 pm »
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.
Lazarus 2.0.6 - CentOS 7.x, Mageia 7.1

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Read data changes from Firebird database
« Reply #11 on: June 03, 2012, 01:21:51 pm »
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

  • Sr. Member
  • ****
  • Posts: 284
Re: Read data changes from Firebird database
« Reply #12 on: June 03, 2012, 01:40:46 pm »
I added this line
SQLTransaction.CommitRetaining;

to the code, but it still doesn't work.
Lazarus 2.0.6 - CentOS 7.x, Mageia 7.1

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Read data changes from Firebird database
« Reply #13 on: June 03, 2012, 07:01:51 pm »
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

  • Sr. Member
  • ****
  • Posts: 284
Re: Read data changes from Firebird database
« Reply #14 on: June 03, 2012, 08:18:50 pm »
Thank you.
With this parametr Refresh of dataset works.

Code: [Select]
SQLTransaction1.Params.text:='isc_tpb_read_committed';
Lazarus 2.0.6 - CentOS 7.x, Mageia 7.1

 

TinyPortal © 2005-2018