Board index » delphi » Paradox record posted not seen by 2nd workstation

Paradox record posted not seen by 2nd workstation

This is an easy-to-reproduce problem that has me stumped...

If a workstation posts a record, and a 2nd workstation then runs a query,
the record posted is omitted from the query.  If you fiddle with the 2nd
workstation (i.e. do a GetNearest and scroll through the newly added
record), the 2nd machine does finally see the record.  After that, it is
included in the query result.

This is causing me grief, especially in light of the fact that the query is
to calculate a unique primary key value.

What's up with this?

Here's the specifics...

I have a simple Paradox table (record size about 1K, 2-field primary key, 5
secondary indexes, only about 6,000 rows or so), and a half-dozen or so
other tables in the database, not directly involved in this example.

It is on a shared directory (either NT or Win95--doesn't matter).

I have two stations besides the one that the data is physically on.  All
three machines have an alias (using UNC, per Borland FAQ), and all three are
refrencing the same NET file (again via UNC, in the root directory of the
volume where the data is located).

Local Share is enabled on all 3 machines (per Borland FAQ).

The tables are all clean--freshly rebuilt with TUtility.  No other
workstations are touching the database.

There is a single instance of my Delphi 3 app on each workstation.

Each machine has a unique PrivateDir.

I can post a new record from any of the 3 machines, and have the problem I
just described manifest itself on any of the remaining 2  (i.e. it doesn't
matter whether the data is local or not).

Occasionally (1 time in 6 or so) the newly posted record IS included in the
query results.

The query is very basic:  SELECT Max(IDPart2) FROM MyTable WHERE
IDPart1='constant'

(IDPart1 and IDPart2 are the two fields that make up the primary key)

I am running BDE 4.51

Pseudo-code for what I'm trying to do:

TTable.Insert
allow user to edit record until done
Run query
Increment Max value returned by query
Store that value in the record being inserted
TTable.Post

It works perfectly on a single workstation....

Thanks in advance for your help.  I really need it...

David Rueter
drue...@assyst.com

 

Re:Paradox record posted not seen by 2nd workstation


I don't know about the cause, but from a multiuser point of view I find
the solution a bit fragile (in that, even if everything worked
correctly, two processes could theoretically run the algorithm at the
same time and end up with the same "unique" key value).  

Although it can pose other integrity problems, it would be more secure
to store the key value explicitly in another (single-record) table, and
use locks to ensure only one process updates it at a time... that is, of
course, assuming that all stations "see" updates to existing records
instantly, which is likely.

HTH,

SPL

-------------------------------------

David Rueter a crit:

Quote

> This is an easy-to-reproduce problem that has me stumped...

> If a workstation posts a record, and a 2nd workstation then runs a query,
> the record posted is omitted from the query.  If you fiddle with the 2nd
> workstation (i.e. do a GetNearest and scroll through the newly added
> record), the 2nd machine does finally see the record.  After that, it is
> included in the query result.

> This is causing me grief, especially in light of the fact that the query is
> to calculate a unique primary key value.

> [stuff deleted]

> Pseudo-code for what I'm trying to do:

> TTable.Insert
> allow user to edit record until done
> Run query
> Increment Max value returned by query
> Store that value in the record being inserted
> TTable.Post

> It works perfectly on a single workstation....

> Thanks in advance for your help.  I really need it...

> David Rueter
> drue...@assyst.com

Re:Paradox record posted not seen by 2nd workstation


I'm aware of the weakness in the algorithm I posted--I will clean that up,
and your idea about a single record table with explcit locks is a good one.

But I'm still concerned by this problem, for what's to say that even updates
to the single-record table will be seen by workstations?

BTW:  neither doing a dbiSaveChanges in the AfterPost handler, nor setting
RequestLive to true in the query has any effect.

Thanks for your help.  Are there any other things I should look at?  How
does the BDE work:  if the query is not being run against the current
physical contents of the table, what is it being run against?

David Rueter
drue...@assyst.com

Re:Paradox record posted not seen by 2nd workstation


Implementing a more robust algorithm similar to what you suggested seems to
work, and meets my immediate need.  Thanks!

But I'm still curious as to why a query wouldn't see a record that has been
posted by another workstation, while a TTable.FindNearest WILL see that same
record...

Can anyone explain this to me?

David Rueter
drue...@assyst.com

Re:Paradox record posted not seen by 2nd workstation


Go to delphi Deli and get the TNetDataLink component. It auto updates all
users on a network whenever someone does a post or refresh.  We've used it
in our apps and it works like a dream. I think full liscens + code is $30
US.

Glad to help!
Jim  jr...@shentel.net

Re:Paradox record posted not seen by 2nd workstation


In article <6jhp43$9v...@news01.deltanet.com>, "David Rueter"

Quote
<drue...@assyst.com> writes:
>I'm aware of the weakness in the algorithm I posted--I will clean that up,
>and your idea about a single record table with explcit locks is a good one.

>But I'm still concerned by this problem, for what's to say that even updates
>to the single-record table will be seen by workstations?

>BTW:  neither doing a dbiSaveChanges in the AfterPost handler, nor setting
>RequestLive to true in the query has any effect.

I've posted to this type of question before.  The only method that I have found
that works 100% of the time is to use a separate TDatabase for the table in
question and close it after the post.  That actually forces the buffers to
flush.  Otherwise, corrupted indices and missed data.... My application was
multiple workstations checking every few seconds for a message in an e-mail
like application.  Checking every few seconds clearly put the BDE to a hard
test, but it will work if you use the TDatabase solution as above.
/js

/js

Re:Paradox record posted not seen by 2nd workstation


came late in at this one and can't see original question,
did anyone try to call Table/Query.'refresh'?
christi...@lantic.co.za

Quote

Other Threads