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

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 :)