Author: Erwin Molendijk 


Running a TIBStoredProc as if it where a delphi procedure.

Answer:
1   
2   {
3   Copyright (c) 2001 by E.J.Molendijk
4   
5   TIBStoredProc is handy, but multiple lines of code are required to execute it. The 
6   routine in this article handles preparing, assigning params, execution and 
7   transactions for you.
8   }
9   
10  {
11    ExecSP
12    Execute a InterBase Stored Procedure.
13    Transaction gets Committed after excution.
14  
15    input:
16      SP = InterBase Stored Procedure
17      P  = Array with parameters for the SP. No param checking!
18  
19    output:
20      Check the SP.Params for output (if any).
21  }
22  
23  procedure TSPMod.ExecSP(SP: TIBStoredProc; P: array of Variant);
24  var
25    A, B: Integer;
26  begin
27    // make sure there's a transaction context
28    if not SP.Transaction.Active then
29      SP.Transaction.StartTransaction;
30  
31    try
32      // make sure stored procedure is closed
33      SP.Close;
34  
35      // prepare (attach params)
36      if not SP.Prepared then
37        SP.Prepare;
38  
39      // Set all Input params
40      B := 0;
41      for A := 0 to SP.ParamCount - 1 do
42        if (SP.Params[A].ParamType in [ptInput, ptInputOutput]) then
43        begin
44          SP.Params[A].Value := P[B];
45          Inc(B);
46        end;
47  
48      // run the procedure on the server
49      SP.ExecProc;
50    finally
51      // commit
52      SP.Transaction.Commit;
53    end;
54  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. 
55  
56  // Example without returning data:
57  
58  procedure TSPMod.OpenSession(SessionID: Integer);
59  begin
60    ExecSP(spOpenSession, [SessionID]);
61  end;
62  
63  // Example with a integer as result
64  
65  function TSPMod.GetTicketNr: Integer;
66  begin
67    ExecSP(spGetTicketNr, [CurrentSessionID]);
68    Result := spGetTicketNr.ParamByName('TicketNr').AsInteger;
69  end;