Stored Procedures

Connecting to Firebird from ApplicationsFirebird Events

Stored Procedures

In Firebird, we can have two types of stored procedures, known as executable and selectable.Both types can take input parameters and return output, but they differ both in the way they are written and in the mechanism for calling them.

Output from an executable procedure is optional and any output returned is a set of not more than one “row” of values.If output is defined and none is produced, the output is null.

Returning data is not the primary goal of an executable procedure.Its purpose is to perform data operations that are invisible to the user.

The mechanism for calling an executable procedure is the SQL statement EXECUTE PROCEDURE.For example,
```
execute procedure MyProc(?,?)
```
A selectable procedure is written with the objective of returning a set of zero, one or many rows of data.It can be used to change data, but it should not be written to do that.The PSQL statement SUSPEND is used in this style of procedure to pass a row of output that has been collected inside an iteration of a FOR SELECT.. loop out to a buffer.

The mechanism for calling a selectable procedure is the SQL statement SELECT.

In this example we have a selectable procedure from which we expect to receive a set of zero or more rows based on the input parameters:
```
select * from MyProc(?,?)
```

Microsoft Excel and some other applications use this statement to call a stored procedure:

{[? =] Call MyProc (?,?)}.

The Firebird ODBC/JDBC driver determines what call to use when executing a stored procedure, from the metadata obtained from the Firebird engine.Firebird flags a procedure as ‘executable’ or ‘selectable’ according to count of SUSPEND statements in the assembled (BLR) code of its definition.For a trivial example:

create procedure TEST
  as
    begin
    end

Because the procedure has no SUSPEND statements, the ODBC driver knows to pass the call as execute procedure TEST.

For this procedure:

create procedure "ALL_LANGS"
   returns ("CODE" varchar(5),
         "GRADE" varchar(5),
         "COUNTRY" varchar(15),
         "LANG" varchar(15))
   as
   BEGIN
     "LANG" = null;
     FOR SELECT job_code, job_grade, job_country FROM job
     INTO :code, :grade, :country
     DO
       BEGIN
         FOR SELECT languages FROM show_langs(:code, :grade, :country)
         INTO :lang
           DO
             SUSPEND;
             /* Put nice separators between rows */
             code = '=====';
             grade = '=====';
             country = '===============';
             lang = '==============';
             SUSPEND;
       END
     END

the BLR code for the stored procedure contains more than zero SUSPEND statements, so the ODBC Driver will use select * from "ALL_LANGS".

`ARRAY` Data Type

To modify single dimension array data type fields, you need to conform to the following rules:

Specify simple types (INTEGER, etc.) as {1, 2, 3}
Specify string types (CHAR, etc.) as {'1', '2', '3'}

Warning

TRAPS!

If you edit an element of the array e.g. element 1, 2 and 5, and do not specify the other elements of the array, e.g. 3 and 4, then the other elements of the array will be zeroed (integer), or blank (string).

With some programs where columns are dependent on array data, it is possible to enter array data into a currently NULL array column without a validity check being made on the various array elements.Under these circumstances it is essential to enter the array elements before entering the column data.

Figure 1. Data loss when updating an ARRAY field (1)

Figure 2. Data loss when updating an ARRAY field (2)

Stored Procedures

Stored Procedures

ARRAY Data Type

`ARRAY` Data Type