Parameterized TQuery

[Ronin441]

I'm trying to set up a parameterized SQL query using a TQuery, and I'm running into a couple of problems. I can do a boring static query, but as soon as I try adding parameterization stuff, it all falls over. If I walk into the Prepare or Open, I find that my parameters are replaced with '?'; in other words, the Query hasn't successfully matched them up with the Params.

    Query1.SQL.Clear;
    Query1.SQL.Add('SELECT * FROM ' + TableName + ' WHERE ID = :Game;');
    Query1.ParamByName('Game').AsString := '"0001"';
    Query1.Prepare;
    Query1.Open;

I also want to replace the TableName with a parameter, but I'm not sure if this is legit.

My other problem is that I want a WHERE clause that matches a date/time: WHERE TheDateTime = :myDateTime; something like that; but I'm not sure of the SQL syntax. I managed to get as far as WHERE TheDateTime < CAST('1/1/2002' AS DATE); but this doesn't work for equals, only for greater than or less than; and I can't get it to work for a time, only a date.
-- Doug Burbidge mailto:doug@ultrazone.com

http://home.iprimus.com.au/dougburbidge/

 

[Oppenhiemer]

Few suggestions..
Build up an sql string rather than try add it in the sql.add method. So use variables and initialise those so..

var

tname : string;
SqlString : string;

begin
TName := 'Games_table';
SqlString := 'SELECT * FROM ' + tName + ' WHERE ID =:Game;';

Query1.SQL.close;
Query1.SQL.Clear;
Query1.SQL.Add(SqlString);

Query1.ParamByName('Game').ASString := '0001';

Also - dont use quotation marks. I have not had time to look at the date issues. Hope this helps..

Opp.

 

[svanels]

My advice, always make the query filling in the sql property of the query and set your parameters.

If this works fine, the next step is to change the SQL property in run time, I use frequently a combination of a combobox (handy if you build in a case statement) an editbox (entering your search criteria) and a speedbutton for mounting the query

Here is a fragment

with MainDtaMod.qryTrapDetails do
begin
close;
SQL.Clear;
SQL.Add('SELECT TCODE, LINE, ZONE,');
SQL.Add('ROUTE, REMARKS, LOC, MOD, SERVICE, MODEL,');
SQL.Add('REF1, REF2, SMCODE, SEQUENCE');
SQL.Add('FROM TRAPINV Trapinv');
case cbSelect.ItemIndex of
0: begin
SQL.Add('WHERE LINE LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
SQL.Add('ORDER BY SMCODE, SUPVALVE');
end;


The time issue I have not used it yet, I have to dive into the books S. van Els
SAvanEls@cq-link.sr