1

I am developing an application which has a simple database. All of the functions are going well but when a user is editing the database from the program, the other user cannot see the content immediately. The other user needs to close the program and reopen it for the data to appear and its DBGrid be updated with those changes form the other computers. I am using Delphi 7 for this and ZeosLib to access my Firebird database. I tried using the refresh button on the DBNavigator but it doesn't work.

The components I used to connect to the database are:

  • ZConnection
  • ZQuery
  • DataSource
  • DBGrid
  • DBNavigator

This is the code for my ZConnection and ZQuery.

object ZConnection1: TZConnection ControlsCodePage = cGET_ACP UTF8StringsAsWideField = False Connected = True Port = 3051 Database = '192.168.254.254:test' User = 'test' Password = 'test' Protocol = 'firebird-2.5' Left = 96 Top = 8 end object ZQuery1: TZQuery Connection = ZConnection1 Active = True SQL.Strings = ( 'select * from "test"') Params = <> Left = 128 Top = 8 object ZQuery1ID: TStringField FieldName = 'ID' Required = True Size = 8 end
Improve this question
16
  • 3
    Hint: en.wikipedia.org/wiki/Shift_key. In English, you use capital letters (1) in the beginning of a new sentence, (2) in the word 'I', and (3) in names (e.g. Delphi). Commented May 1, 2013 at 18:43
  • 3
    My advice: don't rush. Take your time. Make the question really good. Show us that you care, so that we too should care. Commented May 1, 2013 at 18:54
  • 1
    Try reading over our FAQ to understand what types of questions are or are not appropriate and how to make your questions good: stackoverflow.com/faq Commented May 1, 2013 at 19:02
  • 3
    This question has the same problem as your previous ones. "I have a problem. The problem is something doesn't work. I'm not going to show you what I'm doing, or give you any information to use to try and demonstrate it. Please give me a solution.". You've posted no code showing how you're interacting with the database to change it, so it's impossible to answer why the other clients aren't seeing the changes. Are you using transactions? When are you starting and ending them? How are you changing the data? How is the grid getting the data (a table or a query)? You've given no details at all. Commented May 1, 2013 at 19:27
  • 2
    Well, it's better than no code. :-) But you need to post the relevant code, not the code from every single form in your application, and reduce the code to the part that retrieves data from the database and populates the grid and the code that edits the data, and nothing more. :-) Commented May 1, 2013 at 20:26

4 Answers 4

Reset to default
3

Sounds like you're running afoul of ACID. This is a basic guarantee of SQL-style databases, that all database updates will be Atomic, Consistent, Isolated, and Durable, and is accomplished through transactions.

Specifically, you're having trouble with the Consistency and the Isolation, which ensure that an external viewer never sees an update before it's finished, even if that update contains more than one change. (The classic example is a bank transfer, which requires subtracting money from one account and adding it to another. If you only see one of these two actions but not the other one, you have bad data.)

You can think of a transaction as an independent view of the state of the database. Every database connection has its own transaction, and any changes it makes are invisible to anyone else (Isolated) until they Commit (finalize) the transaction. Depending on the transactions' isolation settings, they may remain invisible to other users even after that, if they have an ongoing transaction, until they commit their transaction and begin a new one. It sounds like your code isn't taking this into account.

If you need updates to become visible immediately, you'll want to ensure that the transaction's isolation mode is READ COMMITTED, and set up database events to send notifications to connected clients when various things get updated, so the clients can perform refreshes of their data. You'll also want to ensure that the user updates result in a Commit action right away, so that the isolated data will become available.

Since I don't use ZeosLib, I can't explain all the details of how you'll need to set this all up, but this is enough to get you on the right track.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

OIC now I understand what those atomic, consisted, isolated and durable means. Sorry if I posted this I already have read this on the zeos lib tutorial but I can't comprehend what the tutorial is referring to.
0

I suggest that you add a timer to the form which displays the grid. Set the timer so that it fires its OnTimer event once a minute (or longer). In this event, close the query then reopen it. This way, everyone always gets current information (albeit a minute late).

with qWhatever do // this is the query which is connected to the grid try disablecontrols; close; open finally enablecontrols end;
Improve this answer

5 Comments

This ist not an elegant solution and it makes a lot of problems. Performance would be bad, a lot of database-traffic and what, if the user is just editing some data? Don't do it that way!
Where do I put the timer in that code? Can I make it less than a minute? Let's say 15 seconds? Thanks for this post I will try this out also.
@ChunkChunk: You put the timer on the form in which the grid is displayed.
@ChunkChunk: This is one of the fallacies of personal software. I work with an ERP program: when I display data on the screen (in other words, obtain data from the database and display on a grid), the data is guaranteed to be correct to the moment it was extracted. There are no updates! So even if someone adds a new record somewhere, I won't see it unless I issue a new query.
@No'amNewman oic, I'm just beginning to learn programming and I started on this project, which will be mainly used for a local area network. Thanks for your post btw.
0

For a multi-user application, where clients need to receive notifications, one option is to use Firebird events to send a 'broadcast' message for every data change (SQL INSERT, UPDATE or DELETE).

Clients can 'register' (listen) for a specific message type, and whenever the Firebird server sends a message with this type, they will receive it, and run client application code, which in your case would refresh the user interface (grid).

While this can be a sufficient solution in many simple use cases, there are also some restrictions. I recently blogged about this topic here:

(I am author of middleware libraries for Delphi and Free Pascal)

Improve this answer

1 Comment

Very nice article. Thank you.
0

I solved this problem by adding this before query.

IBDatabase1.Close;

IBDatabase1.Open;

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.