IBPhoenix Development

Using Stored Procedures with Delphi and InterBase

By Scott Clinton, Borland Developers Conference 1995

Overview

This paper will focus on improving application performance and maintainability using remote stored procedures in InterBase. We will examine Delphi's TStoredProc component functionality and offer some tips on optimization. In addition we will focus on some practical uses of stored procedures and solutions to common issues that developers may encounter.

The Great Debate : Dynamic SQL (DSQL) vs. Static Stored Procedures

There is still a raging debate in SQL development circles regarding the usage of Dynamic SQL (DSQL) and stored procedures. The debate is made up of those who believe that DSQL should be used for all operations involving business rule enforcement and those who believe all data-related operations should be contained within stored procedures. When the rubber hit the road, most solutions result in something in the middle. The most important aspect is to understand the strengths and weaknesses of each approach so they can be melded together to pick up the slack when either approach reaches its limits.

Dynamic SQL (DSQL)

Dynamic SQL has a number of appealing features. With DSQL you can build SQL statements to execute on the fly. The client application can build these statements based on a sequence of choices a user makes in the client application. In addition, all of the coding remains on the client. This is especially useful for those who are creating applications which may have to work against different DBMS in different environments.

Query1.SQL.ADD('SELECT CUSTOMER_NAME, CUSTOMER_ORDER');
Query1.SQL.ADD('FROM '+ :TableName');
Query1.SQL.ADD('WHERE STATE = :StateVal AND');
Query1.SQL.ADD('CITY = :CityVal');
Query1.Prepare;
Query1.Params[0].AsString := ComboBox1.Text;
Query1.Params[1].AsString := Edit1.Text;
Query1.Params[2].AsString := Edit2.Text;

The downside of this approach is that the developer does not fully utilize the capabilities of the database server. Also, each DSQL statement must be first passed across the network in its entirety. Once the DSQL statement reaches the server, it must then be decoded and complied by the RDBMS server. This process is repeated each time the request to execute the DSQL code is made by the client.

DSQL Execution Sequence

Application Maintenance also becomes an issue when using DSQL. If a change is made in database structure or in the applications accessing that data, each of the applications must be manually modified and re-tested to be sure the modifications have been made properly. If there are a fair number of applications utilizing the same data, which is common, each application will require modification and rebuilding. This can severely impact users, especially when the application is uses to process day-to-day information.

Stored Procedures

Stored procedures also provide compelling reasons for use. When a stored procedure is added to the database it is decoded and compiled becoming a semi-permanent addition to your database definition. Since a procedure is only decoded and compiled once, upon adding it to the database, each subsequent request can avoid going through those performance robbing steps. This becomes especially apparent when large DSQL statements are converted to stored procedures.

Network traffic is also reduced, a key for large systems, since only the procedure name and parameters are being passed across the network.

Stored Procedure Execution Sequence

From a maintenance perspective stored procedures allow you to maintain a common set of code which is used by all applications wanting to access specific data. Another way to think of stored procedures are as re-usable components.

Changes resulting from business need variations are more easily incorporated by making the change to one stored procedure. Those changes are then reflected in each of the client application. Careful modifications to existing stored procedures can eliminate or at least reduce additional coding necessary in each of the client applications.

Stored procedures can also be used to invoke other stored procedures or be included in database triggers to extend the capabilities for data validation.

Making the Decision : DSQL vs. Stored Procedure

There are a number of factors that lead to the decision of when and where to use stored procedures. There are a few questions that can be asked about the application which can help guide in the decision to use stored procedures or DSQL. The following questions are meant as guidelines, not as hard and fast rules.

Will this data-set be used by more that one application?

If you have more that one application that will need access to the same data, creating a stored procedure would keep you from having to duplicate the DSQL code in each of the application that need this information.

How large is the SQL statement? Does it involve 3 or more tables?

The Larger and more complex the DSQL statement is the more network traffic it incurs and the longer it takes to be translated by the server into executable statements.

Does this statement enforce business rules on your data?

If the DSQL statement is enforcing specific business rules designed to manage business routines it is preferable to use a stored procedure. All clients attaching to the database will have access to or be forced to use the same data access method. This approach assures the adherence to the specified business rule.

InterBase and Stored Procedures

InterBase version 4.0 added the capability to create and execute stored procedures. This capability was also extended to the Local InterBase Server (LIBS), currently included with Delphi. From within Delphi you can develop applications using LIBS, taking advantage of the capability of stored procedures and move the same code over to an InterBase Workgroup (NT, NLM), or UNIX Server. InterBase stored procedures can be broken into two categories, executable and select procedures.

Executable Stored Procedures

Executable stored procedures are differentiated in that they either have no or a single row as a return value. Executable stored procedures do not associate a cursor with a result set. An example of a executable procedure would be a procedure that performs a delete on a list of customers who have not ordered any items in the last two years.

The procedure appeared in the paper like this:

CREATE PROCEDURE PERGE_CUST_LIST (CUST_NO INT)
		AS
		BEGIN
			DELETE * FROM CUSTOMER
			WHERE LAST_ORD_YR < 93
		END;

A better version might look like this:

CREATE PROCEDURE PURGE_CUST_LIST (CUST_NO INT)
		AS
		BEGIN
			DELETE FROM CUSTOMER
			WHERE LAST_ORD_YR < 93
		END;

Select Stored Procedures

Select procedures return a cursor from the database. Usually select procedures are used to return multi-row result-sets. They can also be used for returning singleton results with a cursor. When developing applications with Delphi select procedures will return data through the data-aware controls just as would a typical TTable or a TQuery component.

These special stored procedures are referred to as SELECT procedures since their use the SQL SELECT statement to execute the procedure and return the data.

Syntax:
SELECT * FROM <stored proc name> (<stored proc parameters>, ..)

Example:
SELECT * FROM MAIL_LABEL(1004)

SELECT procedures can be identified by the use of a SUSPEND in the procedure code. Below is an example of a SELECT procedure which returns all the projects an employee is currently assigned.

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
	RETURNS (PROJ_ID CHAR(5))
	AS 
	BEGIN
	 FOR SELECT proj_id
	  FROM employee_project
	  WHERE emp_no = :emp_no
	  INTO :proj_id
	 DO
	  SUSPEND;
	END

Delphi and Stored Procedures

Delphi has a number of mechanisms for using stored procedures. The two key components are TStoredProc and TQuery. Each component has specific advantages depending on the database they are being used with. For our purposes we will examine which Delphi components to use and when, with regard to InterBase procedures

Using the TStoredProc Component with InterBase

The TStoredProc component is designed to work with InterBase's execute procedures. TStoredProc allows the developer to execute any procedure which returns a single column or row as a result.

Input parameters can be specified in design mode, using the parameters property editor. Result sets from a TStoredProc will NOT be propagated through Delphi's data-aware controls. The property editor will present you with each of the parameters required to execute the procedure as well as their appropriate data-type.

TStoredProc parameters property editor

The TStoredProc's Active property cannot be opened (set to True) in design-mode. Execution of the procedure must be done pragmatically using the .ExecProc method. Since TStoredProc only returns the result to an array, setting the Active property to True will only cause an error then the data-set attempts to create a cursor handle.

Error dialog - setting Active property to True

You can also define parameter values programaticaly using the same .Params[ n ] property which references the specific parameter by it's index value or by using .ParamByName and referencing the parameter by it's given name.

StoredProc1.Prepare;
StoredProc1.ParamByName('LAST_NAME').AsString := Edit1.Text;
StoredProc1.ExecProc;

Using the TQuery Component to Execute Stored Procedures

The TQuery component is used to return values from InterBase Select procedures. Using TQuery will allow the developer to return multi-row result-sets associated with a cursor. Since the result-set is associated with a cursor, the result-set is then propagated through the data-aware controls. You can also use the TQuery component to return singleton or single row/column result sets when you want the result to be available to the data-aware components.

Passing parameters to the select procedure uses the same mechanism as do DSQL statements using the TQuery component.

Query1.SQL.ADD('SELECT * FROM MAIL_LABEL(:CUST_ID)');
Query1.Prepare;
Query1.Params[0].AsString := ComboBox1.Text;
Query1.Open;

Using Stored Procedures for Security

Stored procedures can be used to manage database security. There are a number of ways this can be implemented. Client applications can be limited to only accessing the data through stored procedures.

This approach is somewhat similar to using views but allows for greater flexibility. A developers can have greater control over what the user can gain access to and when. With stored procedures, permissions can be modified dynamically depending on the operation being performed.

In order to have a better understanding of how this may work it is necessary to have an understanding of how security is managed and maintained in an InterBase database.

InterBase Security

InterBase allows for the specification of security on a number of levels. Security can be enforced individually or as a group. Security can be specified not only for each user or group, but also for system objects.

Adding users to InterBase can be done easily through the Server Manger utility. If it is necessary to create groups, it can only be done on the server using the GSEC security utility.

Controlling Access

Data security is generally managed by granting each individual user or group rights to a specific server object (ie. Table, View, etc.). This method of decentralized security can lead to improper data access and modification.

Basic Security methodology

Stored procedures can improve data security by providing data access only through the execution of the procedure. The user or group has all rights to Tables and Views revoked so they can in no way view or edit their contents. Instead the user gains access to the data by executing a stored procedure which returns the desired data-set.

Procedure based security

Rather than providing the user with access to the tables, appropriate access rights are granted to the procedure. The user is then granted execute rights to the procedure. At no time does any user have direct access to a table or the data contained within it. Below is a sample procedure providing SELECT access to the data.

CREATE PROCEDURE READ_EMPLOYEE_INFO 
		RETURNS(EMP_NO SMALLINT, FIRST_NAME VARCHAR(15),
		LAST_NAME VARCHAR(20), PHONE_EXT VARCHAR(4),
		HIRE_DATE DATE, DEPT_NO CHAR(3),
		JOB_CODE VARCHAR(5), JOB_GRADE SMALLINT,
		JOB_COUNTRY VARCHAR(15),
		FULL_NAME VARCHAR(35))
		AS 
		BEGIN
   		FOR SELECT * FROM EMPLOYEE
   		INTO :EMP_NO, :FIRST_NAME, :LAST_NAME,
            		:PHONE_EXT, :HIRE_DATE, :DEPT_NO,
            		:JOB_CODE, :JOB_GRADE, :JOB_COUNTRY,
            		:FULL_NAME
 		DO
 		      SUSPEND;
		END

The procedure in code example 6 returns to the caller all the fields in the Employee table except for the Salary field. This technique can be used to secure sensitive data.

Once procedures have been created you can REVOKE individual user's rights from the tables you wish to control access on.

REVOKE ALL ON EMPLOYEE FROM PUBLIC

With the users' rights revoked each stored procedure must be granted the appropriate access rights to the tables they interact with. If a procedure does not have the correct level of access to a table, the procedure will fail.

This approach removes the responsibility for gaining access to the tables from the user and places it in the hands of each procedure. New users can be added without a great deal of administration overhead.