Passing MySQL date range query in Delphi using UniQuery with variable string date values gives me returns NULL

1
votes

I am passing MySQL query using UniQuery in Delphi and it is returing NULL.

I already change the date format in the property of my TDBDateTimeEditEh to match with mysql format yyyy-mm-dd and pass this to my string variable at runtime.

Here's my code:

    procedure TfrmPayroll.Button1Click(Sender: TObject);
    var
      DateRangeQry, StartDate, EndDate : string;
    begin
      StartDate := dthStart.Text;
      EndDate := dthEnd.Text;
      DateRangeQry := 'SELECT * FROM mytimesheet WHERE Date >= '+ StartDate +' AND date <= '+ EndDate +'';

    //ShowMessage(StartDate +' to '+ EndDate); // result yyyy-mm-dd to yyyy-mm-dd

      with dm_u.dmPayroll do
        begin
          uqMyTimesheet.SQL.Clear;
          uqMyTimesheet.sql.Text := DateRangeQry;
          uqMyTimesheet.ExecSQL;
          cdsMyTimesheet.Refresh;
        end;
    end;

I did check the value in the showmessage and it is matching the date format in mysql.

I supposed my codes about would generate range of records from the database within the date range specified but no avail.

I will appreciate any help from you guys.

Updates:

enter image description here

2
mysqldelphi
1. Stop using concatenated strings for your queries (lookup SQL injection) 2. Use parameters (look here for example 3. all your problems are solved... – whosrdaddy
4. uqMyTimesheet.SQL.Clear; is pointless. Setter of property Text calls Clear within BeginUpdate .. EndUpdate block. – Peter Wolf

2 Answers

3
votes

Using parameters instead of OLD concatenated queries style, is better, clear and it works ever! Try this code.

procedure TForm5.Button1Click(Sender: TObject);
var
      DateRangeQry, StartDate, EndDate : string;
begin

      StartDate:='01/01/2019';
      EndDate:='01/01/2020';

      DateRangeQry:='SELECT * FROM mytimesheet WHERE Date >= :StartDate AND date <= :EndDate';
      uqMyTimesheet.SQL.Text:=DateRangeQry;
      uqMyTimesheet.ParamByName('StartDate').AsDate := StrToDate(StartDate);
      uqMyTimesheet.ParamByName('EndDate').AsDate := StrToDate(EndDate);
      uqMyTimesheet.Open;



end;

Another approach when you have different date formats is convert date to string

  StartDate:='2019-01-01';
  EndDate:='2020-01-01';

  DateRangeQry:='SELECT * FROM mytimesheet WHERE DATE_FORMAT(Date, ''%Y-%m-%d'') between :StartDate AND :EndDate';
  UniQuery1.SQL.Text:=DateRangeQry;
  UniQuery1.ParamByName('StartDate').AsString := (StartDate);
  UniQuery1.ParamByName('EndDate').AsString := (EndDate);
  UniQuery1.Open;
0
votes

Use a parameterized query instead, using TDate instead of string for the parameter values, eg:

procedure TfrmPayroll.QueryTimeSheet(StartDate, EndDate: TDate);
begin
  //ShowMessage(DateToStr(StartDate) + ' to ' + DateToStr(EndDate)); // result yyyy-mm-dd to yyyy-mm-dd
  with dm_u.dmPayroll do
  begin
    uqMyTimesheet.SQL.Text := 'SELECT * FROM mytimesheet WHERE Date >= :PStartDate AND date <= :PEndDate';
    uqMyTimesheet.ParamByName('PStartDate').AsDate := StartDate;
    uqMyTimesheet.ParamByName('PEndDate').AsDate := EndDate;
    uqMyTimesheet.ExecSQL;
    cdsMyTimesheet.Refresh;
  end;
end;

procedure TfrmPayroll.Button1Click(Sender: TObject);
begin
  QueryTimeSheet(dthStart.Value, dthEnd.Value);
end;