Tablename as parameter

[Chaosworld]

Is it possible to submit the name of the table for a query as a parameter? I am using a query that is combined several times with union, so i would have to change several times the query.
The original query looks like this (only an example, the original is more complex)
Select Name
by l06_parameter
where ...
UNION
Select Name
by l06_parameter
where ...
...

this works perfect. So i tried to make it this way:
Select Name
by :LTABLE
where ...
UNION
Select Name
by :LTABLE
where ...

.ParamByName(':LTABLE').asstring:=l06_parameter;
If i open now the query, i get an error like this:
SQL Error: You have an error in your SQL syntax; check the manual that correspond to your MySQL server version for the right syntax to use near ''l06_parameterdaten' where ...' at line 1.

The problem is, that with the parameter i get quote-signals around the name.
But i don't find any solution for the problem.

Thanks for every answer

Chaosworld

PS: If this question is wrong here, could anybody move it to the correct place, i am not sure where is the best place.

[Terence]

Try .ParamByName('LTABLE').asstring:=l06_parameter;

Leave the ':' out.
The ':' just implys an upcoming Parameter in the sql, just to differ between normal names and Parameter, but only in the sql. The Param is used without ':' later.

[Chaosworld]

Hi Terence
thanks for your answer, but how do you mean it?
I just tried it:

with ZReadOnlyQuery2 do
begin
sql.Clear;
sql.add('Select');
sql.add('IFNULL(');
sql.add('(Select Ergebnis');
sql.add('from LTABLE');
sql.add('where Probennr=106010050');
sql.add('and Parameternr =19),'') Parameter');

ParamByName('LTABLE').asstring:='l06_parameter';
open;
end;

But it tell me, that the Parameter LTABLE does not exist.
Or how else do you mean it?

Chaosworld

[Terence]

Try:

Code: Select all

with ZReadOnlyQuery2 do
begin
sql.Clear;
sql.add('Select');
sql.add('IFNULL(');
sql.add('(Select Ergebnis');
sql.add('from :LTABLE');
sql.add('where Probennr=106010050');
sql.add('and Parameternr =19),'') Parameter');

ParamByName('LTABLE').asstring:='l06_parameter';
open;
end;

[mdaems]

Hi Chaosworld, Terence,

Be carefull using this trick. I think this might give trouble with databases that do not support table names as parameters in prepared statements. I'm sure Mysql prepared statements do not support this. This is not a problem right now as we don't support prepared statements for mysql yet (we emulate them, wat could make your trick work). I don't know if it works for other databases.

Mark

[Terence]

Well, i didn't say its tested, just give it a try. Inever used it that way too.

In any case i would propose to create a method taking the tablename as param and creating sql query as string or returning whole query- there are much possibilities.

[Chaosworld]

Hi Terence
I just tried your code and i get the same like at the beginning:
SQL Error: You have an error in your SQL syntax; check the manual that correspond to your MySQL server version for the right syntax to use near ''l06_parameterdaten' where ...' at line 1.

Well if really nothing works, i have to create for every table the query again new.

@mdaems
well at the moment i am using the very old version of zeos 6.5.1 from 2004 cos it is the only version that works with delphi 5. Well so if i read your text, i think it would be better to work without the parameter, but would be much more comfortable :-D