IBDAC

Using Stored Procedures

This tutorial describes how to work with stored procedures using the TIBCStoredProc component to insert data into tables.

  1. Requirements
  2. General information
  3. Input Parameters
  4. Output Parameters
  5. Input/Output Parameters
  6. Using Firebird Stored Functions
  7. Returning Result Sets

Requirements

This tutorial assumes that you have already connected to the server (see Connecting to InterBase and Firebird, created the necessary objects on the server (see Creating Database Objects), and inserted data into tables (see Inserting Data Into Tables). To insert data at runtime, add the IBC unit to the uses clause for Delphi or include the IBC.hpp header file for C++ Builder.

General Information

A stored procedure is a group of one or more SQL statements grouped as a logical unit and stored in the database. Stored procedures are intended to perform a specific task or a set of related tasks. They combine the ease and flexibility of the SQL language with the procedural functionality of a structured programming language. Complicated business rules and programming logic that may require execution of multiple SQL statements should be kept in stored procedures, which can be called by the client applications.

A stored function is similar to a stored procedure, but there are some differences: a function must return a value, whereas in a stored procedure it is optional; a function can have only input parameters, whereas a procedures can have input or output parameters; a function can be called from a procedure, whereas a procedure cannot be called from a function.

Input parameters

Input parameters are used to pass values from the calling program to the stored procedure. If the procedure changes the input value, the change has effect only within the procedure, and the input variable will preserve its original value when control is returned to the calling program. The following procedure inserts a new row into the table dept:

CREATE PROCEDURE InsertDept(
p_deptno integer,
p_dname varchar(14),
p_loc varchar(13)
) AS
BEGIN
  INSERT INTO dept(deptno, dname, loc) VALUES(:p_deptno, :p_dname, :p_loc);
END;

The procedure accepts three input arguments that correspond to the fields of the table, and can be executed as follows:

EXECUTE PROCEDURE InsertDept(10,'ACCOUNTING','NEW YORK');

The code below demonstrates the use of the TIBCStoredProc component to execute the InsertDept stored procedure:

Delphi

var
  IBCStoredProc1: TIBCStoredProc;
begin
  IBCStoredProc1 := TIBCStoredProc.Create(nil);
  try
    // IBCConnection1 was set up earlier
    IBCStoredProc1.Connection := IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1.StoredProcName := 'InsertDept';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1.PrepareSQL;
    // searches parameters by their names and assigns new values
    IBCStoredProc1.ParamByName('p_deptno').AsInteger := 10;
    IBCStoredProc1.ParamByName('p_dname').AsString := 'ACCOUNTING';
    IBCStoredProc1.ParamByName('p_loc').AsString := 'NEW YORK';
    // executes the stored procedure
    IBCStoredProc1.Execute;
  finally
    IBCStoredProc1.Free;
  end;
end;

C++Builder

TIBCStoredProc* IBCStoredProc1 = new TIBCStoredProc(NULL);
try {
    // IBCConnection1 was set up earlier
    IBCStoredProc1->Connection = IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1->StoredProcName = "InsertDept";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1->PrepareSQL();
    // searches parameters by their names and assigns new values
    IBCStoredProc1->ParamByName("p_deptno")->AsInteger = 10;
    IBCStoredProc1->ParamByName("p_dname")->AsString = "ACCOUNTING";
    IBCStoredProc1->ParamByName("p_loc")->AsString = "NEW YORK";
    // executes the stored procedure
    IBCStoredProc1->Execute();
}
__finally {
    IBCStoredProc1->Free();
}

Output Parameters

Output parameters are used to return values from the procedure to the calling application. The initial value of the parameter in the procedure is NULL, and the value becomes visible to the calling program only when the procedure returns it. The following stored procedure returns the count of records in the dept table:

CREATE PROCEDURE CountDept
RETURNS (cnt integer)
BEGIN
  SELECT count(*) FROM dept into cnt;
END;

The code below demonstrates the use of the TIBCStoredProc component to execute the CountDept stored procedure:

Delphi

var
  IBCStoredProc1: TIBCStoredProc;
begin
  IBCStoredProc1 := TIBCStoredProc.Create(nil);
  try
    // IBCConnection1 was set up earlier
    IBCStoredProc1.Connection := IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1.StoredProcName := 'CountDept';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1.PrepareSQL;
    // executes the stored procedure
    IBCStoredProc1.Execute;
    // shows the value of the output parameter
    ShowMessage(IntToStr(sp.ParamByName('cnt').AsInteger));
  finally
    IBCStoredProc1.Free;
  end;
end;

C++Builder

TIBCStoredProc* IBCStoredProc1 = new TIBCStoredProc(NULL);
try {
    // IBCConnection1 was set up earlier
    IBCStoredProc1->Connection = IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1->StoredProcName = "CountDept";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1->PrepareSQL();
    // executes the stored procedure
    IBCStoredProc1->Execute();
    // shows the value of the output parameter
    ShowMessage(IntToStr(sp->ParamByName("cnt")->AsInteger));
}
__finally {
    IBCStoredProc1->Free();
}

Input/output parameters

A stored procedure that contains input and output parameters can both accept and return values. Programs can pass a value to the stored procedure, which does something under the hood, and passes the resulting value back to the calling program. The input value must be set before executing the stored procedure. The output value is returned after executing the stored procedure.

The following stored procedure returns the salary with a 5% percent bonus:

CREATE PROCEDURE GiveBonus(sal float)
RETURNS (bonus float)
AS
BEGIN
  bonus=sal * 1.05;
END;

The code below demonstrates the use of the TIBCStoredProc component to execute the GiveBonus stored procedure:

Delphi

var
  IBCStoredProc1: TIBCStoredProc;
begin
  IBCStoredProc1 := TIBCStoredProc.Create(nil);
  try
    // IBCConnection1 was set up earlier
    IBCStoredProc1.Connection := IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1.StoredProcName := 'GiveBonus';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1.PrepareSQL;
    // searches a parameter by its name and assigns a new value
    IBCStoredProc1.ParamByName('sal').AsFloat := 500.5;
    // executes the stored procedure
    IBCStoredProc1.Execute;
    // shows the resulting value
    ShowMessage(IBCStoredProc1.ParamByName('sal').AsString);
  finally
    IBCStoredProc1.Free;
  end;
end;

C++Builder

TIBCStoredProc* IBCStoredProc1 = new TIBCStoredProc(NULL);
try {
    // IBCConnection1 was set up earlier
    IBCStoredProc1->Connection = IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1->StoredProcName = "GiveBonus";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1->PrepareSQL();
    // searches a parameter by its name and assigns a new value
    IBCStoredProc1->ParamByName("sal")->AsFloat = 500.5;
    // executes the stored procedure
    IBCStoredProc1->Execute();
    // shows the resulting value
    ShowMessage(IBCStoredProc1->ParamByName("sal")->AsString);
}
__finally {
    IBCStoredProc1>Free();
}

Using Firebird Stored Functions

The tasks above can also be accomplished using stored functions in Firebird. For example, the following stored function returns the salary with a 5% percent bonus:

CREATE FUNCTION GiveBonusFunc(sal float)
RETURNS float
AS
BEGIN
  RETURN sal * 1.05;
END;

This function can be executed as follows:

SELECT GiveBonusFunc(500.5);

The code below demonstrates the use of the TIBCStoredProc component to execute the GiveBonusFunc stored function:

Delphi

var
  IBCStoredProc1: TIBCStoredProc;
begin
  IBCStoredProc1 := TIBCStoredProc.Create(nil);
  try
    // IBCConnection1 was set up earlier
    IBCStoredProc1.Connection := IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1.StoredProcName := 'GiveBonusFunc';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1.PrepareSQL;
    // searches a parameter by its name and assigns a new value
    IBCStoredProc1.ParamByName('sal').AsFloat := 500.5;
    // executes the stored procedure
    IBCStoredProc1.Execute;
    // shows the resulting value
    ShowMessage(IBCStoredProc1.ParamByName('result').AsString);
  finally
    IBCStoredProc1.Free;
  end;
end;

C++Builder

TIBCStoredProc* IBCStoredProc1 = new TIBCStoredProc(NULL);
try {
    // IBCConnection1 was set up earlier
    IBCStoredProc1->Connection = IBCConnection1;
    // indicates the name of the stored procedure to call
    IBCStoredProc1->StoredProcName = "GiveBonusFunc";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc1->PrepareSQL();
    // searches a parameter by its name and assigns a new value
    IBCStoredProc1->ParamByName("sal")->AsFloat = 500.5;
    // executes the stored procedure
    IBCStoredProc1->Execute();
    // shows the resulting value
    ShowMessage(IBCStoredProc1->ParamByName("result")->AsString);
  }
__finally {
    IBCStoredProc1->Free();
}
}

Note: To retrieve the result returned by the stored function using TIBCStoredProc, use the automatically created 'result' parameter.

Returning Result Sets

Besides scalar variables, a stored procedure can return a result set generated by the SELECT statement. See Using Stored Procedures with Result Sets for more information.

© 1997-2022 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback