IBPhoenix Development

Using InterBase Stored Procedures with Delphi

By Jerry Coffey, Borland Developers Conference London 1997

One of the cornerstones of successful client/server programming is the stored procedure. Unfortunately, InterBase stored procedures are woefully underdocumented, especially when it comes to the Delphi connection. This paper is an attempt to help fill the documentation gap.

This paper 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 paper will focus on their use to perform the mainstay SQL statements: SELECT, INSERT, UPDATE, and DELETE.

SELECTs 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.

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.

Figure 1: An example of a select procedure.

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

This is a straightforward select procedure that returns four columns from any row in the Address table that has a ProviderID column value equal to the ProviderID provided as an input argument.

Stored Procedure Basics

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.

Figure 2: InterBase data types (based on chart from InterBase Workgroup Server Data Definition Guide, pp. 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 are used for strings.

Header and body. The CREATE and RETURNS statements (if there is one) 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 also considered part of the body. These statements declare local variables for the stored procedure, and we’ll discuss them under "More Complex Procedures" below. The FOR, INTO, and DO SUSPEND statements will be discussed later.

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 resent 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 executes the procedure and sends 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 any 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 needed, it’s better to store it in the database and call it. It’s the same idea as maintaining a library of procedures and functions that are 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 already it bears repeating that creating a stored procedure makes it 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

For the purposes of this paper 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.)

Okay — 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’s what 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. You’ll note an additional clause on the SELECT statement that may be new to you. The INTO clause describes the variables that will be loaded with the result of the SELECT statement and then returned by the stored procedure via the variables described in the RETURN 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 row, there’s no need for a FOR...DO loop (see Figure 3). However, it’s important to make certain 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.

The SELECT 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.)

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.

SET TERM ^ ;
CONNECT "c:dojcmiscmis.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 ; ^ 

Note that 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 is really 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 RETURN variable is necessary for an INSERT stored procedure (see Figure 4). (Not shown here is that a column that is the primary key is automatically assigned a value using an InterBase trigger.)

Figure 4: This stored procedure describes a SQL INSERT statement.

SET TERM ^ ;
CONNECT "c:dojcmiscmis.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 ; ^

An UPDATE

An UPDATE statement is used to modify one or multiple columns of an existing row in an InterBase table. No RETURN 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.

Figure 5: This stored procedure describes a SQL UPDATE statement.

SET TERM ^ ;
CONNECT "c:dojcmiscmis.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=:PartyID,
PenaltyType=:PenaltyType,
PenaltyUnitType=:PenaltyUnitType,
DateOfPenalty=:DateOfPenalty,
PenaltyUnits=:PenaltyUnits
WHERE PenaltyID=:PenaltyID;
END^
SET TERM ; ^

A DELETE

A DELETE statement is used to remove an existing row or rows from an InterBase table. No RETURN 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.

Figure 6: This stored procedure describes a SQL DELETE statement.

SET TERM ^ ;
CONNECT "c:dojcmiscmis.gdb"^
CREATE PROCEDURE SPD_LicenseToBill (ProviderID INTEGER)
AS
BEGIN
DELETE FROM LicenseToBill
WHERE ProviderID=:ProviderID;
END^
SET TERM ; ^

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 in this paper 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, e.g.

CONNECT "c:dojcmiscmis.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 an unusual character. 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 form 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 by using a Query object (of class TQuery). This is despite the fact that we’re actually calling a stored procedure; again, a Delphi Query object is used for any statement that returns a value. The other SQL statements — INSERT, UPDATE, and DELETE — are called by using a Delphi StoredProc object (of class TStoredProc).

Calling a Select Procedure

We’ll describe how the 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).

Figure 7: Describing and executing a SQL SELECT statement with Object Pascal.

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;

First the Query object, QueryAddress, is instantiated, and it’s 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).

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

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;

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.)

Note that 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 such 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. Therefore, they’re used to call stored procedures that contain INSERT, UPDATE, and DELETE statements.

From a Delphi standpoint, these three statements are handles the same, so we’ll just look at one — an UPDATE. The Object Pascal code in Figure 9 calls a stored procedure that contains an UPDATE statement (it’s shown in Figure 5).

Figure 9: Executing a stored procedure that contains an INSERT statement.

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;

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, SPU_Penaltyin this case. 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).

Once Prepare has been called, the parameters can be assigned just as they are with Query objects, i.e. using the ParamByName method. Finally, the ExecProc method is used to execute the stored procedure (in lieu of the Query Open method, since 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 is 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.