Add parameters to a TUniQuery at runtime

[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;

[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

[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?

[ertank]

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 @

[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));