<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="get-diagnostics">
      </a>
      15.6.7.3 GET DIAGNOSTICS Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045175928240">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53566021"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">GET</span> <span class="token punctuation">[</span><span class="token keyword">CURRENT</span> <span class="token operator">|</span> <span class="token keyword">STACKED</span><span class="token punctuation">]</span> <span class="token keyword">DIAGNOSTICS</span> {
    <em class="replaceable">statement_information_item</em>
    <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">statement_information_item</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token operator">|</span> <span class="token keyword">CONDITION</span> <em class="replaceable">condition_number</em>
    <em class="replaceable">condition_information_item</em>
    <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">condition_information_item</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
}

<em class="replaceable">statement_information_item</em>:
    <em class="replaceable">target</em> <span class="token operator">=</span> <em class="replaceable">statement_information_item_name</em>

<em class="replaceable">condition_information_item</em>:
    <em class="replaceable">target</em> <span class="token operator">=</span> <em class="replaceable">condition_information_item_name</em>

<em class="replaceable">statement_information_item_name</em>: {
    <span class="token keyword">NUMBER</span>
  <span class="token operator">|</span> <span class="token keyword">ROW_COUNT</span>
}

<em class="replaceable">condition_information_item_name</em>: {
    <span class="token keyword">CLASS_ORIGIN</span>
  <span class="token operator">|</span> <span class="token keyword">SUBCLASS_ORIGIN</span>
  <span class="token operator">|</span> <span class="token keyword">RETURNED_SQLSTATE</span>
  <span class="token operator">|</span> <span class="token keyword">MESSAGE_TEXT</span>
  <span class="token operator">|</span> <span class="token keyword">MYSQL_ERRNO</span>
  <span class="token operator">|</span> <span class="token keyword">CONSTRAINT_CATALOG</span>
  <span class="token operator">|</span> <span class="token keyword">CONSTRAINT_SCHEMA</span>
  <span class="token operator">|</span> <span class="token keyword">CONSTRAINT_NAME</span>
  <span class="token operator">|</span> <span class="token keyword">CATALOG_NAME</span>
  <span class="token operator">|</span> <span class="token keyword">SCHEMA_NAME</span>
  <span class="token operator">|</span> <span class="token keyword">TABLE_NAME</span>
  <span class="token operator">|</span> <span class="token keyword">COLUMN_NAME</span>
  <span class="token operator">|</span> <span class="token keyword">CURSOR_NAME</span>
}

<em class="replaceable">condition_number</em><span class="token punctuation">,</span> <em class="replaceable">target</em>:
    <span class="token punctuation">(</span>see <span class="token keyword">following</span> discussion<span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   SQL statements produce diagnostic information that populates the
        diagnostics area. The
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET
        DIAGNOSTICS
    </code>
   </a>
   statement enables applications to inspect
        this information. (You can also use
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW
        WARNINGS
    </code>
   </a>
   or
   <a class="link" href="show-errors.html" title="15.7.7.18 SHOW ERRORS Statement">
    <code class="literal">
     SHOW ERRORS
    </code>
   </a>
   to see conditions or errors.)
  </p>
  <p>
   No special privileges are required to execute
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET DIAGNOSTICS
    </code>
   </a>
   .
  </p>
  <p>
   The keyword
   <code class="literal">
    CURRENT
   </code>
   means to retrieve
        information from the current diagnostics area. The keyword
   <code class="literal">
    STACKED
   </code>
   means to retrieve information from
        the second diagnostics area, which is available only if the
        current context is a condition handler. If neither keyword is
        given, the default is to use the current diagnostics area.
  </p>
  <p>
   The
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET DIAGNOSTICS
    </code>
   </a>
   statement is
        typically used in a handler within a stored program. It is a
        MySQL extension that
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET [CURRENT]
        DIAGNOSTICS
    </code>
   </a>
   is permitted outside handler context to
        check the execution of any SQL statement. For example, if you
        invoke the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client program, you can
        enter these statements at the prompt:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17667820"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> test<span class="token punctuation">.</span>no_such_table<span class="token punctuation">;</span>
<span class="token output">ERROR 1051 (42S02)<span class="token punctuation">:</span> Unknown table 'test.no_such_table'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">GET</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token number">1</span>
         <span class="token variable">@p1</span> <span class="token operator">=</span> <span class="token keyword">RETURNED_SQLSTATE</span><span class="token punctuation">,</span> <span class="token variable">@p2</span> <span class="token operator">=</span> <span class="token keyword">MESSAGE_TEXT</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@p1</span><span class="token punctuation">,</span> <span class="token variable">@p2</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @p1   <span class="token punctuation">|</span> @p2                                <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 42S02 <span class="token punctuation">|</span> Unknown table 'test.no_such_table' <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   This extension applies only to the current diagnostics area. It
        does not apply to the second diagnostics area because
   <code class="literal">
    GET STACKED DIAGNOSTICS
   </code>
   is permitted only if
        the current context is a condition handler. If that is not the
        case, a
   <code class="literal">
    GET STACKED DIAGNOSTICS when handler not
        active
   </code>
   error occurs.
  </p>
  <p>
   For a description of the diagnostics area, see
   <a class="xref" href="diagnostics-area.html" title="15.6.7.7 The MySQL Diagnostics Area">
    Section 15.6.7.7, “The MySQL Diagnostics Area”
   </a>
   . Briefly, it contains two
        kinds of information:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Statement information, such as the number of conditions that
            occurred or the affected-rows count.
     </p>
    </li>
    <li class="listitem">
     <p>
      Condition information, such as the error code and message.
            If a statement raises multiple conditions, this part of the
            diagnostics area has a condition area for each one. If a
            statement raises no conditions, this part of the diagnostics
            area is empty.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For a statement that produces three conditions, the diagnostics
        area contains statement and condition information like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa16444333"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">Statement information:
  row count
  ... other statement information items ...
Condition area list:
  Condition area 1:
    error code for condition 1
    error message for condition 1
    ... other condition information items ...
  Condition area 2:
    error code for condition 2:
    error message for condition 2
    ... other condition information items ...
  Condition area 3:
    error code for condition 3
    error message for condition 3
    ... other condition information items ...</code></pre>
  </div>
  <p>
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET DIAGNOSTICS
    </code>
   </a>
   can obtain either
        statement or condition information, but not both in the same
        statement:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To obtain statement information, retrieve the desired
            statement items into target variables. This instance of
      <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
       <code class="literal">
        GET DIAGNOSTICS
       </code>
      </a>
      assigns the
            number of available conditions and the rows-affected count
            to the user variables
      <code class="literal">
       @p1
      </code>
      and
      <code class="literal">
       @p2
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa36243694"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">GET</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token variable">@p1</span> <span class="token operator">=</span> <span class="token keyword">NUMBER</span><span class="token punctuation">,</span> <span class="token variable">@p2</span> <span class="token operator">=</span> <span class="token keyword">ROW_COUNT</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      To obtain condition information, specify the condition
            number and retrieve the desired condition items into target
            variables. This instance of
      <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
       <code class="literal">
        GET
            DIAGNOSTICS
       </code>
      </a>
      assigns the SQLSTATE value and error
            message to the user variables
      <code class="literal">
       @p3
      </code>
      and
      <code class="literal">
       @p4
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79999504"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">GET</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token number">1</span>
  <span class="token variable">@p3</span> <span class="token operator">=</span> <span class="token keyword">RETURNED_SQLSTATE</span><span class="token punctuation">,</span> <span class="token variable">@p4</span> <span class="token operator">=</span> <span class="token keyword">MESSAGE_TEXT</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The retrieval list specifies one or more
   <code class="literal">
    <em class="replaceable">
     <code>
      target
     </code>
    </em>
    =
    <em class="replaceable">
     <code>
      item_name
     </code>
    </em>
   </code>
   assignments,
        separated by commas. Each assignment names a target variable and
        either a
   <em class="replaceable">
    <code>
     statement_information_item_name
    </code>
   </em>
   or
   <em class="replaceable">
    <code>
     condition_information_item_name
    </code>
   </em>
   designator, depending on whether the statement retrieves
        statement or condition information.
  </p>
  <p>
   Valid
   <em class="replaceable">
    <code>
     target
    </code>
   </em>
   designators for storing
        item information can be stored procedure or function parameters,
        stored program local variables declared with
   <a class="link" href="declare.html" title="15.6.3 DECLARE Statement">
    <code class="literal">
     DECLARE
    </code>
   </a>
   , or user-defined
        variables.
  </p>
  <p>
   Valid
   <em class="replaceable">
    <code>
     condition_number
    </code>
   </em>
   designators
        can be stored procedure or function parameters, stored program
        local variables declared with
   <a class="link" href="declare.html" title="15.6.3 DECLARE Statement">
    <code class="literal">
     DECLARE
    </code>
   </a>
   , user-defined variables,
        system variables, or literals. A character literal may include a
   <em class="replaceable">
    <code>
     _charset
    </code>
   </em>
   introducer. A warning occurs
        if the condition number is not in the range from 1 to the number
        of condition areas that have information. In this case, the
        warning is added to the diagnostics area without clearing it.
  </p>
  <p>
   When a condition occurs, MySQL does not populate all condition
        items recognized by
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET
        DIAGNOSTICS
    </code>
   </a>
   . For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa27860080"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">GET</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token number">1</span>
         <span class="token variable">@p5</span> <span class="token operator">=</span> <span class="token keyword">SCHEMA_NAME</span><span class="token punctuation">,</span> <span class="token variable">@p6</span> <span class="token operator">=</span> <span class="token keyword">TABLE_NAME</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@p5</span><span class="token punctuation">,</span> <span class="token variable">@p6</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @p5  <span class="token punctuation">|</span> @p6  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>      <span class="token punctuation">|</span>      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   In standard SQL, if there are multiple conditions, the first
        condition relates to the
   <code class="literal">
    SQLSTATE
   </code>
   value
        returned for the previous SQL statement. In MySQL, this is not
        guaranteed. To get the main error, you cannot do this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa37822294"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">GET</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token number">1</span> <span class="token variable">@errno</span> <span class="token operator">=</span> <span class="token keyword">MYSQL_ERRNO</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Instead, retrieve the condition count first, then use it to
        specify which condition number to inspect:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa85147644"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">GET</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token variable">@cno</span> <span class="token operator">=</span> <span class="token keyword">NUMBER</span><span class="token punctuation">;</span>
<span class="token keyword">GET</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token variable">@cno</span> <span class="token variable">@errno</span> <span class="token operator">=</span> <span class="token keyword">MYSQL_ERRNO</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For information about permissible statement and condition
        information items, and which ones are populated when a condition
        occurs, see
   <a class="xref" href="diagnostics-area.html#diagnostics-area-information-items" title="Diagnostics Area Information Items">
    Diagnostics Area Information Items
   </a>
   .
  </p>
  <p>
   Here is an example that uses
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET
        DIAGNOSTICS
    </code>
   </a>
   and an exception handler in stored
        procedure context to assess the outcome of an insert operation.
        If the insert was successful, the procedure uses
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET DIAGNOSTICS
    </code>
   </a>
   to get the
        rows-affected count. This shows that you can use
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET DIAGNOSTICS
    </code>
   </a>
   multiple times to
        retrieve information about a statement as long as the current
        diagnostics area has not been cleared.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8088955"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> do_insert<span class="token punctuation">(</span><span class="token keyword">value</span> <span class="token datatype">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
  <span class="token comment" spellcheck="true">-- Declare variables to hold diagnostics area information</span>
  <span class="token keyword">DECLARE</span> <span class="token keyword">code</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">'00000'</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> msg <span class="token datatype">TEXT</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> nrows <span class="token datatype">INT</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> result <span class="token datatype">TEXT</span><span class="token punctuation">;</span>
  <span class="token comment" spellcheck="true">-- Declare exception handler for failed insert</span>
  <span class="token keyword">DECLARE</span> <span class="token keyword">CONTINUE</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> <span class="token keyword">SQLEXCEPTION</span>
    <span class="token keyword">BEGIN</span>
      <span class="token keyword">GET</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token number">1</span>
        <span class="token keyword">code</span> <span class="token operator">=</span> <span class="token keyword">RETURNED_SQLSTATE</span><span class="token punctuation">,</span> msg <span class="token operator">=</span> <span class="token keyword">MESSAGE_TEXT</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span><span class="token punctuation">;</span>

  <span class="token comment" spellcheck="true">-- Perform the insert</span>
  <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>int_col<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token keyword">value</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token comment" spellcheck="true">-- Check whether the insert was successful</span>
  <span class="token keyword">IF</span> <span class="token keyword">code</span> <span class="token operator">=</span> <span class="token string">'00000'</span> <span class="token keyword">THEN</span>
    <span class="token keyword">GET</span> <span class="token keyword">DIAGNOSTICS</span> nrows <span class="token operator">=</span> <span class="token keyword">ROW_COUNT</span><span class="token punctuation">;</span>
    <span class="token keyword">SET</span> result <span class="token operator">=</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token string">'insert succeeded, row count = '</span><span class="token punctuation">,</span>nrows<span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token keyword">ELSE</span>
    <span class="token keyword">SET</span> result <span class="token operator">=</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token string">'insert failed, error = '</span><span class="token punctuation">,</span><span class="token keyword">code</span><span class="token punctuation">,</span><span class="token string">', message = '</span><span class="token punctuation">,</span>msg<span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
  <span class="token comment" spellcheck="true">-- Say what happened</span>
  <span class="token keyword">SELECT</span> result<span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Suppose that
   <code class="literal">
    t1.int_col
   </code>
   is an integer column
        that is declared as
   <code class="literal">
    NOT NULL
   </code>
   . The procedure
        produces these results when invoked to insert
        non-
   <code class="literal">
    NULL
   </code>
   and
   <code class="literal">
    NULL
   </code>
   values,
        respectively:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44071147"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CALL</span> do_insert<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> result                          <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> insert succeeded, row count = 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CALL</span> do_insert<span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> result                                                                  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> insert failed, error = 23000, message = Column 'int_col' cannot be null <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   When a condition handler activates, a push to the diagnostics
        area stack occurs:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The first (current) diagnostics area becomes the second
            (stacked) diagnostics area and a new current diagnostics
            area is created as a copy of it.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
       <code class="literal">
        GET
            [CURRENT] DIAGNOSTICS
       </code>
      </a>
      and
      <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
       <code class="literal">
        GET STACKED
            DIAGNOSTICS
       </code>
      </a>
      can be used within the handler to
            access the contents of the current and stacked diagnostics
            areas.
     </p>
    </li>
    <li class="listitem">
     <p>
      Initially, both diagnostics areas return the same result, so
            it is possible to get information from the current
            diagnostics area about the condition that activated the
            handler,
      <span class="emphasis">
       <em>
        as long as
       </em>
      </span>
      you execute no
            statements within the handler that change its current
            diagnostics area.
     </p>
    </li>
    <li class="listitem">
     <p>
      However, statements executing within the handler can modify
            the current diagnostics area, clearing and setting its
            contents according to the normal rules (see
      <a class="xref" href="diagnostics-area.html#diagnostics-area-populating" title="How the Diagnostics Area is Cleared and Populated">
       How the Diagnostics Area is Cleared and Populated
      </a>
      ).
     </p>
     <p>
      A more reliable way to obtain information about the
            handler-activating condition is to use the stacked
            diagnostics area, which cannot be modified by statements
            executing within the handler except
      <a class="link" href="resignal.html" title="15.6.7.4 RESIGNAL Statement">
       <code class="literal">
        RESIGNAL
       </code>
      </a>
      . For information
            about when the current diagnostics area is set and cleared,
            see
      <a class="xref" href="diagnostics-area.html" title="15.6.7.7 The MySQL Diagnostics Area">
       Section 15.6.7.7, “The MySQL Diagnostics Area”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The next example shows how
   <code class="literal">
    GET STACKED
        DIAGNOSTICS
   </code>
   can be used within a handler to obtain
        information about the handled exception, even after the current
        diagnostics area has been modified by handler statements.
  </p>
  <p>
   Within a stored procedure
   <code class="literal">
    p()
   </code>
   , we attempt to
        insert two values into a table that contains a
   <code class="literal">
    TEXT NOT
        NULL
   </code>
   column. The first value is a
        non-
   <code class="literal">
    NULL
   </code>
   string and the second is
   <code class="literal">
    NULL
   </code>
   . The column prohibits
   <code class="literal">
    NULL
   </code>
   values, so the first insert succeeds but
        the second causes an exception. The procedure includes an
        exception handler that maps attempts to insert
   <code class="literal">
    NULL
   </code>
   into inserts of the empty string:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69790457"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> t1<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">TEXT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">PROCEDURE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> p<span class="token punctuation">;</span>
<span class="token keyword">delimiter</span> <span class="token comment" spellcheck="true">//</span>
<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> p <span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
  <span class="token comment" spellcheck="true">-- Declare variables to hold diagnostics area information</span>
  <span class="token keyword">DECLARE</span> errcount <span class="token datatype">INT</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> errno <span class="token datatype">INT</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> msg <span class="token datatype">TEXT</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> <span class="token keyword">EXIT</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> <span class="token keyword">SQLEXCEPTION</span>
  <span class="token keyword">BEGIN</span>
    <span class="token comment" spellcheck="true">-- Here the current DA is nonempty because no prior statements</span>
    <span class="token comment" spellcheck="true">-- executing within the handler have cleared it</span>
    <span class="token keyword">GET</span> <span class="token keyword">CURRENT</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token number">1</span>
      errno <span class="token operator">=</span> <span class="token keyword">MYSQL_ERRNO</span><span class="token punctuation">,</span> msg <span class="token operator">=</span> <span class="token keyword">MESSAGE_TEXT</span><span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> <span class="token string">'current DA before mapped insert'</span> <span class="token keyword">AS</span> op<span class="token punctuation">,</span> errno<span class="token punctuation">,</span> msg<span class="token punctuation">;</span>
    <span class="token keyword">GET</span> <span class="token keyword">STACKED</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token number">1</span>
      errno <span class="token operator">=</span> <span class="token keyword">MYSQL_ERRNO</span><span class="token punctuation">,</span> msg <span class="token operator">=</span> <span class="token keyword">MESSAGE_TEXT</span><span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> <span class="token string">'stacked DA before mapped insert'</span> <span class="token keyword">AS</span> op<span class="token punctuation">,</span> errno<span class="token punctuation">,</span> msg<span class="token punctuation">;</span>

    <span class="token comment" spellcheck="true">-- Map attempted NULL insert to empty string insert</span>
    <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>c1<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">''</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token comment" spellcheck="true">-- Here the current DA should be empty (if the INSERT succeeded),</span>
    <span class="token comment" spellcheck="true">-- so check whether there are conditions before attempting to</span>
    <span class="token comment" spellcheck="true">-- obtain condition information</span>
    <span class="token keyword">GET</span> <span class="token keyword">CURRENT</span> <span class="token keyword">DIAGNOSTICS</span> errcount <span class="token operator">=</span> <span class="token keyword">NUMBER</span><span class="token punctuation">;</span>
    <span class="token keyword">IF</span> errcount <span class="token operator">=</span> <span class="token number">0</span>
    <span class="token keyword">THEN</span>
      <span class="token keyword">SELECT</span> <span class="token string">'mapped insert succeeded, current DA is empty'</span> <span class="token keyword">AS</span> op<span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span>
      <span class="token keyword">GET</span> <span class="token keyword">CURRENT</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token number">1</span>
        errno <span class="token operator">=</span> <span class="token keyword">MYSQL_ERRNO</span><span class="token punctuation">,</span> msg <span class="token operator">=</span> <span class="token keyword">MESSAGE_TEXT</span><span class="token punctuation">;</span>
      <span class="token keyword">SELECT</span> <span class="token string">'current DA after mapped insert'</span> <span class="token keyword">AS</span> op<span class="token punctuation">,</span> errno<span class="token punctuation">,</span> msg<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span> <span class="token punctuation">;</span>
    <span class="token keyword">GET</span> <span class="token keyword">STACKED</span> <span class="token keyword">DIAGNOSTICS</span> <span class="token keyword">CONDITION</span> <span class="token number">1</span>
      errno <span class="token operator">=</span> <span class="token keyword">MYSQL_ERRNO</span><span class="token punctuation">,</span> msg <span class="token operator">=</span> <span class="token keyword">MESSAGE_TEXT</span><span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> <span class="token string">'stacked DA after mapped insert'</span> <span class="token keyword">AS</span> op<span class="token punctuation">,</span> errno<span class="token punctuation">,</span> msg<span class="token punctuation">;</span>
  <span class="token keyword">END</span><span class="token punctuation">;</span>
  <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>c1<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'string 1'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>c1<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">//</span>
<span class="token keyword">delimiter</span> <span class="token punctuation">;</span>
<span class="token keyword">CALL</span> p<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   When the handler activates, a copy of the current diagnostics
        area is pushed to the diagnostics area stack. The handler first
        displays the contents of the current and stacked diagnostics
        areas, which are both the same initially:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa28258247"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">+---------------------------------+-------+----------------------------+
| op                              | errno | msg                        |
+---------------------------------+-------+----------------------------+
| current DA before mapped insert |  1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+

+---------------------------------+-------+----------------------------+
| op                              | errno | msg                        |
+---------------------------------+-------+----------------------------+
| stacked DA before mapped insert |  1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+</code></pre>
  </div>
  <p>
   Statements executing after the
   <a class="link" href="get-diagnostics.html" title="15.6.7.3 GET DIAGNOSTICS Statement">
    <code class="literal">
     GET
        DIAGNOSTICS
    </code>
   </a>
   statements may reset the current
        diagnostics area. statements may reset the current diagnostics
        area. For example, the handler maps the
   <code class="literal">
    NULL
   </code>
   insert to an empty-string insert and displays the result. The
        new insert succeeds and clears the current diagnostics area, but
        the stacked diagnostics area remains unchanged and still
        contains information about the condition that activated the
        handler:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa4719186"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">+----------------------------------------------+
| op                                           |
+----------------------------------------------+
| mapped insert succeeded, current DA is empty |
+----------------------------------------------+

+--------------------------------+-------+----------------------------+
| op                             | errno | msg                        |
+--------------------------------+-------+----------------------------+
| stacked DA after mapped insert |  1048 | Column 'c1' cannot be null |
+--------------------------------+-------+----------------------------+</code></pre>
  </div>
  <p>
   When the condition handler ends, its current diagnostics area is
        popped from the stack and the stacked diagnostics area becomes
        the current diagnostics area in the stored procedure.
  </p>
  <p>
   After the procedure returns, the table contains two rows. The
        empty row results from the attempt to insert
   <code class="literal">
    NULL
   </code>
   that was mapped to an empty-string
        insert:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa3298120"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">+----------+
| c1       |
+----------+
| string 1 |
|          |
+----------+</code></pre>
  </div>
 </div>
 <br/>
</div>
