At 04:48 PM 26-03-02 -0500, Louis Kleiman wrote:

>If you are using the BDE, then turn on SQL Monitor and see what UPDATE
>statements are being sent by each client. The BDE will only update the
>fields that were actually changed by each client. So, if User A changes
>first name, and User B changes last name, then it is extremely possible
>that both updates will make it into the record because User A's UPDATE
>looks like:
> UPDATE table set FirstName = 'UserAFirstName' WHERE keyfield = keyvalue
>and User B's UPDATE looks like
> UPDATE table set LastName = 'UserBLastName' WHERE keyfield = keyvalue

Well, it's an interesting proposition but it's completely false. Updates
in Firebird and IB happen at row level. UpdateWhereAll, etc., affect the
SQL that is sent across the wire by the client, but they don't change the
architecture of the database! The BDE doesn't magically somehow allow two
transactions to update the same version of a row because they happen to be
modifying different columns.

As soon as one transaction requests an update on version n of a row, any
other transaction will be unable to update that version of the row, so your
proposition is absurd. There is only one way that separate transactions
can have access to the same row version and update it independently of
transaction control: and that's if the database engine thinks each
transaction is accessing a different database....the connection path string
bug.

Originally, Joe Martinez said:
<<The connection strings look good. One of them is on the server,
specifying the data file directly by path. The other person is on a
client, accessing it by the same path, but preceeded by the server's IP
address and a colon.
Details:
================
Firebird 0.9-4 for Windows

>>

The problem here is that you are using a very old version of Firebird,
which had not yet fixed the connection path bug. From your description,
your TCP/IP might well be using the "bad" syntax that causes this problem
to surface: since you said the remote client was "accessing it by the same
path, but preceeded (sic) by the server's IP address and a colon". So here
is what you possibly have:
User A (local): c:\databases\MyDb.gdb
User B (remote): 999.999.999.999:c:databases\MyDb.gdb

or this variant:
User A (local): c:databases\MyDb.gdb
User B (remote): 999.999.999.999:c:\databases\MyDb.gdb

So, that is one scenario that will cause the exact problem you
describe. It is serious - the corruption which it causes is usually
irreparable by conventional means.

In this scenario, don't overlook the possibility that someone was
simultaneously accessing the database using another client (e.g. IBConsole)
and causing the same problem outside the control of your application.

I would want to examine the BDE path setup for EVERY user who has ANY sort
of access to this database, including the boss, if you gave him a DB access
tool, and anyone else who can connect to the database outside the control
of your application.

Now, if you can eliminate that one, move on to the next possibility: that
c:databases\MyDb.gdb is pointing to a network share and/or a mapped
drive. Is this possible?

Another possibility is an architectural bug that you can inadvertently
create yourself. In a BDE app connecting to a database, you should use a
TDatabase for the database connection and point its DatabaseName and Alias
properties to a BDE alias, e.g. Then, for all datasets (TTables in your
case) should pick up their DatabaseName property from the TDatabase
(selected in the IDE's property editor, not typed in). This ensures that
all datasets are involved in a single connection and transaction context.

However, Delphi will allow you to type a path name directly into the
DatabaseName property of your TTables. In this case, the TTable will make
its own connection (DatabaseName + TableName) and not operate through the
connection/transaction context that is being used by the rest of the
application.

This feature was *not* designed for native database work: it is there to
enable the application to handle non-database data sources like
spreadsheets and text files as if they were database tables. When you use
it for a table in the same database, you have potentially, under the
surface, a "bad path string bug" situation within one single instance of
the application.

I've always seen it as a bug that Delphi cannot check for this situation
and prevent it; but there's a valid counter-argument that TDataset is a
virtual class and so it is up to the programmer to take care of such
anomalies as arise through polymorphism.

fwiw, Firebird 1 won't let you have a local connection and a TCP/IP
connection simultaneously. If you have a client which acesses the server
locally in a TCP/IP network, you *must* use a local loopback connection. A
Windows local connection has to have exclusive access.

There is another dimension that hasn't been mentioned so far: the BDE
version. The InterBase driver in Delphi 4 and 5 supports InterBase
5.x. The one that shipped with Delphi 4 had known bugs. The one with D5
had known bugs. Neither was ever certified for use with ODS-10 databases
(IB 6.x, Firebird...) Even the BDE 5.2 IB 6.x driver that shipped with
Delphi 6 has known bugs (different ones!)

I don't think it is very sensible of you to have deployed an early beta of
Firebird into production. You should upgrade your customers to Firebird
1.0 without delay, to avoid a large number of known bugs in that old
beta. The obvious architectural flaws (BDE and TTables) you plan to
address...but there is a good chance that your immediate problem will show
up as an exception with Firebird 1.0.

regards,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________