Very Computer

A traditional Paradox (or Delphi) application often works with the
tables on the server directly.  Or it uses a hybrid approach provided by
BDE (cached updates).  This leads to a lot of server-traffic and a lot
of open transactions.  This can be avoided.

How?  Use the local workstation "like the computer IT is, too."

Instead of using direct and constant connections to the live tables,
what if you retrieve the desired records to the local workstation,
manipulate them directly (essentially "off-line" from the server's point
of view), then, when the user presses OK, update them?

(This would consist of starting a transaction that deletes the old
records from the server, inserts the replacement set, and commits.
Say...)

(This involves having local tables that mirror the structure of the ones
on the server...  possibly without keys.)

Naturally you have to have some means of detecting when the records have
changed.  A change-counter in the master record (on the server) is a
simple way to do this.  Each time you update the records, you bump the
counter.  When you're ready to update, make sure (within the
transaction) that the value hadn't changed.

Sure, this requires a little more programming:  but it's a lot more
efficient in many typical order-handling (etc...) office situations.
Check it out!

------------------------------------------------------------------
Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259

Quote:> Fast(!), automatic table-repair with two clicks of the mouse!
> ChimneySweep(R):  "Click click, it's fixed!" {tm}
> http://www.sundialservices.com/products/chimneysweep

(1)  When you design your app to use local tables for editing, then when
you do "press 'OK'" you have one very useful advantage:  a "before and
after picture."  In other words, the database presumably still contains
a copy of the [say] invoice before you started editing it; and your
local tables have a copy of the new version.  This makes it very easy to
compare the two, to archive a copy of the record "as it was," and so on.

(2)  When you design an application like this, locking can be a bit
tricky.  With an SQL database, you simply start a transaction (both for
updating =and= reading the data) with an appropriate isolation level
specified.  With Paradox or a fileserver system, you must use table
locks.  And here, you must be a bit careful to avoid deadlocks.

An "invoice," for example, normally consists of an Invoice table and an
Invoice Line Items table.  Both must be locked while storing -or-
retrieving an invoice.  "Both must be locked at the same time, or none
at all."  In Paradox, the "System" object has a "Lock" method that has
this capability.  But the same technique does not exist (AFAIK) in
Delphi.  The same effect can be simulated by adopting a locking
heirarchy; i.e. "always lock the Invoice table first."

(3)  To clarify what I meant about change-counters (yes, someone already
e-mailed me on that!), I'm referring to a long-integer field which your
application maintains in each master record.  This value is incremented
each time your application changes the record.  {Change-counters in
line-item records are probably overkill.}

So the computer performs this process:
        *  Lock the tables (or start a transaction)
        *  Locate and examine the master record
        *  Verify that the change counter has not changed
        *  Update the change counter in the private copy and post it
           to the database.
        *  To handle line-items, delete all existing line-items and then
           replace them with the new ones.
        *  Commit.

Quote:> Ooops .. forgot a couple of points here.

You do not make any changes to the records in the master database in
this case.

[It can be advantageous to store a "last modified by" and "last modified
timestamp" field in all master records.  This makes it easier for users
to know whom they should chew out.]  ;-)

Quote:> Fast(!), automatic table-repair with two clicks of the mouse!
> ChimneySweep(R):  "Click click, it's fixed!" {tm}
> http://www.sundialservices.com/products/chimneysweep

Quote:> If you determine that the record-counter has changed, then you must:
> *  Remove the locks placed (rollback transaction)
> *  Report to the user that another user has changed the record

> You do not make any changes to the records in the master database in
> this case.

> [It can be advantageous to store a "last modified by" and "last modified
> timestamp" field in all master records.  This makes it easier for users
> to know whom they should chew out.]  ;-)

In this case, so long as the Server's data is being changed by adding the
"Variance" (ie; +1,  -3, etc.) to a field and not simply "updating" the
field with the actual field value,  it doesn't matter if someone else has
changed the data since you first extracted it or not.

However, after adding the "Variance" to the Server's Table, that field on
the Clients (duplicate) table would have to be "Updated" to reflect any
"Potential" changes caused by another user that might have occurred during
the original data acquisition and final change.

Now a problem arises which causes that the remote tables to develop an
out-of-sync condition  from the Server's table.... because remote Clients
would only be updating the fields that they have changed.... other fields
that they didn't change will never get updated in their remote tables.

At the end of the day, the Server's table would be accurate and the remote
clients would be out-of -sync.....

Now what?

Quote:> If you determine that the record-counter has changed, then you must:
> *  Remove the locks placed (rollback transaction)
> *  Report to the user that another user has changed the record

> You do not make any changes to the records in the master database in
> this case.

> [It can be advantageous to store a "last modified by" and "last modified
> timestamp" field in all master records.  This makes it easier for users
> to know whom they should chew out.]  ;-)

In this case, so long as the Server's data is being changed by adding the
"Variance" (ie; +1,  -3, etc.) to a field and not simply "updating" the
field with the actual field value,  it doesn't matter if someone else has
changed the data since you first extracted it or not.

However, after adding the "Variance" to the Server's Table, that field on
the Clients (duplicate) table would have to be "Updated" to reflect any
"Potential" changes caused by another user that might have occurred during
the original data acquisition and final change.

Now a problem arises which causes that the remote tables to develop an
out-of-sync condition  from the Server's table.... because remote Clients
would only be updating the fields that they have changed.... other fields
that they didn't change will never get updated in their remote tables.

At the end of the day, the Server's table would be accurate and the remote
clients would be out-of -sync.....

Now what?

Am I correct in this logic?

...Jim

Quote:> Ooops .. forgot a couple of points here.

> An "invoice," for example, normally consists of an Invoice table and
an
> Invoice Line Items table.  Both must be locked while storing -or-
> retrieving an invoice.  "Both must be locked at the same time, or
none
> at all."

In general, it all seems to be an awful lot of work with all kind of
pitfalls in order to save on network traffic. Just get the people
to buy a faster box! The above approach is, IMO just the wrong way
around because programming costs are always increasing whereas
hardware costs and the performance/cost factor is continually
falling. I say, stay away from it. You as the programmer will
have to sort out any problems and the customer is most likely
unwilling to fork out money to sort out problem you have
created, even if you want to argue that it is to his own
benefit. Bear in mind, that if this your way of approaching
a program he will have no comparative figures, so he will think
that you are just making up a story. If you really want to use
the approach, make him suffer first, then charge him for an upgrade.
And let me see your willingness to make (unnecessary) changes to
your program, creating new bugs, while the other work piles up.
What a programmer wants is simplicity not complications!

I have noticed this before, while Sundial's technical tips are
sound and useful, I find myself often at odds with their *approach*
to programming problems. This extends further to what I see as the
relationship between customers and programmers. You should not take
this as a comment about Sundials' actual customer relationships as I
have never had any dealings with them. It just seems to me that some
of the solutions or potential solutions offered *in this newsgroup*
forum seem to create or increase problems rather than decrease them.

Quote:> I do not see why both have to locked. Presumably, the line items
> all 'belong' to a single invoice (let's call it the header). If the
> header is locked then this is an indication that all the line items
> are not accessible either.

Quote:

> In general, it all seems to be an awful lot of work with all kind of
> pitfalls in order to save on network traffic. Just get the people
> to buy a faster box! The above approach is, IMO just the wrong way
> around because programming costs are always increasing whereas
> hardware costs and the performance/cost factor is continually
> falling. I say, stay away from it. You as the programmer will
> have to sort out any problems and the customer is most likely
> unwilling to fork out money to sort out problem you have
> created, even if you want to argue that it is to his own
> benefit. Bear in mind, that if this your way of approaching