Delphi ADOQuery is not using parsed parameter values. Getting "Syntax error in FROM clause&quot-CodePudding

Trying to run a select statement using the below code. I'm parsing a SQL table name parameter that's value is determined by a case statement. This then assigns the dataset to a global datasource used in another form. However, the app is returning a "Syntax error in FROM clause" dialogue.

I've assigned the correct datatype and during my tests I can confirm that the parameter's value is what it needs to be i.e. "ACCOUNTS" for case 1.

I'm new to using ADO but ADOQUERY.SQL.GetText is returning the SQL statement with the parameter placeholder ":ATABLE" rather than the parameter value, though I am currently assuming this is normal.

procedure TfrmDataModule.FindAllRecords(Sender: TObject; recordType: Integer);

var
  ADOQuery : TADOQuery;
  Param    : TParameter;

begin

  case recordType of
    1 : currentRecordType := 'ACCOUNTS';
    2 : currentRecordType := 'CONTACTS';
    3 : currentRecordType := 'USERS';
  end;

  { SQL Query }
  SQLStr := 'SELECT * FROM :ATABLE';

  { Create the query. }
  ADOQuery := TADOQuery.Create(Self);
  ADOQuery.Connection := ADOConn;
  ADOQuery.SQL.Add(SQLStr);

  { Update the parameter that was parsed from the SQL query. }
  Param := ADOQuery.Parameters.ParamByName('ATABLE');
  Param.DataType := ftString;
  Param.Value := currentRecordType;

  { Set the query to Prepared--it will improve performance. }

  ADOQuery.Prepared := true;

  try
    ADOQuery.Active := True;
  except
    on e: EADOError do
    begin
      MessageDlg('Error while doing query', mtError,
                  [mbOK], 0);

      Exit;
    end;
  end;

  { Create the data source. }
  DataSrc := TDataSource.Create(Self);
  DataSrc.DataSet := ADOQuery;
  DataSrc.Enabled := true;

end;

Edit: More info. The query does work if I comment out the Param lines and replace the SQLStr :ATABLE with the concatenated SQLStr and the case variable currentRecordType.

CodePudding user response：

Delphi Coder to the rescue in the comments.

Appears passing parameters as SQL table names is not allowed.

CodePudding user response：

SQL simply does not allow table names to be provided by parameters in the FROM clause. So, you will just have to use plain ordinary string concatenation instead, eg:

procedure TfrmDataModule.FindAllRecords(Sender: TObject; recordType: Integer);
var
  ADOQuery : TADOQuery;
begin
  case recordType of
    1 : currentRecordType := 'ACCOUNTS';
    2 : currentRecordType := 'CONTACTS';
    3 : currentRecordType := 'USERS';
  end;

  { Create the SQL query. }
  ADOQuery := TADOQuery.Create(Self);
  ADOQuery.Connection := ADOConn;
  ADOQuery.SQL.Text := 'SELECT * FROM '   currentRecordType;

  try
    { Set the query to Prepared--it will improve performance. }
    ADOQuery.Prepared := true;
    ADOQuery.Active := True;
  except
    on e: EADOError do begin
      MessageDlg('Error while doing query', mtError, [mbOK], 0);
      Exit;
    end;
  end;

  { Create the data source. }
  DataSrc := TDataSource.Create(Self);
  DataSrc.DataSet := ADOQuery;
  DataSrc.Enabled := true;
end;