Windows 10 Filename Handling lazarus / 34-Windows ---------------------------------------------------------------------- softair · 2022-02-08 15:58 Newby alert! I'm trying to convert / adapt some existing Delphi apps to Lazarus, and there's one issue that's got me stumped, despite endless Googling over several days. (Oh - latest Lazarus/FPC download, if that matters) I have an Access Database, the relevant records of which contain the names/full file paths of files which should be on the hard drive. My app basically reads the database, and either checks the file(s) exist, or read the files for futher information. I can happily interrogate the database and get the relevant records with a Lazarus version - so far so good. But a significant number of these filenames have "odd/foreign/accented" characters in them. And anything try to access these files just fails to find them. In Delphi, the file names are read from the database as "string"s and passed to the file handling routines like FileExists as "strings" and it all just works, however odd the international filenames.  And seemingly with Delphi , string equates to UnicodeString. But on FPC/Lazarus "string" equates to something else, and I can't yet find a way round this. I can read the filenames from the database as WideString or UnicodeString, but then the file handling procedures are defined for "string" or "UTF8String". I can't believe I'm trying to do anything remarkable and have probably missed something blindingly obvious, but can anyone point me in the right direction? TIA Bob ---------------------------------------------------------------------- Handoko · 2022-02-08 16:24 Hello softair, Welcome to the forum. I think these maybe can help you: https://forum.lazarus.freepascal.org/index.php?topic=9690.0 https://forum.lazarus.freepascal.org/index.php?topic=9583.0 https://www.freepascal.org/docs- html/rtl/sysutils/unicodeandcodepages.html ---------------------------------------------------------------------- ASBzone · 2022-02-08 16:59 Which mode are you using for your FreePascal application? ---------------------------------------------------------------------- marcov · 2022-02-08 19:26 And which delphi version (older or D2009 and newer)? Did you set any form of collation on the Access database? ---------------------------------------------------------------------- softair · 2022-02-09 10:26 Thanks, so far! 1. My Lazarus FPC units all have {$mode Delphi}{$H+} at the top. 2. Delphi 2010 and later versions 3. I don't create the database - it's done by a third party app. I don't specify an collation when I access it as far as I'm aware, and I'm relatively unconcerned about any sorting order issues. Stripped to essentials, the code I'm trying to reproduce is var filename: string ... SQLQuery!.Next; filename := SQLQuery.FieldByName('Filename').AsString; if not FileExists(filename) then memo1.Lines.Add('Missing ' + filename); And I have no control over the actual filenames, which sometimes contain accented characters (including the wierd Eastern European ones) and even some that are probably Hebrew or Kanji or whatever. Many of the files with these odd characters fail the FileExists test, even though they are present and are happily accepted by other windows apps. I've tinkered with changing the filename to UTF8String, Unicode or WideString, so far to no avail. And of course, the file name displayed in the Memo is just full of ??? marks. Any further clues welcome! ---------------------------------------------------------------------- Thaddy · 2022-02-09 11:11 Try {$mode delphiunicode} ---------------------------------------------------------------------- softair · 2022-02-09 18:57 OK. Making a little progress. If my modules all include {$mode delphi}{$H+} then 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 var filename: stringfilename := 'אדמה.test' if FileExists(filename) ...[code=pascal][/code] returns true if the file is present in the directory. Unfortunately, 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 filename := SQLQuery.FindField('Filename').AsString; gets a filename that has lots of ??? marks and FileExists returns False; I've tried setting the ODBCConnection.CharSet to 'UTF-8' , but that doesn't make any difference. So I guess the question is now "How do I get fields with string data like 'אדמה.test' out of the SQL Query? Thanks for the advice so far! ---------------------------------------------------------------------- ASBzone · 2022-02-10 00:00 Quote from: softair on February 09, 2022, 07:57:19 pmOK. Making a little progress. If my modules all include {$mode delphi}{$H+} then Have you tried {$mode delphiunicode} as mentioned earlier? ---------------------------------------------------------------------- marcov · 2022-02-10 08:31 Mode Delphi unicode is not complete enough to emulate Delphi unicode. Databases encodings are usually decoupled from system encodings, since using another databases would otherwise potentially mean recompiling the application and FPC/Lazarus to set a different encodingThat's why it is good to have an unicode main string type and controls(be it lazarus utf8 or Delphi utf16)Most database libraries (Zeos, FCL-DB) allow to set encoding somewhere in the connection. I don't know what you use in Delphi, but maybe some library auto pickups the encoding and FPC not. The first step is to determine what encoding the DB really is. Usually this is done by mucking about in the db admin tool. Then you have to pass it on to the db lib somehow. Zeos afaik allows to set codepage=xxxx somewhere in the connection properties, I don't know about fcl-db, since I only used that for small potato. Probably a bit wiki digging is called for.   Maybe that .charset thing? ---------------------------------------------------------------------- Zvoni · 2022-02-10 09:12 Quote from: softair on February 09, 2022, 07:57:19 pm 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 filename := SQLQuery.FindField('Filename').AsString; gets a filename that has lots of ??? marks and FileExists returns False; I've tried setting the ODBCConnection.CharSet to 'UTF-8' , but that doesn't make any difference. So I guess the question is now "How do I get fields with string data like 'אדמה.test' out of the SQL Query? Thanks for the advice so far! IIRC, in Delphi Unicode is UTF16 Is your sqlite-DB in UTF16? Default being UTF8 IIRC ---------------------------------------------------------------------- softair · 2022-02-10 09:38 The database is an Access/Jet database created and managed by a third party application over which I have no control. I did try $mode delphicode but failed to get anywhere - I'll have another go. I don't think anything that involves code pages is going to help, as the filenames clearly use characters from several different ones, and I have no way of determining which in any given instance. ---------------------------------------------------------------------- Zvoni · 2022-02-10 10:58 Uh, sorry. No idea why i thought it's sqlite. Look at this: https://bytes.com/topic/asp- classic/answers/676832-hebrew-text-ms-access-shows-asp UTF-8 and Codepage 65001? ---------------------------------------------------------------------- Sieben · 2022-02-10 11:30 @softair - Did you try UnicodeToUTF8() from unit System on your database strings? Or sth like this (which in fact uses UnicodeToUTF8): 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 filename := UTF8Encode(UnicodeString(SQLQuery.FindField('Filename').AsString)); Edit: or, after reading wp's answer, .AsWideString of course. ---------------------------------------------------------------------- wp · 2022-02-10 12:11 Quote from: softair on February 08, 2022, 04:58:48 pmIn Delphi, the file names are read from the database as "string"s and passed to the file handling routines like FileExists as "strings" and it all just works, however odd the international filenames.  And seemingly with Delphi , string equates to UnicodeString. Assuming that Access/Jet stores the strings as widestrings, did you try to use AsWideString rather than AsString? 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 var   filename: string  wFileName: widestring;...  wFileName := SQLQuery.FieldByName('Filename').AsWideString;  fileName := wFileName;  // this should do the conversion to UTF8  if not FileExists(filename) then memo1.Lines.Add('Missing ' + filename); ---------------------------------------------------------------------- softair · 2022-03-01 14:09 Seems I may have misnamed the topic - I'm coming to the conclusion that this has nothing to do with filenames per se. Using {$Mode delphi} {$H+}, if I have a directory with files that have names with odd accented , Hebrew or Cyrillic characters, sharp and flat signs etc., I can scan the directory with FindFirst/FindNext, and write the filenames out to a Memo control and it all looks fine. If these same filenames are in an Access Database, and you hook up a DBGrid, Datasource, SQLQuery, SQLTransaction and ODBCConnect at design time, with no code, the grid shows some of the unusual characters as question marks. Some of the accented characters are OK, others not. Changing the ODBCConnect CharSet to UTF-8 makes matters worse, and more of the accented characters are garbled. (This only seems to affect accented characters - the Cyrillic and Hebrew stuff is seemingly unchanged in either mode). Any ideas as to whether this is a problem with the ODBC driver (seems unlikely) or the Database Access routines? TIA Bob ---------------------------------------------------------------------- Thaddy · 2022-03-01 15:22 On windows (Windows 10 is default UTF-16, NOT Ansi or UTF-8)maybe the ODBC driver expects UTF-16 if it is not Ansi. It has been a while since I needed ODBC, though, since most of the time there is native support. ---------------------------------------------------------------------- SymbolicFrank · 2022-03-01 15:36 What Access version? ---------------------------------------------------------------------- softair · 2022-03-01 19:23 Don't know - the database is created by a third party app, rather than Access itself. The database is in a .MDB file. I can read it quite happily with Access 2010 - claims to be Access 2000 file format. ---------------------------------------------------------------------- loaded · 2022-03-02 04:55 Could you try utf8to... variants? 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 uses LConvEncoding; procedure TForm1.Button1Click(Sender: TObject);begin  filename := UTF8ToCP1250(SQLQuery.FindField('Filename').AsString);end; ---------------------------------------------------------------------- Thaddy · 2022-03-02 05:12 Can you give us the metadata from the field(s) that are giving you trouble? Access2000 already supports different string types for text based fields and you should be able to give us the encoding. I think the title of the subject is misleading, I guess you meant the field encoding, not the filename encoding. ---------------------------------------------------------------------- SymbolicFrank · 2022-03-02 08:50 An Access 2000 MDB is an archive that contains the whole description and source code of the database in text format, but normally you need a (depreciated) VBA version of Access to open it. You could try with a text editor, but LibreOffice can probably open it and show you the stats. Although the latest versions don't support the old database formats anymore. Microsoft has basically dropped support for it as well. You can use the ODBC driver, or upgrade to a newer format, but that's about it. ---------------------------------------------------------------------- Zvoni · 2022-03-02 09:06 http://www.grahamwideman.com/gw/tech/accessjet/access2kunicode.htm Is Unicode Compression activated in the Database? ---------------------------------------------------------------------- softair · 2022-03-02 09:38 Responses to various comments... 1. I've already said that I now know the Topic title is misleading: it has little to do with filenames as such, but that's how the problem originally manifested itself. It's now clear that the problem relates to the extraction of multilingual text fields from an Access Database via ODBC/SQL. 2. As stated, the problem can be shown just by dropping stuff on a form, without any code. 3. I have looked at the database as requested: some of the relevant text fields are compressed, some aren't. 4. I will try the UTFUTF8ToCP1250 suggestion and report back! And finally,  under Delphi, it all just works reading "string"s from the SQLQuery/ODBC , but  I would really like to use Lazarus/FPC for this exercise. Thanks for the pointers so far! ---------------------------------------------------------------------- Thaddy · 2022-03-02 11:43 Which Delphi version? That is important to help you!!! Old MS software isn't very good with UTF8, that is the wrong path. ---------------------------------------------------------------------- softair · 2022-03-03 14:41 Delphi 14.3 CE now, but over the years it's been various since Delphi 10. All worked out of the box. ---------------------------------------------------------------------- Thaddy · 2022-03-03 15:02 Not with UTF8, out of the box is UTF16 ---------------------------------------------------------------------- wp · 2022-03-03 15:16 Could you create a demo mdb file which you can upload here? ---------------------------------------------------------------------- softair · 2022-03-04 11:52 I've reluctantly come to the conclusion that I'm going to have to stick with Delphi for this exericise as it all just works. I'd used Lazarus for a couple of other exercises and was suitably impressed, but for this one, trying to get things working under Lazarus/FPC is proving too much of a lengthy distraction. Many thanks for the suggestions offered so far, but I have come to the conclusion that fixing things will not be simple! Thanks again. ---------------------------------------------------------------------- ASBzone · 2022-03-04 15:09 Quote from: softair on March 02, 2022, 10:38:49 amResponses to various comments... 1. I've already said that I now know the Topic title is misleading: ... You can change the subject to be more reflective of what you now know the issue to be related to...