At 09:02 AM 11-09-02 +0000, you wrote:

>WHAT'S COMMITTING? How do I commit? I'm sure I didn't commit.

Here cometh a Longish Lesson.

The typical client-to-server task goes like this:

The client starts a transaction. This tells the server that the client
wants to send it some statements.

The application sends a SELECT statement to the API (this is the library of
functions called gd32.dll if you are a Windows client, or gds.so on Linux).

The API queries the database to validate the query (this is encapsulated in
Delphi as the Prepare procedure).

If the statement is valid, the client library asks for the result set. The
server sends back the result set (or a portion of it, if the application
requested less than the whole set).

The application gets the result set and (usually) buffers it (although some
specialised components, notably IBO, can also manage an unbuffered result set).

Your application decides to perform some DML (update, insert or delete) on
one of the rows represented in its buffer...for example, a Delphi app calls
Insert, Edit or Delete on a query object. Let's say it calls Edit(). At
this stage, although Delphi is treating the buffered set as if it were a
"live" row in the database table, in fact it is just an image of that row
in the client memory. The server doesn't know yet that the row is being
edited.

The user finishes editing the row and calls Post (in Delphi) which sends an
UPDATE statement to the client library. Now, the client library sends the
statement across to the server...if it can...and the server does these four
things:
1. it validates the statement and rejects it if it isn't valid, otherwise
2. it locks the current version of the row in the database to prevent
other transactions from attempting to update or delete it
2. it takes that transaction's "old" view of the row and writes a delta of it
3. it makes a new version of the row and stores it in a holding area in
the database

At this stage, the database itself still has the old version of the row as
the current row. Other transactions still see this old version if they
query it, but they won't be able to edit or delete it.

Now, in the application, other things can go on happening within that same
(as yet uncommitted) transaction, e.g. edits to other rows, inserts,
operations on other datasets within the same transaction....

Eventually, at a practicable moment, the application needs to commit all of
the work in this transaction. To do this, it calls Commit or
CommitRetaining (in Delphi). The API responds by sending a COMMIT WORK
statement telling the server to commit the new record version(s) still
being held in the holding area for this transaction. All being well, the
commit causes the held record version to become the current version of the
row; and the delta becomes obsolete. Some time later, the background
garbage collector will come along and clear those obsolete versions.

Now, if the posted work can't be committed because of a locking conflict
(another transaction posted changes that altered your transaction's
original view, which might happen in some transaction isolation conditions)
then it just stays as posted. The server doesn't terminate the
transaction; the API doesn't terminate it either...not until the
application tells it to.

The only way to terminate a transaction other than committing it is to roll
it back (call Rollback on the transaction object in Delphi).

Some configurations of Delphi transaction components do something called
AutoCommit. This causes the Post of a DML statement and the Commit of the
transaction to occur transparently in one hit. However, the work still
won't get committed if there is an error or a locking conflict, so your app
has to handle the exception to ensure that the task gets resolved safely.

Now - getting back to your experiences with DDL statements. When you
submit these interactively using a tool such as IBConsole, you'll generally
have to commit them explicitly, since you are using a dynamic SQL interface
in these tools and SET AUTODDL ON is not valid outside of ISQL or DDL
scripts. (In fact, IBO has a ServerAutoCommit flag that you can set for
DDL statements in your own applications; but IBConsole is built with IBX,
not IBO).

Usually, the desktop tools will start any transactions you need, without
your having to start them explicitly yourself. In general, you have to
commit your work yourself, since the tool doesn't guess about when you are
ready to commit work. You >>post statements<< and you >>commit
transactions<<. You can post many statements inside a single transaction
context; when you then commit the accumulated work, either all will
succeed or all will fail. I suspect this accounts for your observations
that sometimes your statements "take" and sometimes they don't.

isql is a desktop tool, too; and it happens to be set up to always start a
transaction for a DDL statement and to commit it immediately if SET AUTODDL
is ON.

heLen