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

The mistake you are making is to assume that the query returns a single value. It does not, it returns a result set. Also, although it might not be the case with your data, there may be more than one data row which matches your search criterion. — MartynA, Mar 30 '17 at 07:31
@MartynA thank you for the comment , the query return one row , I know that I can get the result from the `Field` , but I want to return just the name of the employer in the `Output Paramater`. — Ilyes, Mar 30 '17 at 07:34

score 3 · Answer 1 · answered Mar 30 '17 at 02:24

3

There is no need to manually create parameters. Data access components are smart enough to parse the SQL string and populate the parameters collection by themselves

Also to get the result you must read the query's fields. When you call Open on a Query component, the fields collection will be populated with the fields that you specified in the SELECT [fields] SQL statement

As a side note, I advice that you use the type-safe version to get the value from a TField or TParameter object: See more here

var 
  q : TFDQuery;
begin
  q := TFDQuery.Create(Self);
  q.Connection := FDConnection1;
  q.SQL.Text := 'SELECT NOM FROM EMPLOYEE WHERE ID_EMP = :ID';
  q.ParamByName('ID').AsInteger := 1;
  q.Open;
  ShowMessage(q.FieldByName('Nom').AsString);
end;

answered Mar 30 '17 at 02:24

Agustin Ortu

230
4
8

Thank you for the answer , but in my Q i said with `Parameter` , I know how to get the data from the `Field` , I want to do that with `ptOutput` parameter , and If I can't what is the purpose of this kind of `Parameters`. – Ilyes Mar 30 '17 at 07:36
2

@Sami there is no output parameter in your example, so you can't use `ptOutput`. You are selecting a table field, so you have to use the `Fields` property. An output parameter is only used with a stored procedure that actually defines an output parameter. Only then can you use `ptOutput`. – Remy Lebeau Mar 30 '17 at 15:51
@RemyLebeau Thank you :) – Ilyes Mar 30 '17 at 16:08

MartynA · Accepted Answer · 2017-03-30T10:44:00.320

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.

Get the result from the Query

2 Answers2