# 9. SQL Procedure Language Guide

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
SQL Procedure Language Guide
General Principles
Scope of Declarations
Data Types
Handling Result Sets
Result Sets and Array Parameters
Exception Semantics
Virtuoso/PL Syntax
CREATE ASSEMBLY Syntax - External Libraries
CREATE PROCEDURE Syntax - External hosted procedures
Asynchronous Execution and Multithreading in Virtuoso/PL
Performance Tips
Procedures and Transactions
Distributed Transaction & Two Phase Commit
Triggers
Character Escaping
Virtuoso/PL Scrollable Cursors
Virtuoso PL Modules
Handling Conditions In Virtuoso/PL Procedures
Declaring Condition Handlers Stack Trace Reporting On Sql Error Generation
Procedure Language Debugger
Row Level Security

## 9.18. Handling Conditions In Virtuoso/PL Procedures

Condition handlers determine the behavior of a Virtuoso/PL procedure when a condition occurs. You can declare one or more condition handlers in your Virtuoso/PL procedure for general SQL conditions or specific SQLSTATE values.

If a statement in your procedure raises an SQLEXCEPTION condition and you declared a handler for the specific SQLSTATE or SQLEXCEPTION condition the server passes control to that handler.

If a statement in your Virtuoso/PL procedure raises an SQLEXCEPTION condition, and you have not declared a handler for the specific SQLSTATE or the SQLEXCEPTION condition, the server passes the exception to the calling procedure (if any). If the procedure call is at the top-level, then the exception is signaled to the calling client.

Handlers are active only for the duration of the enclosing compound statement. When an exception is thrown outside the handler's scope then this handler is never called.

### 9.18.1. Declaring Condition Handlers

The general form of handler declaration is:

    DECLARE <handler_type> HANDLER FOR
    	<condition> [, <condition [...]]
    	<sql_procedure_statement>;

For compatibility handlers can be declared also as :

    WHENEVER <condition> [GOTO <label>|DEFAULT];

When Virtuoso raises a condition that matches the <condition>, the <sql_procedure_statement> gets executed and when (and if) it finishes the execution continues according to the <handler_type>.

    <handler_type>

CONTINUE - Specifies that after <sql_procedure_statement> completes, execution continues with the statement after the statement which caused the error.

EXIT - Specifies that after <sql_procedure_statement> completes, execution continues after the end of the compound statement that contains the declaration of the handler.

    <condition>

NOT FOUND - Identifies any condition that results in SQL STATE = SQL_NO_DATA_FOUND (+100)

SQLEXCEPTION - Identifies all character SQL STATEs excluding ones starting with '01', '02' and '00'

SQLWARNING - Identifies character SQL STATEs starting with '01'. This is a shortcut for SQLSTATE '01*'

SQLSTATE [VALUE] '<sql_state_mask>' - Identifies character SQL STATEs. The <sql_state_mask> can be a full 5 character value or 0-4 characters followed by an '*'. When a '*' is present then any SQL STATE signal led which starts with the same characters as the <sql_state_mask> before the '*' will cause that handler to execute. For example the exception with SQL state '42S22' will match all the following

    SQLSTATE <conditions> :
    SQLSTATE '*' SQLSTATE '42*'
    SQLSTATE '42S22'

Note:

Handler's call priority is determined by the number of matching characters in this mask.

    <sql_procedure_statement>

This can be any allowed Virtuoso/PL statement as well as an compound statement. This statement is executed in the same procedure context as the procedure body itself, so any labels and variables in the procedure body can be used and RETURN causes the procedure to end. No handler is active while the <sql_procedure_statement> is executed. So any exception raised is passed directly to the procedure caller. The <sql_procedure_statement> can be empty resulting in the <handler_type> action being taken right after setting the __SQL_STATE & __SQL_MESSAGE variables.

When multiple active handlers <condition>s match the exception being raised Virtuoso chooses the closest to the statement raised the exception that has a largest call priority. This means that if an exception is there are two handlers with condition SQLSTATE '4*' and SQLSTATE '42*' and an exception with SQL STATE '42S22' is raised the handler with <condition> '42*' will be called.

    WHENEVER <condition> GOTO <label>

is an equivalent of:

    DECLARE EXIT HANDLER FOR <condition> GOTO label.

    WHENEVER <condition> DEFAULT

is equivalent of:

    DECLARE EXIT HANDLER FOR <condition> RESIGNAL;

The following examples demonstrate simple common handlers:

CONTINUE handler: The handler assigns a value of 1 to a local variable at_end when a NOT FOUND condition is raised. The execution then continues with the statement after the signal.

    create procedure test1 ()
    {
      declare at_end integer;

      at_end := 0;
      declare continue handler for NOT FOUND at_end := 1;

      result_names (at_end);

      result (at_end);
      signal (100);
      result (at_end);
    }

When this procedure gets executed it returns the following result set :

    0
    1

EXIT handler: The handler assigns a value of 2 to a local variable at_end when a NOT_FOUND condition is raised. The execution then continues with the statement after the compound statement containing the signal.

    create procedure test2 ()
    {
      declare at_end integer;

      result_names (at_end);

      at_end := 0;
      declare exit handler for NOT FOUND at_end := 1;

        {
          result (at_end);
          signal (100);
          result (3);
        }
      result (at_end);
    }

When this procedure gets executed it returns the following result set :

    0
    1

    __SQL_STATE and __SQL_MESSAGE variables.

All Virtuoso/PL procedure have two variables implicitly declared :

    declare __SQL_STATE any;
    declare __SQL_MESSAGE varchar;

Initially they are set to 0.

When an exception is raised these variables are set as follows :

    __SQL_STATE gets the SQL STATE (character string or integer 100 for NOT FOUND)
    __SQL_MESSAGE gets the SQL MESSAGE (character) or NULL if no message.

Their values are preserved until the next exception overwrites them.

    RESIGNAL statement

    Syntax :
    RESIGNAL [ '<new_sql_state>' ]

    RESIGNAL  is a shortcut for    signal (__SQL_STATE, __SQL_MESSAGE)
    RESIGNAL '<new_sql_state>' is a shortcut for     signal ('<new_sql_state>', __SQL_MESSAGE);

This statement resignals the current exception to the caller of the procedure.

### 9.18.2. Stack Trace Reporting On Sql Error Generation

When an exception occurs the Virtuoso server has the ability to provide information about the procedure call stack. It appends the call stack information to the error message text. There are also line numbers besides each level of the call stack which are a Virtuoso/PL procedure. The line numbers mark the beginning of the innermost compound statement.

The call stack reporting mode is controlled by the "CallstackOnException" option in the Parameters section of the Virtuoso INI file.

This parameter takes the following values:

- 0 (default) - Call stack reporting disabled.
- 1 - Call stack is reported but does not include values of arguments.
- 2 - Call stack is reported and contains all available information.

Call stack reporting can be a security hole because it can demonstrate internal logic of the system to the end user; this is especially important for dynamic web pages. Mode 2 is especially insecure because it may print values of function arguments that may contain confidential information.

Some client applications are unable to handle long error messages properly. Client-side APIs for ODBC and similar protocols assume that client should allocate a buffer for error message string and then ask the API to save the message string to the specified buffer of the specified size. Not all client applications work properly if a message does not fit in the buffer. If an client application you use reports an empty string instead of error message or displays a message like 'Error message is too long' then you may wish to decrease the value of the "CallstackOnException" option to keep messages shorter.

Previous Virtuoso PL Modules | Chapter Contents | Next Procedure Language Debugger