Executing TIBStoredProc with one line of code

Problem/Question/Abstract:

Running a TIBStoredProc as if it where a delphi procedure.

Answer:

{
Copyright (c) 2001 by E.J.Molendijk

TIBStoredProc is handy, but multiple lines of code are required to execute it. The routine in this article handles preparing, assigning params, execution and transactions for you.
}

{
  ExecSP
  Execute a InterBase Stored Procedure.
  Transaction gets Committed after excution.

  input:
    SP = InterBase Stored Procedure
    P  = Array with parameters for the SP. No param checking!

  output:
    Check the SP.Params for output (if any).
}

procedure TSPMod.ExecSP(SP: TIBStoredProc; P: array of Variant);
var
  A, B: Integer;
begin
  // make sure there's a transaction context
  if not SP.Transaction.Active then
    SP.Transaction.StartTransaction;

  try
    // make sure stored procedure is closed
    SP.Close;

    // prepare (attach params)
    if not SP.Prepared then
      SP.Prepare;

    // Set all Input params
    B := 0;
    for A := 0 to SP.ParamCount - 1 do
      if (SP.Params[A].ParamType in [ptInput, ptInputOutput]) then
      begin
        SP.Params[A].Value := P[B];
        Inc(B);
      end;

    // run the procedure on the server
    SP.ExecProc;
  finally
    // commit
    SP.Transaction.Commit;
  end;
end;

Examples:

Assume you have a datamodule called SPMod. And assume it contains some stored procedures:

SPMod.spOpenenSession
SPMod.spGetTicketNr

The following routines can be added to encapsulate the StoredProcs.

// Example without returning data:

procedure TSPMod.OpenSession(SessionID: Integer);
begin
  ExecSP(spOpenSession, [SessionID]);
end;

// Example with a integer as result

function TSPMod.GetTicketNr: Integer;
begin
  ExecSP(spGetTicketNr, [CurrentSessionID]);
  Result := spGetTicketNr.ParamByName('TicketNr').AsInteger;
end;