Hi Helen,

No, it has nothing to do with not committing transactions (at least not
in my code). The transactions are supposed to be handled by DAO/ODBC,
ie each step of everything I do in the front-end code gets wrapped in a
transaction. Therefore there's a transaction around the table
modification (which gets committed), and there are transactions around
where I set the new field values (which get committed). I've checked
this in the past with Wireshark. That's not to say there isn't a
transaction-related bug in one of these libraries which has only now
revealed itself (after many years of adding new features).

When working with FlameRobin I do the transactions using the tools provided.

Please rest-assured that the front-end application traps all exceptions
and responds appropriately; it was an exception trap that alerted me
here to something not behaving as expected.

As for ugly workarounds, I prefer to avoid them wherever possible. If
I was satisfied with such things, I wouldn't have asked for help.

Regards,

Robert.

Helen Borrie wrote:

> At 10:17 PM 19/05/2009, you wrote:
>> I have a table called "Readers" in a Firebird 2.1 database (FlameRobin
>> reports the server version as "WI-V2.1.0.17798 Firebird 2.1"). In the
>> latest version of the front-end software driving this database I update
>> the table using the following SQL:
>>
>> ALTER TABLE "Readers" ADD "AlwaysMonitor" SMALLINT NOT NULL;
>
> ...and COMMIT the transaction as soon as it has performed the DDL? This is essential, before you proceed to perform any DML on the existing rows.
>
>> Since the field (like others in the table) must not be NULL, the
>> front-end software then updates all existing records to have a value of
>> 0, and this update is successful (no errors are reported, and FlameRobin
>> shows the field as being set to 0, not NULL).
>
> At best at this point (hoping you *did* commit the transaction that performed the DDL before you tried to update the data), you have a transaction with uncommitted DML. As long as it is uncommitted, it remains visible only to the transaction that performed it. That's a problem for you if you don't understand that that is how a transactional database system is designed to behave.
>
>> However, if I then try to update any other field, I get an error
>> reported. DAO reports it as error 3197, but FlameRobin reports:
>>
>> The insert failed because a column definition includes validation
>> constraints.
>> Engine Code: 335544347
>> Engine Message:
>> validation error for column AlwaysMonitor, value "***null***".
>
> Different transactions. An INSERT is not an update. However, the other transactions are aware that the new column and its constraint exist, so the validation fails in the newer transactions in order to preempt an inconsistent state.
>
>> The problem is that AlwaysMonitor isn't NULL; it's 0.
>
> To the later transactions, it is still null.
>
>> Note also that
>> there is no error message reporting other similarly constrained fields
>> as being set to NULL. However, if I edit the value of AlwaysMonitor
>> (eg I change it from 0 to 1), and then change whatever field I really
>> wanted to change, all is OK; I can even change the value of
>> AlwaysMonitor back to 0 afterwards.
>
> Lessons learnt: totally separate DDL from DML; perform *and commit* essential DML (such as populating constrained new columns) *before* admitting access to other transactions; exceptions ultimately are the way that the database protects the consistency of data state and interpreting them is a vital part of application design and workflow.
>
>> The front-end software is able to work with other database servers. It
>> modifies the "Readers" table using the same method when working with
>> MySQL (the only difference being the delimiters in the SQL), and
>> everything works as intended.
>
> This isn't MySQL: it's an ACID-compliant, transactional database management system. The MySQL you're talking about is not ACID-compliant and can't protect itself from consistency violations; and it's non-transactional. (Some of its non-free backends do both consistency protection and transactions, however.)
>
>> Can anyone tell me please why Firebird is having problems with this
>> table modification, and more importantly, what I should do about it?
>
> Learn to appreciate the importance of consistency and it will become clearer why MySQL's behaviour has led to your misconception that consistency violation exceptions are "problems to be worked around".
>
>> I need to modify other tables in a similar way, so the clunky workaround I
>> discovered is not really an option.
>
> Clunky workarounds to fix avoidable problems are not a safe practice at all. Stick to the road and watch for the signposts!
>
> 1. Perform structural changes - that's *any* DDL - with exclusive access, as Owner of the object, in their own transactions and commit immediately.
>
> 2. Follow up immediately with any requisite DML (like your population of a non-nullable new column) in its own transaction and commit immediately.
>
> 3. When there is existing data, always be prepared to catch exceptions, interpret them and provide appropriate action (roll back and step back).
>
> Note, if you're planning to impose referential integrity constraints then there is potential for existing data to be in violation. For this type of task, there's a Step Zero: to check and fix up data in columns that will be involved in the constraints - and commit the work before you proceed to Step 1, of course!
>
> ./heLen
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.339 / Virus Database: 270.12.34/2122 - Release Date: 05/19/09 06:21:00
>

----------


----------


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


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