Fetch ASCII string from Firebird Database

Question

I have a firebird database that has some columns of type char[100] ASCII encoding. Now I want to fetch this values using ADO.NET. In these columns I have some text with special characters like "ó,ż,ć" etc. If in connection string charset ASCII is specified i get some werid characters instead of special ones for example "óóABC" is "??ABC". If charset in connection string is UTF 8 I am getting this error:

arithmetic exception, numeric overflow, or string truncation Cannot transliterate character between character sets

From what I now in Visual Studio the default encoding is unicode. Is there any way I can fetch these values and covert ASCII to unicode?

Answer 1

ASCII only defines characters for bytes 0-127, and it only contains basic latin a-z and A-Z. The characters ó, ż and ć do not exist in ASCII. For those characters you need one of the 'extended' ASCII (or extended ANSI) character sets, which is an imprecise term for single byte character sets where bytes 0-127 map to ASCII and bytes 128-255 map to the character set specific characters. Examples include ISO-8859-1 (Firebird: ISO8859_1), Windows-1252 (FB: WIN1252) and a few dozen others.

It sounds like the data was originally stored by a driver which used connection character set NONE, but sent bytes in the default character set of the client OS. Using connection character set NONE allows the bytes sent to be stored as is. But when the characters are retrieved as ASCII by the Firebird.net driver, they will be mapped to ? as no mapping exists for those bytes in ASCII.

Similarly, when you try to cast to UTF8 in Firebird, Firebird doesn't know what to do with the unmapped bytes and raises a transliteration error. The workaround to that is to first cast to NONE, then to the correct character set (and optionally to the target character set). For example

cast(cast(yourcolumn as varchar(100) character set none) as varchar(100) character set win1250)

To fix this, you need to know what the original character set was (looking at that ż it could be Windows-1250, Firebird name WIN1250), and fix your database.

This can be done in a number of ways:

1. Fix the data in the current database, or
2. create a new database with the correct character set and pumping the data from the old to new (with appropriate casts where necessary)

Option 1 can be quite complicated, especially if blobs are involved, you may need to create new columns to copy the data and fix the character set. Option 2 is usually simpler, but as the whole database needs to copied to a new one, it may take longer if only a few columns need to be fixed.