Understanding Database Procedures, Sessions, and Events

This chapter discusses the following features of SQL:

• Database procedures
• Rules
• Multiple session connections
• Database events

How Database Procedures Are Created, Invoked, and Executed

A database procedure is a named routine composed of SQL statements stored in a database.

Database procedures are created using the create procedure statement and dropped using the drop procedure statement.

Database procedures can be called or invoked in the following ways:

• From an embedded SQL program
• From interactive SQL
• From a 4GL program
• By rules

A database procedure query execution plan is created at the time the procedure is created. If objects named in the procedure are modified in a way that invalidates the query execution plan, the DBMS Server recreates the query execution plan the next time the procedure is invoked.

Benefits of Database Procedures

Database procedures provide the following benefits:

• Enhanced performance
• Reduced amount of communication between an application and the DBMS Server. The DBMS Server retains the query execution plan for a database procedure, reducing execution time.
• Control over access to data. The DBA can use the grant statement to enable a user to execute a procedure even if the user does not have permission to directly access the tables referenced in the procedure.
• Reusability and reduced coding time. The same procedure can be used in many applications.
• The ability to enforce integrity constraints (when used in conjunction with rules).

Contents of Database Procedures

A database procedure can include the following entities:

• Local variable declarations
• Data manipulation statements such as select or insert
• Control flow statements such as if, for, and while
• Status statements, such as message, return, return row, and raise error

Database procedures can be executed by running the create procedure statement from a terminal monitor or including them in an embedded SQL program.

The DBMS Server resolves all references to database objects in a database procedure at the time the procedure is created. For this reason, all referenced objects must exist at the time the procedure is created. If, at the time it is created, a procedure refers to a DBA-owned table, the procedure always uses that table, even if a user that owns a table with the same name executes the procedure.

Permissions on Database Procedures

A procedure is owned by the user who creates it or by the group or role specified in the create procedure statement. A procedure can be executed by the owner and by any user, group, or role to whom the owner has granted execute permissions. Users, groups, and roles to which the owner has granted execute permission with grant option can grant execute permission to other users.

Although a user can create a private procedure that accesses public tables, the user must have all required permissions on those tables to be able to execute the procedure.

Methods of Executing Procedures

Database procedures can be executed in the following ways:

• By using the execute procedure statement in an embedded SQL application. This statement executes a specified procedure and passes parameter values to the procedure. To specify different parameter lists at runtime, use the dynamic version of the execute procedure statement. To execute a procedure owned by a user other than the effective user of the session, specify the procedure name using the schema.procedure_name syntax.
• When a rule is fired. Indirectly executed procedures must handle errors and messages differently than directly executed procedures. A procedure that is executed as the result of a rule can execute statements that trigger other rules, and so on.
• From interactive SQL, from within another database procedure, or by using the dynamic SQL execute immediate statement. A procedure cannot be executed using the dynamic SQL prepare and execute statements. For details about executing a database procedure, see Execute Procedure in the chapter "SQL Statements." For information about using database procedures with rules, see Rules.

All referenced objects must exist at the time the procedure is executed. Between the time of creation and the time of execution, objects such as tables and columns can be modified, reordered, or dropped and recreated without affecting the procedure definition. However, if an object is redefined in a way that invalidates the procedure definition, drop and recreate the procedure.

How Parameters Are Passed in Database Procedures

By default, the execute procedure statement passes parameters to a database procedure by value. To pass a value by reference, use the BYREF option. If a parameter is passed by reference, the called database procedure can change the contents of the variable, and the change is visible to the calling program.

In addition to the BYREF option for passing parameters from an invoking application, parameters can be passed in one of three declared modes: IN, OUT, and INOUT. IN is the default parameter mode and is equivalent to the by value mode described above. It offers no capability to return values to the calling procedure, rule or application. The OUT and INOUT modes allow the passing of possibly modified parameter values back to the calling database procedure or triggering operation, for example, allowing the application to modify a column value in a row before it is inserted or updated.

Row Producing Procedures

A row producing procedure is a database procedure that is capable of returning 0 or more rows to its caller.

Format of Row Producing Procedures

The format of row producing procedures is defined by the Result Row clause in the procedure definition. The value returned in each "column" of a result row is specified in a Return Row statement and can be a local variable or parameter of the procedure, or any expression involving constants, local variables and parameters. The local variables must contain data retrieved in the procedure by a Select statement. Multiple result rows must be returned to the caller using the For-loop that retrieves data from a Select statement.

Row producing procedures must only be called directly from an embedded SQL host program (not using dynamic SQL, a terminal monitor, or by nesting a call in another database procedure). However, the host program must include a Begin/End block to process the rows as they are returned from the procedure. This block functions much the same as the "select block" used with embedded Select statements.

Effects of Errors in Database Procedures

When an error occurs in a database procedure, the behavior of the DBMS Server depends on whether the procedure was invoked by a rule or executed directly (using execute procedure).

If the procedure was invoked by a rule, an error has the following effects:

• The procedure is terminated.
• Those statements in the procedure, which have been executed, are rolled back.
• The statement that fired the rule is rolled back.

If the procedure was executed directly, an error has the following effects:

• All statements in the procedure up to the point of the error are rolled back.
• The procedure continues execution with the statement following the statement that caused the error.
• Parameters passed by reference are not updated.

In both instances, the error is returned to the application in SQLSTATE, SQLCODE and errorno. In the case of the directly executed procedure, an error number is also returned to iierrornumber, a built-in variable available only in database procedures for error handling.

iierrornumber and iirowcount Variables

The iierrornumber and iirowcount variables, in conjunction with the Raise Error statement, handle errors in database procedures.

The iirowcount variable contains the number of rows affected by the last executed SQL statement. The iierrornumber variable contains the error number (if any) associated with the execution of a database procedure statement.

Because both iierrornumber and iirowcount reflect the results of the preceding query, beware of inadvertently resetting the value of one when checking the other.

The following example from a database procedure illustrates this error:

...

update emp set ...

/* The following statement resets iierrornumber, which will reflect the results of the second statement and not the first, as desired. */

/* wrong way to check iirowcount */

rcount = iirowcount;

/* The error number reflects the results of the preceding assignment, not the update statement */

enumber = iierrornumber;

The following example illustrates the correct way to check iierrornumber and iirowcount: select both values into variables, and then check the contents of the variables (because iierrornumber and iirowcount is reset to reflect the results of the select statement).

...

update emp set ...

/* right way to check iirowcount (using select) */

select iirowcount, iierrornumber into rcount, enumber;

The following table lists the values of iirowcount and iierrornumber after the successful or unsuccessful execution of an SQL statement:

The execution of each database procedure statement sets the value of iierrornumber either to zero (no errors) or an error number. To check the execution status of any particular statement, iierrornumber must be examined immediately after the execution of the statement.

Errors occurring in if, while, message, and return statements do not set iierrornumber. However, any errors that occur during the evaluation of the condition of an if or while statement terminate the procedure and return control to the calling application.

Raise Error Statement

The raise error statement generates an error. The DBMS Server responds to this error exactly as it does to any other error. If the raise error statement is issued by a database procedure that is directly executed, the error is handled using the default error handling behavior or the user-supplied error handling mechanism. If the statement is executed inside a procedure invoked by a rule, the DBMS Server terminates the database procedure and rolls back any changes made by the procedure and any made by the statement that fired the rule.

The error number that is specified as an argument to raise error is returned to sqlerrd(1), and can be accessed using inquire_sql(dbmserror).

The raise error statement can be used in conjunction with the conditional statements to tell the DBMS Server that the results from the statement that fired the rule violate some specified condition or constraint. For example, if a user attempts to update a table, a rule can invoke a database procedure that checks the updated values for compliance with a specified constraint. If the updated values fail the check, the raise error statement can be used to roll back those updates.

Messages from Database Procedures

Database procedures use the SQL message statement to return messages to users and applications. (The SQL message statement is not the same as the forms message statement.) Messages from database procedures can be trapped using the whenever sqlmessage statement or the set_sql(messagehandler) statement.

Messages from database procedures can return to your application before the database procedure has finished executing. For this reason, any message-handling routine must not execute any database statements in the current session. To issue database statements from a message-handling routine, switch sessions or open another session; if your message-handling routine switches sessions, it must switch back to the original session before returning from the message-handling routine.

Message Handling Using the Whenever Statement

If your application does not include an SQLCA, messages from database procedures are displayed on the terminal. If your application includes an SQLCA, use the whenever statement to trap and handle messages from database procedures. If your application includes an SQLCA, messages are displayed only if your application issues the whenever sqlmessage call sqlprint statement.

The whenever statement handles the following scenarios:

• All messages returned from directly executed database procedures
• The last message returned from a procedure called when a rule is fired

Messages issued by database procedures return message text and a message number to the calling application, and set sqlcode to +700.

Note: If a database procedure issues a message statement and subsequently raises an error, the whenever sqlmessage does not trap the message. To trap all messages, use a message handler routine.

Message Handling Using User-Defined Handler Routines

To define a message handler routine, use the set_sql messagehandler statement. Routines defined this way can trap all messages returned by procedures that are executed by rules; the whenever statement traps only the last message.

To enable or disable a message-handling routine, your application must issue the following set_sql statement:

exec sql set_sql(messagehandler = message_routine | 0)

To enable message handling, specify message_routine as a pointer to your message-handling routine or function. (For more information about pointers to functions, see the Embedded SQL Companion Guide.) To disable message handling, specify 0.

In addition to issuing the set_sql statement shown above, create the message-handling routine and link it with your embedded SQL application.