Retrieving Field List from DBExpress TSQLQuery

Question

I had a problem getting a list of fields from a query that was determined by users of my program at runtime. I allow my users to enter an SQL query into the memo control, and then I want them to go through the returned fields and do things like formatting output values, column sums, etc. So, I need to get the column names so that they can provide additional information.

I would do everything so that there are no parameters, but I should also let them determine the filter parameters for the request. So, if I want to set the parameters to null, I should know what the parameter data type is.

I am using Delphi 2006. I am connecting to the Firebird 2.1 database using DBExpress TSQLConnection and TSQLQuery. I used to successfully use:

for i: = 0 for Qry.Params.Count - 1 do Qry.Params [i] .value: = varNull;

I found that I had a problem when I tried to use the date parameter. It was just a coincidence that all my parameters before that were integers (record identifiers). It turns out that varNull is just an enumerated constant with a value of 1, so I get acceptable results (no entries) it works fine.

I only need a list of fields. Perhaps I just need to parse the SELECT clause of the SQL statement. I thought setting up Qry.Prepared to True would get me a list of fields, but there would be no such luck. He wants values ​​for the parameters.

, . .

Answer 1

. . ( ), params, :)

, , , , .

, , . , . - " " SQL. , sql :

SELECT s.hEmployee, e.sLastName
    FROM PR_Paystub s
    INNER JOIN PR_Employee e ON e.hKey = s.hEmployee
    WHERE s.dtPaydate > '01/01/2008 '

. , :)

Answer 2

, , TClientDataset :)

for i := 0 to FilterDataSet.Params.Count -1 do  
begin  
 Case FilterDataSet.Params.Items[i].Datatype of  
    ftString:  
    ftSmallint, ftInteger, ftWord:  
    ftFloat, ftCurrency, ftBCD:  
    ftDate:  
    ftTime:  
    ftDateTime:  
    .  
    .  
    . 
end;   

;
- ?

Answer 3

, , :

for i := 0 to Qry.Params.Count - 1 do begin
    Qry.Params[i].Clear;
    Qry.Params[i].Bound := True;
end;

Answer 4

, Delphi . Delphi 2006 " " :

Borland.Delphi.System.TDateTime . Borland.Delphi.System.TDateTime - , . , Borland.Delphi.System.TDateTime, Double, -. Borland.Delphi.System.TDateTime, -, . Borland.Delphi.System.TDateTime, 0. Null Borland.Delphi.System.TDateTime .

The last sentence seems important to me. I would read that since varNull cannot be converted to TDateTime to put in a field, and therefore you get the exception you are experiencing.

It also means that this is the only special case.

Could you do something like:

for i := 0 to Qry.Params.Count - 1 do 
begin
  if VarType(Qry.Params[i].value) and varTypeMask = varDate then
  begin
    Qry.Params[i].value := Now; //or whatever you choose as your default
  end
  else
  begin
    Qry.Params[i].value := varNull;
  end;
end;

Answer 5

TmpQuery.ParamByName('MyDateTimeParam').DataType := ftDate;
TmpQuery.ParamByName('MyDateTimeParam').Clear;
TmpQuery.ParamByName('MyDateTimeParam').Bound := True;

Answer 6

What I finished was as follows:

sNull := 'NULL';
Qry.SQL.Add(sSQL);
for i := 0 to Qry.Params.Count - 1 do begin
  sParamName := Qry.Params[i].Name;
  sSQL := SearchAndReplace (sSQL, ':' + sParamName, sNull, DELIMITERS);
end;

I had to write SearchAndReplace, but it was easy. Separators are simply characters that signal the end of a word.