Search code examples
databasedelphidatasetspecial-charactersfiredac

Retrieving data with single quote from dataset (Delphi)

Currently I have something like but I'm unable to retrieve my data from the dataset when it has single quote in the data

procedure TForm1.AfterConstruction;
begin
  inherited;
  cdsMain.FieldDefs.Add('ItemCode', ftWideString, 20);
  cdsMain.CreateDataSet;

  cdsDetail.FieldDefs.Add('ItemCode', ftWideString, 20);
  cdsDetail.FieldDefs.Add('Project', ftWideString, 20);
  cdsDetail.CreateDataSet;

  var S := '6x8''''';
  cdsMain.AppendRecord([S]);
  cdsDetail.AppendRecord([S, 'P01']);
  cdsDetail.AppendRecord([S, 'P02']);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  if FDConnection1.Connected then
    FDConnection1.Close;
  if FDLocalSQL1.Active then
    FDLocalSQL1.Active := False;

  FDLocalSQL1.Active := True;
  FDQuery1.Open('SELECT A.ItemCode, B.Project FROM Main A INNER JOIN Detail B ON (A.ItemCode=B.ItemCode)');
end;

My expected result are
ItemCode Project
6x8' P01
6x8' P02

The error I get when I use var S := '6x8'''' enter image description here
Source code

Solution

  • From what I can deduce from the source files, there's a bug in FireDAC in this case. If I eliminate the ON clause in the SQL, it works, even with an ASCII Single-Quote (#39). If I replace the ASCII Single-Quote (#39) with the UNICODE Single-Quote (#8216) or remove it altogether, it works.

    It seems like the parser for TFDLocalSQL (I think) has a bug when it tries to resolve the ON clause and there's an ASCII Single-Quote involved.

    You should report the error to Embarcadero at https://quality.embarcadero.com, including your source code and an explanation.