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

Question

Trying to run a select statement using the below code. I'm parsing a SQL table name parameter whose 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.

You can't use SQL Parameters for table names, only for fields! — Delphi Coder, Oct 25 '22 at 12:38
That seems crazy to me but I'm sure there is a good reason so thank you for your help, much appreciated. — Harrebow, Oct 25 '22 at 12:46
Nothing crazy about it. You can't do it in ADO code that works with SQL Server, either. That's not what parameters are for in the first place. — Ken White, Oct 25 '22 at 22:55

score 0 · Answer 1 · edited Oct 28 '22 at 02:11

0

Credit to @DelphiCoder, it appears passing parameters as SQL table names is not allowed.

edited Oct 28 '22 at 02:11

James Risner

5,451
11
25
47

answered Oct 25 '22 at 12:48

Harrebow

3
2

score 0 · Accepted Answer · answered Oct 26 '22 at 00:46

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;

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

2 Answers2