simple query with params

Josef Koller · Post by **Josef Koller** » 07.01.2006, 21:22

Hello,

I would like to start the following SQL-statement:

dmlager.zquery2.SQL.Clear;
dmlager.ZQuery2.SQL.Add('select * from "T_BUCHUNG" where "LAGERORT" = :vrLager order by :vrsort');
dmlager.ZQuery2.Params[0].AsString:=vrLager;
dmlager.zquery2.params[1].asstring:=vrsort;
dmlager.ZQuery2.Open;

but I get the following error message:

An error was found in the application program input parameters for the sql statement. The sql: select * from "T_BUCHUNG" where "LAGERORT" = ? order by ?;' . .....

vrLager has the value: for example KOLL and vrsort BUCHUNGSDATUM.

Only with the following statement I got a result:

dmlager.zquery2.SQL.Clear;
dmlager.ZQuery2.SQL.Add('select * from "T_BUCHUNG" where "LAGERORT" = ' +qoutedstr(vrLager)+' order by '+quotedstr(vrsort)+'');
//dmlager.ZQuery2.Params[0].AsString:=vrLager;
//dmlager.zquery2.params[1].asstring:=vrsort;
dmlager.ZQuery2.Open;

That's not normal, I think. What's wrong in my first statement?

Many thanks and best regards

Josef

pol · Post by **pol** » 09.01.2006, 11:23

Hello Josef,

You want to pass the column name for your "order by" as a bind variable? I don't think that this is possible.

Best regards,
Rüdiger

designshouse · Post by **designshouse** » 09.01.2006, 14:16

dmlager.zquery2.SQL.Clear;
dmlager.ZQuery2.SQL.Add('select * from "T_BUCHUNG" where "LAGERORT" = :vrLager order by :vrsort');
dmlager.ZQuery2.ParamByName('vrLarger').AsString:=vrLager;
dmlager.zquery2.ParamByName('vrsort').AsString:=vrsort;
dmlager.ZQuery2.Open;

Josef Koller · Post by **Josef Koller** » 09.01.2006, 17:52

Hi,

thanks for answer, but with your statement I get the same error.
The problem is the paramter for the field in the order statement BUCHUNGSDATUM.

At first I thougt that the ' ' are the problem. but I found in the JEDI-Lib a function StrTrimQuotes but I get the errer, too.

I find no way to declare a fieldname with a parameter statement. Only

sql. add('Select * from ..........order by '+:vrSort) works.

Any other ideas?

Kind regards

Josef

zippo · Post by **zippo** » 11.01.2006, 10:53

Quick and dirty: Insert the parameters diractly into SQL, example:

Code: Select all

dmlager.zquery2.SQL.Clear;
dmlager.ZQuery2.SQL.Add('select * from "T_BUCHUNG" where "LAGERORT" = '+LagerVar+' order by '+VSortVar);
dmlager.ZQuery2.Open;

Where LagerVar and VSortVar is your variable. If needed, apply IntToStr, FloatToStr,.. etc.

Byungho · Post by **Byungho** » 19.01.2006, 16:10

An error was found in the application program input parameters for the sql statement. The sql: select * from "T_BUCHUNG" where "LAGERORT" = ? order by ?;' . .....

I got a same error, too! Bellow code does not work correctly!!

Code: Select all

ZQuery1.ParamByName('USERID').AsString := 'test';

My app works on D7 Enterprise and Zeos 6.1.5 with path2

regards
byungho

pol · Post by **pol** » 20.01.2006, 09:56

@Byungho:
What is your SQL?

Regards,
Rüdiger

zippo · Post by **zippo** » 21.01.2006, 18:31

I always construct the entire SQL statement by myself and works well... I had similar problems with params in the past, so I switched to "manual"...

Byungho · Post by **Byungho** » 23.01.2006, 17:00

pol wrote:@Byungho:
What is your SQL?

Regards,
Rüdiger

I use embedded firebird-1.5 , D7 and Zeos 6.1.5!! Windows XP SP2

pol · Post by **pol** » 24.01.2006, 09:30

No, I mean, what is ZQuery1.SQL?