At 02:51 PM 9/02/2005 +0000, you wrote:

>If I set a 1.5.2 database to dialect 3 using gfix and then do a
>backup/restore successfully is that sufficient to prove that I don't have
>any invalid triggers, SP, etc as described below?

No. Setting the dialect to 3 doesn't touch or validate the existing data
at all. Existing data stays being dialect 1 data unless you fix it yourself.

>http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;PAGES;NAME='ibp_60_dialect'
>
>Database Objects Will Work Even With Invalid Dialect 3 Syntax
>Another thing I discovered is that certain database objects (I've tried this
>with triggers and stored procedures) will continue to work in dialect 3 even
>though they are using functionality of dialect 1. Here is an example trigger
>that clearly shouldn't work in dialect 3:
>
>create trigger blah_trigger for blah
>before insert
>as
>begin
>new.v1="blah";
>end!!
>This trigger is valid in dialect 1. The double quotes are allowed for string
>literals. However, you will get an error if you try to create this trigger in
>a dialect 3 database.

Correct. But SPs and triggers are compiled objects. The source isn't
touched at run-time. You should be more concerned about calculations
inside these compiled objects, as they will be done according to Dialect 3
rules from this point forward. Integer division, for example, will give
different results.

>You will get the "Column unknown" error.

Sure, as soon as the engine is asked to reprocess the PSQL source code, it
will find your syntax errors.

> But, this is where it gets interesting...
>
>If this trigger was created in a dialect 1 database and migrated to a dialect
>3 database (changing the database dialect via gfix), the trigger will
>continue to work. Why you ask...

Yes - because triggers are compiled objects too. And the same traps exist.

To answer the question in your thread subject, "you can't tell". In short,
don't use either gfix or the dialect 2 route to change the dialect. There
are so many traps that something is going to catch you. Instead, use a
reliable metadata extract tool to script the database, recreate the empty
database as dialect 3, and datapump everything. This is the only reliable
way to convert dialect 1 objects and data to dialect 3.

./heLen