GET DIAGNOSTICS
NAME
GET DIAGNOSTICS: read the diagnostics area (in a program)
SYNOPSIS
<get diagnostics statement> ::=
GET [ CURRENT | STACKED ] DIAGNOSTICS
{ <statement information> | <condition information> | <all information> }
<statement information> ::=
<parameter name> = <statement information item name>
[ { , <parameter name> = <statement information item name> }... ]
<condition information> ::=
{ CONDITION | EXCEPTION } <condition number>
<parameter name> = <condition information item name>
[ { , <parameter name> = <condition information item name> }... ]
<all information> ::=
<parameter name> = ALL [ STATEMENT | CONDITION [ <condition number> ] ]
<condition number> ::= <parameter name> | <literal>
In a routine's body or a compound statement, a <parameter name> may
name an SQL parameter or an SQL variable. STACKED is read as a word,
not a key word.
DESCRIPTION
GET DIAGNOSTICS copies what the session's last statement left in its diagnostics area into parameters. Out of a compound statement it runs only in a program that embeds turborel, through the Rust API, since its targets are parameters, which direct SQL, and so the client, has none of. The client shows each statement's outcome itself. In a routine's body or a compound statement (BEGIN), its targets are SQL parameters and SQL variables, so it runs wherever they do.
In a program, each statement empties the area and leaves its own condition there. The diagnostics area is a stack. A handler's activation pushes it, so while its action runs, GET STACKED DIAGNOSTICS reads the condition the handler handles, and GET CURRENT DIAGNOSTICS, as GET DIAGNOSTICS does, the area of the action's last statement. The stack is popped as the action ends. STACKED where no handler is active fails with SQLSTATE 0Z002.
The area holds one condition after each statement: its completion, SQLSTATE 00000, or the warning or exception it raised. GET DIAGNOSTICS changes nothing of it, so it may be read again.
The statement information items:
| Item | Type | Value |
|---|---|---|
| NUMBER | exact | The number of conditions: 0 before any statement, else 1 |
| MORE | character | N, or Y if the statement raised a condition the area does not hold |
| COMMAND_FUNCTION | character | The statement, as INSERT, UPDATE WHERE, DELETE WHERE, COMMIT WORK, DROP VIEW; empty for a statement that did not parse |
| COMMAND_FUNCTION_CODE | exact | Its code, as 50 for INSERT; 0 for a statement that did not parse |
| DYNAMIC_FUNCTION | character | Of EXECUTE, the statement prepared; else empty |
| DYNAMIC_FUNCTION_CODE | exact | Its code; else 0 |
| ROW_COUNT | exact | The rows an INSERT, a searched UPDATE or DELETE changed, and those a MERGE read (see MERGE) |
| TRANSACTIONS_COMMITTED | exact | The transactions the statement committed, 0 or 1 |
| TRANSACTIONS_ROLLED_BACK | exact | The transactions the statement rolled back, a COMMIT that failed among them, 0 or 1 |
| TRANSACTION_ACTIVE | exact | 1 if a transaction is active, else 0 |
Each statement's code is the standard's. SELECT has three, 65 for SELECT
... INTO, 21 for a cursor the Rust API reads whole and 41 for a statement
prepared and executed INTO targets. A cursor specification prepared and
opened is SELECT CURSOR, code 85. A positioned UPDATE or DELETE of a
dynamic cursor is coded 42 or 38, and one prepared 55 or 54, though both
are named DYNAMIC UPDATE CURSOR or DYNAMIC DELETE CURSOR. turborel's
own statements have negative codes.
| Statement | Code |
|---|---|
| DECLARE LOCAL TEMPORARY TABLE | -1 |
| SET of a setting | -3 |
| SET DATABASE | -4 |
| CREATE INDEX, ALTER INDEX, DROP INDEX | -5, -6, -7 |
| CREATE USER, ALTER USER, DROP USER | -8, -9, -10 |
| ALTER DATABASE | -11 |
| END CONNECTION, CANCEL CONNECTION | -12, -13 |
| EXPLAIN | -14 |
The condition information items, of condition 1:
| Item | Type | Value |
|---|---|---|
| CONDITION_NUMBER | exact | 1 |
| RETURNED_SQLSTATE | character | The SQLSTATE |
| CLASS_ORIGIN, SUBCLASS_ORIGIN | character | ISO 9075 for the standard's classes; TURBOREL for turborel's own, as TB911 |
| MESSAGE_TEXT | character | The message, as the client shows it, without its position |
| MESSAGE_LENGTH, MESSAGE_OCTET_LENGTH | exact | Its length in characters, and in octets |
| CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME | character | The constraint or assertion broken |
| CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME | character | The table the condition is about, and the column, where it is about one |
| CURSOR_NAME | character | The cursor the condition is about |
| SERVER_NAME, CONNECTION_NAME | character | DEFAULT: a session embedded in a program is on the default connection |
| ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, SPECIFIC_NAME | character | Of SQLSTATE 2F005, or of an argument stored in its parameter, the function or procedure; else empty. A condition of a distinct type's function is one of the CAST it stands for |
| PARAMETER_MODE, PARAMETER_NAME | character | Of an argument stored in its parameter, the parameter's mode and name; else empty |
| PARAMETER_ORDINAL_POSITION | exact | Of an argument stored in its parameter, the parameter's position, from 1; else 0 |
| TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME | character | Of SQLSTATE 09000, the trigger whose action failed; else empty |
| CONDITION_IDENTIFIER | character | Of a condition SIGNAL raised of a condition declared, its name; else empty |
An item that does not apply is empty, or 0. Of SQLSTATE 09000, CATALOG_NAME, SCHEMA_NAME and TABLE_NAME are the trigger's table, and MORE is Y: the condition the action failed with is left out, its SQLSTATE and message within MESSAGE_TEXT. A name of a table the session holds no privilege on is withheld, but for a syntax error or access rule violation (SQLSTATE class 42 or 2A). An assertion's name is withheld from a session that does not own its schema. An assertion broken also names the one table the statement modified of those it reads.
A syntax error or access rule violation names the table that caused
it: a table the statement names that is not defined, or has no column
it names, or that a privilege is refused on, or that a foreign key
references; else the table or view the statement inserts into, updates,
deletes from, creates, alters, drops, indexes or grants or revokes on.
It names the column where a privilege on it is refused, or the table
has no column of the name. A declared local temporary table's
CATALOG_NAME is one space, its SCHEMA_NAME MODULE.
ALL puts every item into one character string, each as NAME = value,
joined by semicolons, an item whose value is empty left out. The
statement items come first, in the order of the first table, then of
each condition its CONDITION_NUMBER and items, in the order of the
grammar. ALL STATEMENT gives the statement items alone, ALL CONDITION the
conditions' alone, and ALL CONDITION with a number that condition's
items alone, without its CONDITION_NUMBER.
Each item is assigned to its target as FETCH assigns a column: a number to a target of any numeric type, a character string to one of any character string type, cut to fit with a warning.
RULES
- Each target is a parameter declared, or an SQL parameter or SQL variable in scope, of a type the item's value may be assigned to: numeric for a number, a character string for a character string.
- ALL's target is of a character string type.
- The condition number is exact with scale 0, and 1 to the number of conditions there are.
PRIVILEGES
None.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2A000 | -102 | An item's name is not one of those above |
| 2A000 | -103 | A target is not a parameter declared, or not of a type its item may be assigned to; ALL's target is not a character string |
| 35000 | -301 | There is no condition of the number |
| 0Z002 | -301 | STACKED where no handler is active |
| 22003 | -301 | A number too large for its target |
| 01004 | 0 | Not an error: a warning, a value cut to fit its target, as FETCH cuts it; the area is left as it was |
EXAMPLES
In a program, of a table STAFF (ID INTEGER PRIMARY KEY, NAME VARCHAR(30), CONSTRAINT PAID CHECK (ID > 0)):
let s = db.session("ANN");
let mut p = Params::new()
.declare("N", DataType::Exact { precision: 9, scale: 0, spelled: Spelled::Numeric }, Value::Null)
.declare("CMD", DataType::varying(128), Value::Null)
.declare("STATE", DataType::character(5), Value::Null)
.declare("NAME", DataType::varying(128), Value::Null)
.declare("REPORT", DataType::varying(1000), Value::Null);
s.run("INSERT INTO STAFF VALUES (1, 'Ada'), (2, 'Grace')")?;
s.execute("GET DIAGNOSTICS N = ROW_COUNT, CMD = COMMAND_FUNCTION", &mut p)?;
// N = 2, CMD = INSERT
s.execute("GET DIAGNOSTICS REPORT = ALL STATEMENT", &mut p)?;
// REPORT = NUMBER = 1;MORE = N;COMMAND_FUNCTION = INSERT;COMMAND_FUNCTION_CODE = 50;DYNAMIC_FUNCTION_CODE = 0;
// ROW_COUNT = 2;TRANSACTIONS_COMMITTED = 0;TRANSACTIONS_ROLLED_BACK = 0;TRANSACTION_ACTIVE = 1
let _refused = s.run("INSERT INTO STAFF VALUES (-1, 'Edsger')");
s.execute("GET DIAGNOSTICS CONDITION 1 STATE = RETURNED_SQLSTATE, NAME = CONSTRAINT_NAME", &mut p)?;
// STATE = 23000, NAME = PAID
s.commit()?;
s.execute("GET DIAGNOSTICS N = TRANSACTIONS_COMMITTED", &mut p)?;
// N = 1
Refused:
GET DIAGNOSTICS X = NUMBER -103 (2A000): parameter X is not declared [X3.135-1986 §7.3 SR3]
GET DIAGNOSTICS N = NOPE -102 (2A000): expected a <statement information item name>, found NOPE
GET DIAGNOSTICS N = MORE -103 (2A000): a CHARACTER VARYING(32767) item cannot be assigned to N (NUMERIC(9,0)) [ISO/IEC 9075-2:2023 §23.1 SR1, §9.1 SR3]
GET DIAGNOSTICS N = ALL -103 (2A000): target N of ALL is NUMERIC(9,0), not a character string [ISO/IEC 9075-2:2023 §23.1 SR2]
GET DIAGNOSTICS CONDITION 5 STATE = RETURNED_SQLSTATE -301 (35000): there is no condition 5: the area holds 1 [ISO/IEC 9075:1992 §18.1 GR2]
In a compound statement run directly, a handler reads the condition it handles with STACKED, and the row count of its own UPDATE with CURRENT:
CREATE TABLE SEEN (STATE CHAR(5), CHANGED INTEGER);
CREATE TABLE DUES (ID INTEGER, AMOUNT INTEGER);
INSERT INTO DUES VALUES (1, 10), (2, 20);
BEGIN
DECLARE S CHAR(5);
DECLARE N INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET STACKED DIAGNOSTICS CONDITION 1 S = RETURNED_SQLSTATE;
UPDATE DUES SET AMOUNT = 0;
GET CURRENT DIAGNOSTICS N = ROW_COUNT;
INSERT INTO SEEN VALUES (S, N);
END;
UPDATE DUES SET AMOUNT = AMOUNT / 0;
END;
SELECT * FROM SEEN;
BEGIN DECLARE N INTEGER; GET STACKED DIAGNOSTICS N = NUMBER; END;
OK
OK
OK
OK
STATE | CHANGED
-------+--------------
22012 | 2
(1 row)
turborel: SQLCODE -301 (SQLSTATE 0Z002): GET STACKED DIAGNOSTICS where no handler is active [ISO/IEC 9075-2:2023 §23.1 GR1b]
STANDARD
ISO/IEC 9075:1992 §18.1, of the diagnostics area of §4.19. SQL-92 does
not make it a <directly executable statement> [§20.1]. SQL-86 has no
diagnostics area. ISO/IEC 9075-2:2023 §23.1 adds CONDITION in
EXCEPTION's place, the codes and the items of routines and triggers,
targets of another type (Feature F122), ALL (Feature F123) and the
transaction counts (Feature T511). ISO/IEC 9075-4:2006 draft §17.1 adds
CURRENT and STACKED (Feature P007), CONDITION_IDENTIFIER, and SQL
variables as targets.