Mega Code Archive
Executing TIBStoredProc with one line of code
<![CDATA[
Title: Executing TIBStoredProc with one line of code.
Question: 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;
]]>