> true but the original OP was doing an alter table - not null i.e. DDL then
> issuing a DML update to all row values.
> He wasn't specific about when commits were done nor clear on whether users
> were live on the system (including his own numerous connections to admin the
> DB) at the time of these operations.
> Seems to me that a lot can happen with these possibilities. I like to do
> this kind of alter with noone on the DB and I know I can commit everything
> without there being open transactions on the values.

Just to answer that, when I first discovered there was a problem there
was just one user connected (the default administrator). As I
explained (in more detail) in my reply to Helen, the commits are
supposed to be performed by DAO/ODBC, and I've not had a problem with
that in the past.

I've "solved" the problem by changing how I do the update, not where.
For reasons that are not relevant here the update was being done one
record at a time. Updating all the records with one UPDATE query seems
to solve the problem. I put solved in quotes because I don't like
mysteries, and I haven't got to the bottom of this. Unfortunately I
don't have time to look into this further right now.

One thing I did discover before I had to move on is that ordinarily null
fields get reported as [null] in FlameRobin, but that didn't appear to
be the case here. At one point I paused the front-end code at the
point where the table had been altered and the transaction committed,
but before the field values had been set. I then connected to the DB
with FlameRobin, which reported the fields as being set to 0, not
[null]. Since the original symptom was that changing other fields was
being rejected on the grounds that the new field was NULL, that suggests
that the original one-by-one code was not in fact setting the field
values (even though no exceptions were being thrown). Each of those
one-by-one updates should have been wrapped in a transaction, which if
successful would leave the table between transactions with some records
having the constrained field set, and some not. Would Firebird object
to that? If so, that would explain what I was seeing, though not why
an exception didn't get raised.

Regards,

Robert.

----------


----------


No virus found in this outgoing message.
Checked by AVG - www.avg.com
Version: 8.5.339 / Virus Database: 270.12.36/2126 - Release Date: 05/21/09 06:22:00


[Non-text portions of this message have been removed]