CODEWITHSUNDEEP
delphimydac
John John
John John

Reputation: 373

How do I pass a table's name as a parameter in MyAccess?

 query.SQL.Text := 'INSERT INTO :table VALUES (ID = 2, Name = ''John'', Value = 1.1);';
 query.ParamByName('table').AsString := 'testtable';

This leads to the table's name being put in quotes, thus breaking MySQL syntax. This makes sense, but it means I have no idea of how to actually have the table name as a parameter.

Thanks !

Upvotes: 1

Views: 130

Answers (3)

kris
kris

Reputation: 11

use MacroByName

https://docwiki.embarcadero.com/RADStudio/Athens/en/Preprocessing_Command_Text_(FireDAC)

query.SQL.Text := 'INSERT INTO &table VALUES (ID = 2, Name = ''John'', Value = 1.1);';
query.MacroByName('table').AsIdentifier := 'testtable';

Upvotes: 1

teovaliente
teovaliente

Reputation: 1

If you are building your SQL by hand, my suggestion is you could use a variable and concat it into the SQL text.

tname:= 'table1';
sSql:= 'Insert into '+tname+'(ID,Name,Value) ';
sSql:= sSql + 'VALUES (2,"John",1.1)';
Query1.SQL.Text:= sSql;

Of course, tname and sSql are String variables.

Upvotes: 0

Ken White
Ken White

Reputation: 125671

You can't. Only column values are allowed as parameters. Table and column names are not supported. (BTW, it makes no sense to try to parameterize a table name when you're concatenating string values into the columns.)

You can work around this by using the format function before setting parameter values:

var
  SQLText: string;
begin
  qry.SQL.Text := 'INSERT INTO %s VALUES (ID = 2, Name = :FirstName, Value = :Value);';
  qry.SQL.Text := Format(SQLText, ['TableName']);
  qry.ParamByName('FirstName').AsString := 'John';
  qry.ParamByName('Value').AsFloat := 1.1;
end;

Upvotes: 5

Related Questions