Forum > Database

Error in ZMSQL with column names containing diacritics

<< < (2/3) > >>

wp:
Maybe you should try howardpc's code from https://forum.lazarus.freepascal.org/index.php/topic,46804.msg334219.html#msg334219.

maurobio:
Hi, @wp!

The solution suggested by @howardpc in the post you pointed out in fact does what I need, that is, removing diacritics from field names - but the SQL query still does not work! It keeps returning an empty record (see my modified code).

It looks like this problem is more complex than it appeared at first.  :o

With best wishes,

wp:
You replace only the non-ASCII fieldnames in the SQL text, but the fields in the dataset still have their old names. This way the result set must be empty.

To fix the fieldnames in the dataset itself, you could recreate the FieldDefs and replace the field names. But this is not straightforward because the Name field of a TFieldDef cannot be changed any more.

A relatively easy option is to read the xls file with fpspreadsheet, replace the strings in the first row by their ASCII counter-parts and then rewrite the file, before you begin your own operations.


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---uses  FPSpreadsheet; procedure FixFieldNames(SrcFileName, DestFileName: String);var  workbook: TsWorkbook;  worksheet: TsWorksheet;  c: Integer;  s: String;begin  workbook := TsWorkBook.create;  try    workbook.ReadFromFile(SrcFileName);    worksheet := workbook.GetFirstWorksheet;    for c := 0 to worksheet.GetLastColIndex(true) do    begin      s := worksheet.ReadAsText(0, c);      if s <> '' then        worksheet.WriteText(0, c, AccentedNameToAscii(s));    end;    workbook.WriteToFile(DestFileName, true);  finally    workbook.Free;  end;end;  procedure TForm1.FormShow(Sender: TObject);begin  FixFieldNames('PlantList.xls', 'PlantList1.xls');  sWorkSheetDataset1.FileName := 'PlantList1.xls';  sWorkSheetDataset1.Open;  ZMConnection1.DatabasePath := GetCurrentDir;  ZMQueryDataSet1.TableName := 'PlantList';end;  procedure TForm1.Button1Click(Sender: TObject);begin  ZMQueryDataset1.CopyFromDataset(sWorksheetDataset1);end; procedure TForm1.Button2Click(Sender: TObject);var  FAMILY, SPECIES: string;begin  FAMILY := QuotedStr(ZMQueryDataset1.Fields[2].FieldName);  SPECIES :=QuotedStr(ZMQueryDataset1.Fields[3].FieldName);   ZMQueryDataset1.SQL.Text := 'SELECT ' + FAMILY + ', ' + SPECIES +    ' FROM PlantList GROUP BY ' + FAMILY + ', ' + SPECIES + ' ORDER BY ' +    FAMILY + ', ' + SPECIES;  ShowMessage(ZMQueryDataset1.SQL.Text);  ZMQueryDataset1.Close;   // This removes the empty columns from the final result  ZMQueryDataset1.QueryExecute;  ZMQueryDataset1.First;end;
This way the result is not empty, however, does not contain the correct field values. I don't know... Are you sure that SQL SELECT is working in ZMSQL? Please test simpler queries first.

maurobio:
Hi, @wp!

I was musing here exactly that the solution should pass through FPSpreadSheetDataset itself, as you just pointed out.

I have been using TZMSQL heavily in my application in connection with FPSpreadSheetDataset without any further trouble (except for this problem with diacritics in column names).

For the time being, I will consider to simply instruct users to avoid using diacritics in column names until a proper solution (if any) can be found.

Thank you very much for your time and patience!

With best wishes,

wp:
Can you post for me a modification of the program using the non-diagcritics fieldnames to demonstrate that the SQL is working. I am asking because after modification according to my previous commit there are no diacritics any more, and I don't see why this simply duplicates the field names in the rows.

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version