How do I use parameters in SQL executed in context of the Session? I'm getting error #700 with message text "Invalid expression ? found, dynamic parameter references are not allowed)".

Here's my code:-

procedure TForm3.DoSecpolUser;
const
 cPass = 'SecretSquirrel';
 cSQL = 'ALTER USER "SECPOL" PASSWORD :PASS ATTRIBUTES :ATTRIBS';
var
 Prms: TParams;
 S: TStringlist;
begin
 Prms:=TParams.Create(nil);
 S:=TStringlist.Create;
 S.Add('ID=123');
 S.Add('Yobbo=Charles');

 Prms.CreateParam(ftWidestring, 'PASS', ptInput);
 Prms.CreateParam(ftWideMemo, 'ATTRIBS', ptInput);

 Prms.ParamByName('PASS').AsString:=cPass;
 Prms.ParamByName('ATTRIBS').AsString:=S.Text;

 with EMAdminDatabase(DB) do try
   Session.Execute(cSQL, Prms);
 finally
   Free;
   Prms.Free;
   S.Free;
 end;

end;

Charles


Trying in EDBManager with

ALTER USER "NU"
PASSWORD
ATTRIBUTES :atr

I get

ElevateDB Error #700 An error was found in the statement at line 2 and column 10 (Invalid expression ? found, dynamic parameter references not allowed)

So the password is considered as a dynamic parameter - I thought it was limited to things like columns names or bits in an IN () but obviously not.

You'll have to make cSQL a variable and build the string prior to passing across eg


 cSQL:= 'ALTER USER "SECPOL" PASSWORD '+ QuotedStr(cPass) +' ATTRIBUTES ' +QuotedStr(S.Text):

I think you'll need to work on S. Without trying it I'm not certain that the CRLF will translate across properly inside quotes

Roy Lambert

Roy Lambert wrote:

<<Trying in EDBManager with

ALTER USER "NU"
PASSWORD
ATTRIBUTES :atr >>

I've changed it so the only parameter is for the attributes - password explicitly in the SQL - but same problem. I've tried it using a query component too. Same error.

ALTER USER "SECPOL" PASSWORD 'MyPass' ATTRIBUTES :ATTRIBS

I'm beginning to suspect DDL statements can't be parameterised.

Guess what, SQL Server 2017 only supports parameters in DML statements and not in DDL...

I think I've got my answer. Hmmm...