Last week's episode1 concluded with the successful migration of badly encoded Hebrew from one table to the Unicode database. The next day, I tried converting another table to Unicode and hit a wall: this table contained some character fields but as they did not have (and will not have) any Hebrew in them, there was no need for the painful conversion code. But the migrator tried to convert them anyway, resulting in error messages.
When is a door not a door*, or rather, when is a string field not a Hebrew string field? Basically, there's no way of knowing, as SQL databases don't offer such meta-information. In the end, after a great deal of to-ing and fro-ing, I and CoPilot hit upon the following scheme: an external file will be maintained where the lines contain a <table name>.<field name>=<code page> structure. Only fields found in this file will be converted. The code page is important as it forces the correct encoding to be used. At the moment, it looks like everything should be encoded as WIN-1255 but I can't take the risk of assuming that every Hebrew field is 1255. Specifically the list looks like this:
accumulations.name=1255 ACTIVITIES.NAME=1255 calls.subject=1255 calls.details=1255
Don't take this as meaning that there are only four Hebrew text fields in the database! At the moment of writing this, I've only successfully converted two tables (accumulations and activities) and that was after a great deal of hard work. I wanted to see whether the sixth table (in alphabetical order) would pose any problems, as this table has two character fields that need converting and at least one more that does not need converting.
And this is when I hit another problem: there is a date field in this table, but every attempt to convert it results in an error. To quote CoPilot,
Today you uncovered the key detail we needed:
the source field is a TSQLTimeStampField, which means FireDAC is always handing you a timestamp, even when
the SQL type is DATE. That’s the heart of the whole mystery, and tomorrow we
can finally untangle it cleanly.
When you’re fresh again, we’ll sort out:
- how to reliably detect true DATE columns
- how to bypass FireDAC’s timestamp mapping
- how to force DATE‑only semantics even when the Delphi field class lies to you
You’re much closer to the finish line than it feels right now.
Unfortunately, after about an hour of butting my head against a wall, it seems that there is no option but to define the field in the new table as a timestamp. What does CoPilot have to say about this? Given everything you’ve tried — and everything FireDAC has refused to do — switching the column to TIMESTAMP is not a workaround. It is the correct engineering decision. You will save yourself hours of frustration, and your code will become simpler and more robust.
Similar but much more easily solved problems were encountered with numerical fields and blobs. Eventually all the problems of the first six tables were ironed out, and at the same time I improved certain aspects of the actual convertor program. I asked CoPilot to create a summation document of the entire process that I have saved. It's slightly more terse than I would have prepared but otherwise it's fine.
Now all I have left (he wrote hopefully) is the mechanical work involved in converting the remaining 104 tables. There shouldn't be any more surprises as all the main data types have been encountered.
(*) When is a door not a door? When it's ajar.
Internal links
[1] 2070
| Title | Tags | ||
|---|---|---|---|
| 333 | Idea for startup | Food science, Startup | |
| 549 | Another evening (2) | MIDI | |
| 810 | Changes in fortune | DBA | |
| 811 | Ordinary people | Films | |
| 1007 | A certain kind of academic recognition | ERP |
