Что означает информация, возвращаемая параметром SQLSTATE

В элементах CONNECTTON_NAME и ENVIRONMENTJVAME хранятся названия соединения и той среды, с которой было установлено соединение во время выполнения оператора SQL.

Если информация из параметра SQLSTATE относится к работе с таблицей, то эту таблицу определяют элементы CATALOG_NAME, SCHEMA_NAME и TABLE_NAME. Если появление ошибки как-то связано со столбцом таблицы, его имя помещается в элемент COLUMN_NAME. Если нештатная ситуация имеет отношение к курсору, его имя будет находиться в элементе CURSOR_NAME.

Иногда СУБД, чтобы объяснить ситуацию, создает строку текста на каком-либо языке, например английском. Такого рода информация содержится в элементе MESSAGE_TEXT. Его содержимое определяется не стандартом SQL.2003, а реализацией. Если в элементе MESSAGEJTEXT имеется сообщение, его длина в символах записывается в элемент MESSAGE_LENGTH, а длина в октетах — в элемент MESSAGE_OCTET_LENGTH. У сообщения, состоящего из обычных ASCII-символов, значения MESSAGE_LENGTH и MESSAGE_OCTET_LENGTH равны между собой. А если сообщение составлено на китайском, японском или любом другом языке, в котором для выражения символа требуется больше одного октета, то значения MESSAGE_LENGTH и MESSAGE_OCTET_LENGTH будут разными.

Чтобы получить диагностическую информацию из области заголовка, воспользуйтесь выражением:

GET DIAGNOSTICS переменная1 = элемент1 [, переменная2 = елемент2] ... ;

где переменная_n — это параметр или базовая переменная; элемент_n — любое из следующих ключевых слов: NUMBER, MORE, COMMAND_FUNCTION, DYNAMIC_FUNCTION или ROW_COUNT.

А чтобы получить диагностическую информацию из информационной области, используют следующий синтаксис:

GET DIAGNOSTICS EXCEPTION номер-состояния

переменная1 = элемент1 [, переменная2 = элемент2] ... ;

где переменная_n — это параметр или базовая переменная; элемент_n — любое из семнадцати ключевых слов элементов информационной области. Эти ключевые слова приведены в табл. 20.2. И наконец, номер состояния— это значение элемента CONDITION_NUMBER информационной области.

Область диагностики

Хотя параметр SQLSTATE в состоянии дать информацию о том, почему неудачно завершился некоторый оператор, но такая информация все же неполная. Поэтому стандарт SQL:2003, кроме того, еще дает возможность перехватывать дополнительную информацию о состоянии и хранить ее в области диагностики. Многократное определение области диагностики работает по принципу стека LIFO (last-in-first-out — последним вошел, первым вышел). Дополнительная информация о состоянии может быть особенно полезной тогда, когда при выполнении единственного оператора SQL появилось множество ошибок. Параметр SQLSTATE сообщает только об одной из них, а область диагностики может рассказать сразу о множестве ошибок (а возможно, и обо всех).

Область диагностики — это структура данных СУБД, состоящая из двух компонентов.

Заголовок. В нем находится общая информация о последнем выполнявшемся операторе SQL.

Информационная область. В ней находится подробная информация о каждом коде (ошибка, предупреждение или успешное выполнение), который был сгенерирован в результате выполнения оператора.

Область заголовка диагностики

В главе 14 мы изучали оператор SET TRANSACTION (задать транзакцию). В нем можно указать DIAGNOSTICS SIZE (размер диагностики). Этот размер является количеством информационных областей, выделяемых для хранения информации о состоянии. Если в операторе

SET TRANSACTION не будет предложения DIAGNOSTICS SIZE, то СУБД выделит количество информационных областей, которое в ней установлено по умолчанию.

Заголовок области состоит из восьми элементов, которые приведены в табл. 20.1.

Обработка исключений

Если параметр состояния SQLSTATE не равен 00000, 00001 или 00002, значит, возникла исключительная ситуация, которую, возможно, нужно будет обработать.

Обработка может состоять в возвращении управления родительской процедуре, в которой была вызвана подпроцедура — источник исключения.

Второй способ обработки состоит в использовании предложения WHENEVER для перехода на нужную процедуру обработки или выполнения какого-либо другого действия.

Третий способ заключается в том, чтобы обрабатывать ситуацию прямо на месте, используя для этого составной оператор SQL. Составным называется такой SQL-оператор, который состоит из более простых операторов SQL, находящихся между ключевыми словами BEGIN (начало) и END (конец).

Ниже приведен пример составного оператора, выполняющего обработку исключительных ситуаций.

BEGIN

DECLARE ValueOutOfRange EXCEPTION FOR SQLSTATE '73003' ;

INSERT INTO FOODS

(Calories)

VALUES

(:cal)

SIGNAL ValueOutOfRange ;

MESSAGE 'Обрабатывается новое значение количества калорий'

EXCEPTION

WHEN ValueOutOfRange THEN

MESSAGE 'Обработка ошибки выхода количества калорий за допустимый диапазон' ;

WHEN OTHERS THEN

RESIGNAL ;

END

С помощью одного или нескольких объявлений можно задать имена для всех возможных значений параметра SQLSTATE. Одним из операторов, которые могут вызвать исключительную ситуацию, является INSERT. Если значение :cal превысит максимальное значение элемента данных типа SMALLINT, параметру SQLSTATE будет присвоено значение '73003'. Сигнал о возникновении исключительной ситуации подает оператор SIGNAL (сигнализировать). Этот оператор очищает область диагностики, а также присваивает полю RETURNED_SQLSTATE этой области значение параметра SQLSTATE. Если исключения не было, выполняется обычная последовательность операторов, роль которой в нашем примере играет MESSAGE 'Обрабатывается новое значение количества калорий1. Если же это исключение все же имело место, то обычная последовательность пропускается и выполняется оператор EXCEPTION.

Как только возникает исключение ValueOutOfRange (значение вне диапазона), выполняется последовательность операторов, представленных в нашем примере одним — MESSAGE 'Обработка ошибки выхода количества калорий за допустимый диапазон'. Когда возникнет какое-либо другое исключение, выполняется оператор RESIGNAL (сигнализировать повторно). Он просто передает управление вызывающей родительской процедуре. В этой процедуре, возможно, есть дополнительный код обработки ошибок, с помощью которого можно обрабатывать исключительные ситуации, не связанные с выходом за пределы диапазона.

Предложение WHENEVER

А зачем, собственно, знать, что оператор SQL не выполнился успешно, если с этим уже ничего не поделаешь? Дело здесь вот в чем. Если произошла ошибка, то нельзя, чтобы приложение выполнялось так, будто ничего не случилось. Нужно иметь возможность узнать об ошибке и затем что-то предпринять, чтобы ее исправить. А если исправить ее невозможно, нужно сообщить об ошибке пользователю и корректно завершить приложение. В SQL для обработки исключительных ситуаций, или, как их еще называют, исключений, имеется такой механизм, как предложение WHENEVER.

Предложение WHENEVER фактически является объявлением, поэтому его помещают в разделе объявлений SQL-приложения перед выполняемым кодом SQL. У этого предложения такой синтаксис:

WHENEVER состояние действие ;

Состояниями могут быть SQLERROR (ошибка SQL) или NOT FOUND (не найден). А действием — CONTINUE (продолжать) или GOTO адрес (перейти по адресу). Если код класса в параметре SQLSTATE не равен 00, 01 или 02, то возникает состояние SQLERROR. А если параметр SQLSTATE равен 02000, то возникает состояние NOT FOUND.

Если действием является CONTINUE, то выполнение кода происходит по обычному сценарию. Но когда вместо CONTINUE задано GOTO адрес (или GO TO адрес), точка выполнения перемещается по указанному адресу в программе. Адресом перехода может быть и условное выражение, которое проверяет значение параметра SQLSTATE и, в зависимости от результатов проверки, переводит выполнение по требуемому адресу. Вот несколько примеров предложения WHENEVER:

WHENEVER SQLERROR GO TO error_trap ;

ИЛИ

WHENEVER NOT FOUND CONTINUE ;

GO TO — это макрокоманда. Реализация, точнее препроцессор встроенного языка, вставляет после каждого оператора EXEC SQL следующую проверку:

IF SQLSTATE <> '00000'

AND SQLSTATE <> '00001'

AND SQLSTATE <> '00002'

THEN GOTO error_trap;

Опция CONTINUE означает отсутствие действий, т.е. действие "игнорируй ошибку".

Пример нарушения ограничения

Из всей информации, предоставляемой оператором GET DIAGNOSTICS, самой важной является информация о нарушении ограничения. Рассмотрим пример. Создана таблица EMPLOYEE (сотрудник) со столбцами ID (идентификатор) и Salary (зарплата):

CREATE TABLE EMPLOYEE

(ID CHAR(5) CONSTRAINT EmpPK PRIMARY KEY,

Salary DEC(8,2) CONSTRAINT EmpSal CHECK Salary > 0,

Dept CHAR(5) CONSTAINT EmpDept

REFERENCES DEPARTMENT) ;

Кроме того, имеется таблица DEPARTMENT (отдел) со столбцами DeptNo (номер отдела) и Budget (бюджет):

CREATE TABLE DEPARTMENT

(DeptNo CHAR(5),

Budget DEC(12,2) CONSTRAINT DeptBudget

CHECK(Budget >= SELECT SUM(Salary) FROM EMPLOYEE

WHERE EMPLOYEE.Dept=DEPARTMENT.DeptNo),

...) ;

А теперь посмотрите на следующий оператор INSERT:

INSERT INTO EMPLOYEE VALUES(:ID_VAR, :SAL__VAR, :DEPT_VAR);

Предположим, что вы получили значение SQLSTATE, равное '23000'. Посмотрев в документацию по SQL, вы видите, что этому значению соответствует описание "нарушение ограничения целостности". Это означает, что имеет место одна из следующих ситуаций.

Значение IDJVAR повторяет уже существующее значение ID, т.е. нарушено ограничение PRIMARY KEY.

Значение SALJVAR отрицательное — иначе говоря, нарушено ограничение CHECK на столбце Salary.

Значение DEPT_VAR не является правильным ключом, соответствующим какой-либо из строк таблицы DEPARTMENT, так что нарушено ограничение REFERENCES в столбце Dept.

Значение SAL_VAR настолько большое, что у сотрудников, работающих в отделе, для которого вводятся новые данные, сумма окладов превышает значение Budget для этого отдела. На этот раз имеется нарушение ограничения CHECK в столбце Budget таблицы DEPARTMENT. (Вспомните, что при изменении базы данных должны проверяться все связанные с ним ограничения, а не только те, которые определены в изменяемых таблицах.)

Обычно, чтобы узнать причины невыполнения оператора INSERT, приходится проводить большое количество тестов. Но на этот раз все, что нужно, можно узнать с помощью команды GET DIAGNOSTICS:

DECLARE ConstNameVar CHAR(18) ;

GET DIAGNOSTICS EXCEPTION 1

ConstNameVar = CONSTRAINT_NAME ;

И если значение SQLSTATE равно '23000', то этот оператор GET DIAGNOSTICS присваивает переменной ConstNameVar одно из следующих значений: 'EmpPK', 'EmpSal', 'EmpDept' или 'DeptBudget'. Обратите внимание, для того, чтобы однозначно определить ограничение CONSTRAINT_NAME, еще могут понадобиться значения элементов CONSTRAINT_SCHEMA и CONSTRAINT_CATALOG.

SQLSTATE

Параметр SQLSTATE позволяет определить множество нештатных ситуаций. Он представляет собой строку из пяти символов, в которой могут находиться буквы в верхнем регистре от А до Z и цифры от 0 до 9. Эта строка делится на две группы — двухсимвольный код класса и трехсимвольный код подкласса.

В стандарте SQL:2003 определены все коды класса, начинающиеся с букв от А до Н или с цифр от 0 до 4. Поэтому любой такой код во всех реализациях означает одно и то же. Что касается кодов классов, которые начинаются с букв от I до Z или с цифр от 5 до 9, то их значение определяется конкретными реализациями СУБД. Дело в том, что спецификация SQL не может предусмотреть все ситуации, которые могут произойти в каждой реализации. Впрочем, если говорить откровенно, разработчикам нужно пореже использовать эти нестандартные коды классов. Нестандартные коды усложняют переход с одной СУБД на другую. Лучше, конечно, вообще обойтись стандартными кодами, а нестандартные использовать только в виде исключения.

Если в параметре SQLSTATE код класса равен 00, оператор завершился успешно. А код класса, равный 01, означает уже другое: хотя оператор и завершился успешно, но вывел предупреждение. Если нет данных, значение этого кода становится равным 02. Любое другое значение кода класса, находящееся в параметре SQLSTATE, означает, что выполнение опера-гора не было успешным.

Так как после каждой операции SQL параметр SQLSTATE обновляется, проверять его можно после выполнения каждого оператора. Если значение параметра SQLSTATE — 00000 (успешное завершение), то можно приступить к выполнению следующей запланированной операции. Ну а если в нем находится что-то другое, то для обработки ситуации, возможно, придется отклониться от той обычной последовательности выполнения кода. Какое из нескольких возможных действий следует выполнить — зависит от находящихся в параметре SQLSTATE значений кодов класса и подкласса.

Чтобы параметр состояния SQLSTATE можно было использовать в программе, написанной на модульном языке (о таких программах см. в главе 15), ссылку на этот параметр надо поместить в определении процедуры. Как это делается, показано в следующем примере с процедурой NUTRIENT (питательность), которая работает с таблицей FOODS (продукты питания), имеющей столбцы Foodname (название продукта), Calories (калории), Protein (белки), Fat (жиры) и Carbohidrate (углеводы):

PROCEDURE NUTRIENT

(SQLSTATE, :foodname CHAR (20), :calories SMALLINT,

             :rprotein DECIMAL (5,1), :fat DECIMAL (5,1),

            :carbo DECIMAL (5,1))

INSERT INTO FOODS

    (Foodname, Calories, Protein, Fat, Carbohidrate)

    VALUES

    (:foodname, rcalories, :protein, :fat, :carbo) ;

В нужном месте программы, написанной на процедурном языке, вы можете присвоить параметрам определенные значения (возможно, используя пользовательский ввод), а затем вызвать саму процедуру. Синтаксис этой операции в разных языках разный и выглядит примерно так:

foodname = "Okra, boiled" ;

calories = 29 ;

protein = 2.0 ;

fat = 0.3 ;

carbo = 6.0 ,-

NUTRIENT(state, foodname, calories, protein, fat, carbo) ;

Значение SQLSTATE возвращается переменной state (состояние). Ваша программа может проверять эту переменную и, в зависимости от ее значения, выполнять то или иное действие. (Для тех, кто не знает, окра — это такое растение, которое еще называется бамией. В пищу идут недозрелые стручки, которые варят, и созревшие семена, из которых делают суррогат кофе. — Примеч. пер.)

Область заголовка диагностики

Таблица 20.1. Область заголовка диагностики

Поля	Тип данных
number (количество)	Точный числовой, масштаб О
row_count (количество строк)	Точный числовой, масштаб О
command_functton (командная функция)	Символьная строка переменного размера, не менее 128 символов
COMMAND_FUNCTION_CODE (КОД КОМЭНДНОЙ фуНКЦИИ)	Точный числовой, масштаб О
more (больше)	Символьная строка, длина 1
transact ions_commi ted (фиксированные транзакции)	Точный числовой, масштаб О
transactions_rolled_back (откатанные транзакции)	Точный числовой, масштаб О
transactions_active (состояние транзакции)	Точный числовой, масштаб О

Ниже эти элементы описаны более детально.

В поле NUMBER хранится количество областей, заполненных диагностической информацией о текущей операции.

В поле ROW_COUNT содержится количество строк, задействованных при выполнении оператора SQL INSERT, UPDATE или DELETE.

Поле COMMAND_FUNCTION описывает только что выполненный динамический оператор SQL (если последний выполненный оператор SQL действительно был динамическим).

Поле COMMAND_FUNCTION_CODE отображает номер кода для только что выполненного динамического оператора SQL (если последний выполненный оператор SQL действительно был динамическим). Каждая динамическая функция имеет соответствующий код.

Поле MORE содержит одно из значений: 'Y' (да) или 'N' (нет). Значение 'Y' указывает на то, что записей состояния больше, чем может вместить область диагностики. Значение 'N' означает, что все сгенерированные записи состояния представлены в области диагностики. Вы можете получить необходимое количество записей, увеличив его, используя оператор SET TRANSACTIONS, однако эта возможность зависит от используемой вами реализации.

Поле TRANSACTIONS_COMMITED содержит количество совершенных транзакций.

Поле TRANSACTIONS_ROLLED_BACK содержит количество транзакций, которые были откатаны.

Поле TRANSACTIONS_ACTIVE содержит значение 'Г, если транзакция в настоящее время активна, и значение '0' в противном случае. Транзакция считается допустимой для активизации, если открыт курсор или СУБД находится в ожидании отложенного параметра.

Информационная область диагностики

В информационных областях хранятся данные по каждой отдельной ошибке, предупреждению или состоянию успешного завершения. Каждая информационная область (табл. 20.2) состоит из 26 элементов.

Информационная область диагностики

Таблица 20.2. Информационная область диагностики

Элемент	Тип данных
CONDITION_NUMBER (номер состояния)	Точный числовой, масштаб 0
RETURNED_SQLSTATE (значение SQLSTATE)	Символьная строка, 6 символов
MESSAGE_TEXT (текст сообщения)	Символьная строка переменного размера, не менее 128 символов
MESSAGE_LENGTH (длина сообщения)	Точный числовой, масштаб 0
MESSAGE OCTET LENGTH (длина сообщения в октетах)	Точный числовой, масштаб 0
CLASS_ORIGIN (источник класса)	Символьная строка переменного размера, не менее 128 символов
SUBCLASS_ORIGIN (источник подкласса)	Символьная строка переменного размера, не менее 128 символов
CONNECTION_NAME (имя соединения)	Символьная строка переменного размера, не менее 128 символов
SERVER_NAME (имя сервера)	Символьная строка переменного размера, не менее 128 символов
CONTRAINT_CATALOG (каталог ограничения)	Символьная строка переменного размера, не менее 128 символов
CONTRAINT_SCHEMA (схема ограничения)	Символьная строка переменного размера, не менее 128 символов
CONSTRAINT_NAME (имя ограничения)	Символьная строка переменного размера, не менее 128 символов
ENVIRONMENT_NAME (имя среды)	Символьная строка переменного размера, не менее 128 символов
CATALOG_NAME (имя каталога)	Символьная строка переменного размера, не менее 128 символов
SCHEMA_NAME (имя схемы)	Символьная строка переменного размера, не менее 128 символов
TABLE_NAME (имя таблицы)	Символьная строка переменного размера, не менее 128 символов
COLUMN_NAME (имя столбца)	Символьная строка переменного размера, не менее 128 символов
CURSOR_NAME (имя курсора)	Символьная строка переменного размера, не менее 128 символов
CONDITION_IDENTIFIER (идентификатор состояния)	Символьная строка переменного размера, не менее 128 символов
PARAMETR_NAME (имя параметра)	Символьная строка переменного размера, не менее 128 символов
ROUTINE_CATALOG (каталог программы)	Символьная строка переменного размера, не менее 128 символов
ROUTINE_SCHEMA (схема программы)	Символьная строка переменного размера, не менее 128 символов
ROUTINE_NAME (имя программы)	Символьная строка переменного размера, не менее 128 символов
SPECIFIC_NAME (специфическое имя)	Символьная строка переменного размера, не менее 128 символов
TRIGGER_CATALOG (каталог триггера)	Символьная строка переменного размера, не менее 128 символов
TRIGGER_SCHEMA (схема триггера)	Символьная строка переменного размера, не менее 128 символов
TRIGGER_NAME (имя триггера)	Символьная строка переменного размера, не менее 128 символов

<

В элементе CONDITION_NUMBER содержится порядковый номер информационной области. Если оператор создает пять элементов состояния, которые заполняют пять информационных областей, то значение CONDITION_NUMBER для пятой такой области будет равно пяти. Чтобы получить доступ к конкретной информационной области, используйте оператор GET DIAGNOSTICS (получить диагностику) вместе с требуемым значением CONDITTONJSrUMBER. (Об операторе GET DIAGNOSTICS рассказывается ниже, в разделе "Что означает информация, возвращаемая SQLSTATE".) А в элементе RETURNED_SQLSTATE находится значение SQLSTATE, соответствующее данным этой информационной области.
Элемент CLASS_ORIGIN сообщает, откуда взято значение для кода класса, возвращаемое в параметре SQLSTATE. Если значение определено стандартом SQL, то элемент CLASS_ORIGIN равен TSO 9075'. А если оно определено реализацией СУБД, то в элементе CLASS_ORIGIN находится строка, в которой указана СУБД-источник. Элемент SUBCLASS_ORIGIN, в свою очередь, сообщает источник значения для кода подкласса, которое возвращено в параметре SQLSTATE.
Значение, находящееся в элементе CLASS_ORIGIN, является достаточно важным. Значение SQLSTATE, равное, например, '22012', относится к стандартным значениям этого параметра. Поэтому вам известно, что оно означает одно и то же во всех реализациях SQL. Однако если значение SQLSTATE равно '22500', первые два символа находятся в стандартном диапазоне и указывают на исключительную ситуацию, связанную с отсутствием данных, а последние три символа уже находятся в диапазоне, определяемом реализацией. Ну а если значение SQLSTATE равно '90001', то это значение полностью находится в диапазоне, определяемом реализацией. Одни и те же значения SQLSTATE, находящиеся в таком диапазоне, могут в разных реализациях означать совершенно различные понятия.
А где же найти описание '22500' или '90001' Для этого надо взглянуть в документацию пользователя СУБД. А какой именно СУБД? Ведь с помощью оператора CONNECT можно соединиться сразу с несколькими. Чтобы узнать, какой из них является источником ошибки, взгляните на элементы CLASS_ORIGIN и SUBCLASS_ORIGIN. В них находятся значения, которые определяют каждое приложение. Проверяя эти значения, можно определить, к какой СУБД относятся значения SQLSTATE. Значения, находящиеся в элементах CLASS_ORIGIN и SUBCLASS_ORIGIN, также определяются реализацией, но обычно содержат название компании-разработчика СУБД.
Если ошибка является нарушением ограничения, это ограничение можно определить с помощью элементов CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA и CONSTRAINT_NAME.

было бы замечательно, чтобы каждое

В этой главе...

Подача сигнала об ошибке
Переход к коду обработки ошибок
Ограничение, вызвавшее ошибку
Ошибка какой СУБД произошла
Правда, было бы замечательно, чтобы каждое написанное вами приложение все время работало прекрасно? Еще бы! А если еще и выиграть 57 миллионов долларов в лотерею, что проводится в штате Орегон, то вообще было бы все круто! К сожалению, вероятность первого события не превышает вероятности второго. Те или иные состояния ошибки случаются неизбежно, поэтому полезно знать их причины. В SQL:2003 механизмом, передающим информацию об ошибке, является параметр состояния (или переменная базового языка) SQLSTATE (состояние SQL). С помощью этой информации можно выполнить те или иные действия, которые помогают исправить ошибку.
Скажем, предложение WHENEVER (как только) дает возможность выполнять заранее заготовленное действие, как только возникает некоторая ситуация, например, когда у параметра SQLSTATE появляется ненулевое значение. Кроме того, подробную информацию о состоянии только что выполненного оператора SQL можно найти в области диагностики. В данной главе рассказывается об этих полезных средствах и о том, как их использовать.

Ввод новых ограничений в уже созданную таблицу

Особенно полезен оператор GET DIAGNOSTICS для определения нарушаемых ограничений при изменении исходных таблиц. Например, в таблицу с помощью оператора ALTER TABLE вводят ограничения, которых не было при написании программы:

ALTER TABLE EMPLOYEE

ADD CONSTRAINT SalLimit CHECK(Salary < 200000) ;

Теперь, когда вы вставите данные в таблицу EMPLOYEE или обновите в ней столбец Salary и значение в этом столбце превысит 200000, значение SQLSTATE станет равно '23000'. В таких случаях можно запрограммировать вывод каких-либо полезных сообщений. Например, такого рода: "Неправильное выполнение оператора INSERT: нарушение ограничения SalLimit".