Subject SV: [firebird-support] Firebird caching query results
Author Svein Erling Tysvær
>In my environment I have a software which insert new rows on a certain table. This software and the firebird server is running on the same machine.
>I have a software running on a client which query the the server.
>The problem is there are some sort of cache, because I'm getting the old rows. If I query the database from the the server works fine (I get the new rows),
>but when I'm in na cliente I always get the same old rows from hours ago.

Your problem is not related to any cache, it is related to Firebird using versioning of records rather than locking. One version of a record may be visible to one transaction, whereas another version of the same record is visible to other transactions. This is a feature that gives you as a user more control.

Transactions are fundamental to Firebird, almost everything you do happen in a transaction. Depending on how you set it up, the transaction may either see records as they were when you started the transaction, or the last committed version. In many cases, it is important to make sure transactions are not running indefinitely - e.g. in our system things are rolled back one hour after the transaction started so that people having left a transaction open doesn't hinder Firebird from doing garbage collection. Systems under heavy load will require shorter transactions, whereas tiny systems may not notice any problems even with long-running transactions.

The simplest answer to your particular problem, will be to commit your transaction and then reopen the query. But it would be better if you read more about transactions in Firebird.

Another thing that may be of interest, is events. This can be used to notify your client that things have changed, so that the client can take appropriate action and reload data. Though that is a more advanced subject that can wait until you master transactions.