Using Firedac to run SQL stored procedure

Question

I'm trying to figure out how to run a stored procedure using firedac

unit DataLayer.OilCommanderConnection;

interface

uses
  FireDAC.Phys.FB,
  Generics.Collections,
  Model.Sample,
  Model.Batch,

  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error,
  FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool,
  FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.MySQL, Data.DB,
  FireDAC.Comp.Client, FireDAC.Phys.MSSQL,
  FireDAC.DApt,
  FireDAC.Comp.UI
  ;

type
  TOilCommanderConnection = class
    strict private
    public
      Connection : TFDConnection;

      function GetSampleTypesForBatch(Batch : TBatch) : Boolean;  

      function Connect:Boolean;
      constructor Create;
      destructor Destroy; override;
  end;

implementation

uses
  SysUtils
  ;

function TOilCommanderConnection.Connect:Boolean;
var
    OK : Boolean;
begin
    OK := true;
    Connection := TFDConnection.Create(nil);
    try
        Connection.Params.LoadFromFile('MSSQL.ini');
    finally
        Result := OK;
    end;
end;

function TOilCommanderConnection.GetSampleTypesForBatch(Batch : TBatch) : Boolean;
var
    StoredProc : TFDStoredProc;
begin
    Connect;

    StoredProc := TFDStoredProc.Create(nil);
    try
        StoredProc.Connection := Connection;
        StoredProc.StoredProcName := 'GetSampleTypesForBatch';
        StoredProc.Prepare;

        StoredProc.FetchOptions.Items := StoredProc.FetchOptions.Items - [fiMeta];
        with StoredProc.Params do
        begin
            Clear;
            with Add do
            begin
                Name := 'BatchNo';
                ParamType := ptInput;
                DataType := ftString;
                Size := 6;
            end;            
        end;

        StoredProc.StoredProcName := 'GetSampleTypesForBatch';
        StoredProc.Prepare;
        StoredProc.Params[0].Value := Batch.RackNo;
        StoredProc.ExecProc;

        while not StoredProc.Eof do
        begin
            //StoredProc.FieldByName('').AsS
            StoredProc.Next;
        end;

    finally
        FreeAndNil(StoredProc);
    end;

    Result := true;
end;

constructor TOilCommanderConnection.Create;
begin
    inherited;
    Connection := TFDConnection.Create(nil);
end;

destructor TOilCommanderConnection.Destroy;
begin
    if Assigned(Connection) then FreeAndNil(Connection);

    inherited;
end;


end.

I get an error message a the first occurrence of the line

StoredProc.Prepare;

Here is the message

--------------------------- Debugger Exception Notification

Project RefractiveIndexTests.exe raised exception class Exception with message 'Object factory for class {3E9B315B-F456-4175-A864-B2573C4A2201} is missing. To register it, you can drop component [TFDGUIxWaitCursor] into your project'.

I've called the function using

OilCommanderConnection.GetSampleTypesForBatch(batch);

from a test project.

the tutorial I read didn't explain what to do about this situation.

I've tried adding TFDGUIxWaitCursor into my project as the error message suggests but this has not made any difference. I wonder if this problem is related to me keeping the Database connection logic in a separate unit to my Main Form. I would like to be able to separate my user interface from my Data Layer.

Answer 1

Depending on the type of your application, include one of the following units into any one "uses" clause:

• FireDAC.VCLUI.Wait - for VCL applications;
• FireDAC.FMXUI.Wait - for FireMonkey applications;
• FireDAC.ConsoleUI.Wait - for console / non-visual applications.