How to use dynamic SQL queries from an .ini file?

Question

I'm a newbie in Delphi and software programming.

I want to find some advice for using queries from a .ini file.

It is my purpose that when I choose some file names in a DBGrid first, and click a button which show file names that I selected, and if they are correct then I click 'ok' to execute a query with the file names.

In this situation, the file names will change every time I select them. So, how can I execute the query in .ini file to get dynamic results?

First, here is the text in the Queries.ini file:

[Query]
QSELECTEDFN=SELECT * FROM TEST_01 WHERE FILE_NAME IN (:FILE_NAME);

Here is my code:

ini := TIniFile.Create('C:\file dir\Queries.ini');
try
  qSlctdByFN := ini.ReadString('QUERY', 'QSELECTEDFN', '');
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Text := qSlctdByFN;
  ADOQuery1.Parameters.ParamByName('FILE_NAME').Value := slselectedFN.CommaText;
  ADOQuery1.Active := true;
finally
  ini.Free;
end;

When I selected 1.txt, 2.txt then the query should be:

SELECT * FROM TEST_01 WHERE FILE_NAME IN ('1.txt,2.txt');

When I selected only 3.txt then the query should be:

SELECT * FROM TEST_01 WHERE FILE_NAME IN ('3.txt');

Answer 1

You can't use an SQL parameter with an IN clause. It takes a list of values, and a single parameter can't provide a list.

Your 1st example with 2 file names is actually wrong. It would need to be more like this instead:

SELECT * FROM TEST_01 WHERE FILE_NAME IN ('1.txt','2.txt');

Note the difference between ('1.txt,2.txt') (a single string) and ('1.txt','2.txt') (a list of strings).

So, to handle this, you are going to have to build up the replacement list manually, and then you can use StringReplace() to replace ':FILE_NAME' in the SQL with that list, eg:

ini := TIniFile.Create('C:\file dir\Queries.ini');
try
  qSlctdByFN := ini.ReadString('QUERY', 'QSELECTEDFN', ''); 
finally
  ini.Free;
end;

files := QuotedStr(slSelectedFN[0]);
for i := 1 to Pred(slSelectedFN.Count) do
  files := files + ',' + QuotedStr(slSelectedFN[i];

ADOQuery1.SQL.Text := StringReplace(qSlctdByFN, ':FILE_NAME', files, []);

ADOQuery1.Active := true;