Not sure whether ZMSQL supports non-ASCII characters in field names. Look at the SQL Tokenizer, TjanSQLTokenizer.GetToken in unit janSQLTokenizer: it analyzes the SQL string, detects the command, the field names, the order-by field names etc. While scanning over the SQL string it reckognizes these characters: single quote, comma, 'A'..'Z', 'a'..'z', '0'..'9', brackets, '+', '-', '*', '/', '=', '<', '>', space. Non-ASCII characters are not in this range and will terminate for example the field name extraction. However, when the field names are single-quoted the scanner accepts every byte until it finds the closing single quote - so, non-ASCII field names may be detected here.
But this is not all; further processing must handle these characters correctly as well. In procedure TZMQueryDataSet.InsertDataFromJanSQL, there is a line converting a string from a guessed code-page to UTF8. I don't know whether this applies to fieldnames or fieldvalues, but it is an indication that janSQL (on which the ZMSQL is based) internally works with ANSI codepages. Therefore, my idea is that you should try to test the SQL with field names converted to the codepage of your system. You are from Western Europe? Then CP1252 could be correct.
Next question: How to convert the spreadsheet to CP1252? This is answered together with your other question about how to convert a spreadsheet file to csv directly: For this you must read the spreadsheet with the TsWorkbook method ReadFromFile() and then save it as a CSV file by specifying sfCSV as output format in WriteToFile(). By default, the CSV file is written as UTF8, but you can specify any other code page in the CSVParams record.
Therefore, you could use the following code to convert an xls file directly to a CSV file in your system's codepage. Load this into ZMSQLDataset, and repeat your tests.
uses
fpsSpreadsheet, fpsTypes, fpsCSV, xlsBIFF8;
procedure TForm1.XlsToCSV(XlsFileName, CSVFileName: String);
var
workbook: TsWorkbook;
begin
workbook := TsWorkbook.Create;
try
workbook.ReadFromFile(XlsFileName, sfExcel8);
CSVParams.Encoding := 'cp1252'; // Write the CSV with codepage 1252 (Western Europe)
workbook.WriteToFile(CSVFileName, sfCSV, true);
finally
workbook.Free;
end;
end;