How to prepare SQL in delphi using date?-CodePudding

Before using parameters

  FDQuery.Connection := FDConnection1;
  FDQuery.SQL.Text := 'SELECT * FROM '   symbol   ' WHERE date >= '''  
  datetostr(Dfrom)   ''' AND date <= '''   datetostr(Dto)   '''';
  FDQuery.Active := True;

it can work, but after using parameters, it cannot works. where did i go wrong?

  FDQuery.SQL.Text := 'SELECT * FROM :symbol WHERE date >= :Dfrom AND date <= :Dto';
  FDQuery.ParamByName('symbol').AsString := symbol;
  FDQuery.ParamByName('Dfrom').AsString := '''' datetostr(Dfrom) '''';
  FDQuery.ParamByName('Dto').AsString := '''' datetostr(Dto) '''';
  FDQuery.Active := True;

CodePudding user response：

When you use parameters, you should not convert to string - that's the job of the parameter...

  FDQuery.SQL.Text := 'SELECT * FROM :symbol WHERE date >= :Dfrom AND date <= :Dto';
  FDQuery.ParamByName('symbol').AsString := symbol;
  FDQuery.ParamByName('Dfrom').AsDateTime := Dfrom;
  FDQuery.ParamByName('Dto').AsDateTime := Dto;
  FDQuery.Active := True;

By using parameters, you not only guard against SQL Injection, you also insulate yourself from needing to know how a DateTime is to be passed in the SQL string. Some databases use single quotes, some use double quotes, and some use DATE 'yyyy-mm-dd' (like ElevateDB). But they all use the ParamByName(...).AsDateTime to send a DateTime to the SQL interpreter.

Also, I'm pretty sure that parameters cannot be used to specify the table name (or field names for that matter). Generally speaking, parameters are used for values and not as string substitutions...

Which also means that parameters cannot be used for IN (...) expressions to give the entire list of values (at least not in any way that I know of).

So the correct way to do it using parameters is this:

  FDQuery.SQL.Text := 'SELECT * FROM ' symbol ' WHERE date >= :Dfrom AND date <= :Dto';
  FDQuery.ParamByName('Dfrom').AsDateTime := Dfrom;
  FDQuery.ParamByName('Dto').AsDateTime := Dto;
  FDQuery.Active := True;

assuming that the table name doesn't contain spaces or is a reserved word.

Using FireDAC, you can use substitution values for table name and field names, like this:

  FireDAC.Stan.Option.TFDResourceOptions.MacroExpand := TRUE;
  FireDAC.Stan.Option.TFDResourceOptions.MacroCreate := TRUE;

  FDQuery.SQL.Text := 'SELECT * FROM &symbol WHERE date >= :Dfrom AND date <= :Dto';
  FDQuery.MacroByName('symbol').AsRaw := symbol;
  FDQuery.ParamByName('Dfrom').AsDateTime := Dfrom;
  FDQuery.ParamByName('Dto').AsDateTime := Dto;
  FDQuery.Active := True;

(I'm not 100% sure about the first two statements, as I have never needed to use Macros in my FD code, but from what I can gather from the documentation, these options must be set in order for it to work).

CodePudding user response：

Hi with the help from HeartWare, it can work now. thanks HeartWare.

 FDQuery.Stan.Option.TFDResourceOptions.MacroExpand := TRUE;
 FDQuery.Stan.Option.TFDResourceOptions.MacroCreate := TRUE;
 FDQuery.SQL.Text := 'SELECT * FROM &symbol WHERE date >= :Dfrom AND date <= :Dto';
 FDQuery.MacroByName('symbol').AsRaw := symbol;
 FDQuery.ParamByName('Dfrom').AsDateTime := Dfrom;
 FDQuery.ParamByName('Dto').AsDateTime := Dto;
 FDQuery.Active := True;