Is there a standard way to convert between TVarRec and Variant values?
I want to parse an 'array of const' and use the values to populate parameters in a TMSQuery. To do this I'm using a list of column names (generated from TMSQuery.KeyFields), and matching the values in the array with the column names in KeyFields (by position), then using the column name to set the corresponding parameter using ParamByName.
The code below is what I've come up with, but VarRecToVariant doesn't seem very elegant. Is there a better solution?
keyFields: TStringList;
// List of table column names (keyFields.DelimitedText := query.KeyFields;)
// e.g. Name, Age
query: TMSQuery;
// Parametrized query with a parameter for each field in keyFields
// SELECT * FROM People WHERE Age=:Age AND Name=:Name
// If keyValues is ['Bob', 42] the resulting query should be
// SELECT * FROM People WHERE Age=42 AND Name='Bob'
procedure Read(keyValues: array of const);
var
i: Integer;
name: string;
value: Variant;
begin
...
for i := 0 to keyFields.Count - 1 do
begin
name := keyFields[i];
value := VarRecToVariant(keyValues[i]);
query.ParamByName(name).Value := value;
end;
query.Open
...
end;
function VarRecToVariant(varRec: TVarRec): Variant;
begin
case varRec.VType of
vtInteger: result := varRec.VInteger;
vtBoolean: result := varRec.VBoolean;
vtChar: result := varRec.VChar;
vtExtended: result := varRec.VExtended^;
vtString: result := varRec.VString^;
...
end;
end;
Notes:
- The values in the array of const depend on the parameters in the query. The caller knows what these are, but the method that uses the array doesn't know how many or what type to expect. I.e. I can't change the method to Read(name: string; age: integer).
- The parameters are not necessarily used in the same order that the values are specified in the array of const. In the example, keyFields are specified as "Name,Age" but the query uses Age before Name. This means Params[i].Value := keyValues[i] won't work. I think VarRecToVariant would still be needed anyway, which I'm trying to avoid).