Page 1 of 1
Add parameters to a TUniQuery at runtime
Posted: Fri 23 Sep 2016 19:53
by brian71us
Hello,
I am working on my first project with the Universal Data Access (UniDAC) components in Delphi 10.1 Berlin. The project is a web server that processes remote method calls from a web page. One of the remote method calls executes a query on the server.
How can I add parameters to the TUniQuery at runtime?
for I := 0 to jo.Count - 1 do
begin
pair := jo.Pairs;
parameter := query.Params.AddParameter;
parameter.Name := '@Param' + IntToStr(I + 1);
parameter.Value := pair.JsonValue.Value;
query.Params.AddParam(parameter);
end;
Re: Add parameters to a TUniQuery at runtime
Posted: Tue 27 Sep 2016 09:50
by azyk
UniDAC generates parameter list for dataset automatically, every time when the TUniQuery.SQL.Text property value changes. In order to disable automatic parameters generating, set the TUniQuery.ParamCheck property to False (True by default). For example, before setting the TUniQuery.SQL.Text property value:
Code: Select all
query.ParamCheck := False;
query.SQL.Text := ...
...
for I := 0 to jo.Count - 1 do
begin
pair := jo.Pairs[I];
parameter := query.Params.AddParameter;
parameter.Name := '@Param' + IntToStr(I + 1);
parameter.Value := pair.JsonValue.Value;
query.Params.AddParam(parameter);
end;
See more details about the ParamCheck property in our online documentation:
https://www.devart.com/unidac/docs/?dev ... mcheck.htm
Re: Add parameters to a TUniQuery at runtime
Posted: Tue 27 Sep 2016 13:02
by brian71us
Not for me
procedure TDataModule1.updateRecordsExecute(Sender: TRtcConnection;
Param: TRtcFunctionInfo; Result: TRtcValue);
var
jo: TJSONObject;
columns: string;
criteria: string;
I: integer;
pair: TJSONPair;
data: string;
sql: string;
parameter: TParam;
cmd: TUniQuery;
begin
cmd := TUniQuery.Create(nil);
try
begin
cmd.Connection := self.UniConnection1;
cmd.SQL.Text := 'UPDATE [PhoneNumber] SET Number=@Param1, PhoneType=@Param2 WHERE [Oid] = ''799cd874-5f11-4469-9078-00196ac89d24'''
for I := 0 to jo.Count - 1 do
begin
pair := jo.Pairs
;
if cmd.ParamCount > 0 then <===== cmd.ParamCount = 0 when it gets here
begin
parameter := cmd.ParamByName('@Param' + IntToStr(I + 1));
if Assigned(parameter) then
parameter.Value := pair.JsonValue.Value;
end;
end;
try
cmd.ExecSQL; <======== Throws an exception: Must declare scalar variable "@Param1".
Result.asString := 'Success';
except
on E: Exception do
Result.asString := E.Message;
end;
finally
cmd.Free;
end;
What am I doing wrong?
Re: Add parameters to a TUniQuery at runtime
Posted: Tue 27 Sep 2016 21:19
by ertank
brian71us wrote:Not for me
cmd.SQL.Text := 'UPDATE [PhoneNumber] SET Number=@Param1, PhoneType=@Param2 WHERE [Oid] = ''799cd874-5f11-4469-9078-00196ac89d24'''
What am I doing wrong?
I believe, parameters should be identified with : and not with @
Re: Add parameters to a TUniQuery at runtime
Posted: Wed 28 Sep 2016 08:37
by azyk
In text of a parametrized SQL query, parameter names must start with colon. And when calling a dataset parameter by name, the parameter name must be specified without leading colon. To avoid the error you provided, replace the following line in your sample:
Code: Select all
cmd.SQL.Text := 'UPDATE [PhoneNumber] SET Number=@Param1, PhoneType=@Param2 WHERE [Oid] = ''799cd874-5f11-4469-9078-00196ac89d24'''
with
Code: Select all
cmd.SQL.Text := 'UPDATE [PhoneNumber] SET Number=:Param1, PhoneType=:Param2 WHERE [Oid] = ''799cd874-5f11-4469-9078-00196ac89d24''';
and the line:
Code: Select all
parameter := cmd.ParamByName('@Param' + IntToStr(I + 1));
with
Code: Select all
parameter := cmd.ParamByName('Param' + IntToStr(I + 1));