<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="9. SQL Procedure Language Guide" />
<meta name="dc.subject" content="9. SQL Procedure Language Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlprocedures.html" title="Chapter Contents" />
<link rel="prev" href="plmodules.html" title="Virtuoso PL Modules" />
<link rel="next" href="pldebugger.html" title="Procedure Language Debugger" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>9. SQL Procedure Language Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="handlingplcondit" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>9. SQL Procedure Language Guide</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlprocedures.html">Chapter Contents</a> | <a class="link" href="plmodules.html" title="Virtuoso PL Modules">Prev</a> | <a class="link" href="pldebugger.html" title="Procedure Language Debugger">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlprocedures.html">SQL Procedure Language Guide</a>
</div>
<br />
<div>
<a href="GENERALPRINCIPLES.html">General Principles</a>
</div>
<div>
<a href="SCOPEOFDECLARATION.html">Scope of Declarations</a>
</div>
<div>
<a href="sqlplDATATYPES.html">Data Types</a>
</div>
<div>
<a href="RESULTSETS.html">Handling Result Sets</a>
</div>
<div>
<a href="ARRAYS.html">Result Sets and Array Parameters</a>
</div>
<div>
<a href="EXCEPTIONS.html">Exception Semantics</a>
</div>
<div>
<a href="PLREF.html">Virtuoso/PL Syntax</a>
</div>
<div>
<a href="EXECSQLPROCSELECT.html">Execute Stored Procedures via SELECT statement</a>
</div>
<div>
<a href="EXECSQLPROCFORK.html">Execute Stored Procedures In Background</a>
</div>
<div>
<a href="createassembly.html">CREATE ASSEMBLY Syntax - External Libraries</a>
</div>
<div>
<a href="createexthostproc.html">CREATE PROCEDURE Syntax - External hosted procedures</a>
</div>
<div>
<a href="ASYNCEXECMULTITHREAD.html">Asynchronous Execution and Multithreading in Virtuoso/PL</a>
</div>
<div>
<a href="PERFTIPS.html">Performance Tips</a>
</div>
<div>
<a href="PROCEDURES_TRANSACTIONS.html">Procedures and Transactions</a>
</div>
<div>
<a href="twopcimplementation.html">Distributed Transaction & Two Phase Commit</a>
</div>
<div>
<a href="TRIGGERS.html">Triggers</a>
</div>
<div>
<a href="charescaping.html">Character Escaping</a>
</div>
<div>
<a href="PLSCROLLCRSRS.html">Virtuoso/PL Scrollable Cursors</a>
</div>
<div>
<a href="plmodules.html">Virtuoso PL Modules</a>
</div>
<div class="selected">
<a href="handlingplcondit.html">Handling Conditions In Virtuoso/PL Procedures</a>
<div>
<a href="#declarecondit" title="Declaring Condition Handlers">Declaring Condition Handlers</a>
<a href="#stacktracesql" title="Stack Trace Reporting On Sql Error Generation">Stack Trace Reporting On Sql Error Generation</a>
</div>
</div>
<div>
<a href="pldebugger.html">Procedure Language Debugger</a>
</div>
<div>
<a href="rowlevelsecurity.html">Row Level Security</a>
</div>
<br />
</div>
<div id="text">
<a name="handlingplcondit" />
<h2>9.20. Handling Conditions In Virtuoso/PL Procedures</h2>
<p>
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.
</p>
<p>
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.
</p>
<p>
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 signalled to the calling
client.
</p>
<p>
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.
</p>
<a name="declarecondit" />
<h3>9.20.1. Declaring Condition Handlers</h3>
<p>
The general form of handler declaration is:
</p>
<div>
<pre class="programlisting">
DECLARE <handler_type> HANDLER FOR
<condition> [, <condition [...]]
<sql_procedure_statement>;
</pre>
</div>
<p>
For compatibility handlers can be declared also as :
</p>
<div>
<pre class="programlisting">
WHENEVER <condition> [GOTO <label>|DEFAULT];
</pre>
</div>
<p>
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>.
</p>
<div>
<pre class="programlisting">
<handler_type>
</pre>
</div>
<p>
<strong>CONTINUE</strong> - Specifies that after <sql_procedure_statement> completes,
execution continues with the statement after the statement which caused the error.
</p>
<p>
<strong>EXIT</strong> - Specifies that after <sql_procedure_statement> completes,
execution continues after the end of the compound statement that contains the declaration of the handler.
</p>
<div>
<pre class="programlisting">
<condition>
</pre>
</div>
<p>
<strong>NOT FOUND</strong> - Identifies any condition that results in SQL STATE = SQL_NO_DATA_FOUND (+100)
</p>
<p>
<strong>SQLEXCEPTION</strong> - Identifies all character SQL STATEs excluding ones starting with '01', '02' and '00'
</p>
<p>
<strong>SQLWARNING</strong> - Identifies character SQL STATEs starting with '01'.
This is a shortcut for SQLSTATE '01*'
</p>
<p>
<strong>SQLSTATE [VALUE] '<sql_state_mask>'</strong> - 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
</p>
<div>
<pre class="programlisting">
SQLSTATE <conditions> :
SQLSTATE '*' SQLSTATE '42*'
SQLSTATE '42S22'
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>Handler's call priority is determined by the number of matching characters in this mask.</p>
</div>
<div>
<pre class="programlisting">
<sql_procedure_statement>
</pre>
</div>
<p>
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.
</p>
<p>
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.
</p>
<div>
<pre class="programlisting">
WHENEVER <condition> GOTO <label>
</pre>
</div>
<p>is an equivalent of:</p>
<div>
<pre class="programlisting">
DECLARE EXIT HANDLER FOR <condition> GOTO label.
</pre>
</div>
<div>
<pre class="programlisting">
WHENEVER <condition> DEFAULT
</pre>
</div>
<p>is equivalent of:</p>
<div>
<pre class="programlisting">
DECLARE EXIT HANDLER FOR <condition> RESIGNAL;
</pre>
</div>
<p>
The following examples demonstrate simple common handlers:
</p>
<p>
<strong>CONTINUE handler:</strong> 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.
</p>
<div>
<pre class="programlisting">
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);
}
</pre>
</div>
<p>
When this procedure gets executed it returns the following result set :
</p>
<div>
<pre class="screen">
0
1
</pre>
</div>
<p>
<strong>EXIT handler:</strong> 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.
</p>
<div>
<pre class="programlisting">
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);
}
</pre>
</div>
<p>
When this procedure gets executed it returns the following result set :
</p>
<div>
<pre class="screen">
0
1
</pre>
</div>
<div>
<pre class="programlisting">
__SQL_STATE and __SQL_MESSAGE variables.
</pre>
</div>
<p>
All Virtuoso/PL procedure have two variables implicitly declared :
</p>
<div>
<pre class="programlisting">
declare __SQL_STATE any;
declare __SQL_MESSAGE varchar;
</pre>
</div>
<p>
Initially they are set to 0.
</p>
<p>
When an exception is raised these variables are set as follows :
</p>
<div>
<pre class="programlisting">
__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.
</pre>
</div>
<p>
Their values are preserved until the next exception overwrites them.
</p>
<div>
<pre class="programlisting">
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);
</pre>
</div>
<p>
This statement resignals the current exception to the caller of the procedure.
</p>
<br />
<a name="stacktracesql" />
<h3>9.20.2. Stack Trace Reporting On Sql Error Generation</h3>
<p>
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.
</p>
<p>
The call stack reporting mode is controlled by the "CallstackOnException" option
in the <a href="">
<strong>Parameters</strong> section of the Virtuoso INI file</a>.
</p>
<p>
This parameter takes the following values:
</p>
<ul>
<li>0 (default) - Call stack reporting disabled.</li>
<li>1 - Call stack is reported but does not include values of arguments.</li>
<li>2 - Call stack is reported and contains all available information.</li>
</ul>
<p>
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.
</p>
<p>
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.
</p>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="plmodules.html" title="Virtuoso PL Modules">Previous</a>
<br />Virtuoso PL Modules</td>
<td align="center" width="34%">
<a href="sqlprocedures.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="pldebugger.html" title="Procedure Language Debugger">Next</a>
<br />Procedure Language Debugger</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
771
