Given the following
{------------------------------------------------------------------------------}
function TTestClass.NewQuery(const ASql : String) : TSqlQuery;
begin
Result := TSqlQuery.Create(FConn);
Result.SQLConnection := FConn;
Result.Sql.Text := ASql;
Result.Prepared := True;
end;
{------------------------------------------------------------------------------}
procedure TTestClass.ExecuteSql(const ASql : String);
begin
with NewQuery(ASql) do
try
ExecSql();
finally
Free;
end;
end;
How might I create an ExecSql method that will populate the parameters of the query?
I tried this overloaded method:
{------------------------------------------------------------------------------}
procedure TTestClass.ExecuteSql(const ASql : String; const ParamVals : Array Of Variant);
var
i : integer;
Qry : TSqlQuery;
begin
Qry := NewQuery(ASql);
with Qry do
try
for i := Low(ParamVals) to High(ParamVals) do
Qry.Params[i].Value := ParamVals[i];
ExecSql();
finally
Free;
end;
end;
But I get the error message :
Project MyProj.exe raised exception class EDatabaseError with message 'No value for parameter 'SomeParam''.
Putting a watch on Parameter[0] apparently shows the value is set - and the parameter name is as I would expect. Can anyone suggest what I am doing wrong ?
And I have been criticised in the past for using 'Array Of Variant' - I wonder if there is a better way .
Thanks, all.
I found something interesting :
ParamByName('SomeParam').Value := 1234567;
Gives rise to the same Error Message whereas
ParamByName('SomeParam').AsInteger := 1234567;
Does not.
It is many years since I have used DBExpress - have I forgotten something ?
Edit
I have come up with a method that works, but am not happy with it; By examining the Variant Type of the VALUE I have managed to get some results
{------------------------------------------------------------------------------}
procedure TMyTestCase.SetParamValues(const AQuery : TSqlQuery; const ParamVals : Array Of Variant);
var
i : Integer;
begin
for i := 0 to AQuery.Params.Count - 1 do begin
case VarType(ParamVals[i]) of
varEmpty : AQuery.Params[i].AsInteger := VarNull; //The variant is Unassigned.
varNull : AQuery.Params[i].AsInteger := VarNull; //The variant is Null.
varAny : AQuery.Params[i].AsInteger := VarNull; //Represents a Variant that can hold any value.
varSmallint : AQuery.Params[i].AsInteger := ParamVals[i]; //16-bit signed integer (type Smallint in Delphi, short in C++).
varInteger : AQuery.Params[i].AsInteger := ParamVals[i]; //32-bit signed integer (type Integer in Delphi, int in C++).
varSingle : AQuery.Params[i].AsFloat := ParamVals[i]; //Single-precision floating-point value (type Single in Delphi, float in C++).
varDouble : AQuery.Params[i].AsFloat := ParamVals[i]; //Double-precision floating-point value (type double).
varCurrency : AQuery.Params[i].AsFloat := ParamVals[i]; //Currency floating-point value (type Currency).
varDate : AQuery.Params[i].AsDateTime := ParamVals[i]; //Date and time value (type TDateTime).
varOleStr : AQuery.Params[i].AsString := ParamVals[i]; //Reference to a dynamically allocated UNICODE string.
varDispatch : AQuery.Params[i].AsInteger := VarNull; //Reference to an Automation object (an IDispatch interface pointer).
varError : AQuery.Params[i].AsInteger := VarNull; //Operating system error code.
varBoolean : AQuery.Params[i].AsBoolean := ParamVals[i]; //16-bit Boolean (type WordBool).
varVariant : AQuery.Params[i].AsInteger := VarNull; //Indicates another variant.
varUnknown : AQuery.Params[i].AsInteger := VarNull; //Reference to an unknown object (an IInterface or IUnknown interface pointer).
varShortInt : AQuery.Params[i].AsInteger := ParamVals[i]; //8-bit signed integer (type ShortInt in Delphi or signed char in C++).
varByte : AQuery.Params[i].AsInteger := ParamVals[i]; //A Byte.
varWord : AQuery.Params[i].AsInteger := ParamVals[i]; //Unsigned 16-bit value (Word).
varLongWord : AQuery.Params[i].AsInteger := ParamVals[i]; //Unsigned 32-bit value (type LongWord in Delphi or unsigned long in C++).
varInt64 : AQuery.Params[i].AsInteger := ParamVals[i]; //64-bit signed integer (Int64 in Delphi or __int64 in C++).
varStrArg : AQuery.Params[i].AsString := ParamVals[i]; //COM-compatible string.
varString : AQuery.Params[i].AsString := ParamVals[i]; //Reference to a dynamically allocated string (not COM-compatible).
varArray : AQuery.Params[i].AsInteger := VarNull; //Indicates a Variant array.
varByRef : AQuery.Params[i].AsInteger := VarNull; //Indicates that the variant contains a reference as opposed to a value.
varTypeMask: AQuery.Params[i].AsInteger := VarNull; //
end;
end;
end;
Surely I have missed out a step - Why doesnt the query parameter have a type ?
Edit Again
My current 'Best' solution is to rely on the programmer supplying the correct types and number of values. I have posted the full SetParamValues() method above. This is by NO MEANS thoroughly tested, but hopefully it will assist someone.
All tests performed with Delphi XE3 and MySQL
Behavior Explanation
At designtime you will get the correct parameter data types as long as the parameters depend on a table field from the FROM table without aliases
SELECT id FROM items WHERE id = :id
but it will also fail if not
SELECT id FROM items WHERE id/2 = :id
and this will also fail
SELECT i.* FROM items i WHERE i.id = :id
At runtime both will result in parameters with datatype ftUnknown.
Parameters are setup in private method
// Delphi XE3
Data.SqlExpr.TCustomSQLDataSet.SetParameterFromSQL
Inside this method the tablename is extracted and only
if csDesigning in ComponentState then
a temporary DataSet is created with
SELECT * FROM <tablename> WHERE 0 = 1
Every parameter is checked against the fieldnames from this dataset and if names match the parameter datatype is set.
Thats the reason why you get ftUnknown for your parameters at runtime.
To solve that Delphi does the same you tried with your solution, but dbexpress fails sometime. The Setter for parameters is located at
Data.DB.TParam.SetAsVariant
and value 1234567 has the variant type varLongword and parameter datatype will be set to ftLongword and causes this error.
Workaround
As a workaround you can set parameter datatype to ftString/ftWideString, as this will work in most cases.
procedure TTestClass.ExecuteSql(const ASql : String; const ParamVals : Array Of Variant);
var
i : integer;
Qry : TSqlQuery;
begin
Qry := NewQuery(ASql);
with Qry do
try
for i := Low(ParamVals) to High(ParamVals) do
begin
Qry.Params[i].DataType := ftWideString;
Qry.Params[i].Value := ParamVals[i];
end;
ExecSql();
finally
Free;
end;
end;
To get a better solution you get a procedure to set the parameter datatype to ftString/ftWidestring only for critical variant types (like your method SetParamValues but more general)
procedure SetParamValues( const AParams : TParams; const AValues : array of Variant );
var
LIdx : Integer;
LParam : TParam;
LValue : Variant;
begin
for LIdx := 0 to Pred( AParams.Count ) do
begin
LParam := AParams[LIdx];
LValue := AValues[LIdx];
// only handle the critical parts
case VarType( LValue ) of
varByte, varLongword : LParam.DataType := ftWideString;
end;
// all other will be set here
LParam.Value := LValue;
end;
end;
Solution: the hard way
As i first stated there is no easy solution for this. For a full functional solution you have to parse the whole WHERE statement
SELECT a.*, b*
FROM table1 a
JOIN table2 b ON a.id = b.id
WHERE a.id = :id AND b.count / 2 = :halfcount
and build a query from this
SELECT a.id as Param1, b.count / 2 as Param2
FROM table1 a
JOIN table2 b ON a.id = b.id
WHERE 0 = 1
to get the expected datatype.
Solution: the long way
IMHO this is a bug and should be reported to EMBA ...
Solution: the expensive way
I made a test with UniDAC and everything is the same as dbExpress. Parameter datatype is ftUnknown and setting parameter value will set the datatype to ftLongword.
But there is one special case: You will not get an error and your query is processed as you expect.