Get the result from the Query

Question

I write this code :

Var Q : TFDQuery;
begin
Q := TFDQuery.Create(Self);
Q.Connection := FDConnection1;
Q.Params.CreateParam(ftString,'N',ptOutput);// Try also ptResult
Q.Params.CreateParam(ftInteger,'ID',ptInput);
Q.SQL.Text := 'SELECT NOM FROM EMPLOYEE WHERE ID_EMP = :ID';
Q.Params.ParamByName('ID').Value := 1;
Q.Active := True;
ShowMessage( VarToStr(Q.Params.ParamByName('N').Value) );

The result should be the name of the employer.

I get an error :

'N' parameter not found

How can I get the result from the Query using the parameter?

If I can't , what is the the function of :

• ptOutput

• ptResult

Answer 1

Try this code:

procedure TForm1.ExecuteQuery;
var
  SQL : String;
  Q : TFDQuery;
begin
  SQL := 'select ''Sami'' as NOM';  //  Tested with MS Sql Server backend
  try
    Q := TFDQuery.Create(Self);
    Q.Connection := FDConnection1;
    Q.Params.CreateParam(ftString, 'Nom', ptOutput);// Try also ptResult
    Q.SQL.Text := SQL;
    Q.Open;
    ShowMessage( IntToStr(Q.ParamCount));
    Caption := Q.FieldByName('Nom').AsString;
  finally
    Q.Free;  // otherwise you have a memory leak
  end;
end;

You'll see that the created parameter no longer exists once the FDQuery is opened, because FireDAC "knows" that there is nothing it can do with it.

Then, replace Q.Open by Q.ExecSQL. When that executes you get an exception with the message

Cannot execute command returning result set. Hint: Use Open method for SELECT-like commands.

And that's your problem. If you use a SELECT statement, you get a result set whether you like it or not, and the way to access its contents is to do something like

Nom := Q.FieldByName('Nom').AsString

You asked in a comment what is the point of ptOutput parameters. Suppose your database has a stored procedure defined like this

Create Procedure spReturnValue(@Value varchar(80) out) as select @Value = 'something'

Then, in your code you could do

  SQL := 'exec spReturnValue :Value';  //  note the absence of the `out` qualifier in the invocation of the SP

  try
    Q := TFDQuery.Create(Self);
    Q.Connection := FDConnection1;
    Q.Params.CreateParam(ftString, 'Value', ptOutput);// Try also ptResult
    Q.SQL.Text := SQL;
    Q.ExecSQL;
    ShowMessage( IntToStr(Q.ParamCount));
    Caption := Q.ParamByName('Value').AsString;
  finally
    Q.Free;  // otherwise you have a memory leak
  end;

which retrieves the output parameter of the Stored Proc into Q's Value parameter.