Eof returns True using ADO in Delphi with Access

Hi,

I am using Delphi 7 and have two applications connected to same access database. The applications connect to access using ADO and JET

The first application inserts a record in the access database and the second application reads the database in a loop. In the begining of the loop I close and open the dataset to that any newly inserted records are picked up.

The problem I am facing is that when after the first application inserts one record, it is seen by the second application on the second loop which means that the dataset has closed and opened twice. The EOF property returns true the first time. Any ideas why it might happen?
SecureMetersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Emmanuel PASQUIERFreelance Project ManagerCommented:
You don't say what DB type is it, so it's difficult to say where this problem comes from.
Your DB is probably cached somewhere, either when the first app is writing the data (or ADO, or the DB) or it is the second app that is caching the data being retrieved. Or you are mistaken and the DB was already opened on the first loop.
To be sure of the first case, you must use BeginTrans of TADOConnection before writing, and CommitTrans after posting data.
On the second case, simply call Refresh method of the DataSet should be enough instead of closing/reopening the DB connection.

Whatever, that's not a reliable way to communicate between applications. You should be happy already that it works on the second loop. Is your second application doing that all the time ? If so, you are killing the DB CPU, network bandwidth etc... doing nothing most of the time.

I suggest your second application wait for an event from the first application to tell it to read the data just written. There are many methods, among which TCP/IP is the easiest :
open a server socket in 2nd app when started, and a client socket in the first app when you have commited the data. There is even no need to send anything, if you want, just the fact that you receive a connection request on the server side can tell you that you must read data. Then close the received connection on the server side and you are done until next time.
0
BardobraveCommented:
mmm... I don't know how you coordinate both apps to one open/close DB based on an event of another, but to me it seems a sinchronizing problem. Maybe your apps open/close DB connection BEFORE the pulling of the data do the DB in the first app actually takes effect, so the first loop arrives empty and the second one sees at last the data.

You can try a non empty database and see if records are arriving always "one loop later" to confirm that.
0
SecureMetersAuthor Commented:
Thanks for bothe responses. I have made sure that the data from the first app is definitely in Access database before the second application reads it. As suggested by Master my applications already have a windows message between them. The first application is doing AppendRecord which posts the record and then the event/ message is triggered. As soon as the message is received by the second application the loop starts. But the problem still exists.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Emmanuel PASQUIERFreelance Project ManagerCommented:
Master is not my name ;o)
Have you tried the BeginTrans / CommitTrans in your 1st app, and Refresh in the second app instead of Disconnect + Reconnect ?
Even if it does not solves the problem it will probably be better.
0
BardobraveCommented:
I supose it can sounds weird but... do you use same recordset name in other part of your app? In this case variable reference to the object could be messing things up and pointing first to a previously used and depleted object before finding its way home and starting to work.
0
SecureMetersAuthor Commented:
I tried BeginTrans / CommitTrans in my first app but it did not help.
No Bardobrave. My second app awake only on trigger (and a timer explained below) of this message and I am not using this component reference anywhere else in my app. If if I did the EOF should not be TRUE?

Basically the second app has a thread. The execute event has code below. The function ProcessDatabase has the EOF loop. The WAIT_OBJECT_0 + 1 is triggered either from my first app or on a Timer. To test - when I run it the first time, there are no records in access database. My first app inserts one records and sends a message. The second app gets the message but finds EOF true. Soon the timer is triggered and then the second app finds EOF false

********************************
  while not Terminated do
  begin
    RetVal := WaitForMultipleObjects(Length(mEvents), @mEvents, False, EVENT_TIMEOUT);
    TLogger.Log(LOGTYPE_DEBUG, 'DataPoll INTO EXECUTE RetVal = ' + inttostr(RetVal) );
    case RetVal of
    WAIT_TIMEOUT:
      begin
        // No event signalled, wait again
      end;

    WAIT_OBJECT_0 + 0:
      begin
        // Quit event signalled
        TLogger.Log(LOGTYPE_DEBUG, 'DataPoll thread quit event received');
        break;
      end;

    WAIT_OBJECT_0 + 1:
      begin
        TLogger.Log(LOGTYPE_DEBUG, 'DataPoll received polling event');
        ProcessDatabase();

      end;
    end;
  end;
*****************************

I just feel that there is some sort of delay here with ADO?
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
can you try with a sleep before ProcessDatabase ?
maybe there is a process related to ADO or DB that need the occasion to update things between your first app signal and the second app accessing the DB ?
or better yet, between Close and Open of the DB in ProcessDatabase ?

try first with sleep(1000) , and if it works reduce to sleep(1). 1ms delay will not hurt if that solves the problem
0
SecureMetersAuthor Commented:
Sorry epasquier regarding your name earlier.

I tried sleep as you suggested but unfortunately does not work. Never had such problem with Delphi.
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
I doubt Delphi has something to do with it. ADO is messy.
What about DataSet.Refresh ? it's supposed to make sure that the Dataset is up to date
0
BardobraveCommented:
If you want to know if it's a delay problem try to put a confirmation message to screen before ProccessDatabase. The user interaction is enough slow to confirm there's or not a delay problem.

I'd also try to explore database on the first call while message is stopping execution to confirm that data is already where it's suposed to be (maybe all of we are watching in incorrect direction)
0
SecureMetersAuthor Commented:
I tried Dataset.Refresh - does not work

Instead of a confimation message, I tried putting sleep(10000) because its a thread and that did not work either.

I did try switching off the message from the first app totally and inserted a record (from the first app). the second app picked it up on the first timer trigger without problem. I then tried enabling the message and disabling the timer in the second app and that gets picked up as well without problem. The problem happens only when both are running.

0
Emmanuel PASQUIERFreelance Project ManagerCommented:
now that is fun. are they on the same thread ? how did you implemented the timer event ?
0
SecureMetersAuthor Commented:
Sorry want to correct myself here. Works evertime on the timer when message from first app is switched off but does not work everytime on the message when timer is switched off.
0
SecureMetersAuthor Commented:
The timer is on the main form. It sends a event using SendEvent which is trapped by the execute event of the thread (code in previous post).

Yes there is only one thread.
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
so to be sure :
- DB process works always when triggered from timer
- but not from the other app message, which is closely related to the DB update, the timer being on or off doesn't matter.

Can you try with a sleep(1000) in the first app before signaling the event ?
0
senadCommented:
Hit : ADOQuery.Requery;
That should do...
0
SecureMetersAuthor Commented:
Yes tried that. Unfortunately sleep(1000) does not work but sleep(4000) works 90% of times. I could increase this time and make it 5 seconds but I am sure it will not be acceptable to wait for 5 seconds before the second app starts polling.
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
hum you're right about Requery if the Dataset is a query, because Refresh does not always work for queries (says Delphi help)... Like it was too hard to implement Refresh as Requery for those...
0
SecureMetersAuthor Commented:
Yes I have always done a close and open on databases in Delphi specially these local ones. And Grids always gave a flicker because of this and then you have to use updatecontrols on forms.
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
> Yes tried that. Unfortunately sleep(1000) does not work but sleep(4000) works 90% of times.
> I could increase this time and make it 5 seconds but I am sure it will not be acceptable to wait
> for 5 seconds before the second app starts polling.
Well, that means that there really is a cache issue somewhere, one that will be difficult to control, if even possible. I'm sorry to say that Access and ADO are really not reliable solutions when dealing with that kind of hard real-time requirements.

Seriously, why do you need such real-time constraints ? why using such a complex communication mechanism between App 1 and 2 ? Are those applications running on the same computer, with a local access DB ? or might they work across a network using a server public directory for the DB ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Emmanuel PASQUIERFreelance Project ManagerCommented:
> And Grids always gave a flicker because of this and then you have to use updatecontrols on forms.
you can use Dataset DisableControls and EnableControls before and after refreshing the connection to avoid the flicker
0
SecureMetersAuthor Commented:
You are right about complex communications and they are on the same pc. But its not my design and I would not have used such communications. Actually the first app is already running at the client side and the second app needs to know if the first app has changed the database. Since the message is triggered only once (assuming the timer is off) the second app cannot afford to miss the record.
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
and what about relying only on the timer ? what is its interval ? does it really matter if the second app reacts a few seconds late ?
0
SecureMetersAuthor Commented:
I can't rely on Timer because the user can turn the timer interval to zero which means timer is not fired.
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
...
Then don't allow 0
Sometimes, users must be told that there is no other way
0
SecureMetersAuthor Commented:
My problem did not have a solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.