CODEWITHSUNDEEP
delphidelphi-10-seattlefiredac
Ilyes
Ilyes

Reputation: 14928

Get the result from the Query

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 :

Upvotes: 0

Views: 17808

Answers (2)

MartynA
MartynA

Reputation: 30715

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.

Upvotes: 3

Agustin Ortu
Agustin Ortu

Reputation: 240

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;

Upvotes: 3

Related Questions