Stored procedure returning select and output parameter

[viniciusfbb]

Hello, in the last few years I started to use many stored procedures and it always works well when it returns results using output parameters or internally using SELECT without INTO.
However, there are cases where I feel the need to return both at the same time, that is, return a list of results (SELECT without INTO) and return output parameters at the same time (this is perfectly possible, even dbForge works perfectly , displaying the output parameters and the resulting select at the same time). However, in this case I can't capture the output parameter via MyDAC, I get the message that it doesn't exist. Maybe I'm not doing it the right way, so I bring my code just below:

Stored procedure:



Delphi code:

Code: Select all

function CreateStoredProc(const AStoredProcedureName: string): TMyStoredProc;
begin
  Result := TMyStoredProc.Create(nil);
  try
    Result.Options.FieldsAsString := False;
    Result.Options.EnableBoolean := False;
    Result.Options.BinaryAsString := False;
    Result.Connection := Connection;
    Result.StoredProcName := AStoredProcedureName;
    Result.PrepareSQL;
  except
    FreeAndNil(Result);
    raise;
  end;
end;

procedure GetAccountsSummarized(const ASearchText: string; const ALimitOffset, ALimitRowCount: Cardinal; out ATotalRows: Cardinal; out ASelectResults: TArray<TipGetAccountsSummarizedResult>);
var
  LSelectResults: TList<TipGetAccountsSummarizedResult>;
  LSelectResultsItem: TipGetAccountsSummarizedResult;
  LGetAccountsSummarizedStoredProc: TMyStoredProc;
begin
  LGetAccountsSummarizedStoredProc := CreateStoredProc('get_accounts_summarized');
  LSelectResults := TList<TipGetAccountsSummarizedResult>.Create;
  try
    LGetAccountsSummarizedStoredProc.ParamByName('in_search_text').AsString := ASearchText;
    LGetAccountsSummarizedStoredProc.ParamByName('in_limit_offset').AsLongWord := ALimitOffset;
    LGetAccountsSummarizedStoredProc.ParamByName('in_limit_row_count').AsLongWord := ALimitRowCount;
    LGetAccountsSummarizedStoredProc.Execute;
    try
      ATotalRows := LGetAccountsSummarizedStoredProc.FieldByName('@out_total_rows').AsLongWord;
      while not LGetAccountsSummarizedStoredProc.Eof do
      begin
        LSelectResultsItem.AccountId := LGetAccountsSummarizedStoredProc.FieldByName('account_id').AsLongWord;
        LSelectResultsItem.AccountName := LGetAccountsSummarizedStoredProc.FieldByName('account_name').AsString;
        LSelectResultsItem.AccountEmail := LGetAccountsSummarizedStoredProc.FieldByName('account_email').AsString;
        LSelectResultsItem.AccountDocument := LGetAccountsSummarizedStoredProc.FieldByName('account_document').AsString;
        LSelectResults.Add(LSelectResultsItem);
        LGetAccountsSummarizedStoredProc.Next;
      end;
    finally
      LGetAccountsSummarizedStoredProc.Close;
    end;
    ASelectResults := LSelectResults.ToArray;
  finally
    LSelectResults.Free;
  end;
end;

The error message: Exception class EDatabaseError with message 'Field '@out_total_rows' not found'.

See that in dbForge it works as expected:

[viniciusfbb]

I've tried several different ways, it really seems to be a MyDAC error/limitation. Please investigate this as soon as possible.

[pavelpd]

Hi there,
Thanks for contacting us.

The behavior you describe is considered normal.

In the stored procedure example you provided, the TMyStoredProc component will have two result sets after executing it.
And the 'Field '@out_total_rows' not found' error you mentioned occurs because you are accessing a result set that doesn't have the specified field.

Typically, output parameters will be stored in the latest result set.
To get to the required result set, you need to use the TMyStoredProc.OpenNext method;
More information about OpenNext method can be found via the following link:
https://docs.devart.com/mydac/devart.my ... next().htm

Please note that TMyStoredProc doesn't allow multiple result sets to be accessed or displayed at the same time.

A small code sample, based on your stored procedure example and accessing the '@out_total_rows' field:

Code: Select all

...
MyStoredProc1.StoredProcName := 'testproc';
MyStoredProc1.PrepareSQL;
MyStoredProc1.ParamByName('in_limit_offset').AsInteger := 2;
MyStoredProc1.ParamByName('in_limit_row_count').AsInteger := 3;
MyStoredProc1.FetchAll := false;
MyStoredProc1.Execute;
MyStoredProc1.OpenNext;

ShowMessage(MyStoredProc1.FieldByName('@out_total_rows').AsString);
...