Subject Re: [firebird-support] Error: value in SQL dialect 1, but as an exact numeric value in SQL dialect 3
Author Helen Borrie
At 02:41 AM 21/09/2010, you wrote:
>can anyone explain, what the above exception means in detail ?

It looks like an ODBC-specific interpretation of one of several error conditions that occur when you try to do an operation involving a numeric type that is not supported in a Dialect 1 database.

>It appears when I try to commit a dataset with a bigint field.

A Dialect 1 database cannot store a BigInt.

>How can I solve this ?

First find out whether the database is Dialect 1 or Dialect 3, using gstat -h. If it is Dialect 3 then the problem might be simply the case that your ODBC driver is using Dialect 1.

If the database really is D1 then it depends on how you want to solve it. If you don't want to convert the database to dialect 3 then don't pass data types that are not supported in a dialect 1 database.

To use BigInt, you need to convert the database to Dialect 3. This can be quite a big job. The recommended technique is to extract the metadata from the database into a script (using isql or a more friendly tool), modify the field and domain definitions carefully (you have to watch out for other data types besides numeric ones, e.g. DATE !!) and create a new, empty database under the v.2.1 server. After that, use a data-pumping tool to pump the data from the D1 database to the new one.

To help you figure out what you need to look at, download the original Migration Guide from the downloads area of the IBPhoenix website, viz., The file you want is - you must RIGHT-CLICK on that file in your browser and select a "Save As..." option. (That server does not support rendering acc. to MIME type.)

CAUTION :: IGNORE all the suggestions in that document about using the "transitional dialect 2". It never worked properly and is a recipe for data corruption.