Changing Stored Procedures - Firebird

Firebird 1.0.x offers two ways to change stored procedures using DDL statements, and Firebird 1.5 adds a third one. They are

  • ALTER PROCEDURE, which changes the definition of an existing stored procedure while preserving its dependencies on other objects.
  • RECREATE PROCEDURE, which works even if the named procedure does not exist. If it does exist, the existing version is dropped and then recreated. Existing dependencies do not survive.
  • CREATE OR ALTER PROCEDURE (available from v.1.5 onward), which gives the best of both. If the procedure exists, ALTER rules apply and dependencies are preserved. If not, it will work exactly as CREATE PROCEDURE does.

Any of these operations will fail with an exception if any change is attempted that would break a dependency.

Effect on Applications

Changes made internally to a procedure are transparent to all client applications that use the procedure. You do not have to rebuild the applications unless the changes affect the Interface between the caller and the procedure —type, number, or order of input and output arguments.

Syntax for Changing Procedures

Except for the keyword you choose to effect a change to a stored procedure, the syntax of the statements for each is the same as for CREATE PROCEDURE. Just as with any compiled or interpreted module, there is no way to direct a change at an element without recreating the whole module. Every “alteration,” regardless of the keyword you choose to specify the operation, is a matter of creating a new source version and a new binary-coded object.

The syntax pattern is

{CREATE | ALTER | RECREATE | CREATE OR ALTER} PROCEDURE name
[(var datatype [, var datatype ...])]
[RETURNS (var datatype [, var datatype ...])]
AS
procedure_body;

ALTER PROCEDURE

For ALTER PROCEDURE, the procedure name must be the name of an existing procedure.

This is the low-impact way to change procedure code because, if it has dependencies that are not logically affected by the changes, there will be no structural side effects.

In general, dependencies involving other objects that depend on the changed procedure are not affected, either. However, if the changes to the stored procedure alter the definitions of its input or output arguments, it will be necessary to perform a RECREATE PROCEDURE on any other stored procedure that gets called during the execution.

RECREATE PROCEDURE

RECREATE PROCEDURE is identical to CREATE PROCEDURE, except that it causes any existing procedure of the same name to be subjected internally to a full DROP PROCEDURE operation before the new binary object is created. The procedure name does not have to exist.

You can use it like ALTER PROCEDURE, but it will not preserve existing dependencies. The operation will be blocked if there are dependent objects (views or other procedures that refer to the procedure).

The procedure name need not exist, but take care with case sensitivity of object names if quoted identifiers were used when creating the procedure. For example, suppose you created this procedure:

CREATE PROCEDURE "Try_Me"
RETURNS (AWORD VARCHAR(10))
AS
BEGIN
AWORD = "turtle';
END ^

Now you decide to change it using RECREATE PROCEDURE:

RECREATE PROCEDURE Try_Me
RETURNS (AWORD VARCHAR(10))
AS
BEGIN
AWORD = "Venezuela";
END ^

The original procedure, with its case-sensitive name Try_Me remains, unchanged. The “re-created” procedure is a new and quite separate object with the case-insensitive name TRY_ME.

CREATE OR ALTER PROCEDURE

New in v.1.5, this tolerant syntax creates a new procedure if there is none of the supplied name, or alters an existing procedure of that name.

Fixing the LOG_SALES Procedure

As an example, we are going to fix that procedure, LOG_SALES, that promises to bite us because we overlooked a nullable key. Here is the block that could cause the problems:

CREATE PROCEDURE LOG_SALES (...
...
DO
BEGIN
IF(REP = LASTREP) THEN /* will be false if both values are null */
BEGIN
REPTOTAL = REPTOTAL + ORDTOTAL;
REP_NAME = '"';
END
ELSE
BEGIN
REPTOTAL = ORDTOTAL;
LASTREP = REP;
SELECT FULL_NAME FROM EMPLOYEE
WHERE EMP_NO = :REP
INTO :REP_NAME; /* will return null if variable REP is null */
END
...
END ^

We fix the logic to handle nulls (grouped together at the end of the cursor, because the set is ordered by this column) and use CREATE OR ALTER to update the code:

CREATE OR ALTER PROCEDURE LOG_SALES (...
...
DO
BEGIN
/* ************* */
IF((REP = LASTREP) OR (LASTREP IS NULL)) THEN
/* ************* */
BEGIN
REPTOTAL = REPTOTAL + ORDTOTAL;
REP_NAME = '"';
END
ELSE
BEGIN
REPTOTAL = ORDTOTAL;
LASTREP = REP;
/* ************* */
IF (REP IS NOT NULL) THEN
SELECT FULL_NAME FROM EMPLOYEE
WHERE EMP_NO = :REP
INTO :REP_NAME;
ELSE
REP_NAME = 'Unassigned';
/* ************* */
END
...
END ^
COMMIT ^

“object Is in Use” Error

Committing the change will throw the notorious error (discussed in the previous chapter) if any user is currently using the procedure or another object that depends on it. Even if we clear that hurdle, the new version of the procedure will not be immediately available on Superserver if the old version is still in the cache. All users must log out and, when they log in again, they will see the new version.

On Classic server, the new version will be available to the next client that logs in.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics