InterBase Stored Procedures

Problem/Question/Abstract:

Creating Them and Using Them from Delphi

Answer:

One of the cornerstones of successful client/server programming is the stored procedure. Unfortunately, InterBase stored procedures are woefully underdocumented, especially regarding the Delphi connection. This article attempts to help fill the documentation gap.

This article has two major goals. The first is to provide a general description of an InterBase stored procedure, describe the benefits of stored procedures, and provide specific examples of the more common procedures you'll need to create. The second is to explain how Delphi uses the stored procedures, i.e. explain how they're called.

What Is a Stored Procedure?

A stored procedure is a routine written in InterBase trigger and procedure language (catchy, hunh?) that can be called by a client (e.g. a Delphi application) or another procedure or trigger. Stored procedures can be used for many things, but this article will focus on their use with the mainstay SQL statements: SELECT, INSERT, UPDATE, and DELETE.

Select statements are the most common, so let's tackle them first. A stored procedure that contains a SQL SELECT statement is often referred to as a select procedure.

Stored Procedure Basics

An InterBase stored procedure is created using a CREATE PROCEDURE statement. The code in Figure 1, for example, creates a select procedure named SPS_Address_ProviderID. This straightforward select procedure returns four columns from any Address table row that has a ProviderID column value equal to the ProviderID provided as an input argument.

CREATE PROCEDURE SPS_Address_ProviderID(ProviderID INTEGER)
RETURNS (Address CHAR(60),
City    CHAR(30),
State   CHAR( 2),
ZipCode CHAR( 5))
AS
BEGIN

FOR
SELECT Address, City, State, ZipCode
FROM Address
WHERE ProviderID = :ProviderID
INTO :Address, :City, :State, :ZipCode
DO SUSPEND;

END
Figure 1: An example of a select procedure.

As you can see, a select procedure is essentially a SQL SELECT statement in the form of a function call. Input parameters - if there are any - are included in the CREATE statement as a comma-delimited list within parentheses. There's only one in this example; it's named ProviderID, and is of type INTEGER. The InterBase data types are shown in Figure 2.

Name
Size
Range/Precision
BLOB
variable
None - BLOB segment size is limited to 64KB
CHAR(n)
n characters
1 to 32767 bytes
DATE
64 bits
1 Jan 100 to 11 Dec 5941
DECIMAL(precision, scale)
variable
precision = 1 to 15, least number of precision digits
scale = 1 to 15, number of decimal places
DOUBLE PRECISION
64 bits
1.7 x 10-308 to 1.7 x 10308
FLOAT
32 bits
3.4 x 10-38 to 3.4 x 1038
INTEGER
32 bits
-2,147,483,648 to 2,147,483,648
NUMERIC(precision, scale)
variable
same as DECIMAL
SMALLINT
16 bits
-32768 to 32767
VARCHAR(n)
n characters
1 to 32765 bytes
Figure 2: InterBase data types (based on a chart from the InterBase Workgroup Server Data Definition Guide, pages 46-7).

Any output parameters - and there must be at least one for a select procedure - are described in a RETURNS statement, which also takes the form of a comma-delimited list inside parentheses. In this example, there are four output parameters: Address, City, State, and ZipCode. All are of type CHAR, which is used for strings.

Header and body. The CREATE and RETURNS statements (if RETURNS is present) comprise the stored procedure's header. Everything following the AS keyword is the procedure's body. In this example, the body is contained entirely within the BEGIN and END statements required for every stored procedure.

There can also be statements between the AS and BEGIN keywords that are considered part of the body. These statements declare local variables for the stored procedure; we'll discuss them later, along with the FOR, INTO, and DO SUSPEND statements.

Why Use Them?

They're fast. A query stored on the server as a procedure executes far more quickly than one built and executed on the client. The speed difference is even more pronounced when your database application is running on a LAN or WAN. The main reason is that when the client application sends the query to the server, the server responds with a large amount of metadata (specific database information about the requested query). The query plan is then built, and the query is re-sent to the server for execution.

In contrast, if a stored procedure is used to perform the SQL statement, the client simply requests that the server execute the procedure, and send back the answer set (if any). The result is that just two trips are made between the client and server, instead of four (one of which contains a large amount of data).

They're reusable. In a database application of significant size, you'll find yourself using the same SQL statements (SELECTs, INSERTs, etc.) repeatedly. Rather than recreate a statement on the client each time, it's better to store the statement in the database and call it. It's the same idea as maintaining a library of procedures and functions shared between modules. The benefits are the same, as well: Readability is enhanced, and redundancy, maintenance, and documentation are greatly reduced.

They're part of the database. Although this has been mentioned, it bears repeating that a stored procedure is part of the database. Not only does this make the procedure readily accessible to the database, it also insures that the procedure is syntactically correct, and that the SQL statements included in the procedure are correct. The database will not accept it until it's valid, i.e. the CREATE PROCEDURE statement will fail.

Creating Select Procedures

We'll examine five types of stored procedures:

a SELECT statement that may return multiple rows
a SELECT statement that returns one row, i.e. a singleton select
an INSERT statement
an UPDATE statement
a DELETE statement

Creating a Multi-Row SELECT

When a SELECT statement might return multiple rows, the stored procedure must use the FOR...DO looping construct. We've already seen this in Figure 1:

FOR

SELECT Address, City, State, ZipCode
FROM Address
WHERE ProviderID = :ProviderID
INTO :Address, :City, :State, :ZipCode

DO SUSPEND;

Here a FOR...DO loop has been placed around the SELECT statement. This will cause the SUSPEND command to be executed for each row returned by the SELECT statement. (SQL programmers will recognize this as a fetch loop on an open cursor.)

Fine - but what does SUSPEND do? It's got a lousy name, but a SUSPEND command is absolutely necessary to make the SELECT stored procedure work. It causes the stored procedure to return a value via the variables associated with the INTO clause. (Note: InterBase will accept a stored procedure without a SUSPEND statement, but the stored procedure will never return a value.)

Loading the Output Variables. An additional clause on the SELECT statement may be new to you. The INTO clause describes the variables that will be loaded with the result of the SELECT statement, then returned by the stored procedure via the variables described in the RETURNS statement. They must agree in number, order, and name, or InterBase will not accept the procedure.

A Singleton SELECT

When a SELECT statement will return only one row, there's no need for a FOR...DO loop (see Figure 3). However, it's important to ensure that the SELECT will never attempt to return more than one row, i.e. that the WHERE clause uses a unique row identifier. If InterBase determines that multiple rows are possible, it will not accept the procedure.

SET TERM ^ ;
CONNECT "c:\doj\cmis\cmis.gdb"^

CREATE PROCEDURE SPS_Subject_Confidential(
ProviderID INTEGER)
RETURNS (ConfideCount INTEGER)
AS
BEGIN

SELECT COUNT(*)
FROM Party P, CaseStatus CS, Status S
WHERE P.ProviderID          = :ProviderID
AND CS.ComplaintID        = P.ComplaintID
AND CS.Status             = S.Status
AND S.ConfidentialityFlag = 'T'
AND CS.StatusDate =
( SELECT MAX(StatusDate)
FROM CaseStatus Case
WHERE Case.ComplaintID = P.ComplaintID )
INTO :ConfideCount;

SUSPEND;

END^
SET TERM ; ^
Figure 3: This ISQL script creates a singleton select. This COUNT statement will always return one row, so there is no need for the FOR...DO loop.

The SELECT statement in Figure 3 is returning the result of the aggregate function, COUNT, so it will always return one row. (Incidentally, it also features a sub-select. This type of query is useful in any situation where you need to determine the current status row for something - a "case" in this instance.)

The SELECT statement now requires a terminating semicolon:

INTO :ConfideCount;

as does the one-word SUSPEND statement that immediately follows it.

This is in contrast to the stored procedure shown in Figure 1. It may seem odd, but in the multiple SELECT shown in Figure 1, there's only one statement in the body of the procedure: It's a FOR...DO statement that's terminated just after the SUSPEND command:

DO SUSPEND;

Therefore, there is no terminating semicolon for the SELECT itself.

An INSERT

An INSERT statement is used to add a row to an InterBase table. No RETURNS variable is necessary for an INSERT stored procedure (see Figure 4). Not shown is that an InterBase trigger is using a generator to automatically assign a value to a primary key column - a typical scenario. (These issues are discussed in detail in Bill Todd's article, "InterBase Triggers and Generators.")

SET TERM ^ ;
CONNECT "c:\doj\cmis\cmis.gdb"^

CREATE PROCEDURE SPI_Payment
(
MoneyOwedBMCFID      INTEGER,
AmountPaid           FLOAT,
CheckNumber          CHAR(15),
DateOfCheck          DATE,
DateMoneyReceived    DATE,
DateMoneyDistributed DATE
)
AS
BEGIN

INSERT INTO Payments
(
MoneyOwedBMCFID,
AmountPaid,
CheckNumber,
DateOfCheck,
DateMoneyReceived,
DateMoneyDistributed
)
VALUES
(
:MoneyOwedBMCFID,
:AmountPaid,
:CheckNumber,
:DateOfCheck,
:DateMoneyReceived,
:DateMoneyDistributed
);
END^

SET TERM ; ^
Figure 4: This stored procedure describes a SQL INSERT statement.

An UPDATE

An UPDATE statement is used to modify one or multiple columns of an existing row in an InterBase table. No RETURNS variable is necessary for an UPDATE stored procedure (see Figure 5). However, one or more of the input arguments must be used in a WHERE clause to identify the row to update.

SET TERM ^ ;
CONNECT "c:\doj\cmis\cmis.gdb"^

CREATE PROCEDURE SPU_Penalty
(
PenaltyID       INTEGER,
PartyID         INTEGER,
PenaltyType     CHAR(20),
PenaltyUnitType CHAR(10),
DateOfPenalty   DATE,
PenaltyUnits    INTEGER
)
AS
BEGIN

UPDATE Penalty
SET PartyID
WHERE PenaltyID = :PenaltyID; = :PartyID,
PenaltyType     = :PenaltyType,
PenaltyUnitType = :PenaltyUnitType,
DateOfPenalty   = :DateOfPenalty,
PenaltyUnits    = :PenaltyUnits
END^

SET TERM ; ^
Figure 5: This stored procedure describes a SQL UPDATE statement.

A DELETE

A DELETE statement is used to remove an existing row or rows from an InterBase table. No RETURNS variable is necessary for a DELETE stored procedure (see Figure 6). One or more of the input arguments must be used in a WHERE clause to identify the row(s) to delete.

SET TERM ^ ;
CONNECT "c:\doj\cmis\cmis.gdb"^

CREATE PROCEDURE SPD_LicenseToBill (ProviderID INTEGER)
AS
BEGIN

DELETE FROM LicenseToBill
WHERE ProviderID = :ProviderID;

END^

SET TERM ; ^
Figure 6: This stored procedure describes a SQL DELETE statement.

ISQL Scripts

To add a stored procedure to an InterBase database, you must describe the stored procedure in an ISQL script and then run that script using ISQL. The code examples presented so far are ISQL scripts that must be run through InterBase's interactive interface, ISQL (using the menu command File | Run an ISQL Script). A couple of tricks are required to make these scripts work.

First, although you may already have connected to an InterBase database using ISQL (File | Connect to Database), it is still necessary to explicitly connect each time an ISQL script is executed. This is done with a CONNECT statement; for example:

CONNECT "c:\doj\cmis\cmis.gdb"^

The trouble with terminators. Second, an ISQL script must satisfy two masters: the ISQL tool itself, and the InterBase database it addresses. Both require statement terminators, and both use the semicolon (; ) as their default terminator character. Something's gotta give, so you need to temporarily change the terminator for ISQL. This is done with the SET TERM command. This statement, for example:

SET TERM ^ ;

tells ISQL to use the carat (^ ) character as a terminator until further notice. You can use any character you like as the alternate terminator, but I would highly recommend that you use something unusual. Typically, the last statement in an ISQL script replaces the semicolon as the terminating character.

Calling Stored Procedures from Delphi

Okay, we know how to build the stored procedures. Now how do we call them from Delphi? There are two ways - one is necessary for SELECT statements (i.e. statements that return a value), the other for INSERT, UPDATE, and DELETE statements.

Stored procedures with SELECT statements are called from Delphi using a Query object (of class TQuery). This is despite the fact that we're calling a stored procedure; again, a Delphi Query object is used for any statement that returns the result of a SELECT statement. The other SQL statements - INSERT, UPDATE, and DELETE - are called using a Delphi StoredProc object (of class TStoredProc).

Calling a Select Procedure

We'll describe how stored procedures are called, beginning with a SELECT statement. First, however, let's back up a bit and take a look at how we'd describe and call a "conventional" query (i.e. one not contained in a select procedure) using Object Pascal (see Figure 7).

procedure {... }
var
FetchCount: Word;
QueryAddress: TQuery;
{  ... }

QueryAddress := TQuery.Create(Self);
with QueryAddress do
begin
DatabaseName := 'CMIS_DB';
SQL.Add('SELECT AddressType, Address, City, County,   ');
SQL.Add('       State, ZipCode, ZipPlus4, PhoneNumber');
SQL.Add('  FROM Address                              ');
SQL.Add(' WHERE ProviderID = :ProviderID             ');
ParamByName('ProviderID').AsInteger :=
SubjectUpdateProviderID;
Open;

FetchCount := 0;
while EOF = False do
begin
with StringGridAddress do
begin
RowCount := FetchCount + 1;
Cells[0, FetchCount] := Fields[0].Text;
Cells[1, FetchCount] := Fields[1].Text;
Cells[2, FetchCount] := Fields[2].Text;
Cells[3, FetchCount] := Fields[3].Text;
Cells[4, FetchCount] := Fields[4].Text;
Cells[5, FetchCount] := Fields[5].Text;
Cells[6, FetchCount] := Fields[6].Text;
Cells[7, FetchCount] := Fields[7].Text;
end;
Inc(FetchCount);
Next;
end;

Free;

end;
{ ... }
end;
Figure 7: Describing and executing a SQL SELECT statement with Object Pascal.

First the Query object, QueryAddress, is instantiated, and its Database and SQL properties are assigned values. Then the single query parameter, ProviderID, is assigned a value, and the query is executed using the Open method. In this example, a while loop is used to take the results of the query and load them into a StringGrid component.

All of this is familiar, but how do we change it to call a stored procedure? For this SELECT statement, the changes are fairly minor (see Figure 8). There are two notable differences:

First, the FROM clause now refers to the name of the stored procedure, SPS_Address_ProviderID, not a specific table. (The difference would be more pronounced if there were a list of tables.)
Second, there is no WHERE clause; the WHERE clause is described in the stored procedure. The input parameter is simply placed in parentheses following the FROM clause. (Again, the difference would have been more pronounced if there had been an elaborate WHERE clause.)

var
FetchCount: Word;
QueryAddress: TQuery;
{  ... }
QueryAddress := TQuery.Create(Self);
with QueryAddress do
begin
DatabaseName := 'CMIS_DB';
SQL.Add('SELECT AddressType, Address, City, County,   ');
SQL.Add('       State, ZipCode, ZipPlus4, PhoneNumber');
SQL.Add('  FROM SPS_Address_ProviderID (:ProviderID) ');
ParamByName('ProviderID').AsInteger :=
SubjectUpdateProviderID;
Open;

FetchCount := 0;
while EOF = False do
begin
with StringGridAddress do
begin
RowCount := FetchCount + 1;
Cells[0, FetchCount] := Fields[0].Text;
Cells[1, FetchCount] := Fields[1].Text;
Cells[2, FetchCount] := Fields[2].Text;
Cells[3, FetchCount] := Fields[3].Text;
Cells[4, FetchCount] := Fields[4].Text;
Cells[5, FetchCount] := Fields[5].Text;
Cells[6, FetchCount] := Fields[6].Text;
Cells[7, FetchCount] := Fields[7].Text;
end;
Inc(FetchCount);
Next;
end;

Free;

end;
Figure 8: Executing an InterBase select procedure from Object Pascal.

The rest of the procedure is the same: Multiple rows are being loaded into a StringGrid, with the Next method being used to fetch the next record in the answer stream. Note also that a looping structure would be unnecessary if the code were calling a singleton select.

Calling a Stored Procedure to Perform an INSERT, UPDATE, or DELETE Operation

As mentioned earlier, a Delphi StoredProc object must be used for SQL operations that do not return an answer set, i.e. the result of a SELECT statement. Therefore, they're used to call stored procedures that contain INSERT, UPDATE, and DELETE statements.

From a Delphi standpoint, these three statements are handled the same, so we'll look at just one - an UPDATE. The Object Pascal code in Figure 9 calls a stored procedure that contains the UPDATE statement from Figure 5.

var
StoredProcPenalty: TStoredProc;
{...}
StoredProcPenalty := TStoredProc.Create(Self);
with StoredProcPenalty do
begin
DatabaseName := 'cmis_db';
StoredProcName := 'SPU_Penalty';
Prepare;
ParamByName('PenaltyID').AsInteger := PenaltyPenaltyID;
ParamByName('PartyID').AsInteger := PenaltyPartyID;
ParamByName('PenaltyType').AsString :=
ComboBoxPenaltyType.Text;
ParamByName('PenaltyUnitType').AsString :=
ComboBoxPenaltyUnits.Text;
ParamByName('DateOfPenalty').AsDate :=
StrToDate(MaskEditPenaltyDate.Text);
ParamByName('PenaltyUnits').AsInteger :=
StrToInt(MaskEditPenalty.Text);
ExecProc;
Free;
end;
Figure 9: Executing a stored procedure that contains an INSERT statement.

There are some similarities: A StoredProc object is instantiated in the same way as a Query object, and its Database property must also be assigned.

After that, however, the similarities disappear. The StoredProcName property must be assigned the name of the stored procedure - in this case, SPU_Penalty. Also, the Prepare method must be used to tell the server to get the stored procedure ready to accept input, and otherwise prepare for execution. Note also that the ExecProc method is used instead of Open (just as it is when TQuery objects return no value).

After Prepare has been called, the parameters can be assigned just as they are with Query objects - using the ParamByName method. Finally, the ExecProc method is used to execute the stored procedure (again, in lieu of the Query Open method, because no value is returned).

Conclusion

We've examined real-world examples of how to use InterBase stored procedures to develop a client/server application with Delphi. Along the way, we've covered the basics of InterBase trigger and procedure language, and - among other things - learned how to build select procedures, and how to call stored procedures from Delphi.

Another benefit of learning InterBase trigger and procedure language is that it's very much like the procedural languages used by other database vendors (Oracle's PL/SQL, for example), so once you've mastered the InterBase flavor, you'll make short work of the next.