Creating and Using Stored Procedures

The InterBase 6 Data Definition Guide in the zip file recommended earlier was most useful. It gives these advantages of using stored procedures.

Modular design: Applications that access the same database can share stored procedures, eliminating duplicate code and reducing the size of the applications.
Streamlined maintenance: When a procedure is updated, the changes are automatically reflected in all applications that use it without the need to recompile and relink them; applications are compiled and optimized only once for each client.
Improved performance: Stored procedures are executed by the server, not the client, which reduces network traffic, and improves performance, especially for remote client access.

We used the isql command line tool with a script file to enter the stored procedures FindID and CalcPercent. These are written in procedural SQL (psql). The second procedure calls the first. The syntax is different from Pascal and you are likely to make a few mistakes at first. For example, The assignment operator is = not :=, you declare variables after the keyword DECLARE not VAR and there must be no colon between the identifier and its type.

Note the SET TERM command to change the line terminator to # for the reason given below.

From the Data Definition Guide:

"CREATE PROCEDURE is a statement that must end with a terminator, just as all other SQL statements must. But the CREATE PROCEDURE statement contains other statements within it and these contained statements must also end with the terminator. If isql were to interpret semicolons as statement terminators, then procedures would execute during their creation rather than when they are called."

The code of Procedure FindID follows.

SET TERM # ;
CREATE PROCEDURE FindID(Name VARCHAR(20)) RETURNS (ProgID INT, FirstName 
                        VARCHAR(15), LastName VARCHAR(15)) AS 
BEGIN 
  SELECT ID, Forename, Surname FROM Programmer, Programs 
  WHERE UPPER(Name) = UPPER(:Name) AND ID =  ProgrammerID 
  INTO :ProgID, :FirstName, :LastName;
  SUSPEND; 
END#
SET TERM ;#
GRANT EXECUTE ON PROCEDURE FindID to PUBLIC WITH GRANT OPTION;

The following command is one test of the procedure.

EXECUTE PROCEDURE FindID('Risk');

      PROGID FIRSTNAME       LASTNAME      
============ =============== =============== 
           2 Michael         Wardley

The SUSPEND statement prompts the fetching of the data by the code that requests it. The colon is used to denote a variable rather than a column and is widely used in SQL although not always necessary in psql.

Procedure CalcPercent demonstrates the use of local variables and how to call another stored procedure.

SET TERM # ;
CREATE PROCEDURE CalcPercent (Name VARCHAR(20)) RETURNS (Percent INT) AS
DECLARE Count1 INT;
DECLARE Count2 INT;
DECLARE ProgID INT;
DECLARE FirstName VARCHAR(15);
DECLARE LastName VARCHAR(15);
BEGIN
  EXECUTE PROCEDURE FindID :Name RETURNING_VALUES :ProgID, :FirstName, :LastName;
  SELECT COUNT(Name) FROM Programs WHERE ProgrammerID = :ProgID INTO Count1;
  SELECT COUNT(Name) FROM Programs INTO Count2;
  Percent = Count1 * 100 / Count2;
  SUSPEND; 
END# 
SET TERM ;#
GRANT EXECUTE ON PROCEDURE CalcPercent to PUBLIC WITH GRANT OPTION;

The following command is one test of the procedure.

EXECUTE PROCEDURE CalcPercent('MarbleDrop');

     PERCENT
============
           4

The division with the operator / gives an integer result because we declared Percent as an integer. This procedure uses the COUNT function. Other built-in functions include AVG, MIN and MAX.

The coding of a procedure capable of returning several rows is trickier and needs a loop. We have not added this procedure to the script. We pasted it at the SQL> prompt within isql.

SET TERM # ;
CREATE PROCEDURE FindSurname (Forename VARCHAR(15)) 
  RETURNS (lastname VARCHAR(15)) AS 
BEGIN 
  FOR SELECT Surname FROM Programmer 
      WHERE Forename = :Forename 
      INTO :lastname DO
    SUSPEND;    
END#
SET TERM ; #

We tested it with an argument that returns more than a single record.

SELECT * FROM FindSurname('Adam');

LASTNAME
===============
Renak
Greenberg

If you become keen on writing your own routines, please note that no more than 1,400 input parameters can be passed to a stored procedure!

Program FirebirdDemo4 shows you how to use stored procedures in Pascal. We supply the source for all demonstration programs and the database Contributions.fdb in db_contributions.zip. The Firebird demonstrations are set up with the database in the F:\Firebird directory, so put it there or change the DatabaseName property of the connection to its filepath on your system. The demonstrations need either Firebird server to be running or the embedded Firebird files (fbclient.dll etc.) to be in your program folder. The code follows a screenshot.

Program FirebirdDemo4 in action

uFirebirdDemo4.pas

unit uFirebirdDemo4;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, sqldb, IBConnection, FileUtil, Forms, Controls,
  Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls;

type

  { TfrmSearch }

  TfrmSearch = class(TForm)
    dbedtPercent : TDBEdit;
    dsPercent : TDatasource;
    dsProgrammer : TDatasource;
    dbedtForename : TDBEdit;
    dbedtSurname : TDBEdit;
    edtSearch : TEdit;
    IBConnection1 : TIBConnection;
    Label1 : TLabel;
    sqlqProgrammer : TSQLQuery;
    sqlqPercent : TSQLQuery;
    SQLTransaction1 : TSQLTransaction;
    procedure edtSearchEditingDone(Sender : TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormCreate(Sender : TObject);
  end; 

var
  frmSearch: TfrmSearch;

implementation

{$R *.lfm}

procedure TfrmSearch.edtSearchEditingDone(Sender: TObject);
begin
  dbedtForename.Clear;
  dbedtSurname.Clear;
  dbedtPercent.Clear;
  sqlqProgrammer.Close;
  sqlqProgrammer.Params[0].AsString := edtSearch.Text;
  sqlqProgrammer.Open;
  sqlqPercent.Close;
  sqlqPercent.Params[0].AsString := edtSearch.Text;
  sqlqPercent.Open;
end;

procedure TfrmSearch.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  sqlqProgrammer.Close;
  sqlqPercent.Close;  
  SQLTransaction1.Active:= False;
  IBConnection1.Connected:= False;
end;

procedure TfrmSearch.FormCreate(Sender: TObject);
begin
  label1.Caption:= 'Type the name of a contributed program in the left edit box ' +
                   'to find the programmer and his/her percentage of contributions.';
end;

end.

uFirebirdDemo4.lfm

object frmSearch: TfrmSearch
  Left = 307
  Height = 88
  Top = 186
  Width = 434
  Caption = 'Search'
  ClientHeight = 88
  ClientWidth = 434
  OnClose = FormClose
  OnCreate = FormCreate
  LCLVersion = '0.9.30'
  object edtSearch: TEdit
    Left = 0
    Height = 28
    Top = 55
    Width = 152
    OnEditingDone = edtSearchEditingDone
    TabOrder = 0
  end
  object dbedtForename: TDBEdit
    Left = 160
    Height = 28
    Top = 55
    Width = 120
    DataField = 'FIRSTNAME'
    DataSource = dsProgrammer
    CharCase = ecNormal
    MaxLength = 15
    TabOrder = 1
  end
  object dbedtSurname: TDBEdit
    Left = 288
    Height = 28
    Top = 55
    Width = 112
    DataField = 'LASTNAME'
    DataSource = dsProgrammer
    CharCase = ecNormal
    MaxLength = 15
    TabOrder = 2
  end
  object dbedtPercent: TDBEdit
    Left = 408
    Height = 28
    Top = 55
    Width = 24
    DataField = 'PERCENT'
    DataSource = dsPercent
    CharCase = ecNormal
    MaxLength = 0
    TabOrder = 3
  end
  object Label1: TLabel
    Left = 0
    Height = 50
    Top = 2
    Width = 430
    AutoSize = False
    Color = clDefault
    ParentColor = False
    Transparent = False
    WordWrap = True
  end
  object dsProgrammer: TDatasource
    DataSet = sqlqProgrammer
    left = 136
    top = 65520
  end
  object sqlqProgrammer: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    Database = IBConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
      'Select * from FindID(:ProgName);'
    )
    InsertSQL.Strings = (
      ''
    )
    Params = <    
      item
        DataType = ftUnknown
        Name = 'ProgName'
        ParamType = ptUnknown
      end>
    left = 344
    top = 65520
  end
  object SQLTransaction1: TSQLTransaction
    Active = True
    Action = caNone
    Database = IBConnection1
    left = 248
    top = 65520
  end
  object IBConnection1: TIBConnection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'F:\Firebird\CONTRIBUTIONS.FDB'
    KeepConnection = False
    Transaction = SQLTransaction1
    left = 40
    top = 65520
  end
  object sqlqPercent: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    Active = True
    Database = IBConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
      'SELECT * FROM CalcPercent(:Prog)'
    )
    Params = <    
      item
        DataType = ftUnknown
        Name = 'Prog'
        ParamType = ptUnknown
      end>
    left = 440
    top = 65520
  end
  object dsPercent: TDatasource
    DataSet = sqlqPercent
    left = 96
    top = 64
  end
end

FirebirdDemo4.lpr

program FirebirdDemo4;

{$mode objfpc}{$H+}

uses
  Interfaces, Forms, uFirebirdDemo4;

{$R *.res}

begin
  Application.Initialize;
  Application.CreateForm(TfrmSearch, frmSearch);
  Application.Run;
end.