Getting a Field List from a DBExpress TSQLQuery

Question

I am having a problem getting a list of fields from a query defined at run time by the users of my program. I let my users enter a SQL query into a memo control and then I want to let them go through the fields that will return and do such things as format the output, sum column values and so forth. So, I have to get the column names so they have a place to enter the additional information.

I would do fine if there were no parameters, but I also have to let them define filter parameters for the query. So, if I want to set the parameters to null, I have to know what the parameter's datatype is.

I am using Delphi 2006. I connect to a Firebird 2.1 database using the DBExpress component TSQLConnection and TSQLQuery. Previously, I was successful using:

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

I discovered I had a problem when I tried to use a date parameter. It was just a coincidence that all my parameters up until then had been integers (record IDs). It turns out that varNull is just an enumerated constant with a value of 1 so I was getting acceptable results (no records) was working okay.

I only need a list of the fields. Maybe I should just parse the SELECT clause of the SQL statement. I thought setting Qry.Prepared to True would get me a list of the fields but no such luck. It wants values for the parameters.

If you have an idea, I would sure like to hear it. Thanks for any help.

Answer 1

I'm not sure what version of Delphi you are using. In the Delphi 2006 help under Variant Types, it says:

Special conversion rules apply to the Borland.Delphi.System.TDateTime type declared in the System unit. When a Borland.Delphi.System.TDateTime is converted to any other type, it treated as a normal Double. When an integer, real, or Boolean is converted to a Borland.Delphi.System.TDateTime, it is first converted to a Double, then read as a date-time value. When a string is converted to a Borland.Delphi.System.TDateTime, it is interpreted as a date-time value using the regional settings. When an Unassigned value is converted to Borland.Delphi.System.TDateTime, it is treated like the real or integer value 0. Converting a Null value to Borland.Delphi.System.TDateTime raises an exception.

The last sentence seems important to me. I would read that as varNull cannot be converted to a TDateTime to put into the field, and hence you get the exception that you're experiencing.

It also implies that this is the only special case.

Couldn't 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 2

Although a slightly different dataset type this is what I use with TClientDataset simple and effective :)

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;

end;
can you not do something similar with the query?

Answer 3

You guys are making this way too hard:

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

Answer 4

Thank you all for your responses.

I tried testing each one but with no success. I rolled them into a single test to see if I can get any other ideas. Here is the test apparatus.

This is my Delphi code:

try
  Qry := TSQLQuery.Create(Self);
  Qry.SQLConnection := SQLConnection;
  Qry.SQL.Add(sSQL);
  Debug('GetQueryFields', sSQL);
  Qry.Prepared := True;
  for i := 0 to Qry.Params.Count - 1 do begin
    if VarType(Qry.Params[i].value) and varTypeMask = varDate then
    begin
      Debug('Test1', 'Is a date: True');
      Qry.Params[i].value := Now; //or whatever you choose as your default
    end;
    Qry.Params[i].Clear;
    Qry.Params[i].Bound := True;
    Debug('Test2', 'Null: ' + CStr(Qry.Params[i].IsNull));
    Debug('Test3', 'Bound: ' + CStr(Qry.Params[i].Bound));
  end;
  Qry.Open;
  for i := 0 to Qry.Fields.Count - 1 do begin
    Result := Result + Qry.Fields[i].DisplayName + ',' + CRLF;
  end;
except
  on E: Exception do begin
      Debug('Test4', 'There was apparently a problem in your query.'
                 + CRLF + E.Message);
    end;
end;

This is the Debug output:

GetQueryFields  
      SELECT s.hEmployee, e.sLastName
      FROM PR_Paystub s
      INNER JOIN PR_Employee e ON e.hKey = s.hEmployee
      WHERE s.dtPaydate > :dtStart

Test2   
Null: True

Test3   
Bound: True

Test4   
There was apparently a problem in your query.  This error message may help.
No value for parameter 'dtStart'

Richard, your approach seems like it should be just what was needed, but apparently testing the datatype of the parameters does not yield useful results at that point because Test1 did not execute.

Despatcher, it seems like this will fail for the same reason as Richard's.

Craig, the Bound property seems like it is designed for exactly this purpose. Problem is that I am getting a result that seems like it should be eliminated by the Bound property. I was afraid that my Firebird driver might have been causing the problem so I switched out my DevArt DBExpress driver and used the CodeGear Interbase driver but the results are the same. Can you see that I am overlooking something?

Thanks to all of you for well thought out responses. I really appreciate you taking the time to review my problem.

Jack

Answer 5

Hi, Replied again 'coz I'm interested. My methods works (with my queries) because they have been pre-defined with the params' datatypes preset to the correct type:)

I'm not sure how you are expecting the query to know or derive the datatype of the param given that you are not even selecting the field that it operates against.

So I think your query setup and user input method will need more attention. I've just looked up how I did this a while ago. I do not use a parameterised query - I just get the "parameter values" from the user and put them directly into the SQL. So your sql would then read:

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'

therefore no parameter type knowledge is necessary. Does not stop your users entering garbage but that goes back to input control :)

Answer 6

Wow! Despatcher, you're right! You had it all along. After puzzling over this for days, I woke up this morning with basically the same idea. And you have already solved this same problem. I now have confirmation that it will work. Thanks for writing back!

Jack

Answer 7

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

Answer 8

What I ended up doing was this:

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 that was easy. Delimiters are just the characters that signal the end of a word.