How to add single(') or double(") quotes to a String in pascal?

Question

I am trying to add quotation marks " or ' to a list of strings, which will be used in a SQL query. The system is using Firebird 2.5.

This query has a not in clause and I am looking for the query to have the following format:

Select c.codes
From Table c
where c.cuit not in ("20-11111111","20-11111111","20-11111111".....to n codes)

This procedure loads the string list from a grid:

function loadCodesFromGrid(): WideString;
var
  slAux: TStringList;
  wsAux : WideString;
  stTable: String;
  qCliProv: TFXQuery;
  niCol : Integer;
begin
  niCol := 1;    
  for n := 1 to gDetails.RowCount - 1 do //load cuit from grid. The cuit in my country is like the social security number in United States

    if Trim(gDetails.Cells[niCol, n]) <> '' then
      wsAux := wsAux + iif((Trim(wsAux) = ''), '',  ',') + Trim(gDetails.Cells[3, n]);
    
    slAux :=  fxFormatQuery(wsAux ); 
       
    try
      qCliProv.SQL.Text :=
        ' Select Code' + iif(pboClients, Copy(stTable, 0, 7), Copy(stTable, 0, 9)) + ' As Code' +
        '   From ' + stTable +
        '  Where Active = 1 ';
    
      if slAux.Count > 0 then
        for n := 0 to slAux.Count - 1 do
        begin
          if Trim(slAux.Strings[n]) = '' then
            Continue;
    
          qCliProv.SQL.Add(
            '    And Cuit Not In (' + slAux.Strings[n] + ')'  );
        end;
    
        qCliProv.Open;
        //the rest of the code is not important

This function formats the query:

function fxFormatQuerySQL(pstClients: WideString): TStringList;
var
  slAux, slAuxResult: TStringList;
  niI, niLine: Integer;
begin
  niLine := 0;
    
  slAuxResult := TStringList.Create;
  slAux := TStringList.Create;
    
  try
    slAuxResultado.Add('"'); // 
    
    slAux.Delimiter := ',';
    slAux.DelimitedText := pstClients;
    
    for niI := 0 to slAux.Count - 1 do
    begin
      if ((Frac(niI/100) = 0) and (nII <> 0)) then
      begin
        Inc(niLine);
        slAuxResult.Add('');
      end;
    
      slAuxResult.Strings[niLine] := slAuxResult.Strings[niLine] +
        iif((slAuxResult.Strings[niLine] = ''), '', ',' )  + slAux.Strings[niI];
    end;
    
    Result := slAuxResult;
    
  finally
    FreeAndNil(slAux);
  end;
end;

I have tried a lot of changes, but I can't get the query to be generated in the format I want.

Answer 1

You are not formatting the not in clause correctly. And you are performing the SQL query on each grid row individually, which defeats the purpose of using a not in clause at all.

You also don't need the fxFormatQuery() function at all. You can use the RTL's AnsiQuotedStr() function to handle the quotes for you.

Try something more like this instead:

function loadCodesFromGrid(): String;
var
  slAux: TStringList;
  wsAux, stTable: String;
  qCliProv: TFXQuery;
  I: Integer;
begin    
  ...

  slAux := TStringList.Create;
  try
    for I := 1 to gDetails.RowCount - 1 do
    begin
      if Trim(gDetails.Cells[1, I]) <> '' then
      begin
        wsAux := Trim(gDetails.Cells[3, I]);
        if wsAux <> '' then
          slAux.Add(AnsiQuotedStr(wsAux, '"'));
      end;
    end;

    qCliProv.SQL.Text :=
      ' Select Code' + Copy(stTable, 0, iif(pboClients, 7, 9)) + ' As Code' +
      '   From ' + stTable +
      '  Where Active = 1 ';
    
    if slAux.Count > 0 then
    begin
      slAux.Delimiter := ',';
      slAux.QuoteChar := #0;
      qCliProv.SQL.Add(
        '    And Cuit Not In (' + slAux.DelimitedText + ')' );
    end;
  finally
    slAux.Free;
  end;

  qCliProv.Open;

  ...
end;

Answer 2

Just quote the elements you are generating in fxFormatQuerySQL with

+ AnsiQuotedStr(slAux.Strings[niI], '"')

instead of

+ slAux.Strings[niI];