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 - HOSTCODE

HOSTCODE built-in variable contains the return code of the last OS command.

SET hplsql.onerror = SETERROR;

HOST 'echo hello, world';
IF HOSTCODE <> 0 THEN
  PRINT 'Error';
END IF;

Notes:

• Typically HOSTCODE is 0 when the process terminated successfully, and 1 if it terminated with an error.
• By default hplsql.onerror is set to EXCEPTION, so if the OS command cannot be executed (the process does not exist, no permissions i.e.) the exception is raised and you are not be able to check HOSTCODE in IF statement.

For more information, see Error Handling.

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