Firebird database with charset NONE lazarus / 16-Databases ---------------------------------------------------------------------- marclebrun · 2021-06-23 06:20 Hi  There is a 20+ years old database in a company. The server is Ubuntu 12.04 using Firebird 2.5. And all the applications are running on Windows 10. The database has been created without specifying any charset. Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT rdb$character_set_name FROM rdb$database; => "NONE" There is an application written in Lazarus 1.4.4 using the old FBLib library from Alessandro Batisti https://github.com/graemeg/firebird- lib When connecting, this application specifies a charset of ISO8859_1 Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i DB := TFBLDatabase.Create(nil);DB.CharacterSet := 'ISO8859_1';... So far so good, but... I want to migrate this application from Lazarus 1.4.4 to Lazarus 2.0.12. No matter what charset I use when connecting, the result is the same : all characters with accents are replaced with question marks. Exemple : Code: [Select]H?l?ne J?r?me Jo?l I also tried using the TIBConnection from the Lazarus components but the result is exactly the same. It looks like something has changed between Lazarus 1.4 and Lazarus 2.x regarding character encoding...? I guess I will need to recreate this (big) database and find a way to convert the existing data from its original charset to UTF8. Is it possible to convert the charset while restoring a backup ? Is there another solution ? Many thanks for your answers Marc ---------------------------------------------------------------------- Zvoni · 2021-06-23 06:33 Maybe this can shed some light? https://stackoverflow.com/questions/32249389/convert-character-set- from-iso8859-1-to-utf8 ---------------------------------------------------------------------- Fantablup · 2021-06-23 06:47 Look at this link http://www.destructor.de/firebird/gbak.htm They added two repair fixes in 2.5 Look at "Repair malformed" Hope it helps. I have not ried it. Seems like the only solution. ---------------------------------------------------------------------- marclebrun · 2021-06-23 07:00 Thank you both for your replies. I had already seen that discussion on stackoverflow but the database is so big that I cannot just recreate some columns and migrate the data... I will check the page about gbak, I hope I will find something useful. I also read about FBCopy, do you guys know if it works well for this kind of issue ? Thanks  Marc ---------------------------------------------------------------------- Fantablup · 2021-06-23 07:08 Before version 2.5, they had the fix outside Gbak, but has included it in 2.5. It says that should fix with a database with character set NONE. ---------------------------------------------------------------------- Zvoni · 2021-06-23 07:20 Quote from: marclebrun on June 23, 2021, 09:00:53 amThank you both for your replies. I had already seen that discussion on stackoverflow but the database is so big that I cannot just recreate some columns and migrate the data... Marc Why not? Since it's a one-time thing... As they wrote on SO: Export the DDL, tweak the DDL with the "new" charset create the new Database dump the old data (data only) from the old DB import it into the new DB Test/QA the new Database Roll out ---------------------------------------------------------------------- marclebrun · 2021-06-23 08:11 Quote from: Zvoni on June 23, 2021, 09:20:00 amWhy not? Since it's a one-time thing... Because the database has 362 tables, and some of them have more than 200 fields, ... I know it's not well designed  What tool would you use to dump the data from the old DB to the new one ? ---------------------------------------------------------------------- Zvoni · 2021-06-23 08:22 Since it's FireBird, FlameRobin should be the first try Otherwise DBeaver EDIT: 362 tables? That's..... cute.... Just looking at the main schema of our IBM DB2 i see 1100 physical Tables (not counting views and such) and some 700 Schemas, each with its own number of tables..... ---------------------------------------------------------------------- devEric69 · 2021-06-23 09:37 Hello, To export (in *.sql text-file) just the DDL statements of the database structure (synopsis of the database's scheme), and then to export the structure and its data in order to reimported those data into a new database structure - for example - I use fbsql (incuded in IBX for Lazarus, an IT solution from MWA Software, dedicated to firebird development). It works well for me (but I've never tried it on such a big database). Edit (important): I've only tested this solution with firebird 3 (I see @marclebrun, that you are still in version 2.5)! ---------------------------------------------------------------------- tonyw · 2021-06-23 11:09 FBSQL is an example utility provided as source code with the IBX release. It probably won't do what you want without a small change to the source code. What FBSQL can do is to dump an entire database as metadata plus data into a text file, blobs and arrays included. This means that your entire database can be rendered as text and hence edited. What I suggest you do is to get hold of the IBX source and compile the FBISQL example, but changing line 407 of fbsql.lpr from  FIBDatabase.Params.Values['lc_ctype'] := 'UTF8'; to  FIBDatabase.Params.Values['lc_ctype'] := 'ISO8859_1'; This is so that your database is opened using its proper character set. You can then use this version to dump your database to a textfile using fbisql from the command line using (e.g.) fbsql -A -g -u -p -o If the database is local then is just the path to the database, otherwise it should include the domain name of the server ':' database name - any valid connect string will do. The above should dump the whole database to one big text file in UTF8 encoding. You should now edit the text file to replace all "Character Set none" to "Character set UTF8". Also edit the create database statement to what is needed to define the replacement database. Restore line 407 of fbisql.lpr to its original value and recompile. You can now create the replacement database using: fbsql -b -e  -u -p -i fbsql should now read in the text dump and recreate your database for you. If you are using FB4 then you should get IBX from the MWA software SVN repository. The zip has a bug in it affecting the post RC1 version of FB4 and which is only fixed in SVN. ---------------------------------------------------------------------- devEric69 · 2021-06-23 14:26 Thank you very much for this FBSQL's charset tip, Tony. ---------------------------------------------------------------------- tonyw · 2021-06-23 14:28 Quote from: devEric69 on June 23, 2021, 04:26:15 pmThank you very much for this FBSQL's charset tip, Tony. In the development version, I have already added a "-c" command line switch to allow the connection character set to be set at run time - just like ISQL. ---------------------------------------------------------------------- devEric69 · 2021-06-23 14:37 Quote from: tonyw on June 23, 2021, 04:28:03 pmIn the development version, I have already added a "-c" command line switch to allow the connection character set to be set at run time - just like ISQL. OK, that's good to know. For information, I'm using your tool (FBSQL) in order to create and manage differential *.sql patches. ---------------------------------------------------------------------- zgabrovski · 2021-06-26 06:45 Guys it is very easy to use a FB Database with no character set specified as is. Simple set the TIBConnection property "UseConnectionCharSetIfNone" to true and then set your desired character set. I had exactly the the same problem before and we developed this. ---------------------------------------------------------------------- marclebrun · 2021-06-27 06:57 Thank you all for your great help, I have made some very good progress I've been able to extract the DDL using FlameRobin and recreate an empty database with charset ISO8859_1.Then I wrote a "DataPump" tool which connects to the old database using charset NONE and to the new one using charset ISO8859_1, then it copies the data of each table. It takes some time to run, but it works.As a result, I have a new database in which the data is encoded in ISO8859_1      Now there is something I don't understand very well : In FlameRobin, I'm using ISO8859_1 to connect to the new database, everything looks perfect. Then I change the connection settings to use UTF8, I only have a warning that the charset is different from the database, but then everything still looks perfect. I didn't expect that. BUT : In my Lazarus application, things are different : If I connect in ISO8859_1 the wrong characters appear.If I connect in UTF8 then everything looks perfect. That's strange, isn't it ? Is there something I should be careful about ? Again, many thanks to all for your great advice so far !!! :-D Marc ---------------------------------------------------------------------- tonyw · 2021-06-27 07:54 When you create a Firebird database, you specify a default character set. This is used as the storage character set unless you explicitly specify a character set for a text column. When you connect to a database you specify the character set used for the connection. Firebird will attempt to transliterate, if possible, to and from the stored characters into this character set for read/write operations. If transliteration is not possilbe then you get the notorious arihmetic overflow or string conversion error. Lazarus works internlly in UTF8 for the AnsiString type and a further transliteration is required when the data is received if UTF8 is not used for the connection character set. IBX does this automatically. i cannot speak for other database connectors. When Character set NONE is speciified as the storage character set, a text string is stored as it was received i.e. using the connection character set. When read back, no transliteration takes place and is interpreted using the connection character set. It works fine as long as the same connection character set is used for read/write. For Lazarus, I would always use UTF8 in all cases as this avoids the need for transliteration. UTF8 can store all unicode characters and avoids the risk of tranliteration errors. The only downside is that storage space is always four times the characater width. Your choice depends on how much of an issue this is compared to the limitations and issues that arise from use of a single byte "codepage"character set. ---------------------------------------------------------------------- Fantablup · 2021-06-28 03:43 Why don't you create the database with UTF8 and collations with UNICODE_CI or UNICODE_CI_AI. These two is case insentive, so it is easy to do searches. You will then remove any character problems.