Neville,

At 06:48 PM 10/01/2004 +0000, you wrote:

> > >Two related tables contain beween them about 1 million records. (About 4
> > >detail to 1 master)
> > >
> > >Periodically, it is necessary to update a sizeable and well-defined
>subset
> > >of both tables, re-calculating many column values. The master and detail
> > >calculations are interdependent.
> > >
> > >Can I use a couple of TIBOQuery components to synchronously work through
> > >the required records in both tables, updating as I go?
> >
> > Why do you want to use query components for a batch update?
> >
>There are three parts to the process - selection of the correct subset of
>records, complex calculations (in Delphi), finally update of the records in
>the table. Basically, both I and the application are refugees from BDE
>Paradox. I am somewhat limited in my knowledge of the slicker alternatives
>available in IBO.

The client/server approach breaks these requirements down like this:

>selection of the correct subset of records

..is done with a selector mechanism that let's the user arrive at the
header selection as quickly as possible. The selection merely has to
provide input parameters for a stored procedure.

>, complex calculations (in Delphi),

...complex calculations are handed over to a parameterised stored
procedure. Typically, the SP takes as input the master key of the "current
header" selected by the client and performs the complete operations
(computation and updates) on the master row and the related detail rows,
and exits.

>finally update of the records in the table.

It's not good client/server design to perform recalculations in one place
(the client) and update in another (the database).

If it's possible (and I know it isn't always) to select *all* of the master
rows using one set of parameters, then the client isn't involved at all
other than to collect the parameters for "a job".

>Basically, both I and the application are refugees from BDE
>Paradox. I am somewhat limited in my knowledge of the slicker alternatives
>available in IBO.

Actually, if you break that down into "BDE" and Paradox and look at them
separately...

The BDE *does* have support for the client/server model (it has an
InterBase driver), although it doesn't support some of IB's most important
features AND its data access model works very hard to make all DBMSs behave
like Paradox. It does support stored procedures, though.

Paradox is a desktop database that does nothing but store data. It
provides no capability to crawl around inside and do stuff that the client
doesn't touch. It has very limited capability to take care of database
state and integrity. It is completely dependent on the integrity of client
applications to protect it. The database itself is a complex collection of
highly vulnerable disk files that exist independently of one another. The
"management" part of dbMs is dependent on one, separate, world-writable
lock file (pdoxusrs.lck)....and so on and so on.

And, hey, as a desktop database, Paradox is pretty wonderful, still, after
all these years.

The BDE model is built around the Paradox database engine, which was
architected for a single user on a single machine. Looping through a
dataset buffer and performing calculations is for Paradox, Access and
MySQL, because the BDE was *designed* for that desktop model. Business
rules are implemented in client applications because that's the way Paradox
does it.

Where you have arrived now is IB (a self-contained dbms that takes care of
databases independently of any client application) and IBO (which enables
you to remove the Paradox model from the equation totally).

Sure, there are lots of "slicker alternatives" with IBO, especially if you
take advantage of the native TIB_* classes. However, the real breakthrough
is the architectural one. It comes from making the appropriate separation
of function between the client and the server - until you do that, you
won't be free of the limitations of the desktop database.

Helen