Apache Hive : HPL/SQL - ACTIVITY_COUNT Variable

ACTIVITY_COUNT built-in variable contains the number of rows affected by the last SQL statement.

Important Note: Currently Hive does not support JDBC Statement.getUpdateCount(), so for INSERT statements ACTIVITY_COUNT will return 0 for Hive 0.13 and earlier and -1 for Hive 0.14 and later. See HIVE-7680 for more details.

Currently you can use ACTIVITY_COUNT only with SELECT statements in Hive. If SELECT INTO returns a row ACTIVITY_COUNT is set to 1. Also if you use a cursor each FETCH statement increments ACTIVITY_COUNT by 1.

Apache Hive : HPL/SQL - SQLCODE

SQLCODE built-in variable contains the return code of the last SQL statement. The code can be zero (0), negative or positive:

Note:

• SQLCODE 100 means row not found or end of cursor

DECLARE id INT;
DECLARE cur CURSOR FOR 'SELECT id FROM db.orders';
OPEN cur;
FETCH cur INTO id;
WHILE SQLCODE = 0 THEN
  FETCH cur INTO id;
END WHILE;
CLOSE cur;

Compatibility: IBM DB2.

Apache Hive : HPL/SQL - SQLSTATE

SQLSTATE built-in variable contains a 5-character return status of the last SQL statement.

SQLSTATE status code consists of a 2-character class code followed by a 3-character subclass code. Class code 00 (SQLSTATE ‘00000’ i.e.) means the successful completion.

Example:

SET hplsql.onerror = seterror; -- Prevent raising an exception

SELECT 1 FROM abc.abc;
PRINT SQLSTATE;

Compatibility: IBM DB2.

See also:

• Error Handling
• SQLCODE
• GET DIAGNOSTICS
• RESIGNAL