CODEWITHSUNDEEP
datefirebird
Simon King
Simon King

Reputation: 195

Firebird SQL: error code -104, token unknown

I have a script, below, in Ostendo (an ERP), which runs on a firebird database. The script is written in Pascal. It returns an error "Firebird SQL: error code -104, token unknown, '13/04/2018' at the line TMPQuery2.ExecQuery. I removed the reference text DATEWORKED = {ds ''13/04/2018''} AND and it works fine. I have commented out all the other lines I tried in solving it - of which none worked.

Any ideas as to where I am going wrong? I have a feeling it is do to with date format. I have tried dd-mm-yyyy, yyyy-mm-dd (which is how it works from a OBDC through excel), dd/mm/yyyy (which is how it is displayed in ostendo).

function DuplicateTimesheetDays(ExcelFileName: String; RowCount: Integer; EmployeeName: String;): Boolean;

var
TSDateWorked, SQLStr: String;
TSBatchNo: String;
TMPQuery2     : TpFIBQuery;
ErrorMessage : String;
DuplicateDay : String;
DisplayErrorMessage : Boolean;
x: Integer;

begin

DisplayErrorMessage := False;
ErrorMessage := 'The following Days have previously been added: ' + #13#10;

LoadSpreadSheet(ExcelFileName);


for x := 1 to RowCount -1 do
   begin
            //
            //TSDateWorked := SSGetCellText(0,x);               //convert to YYYY-MM-DD
   TSDateWorked := FormatDateTime('YYYY-MM-DD',strtodate(SSGetCellText(0,x)));
   //SQLStr := 'SELECT TIMESHEETBATCHNO FROM TIMESHEETLINES WHERE DATEWORKED = {d ''' + TSDateWorked + '''} AND EMPLOYEENAME = ''' + EmployeeName + '''';
   //SQLStr := 'SELECT TIMESHEETBATCHNO FROM TIMESHEETLINES WHERE DATEWORKED = {ds ''2018-04-13''} AND EMPLOYEENAME = ''DE BEER''';
   SQLStr := 'SELECT TIMESHEETBATCHNO FROM TIMESHEETLINES WHERE DATEWORKED = {ds ''13/04/2018''} AND EMPLOYEENAME = ''DE BEER''';
   //SQLStr := 'SELECT TIMESHEETBATCHNO FROM TIMESHEETLINES WHERE EMPLOYEENAME = ''DE BEER''';
   Showmessage(SQLStr);

   try
            TMPQuery2 := TpFIBQuery.Create(nil);
            TMPQuery2.Database := OstendoDB;
            TMPQuery2.Options := qoStartTransaction + qoAutoCommit;
            TMPQuery2.SQL.clear;
            //TMPQuery2.SQL.Add('SELECT TIMESHEETBATCHNO FROM TIMESHEETLINES WHERE DATEWORKED = {d ''' + TSDateWorked + '''} AND EMPLOYEENAME = ''' + EmployeeName + '''');
            TMPQuery2.SQL.Add(SQLStr);
   Showmessage('About to execute query');
   TMPQuery2.ExecQuery;
            if not TMPQuery2.EOF then
              begin
     TSBatchNo := TMPQuery2.FN('TIMESHEETBATCHNO').value;
              //Showmessage('Position of Batch Number in error message is: ' + pos(TSBatchNo, ErrorMessage));
              // If pos(TSBatchNo, ErrorMessage) = 0 then
        ErrorMessage := ErrorMessage + 'Duplicate Timesheet already entered on Batch No ' + TSBatchNo + ' for day ' + TSDateWorked + '.' + #13#10;
              DisplayErrorMessage := True;
              Result := True
     end;

            finally

            TMPQuery2.close;

            end;
   end; // For loop

//Display error messages if duplicates exist.
If DisplayErrorMessage then MessageDlg(ErrorMessage,mtinformation,mbok,0);

end;

Upvotes: 0

Views: 2995

Answers (1)

ain
ain

Reputation: 22759

From where did you get the idea that date literal in Firebird is represented in format like {ds '2018-04-13'}? The legal formats to cast string data types to the DATE is listed at "Literal Formats" section in the "Conversion of Data Types" chapter.

Basically the yyyy-mm-dd format is right but it must be a simple string ie DATEWORKED = ''' + TSDateWorked + ''' when concatenating strings in pascal.

But instead of building the query as a string one should use parametrized queries. Parameters in SQL string are usually represented by name which has a colon in front of it, ie :DateWorked. So your query would look like

SQLStr := 'SELECT TIMESHEETBATCHNO FROM TIMESHEETLINES WHERE DATEWORKED = :DateWorked AND EMPLOYEENAME = :employee';
...
TMPQuery2.Params[0].AsDateTime := strtodate(SSGetCellText(0,x));
TMPQuery2.Params[1].AsString := 'DE BEER';
TMPQuery2.ExecQuery;

The query component usually also has ParamByName methods so that instead of parameter position you can assign values using the names. I don't konw the TpFIBQuery component so the property/method names might differ, consult the help of the component.

Upvotes: 3

Related Questions