SQL System Information Functions

System Information Functions

Several functions are available that extract session and system information.

Function Description

CURRENT_SCHEMA()

Returns the name of the schema that will be used for any tables or other named objects that are created without specifying a target schema.

SELECT CURRENT_SCHEMA FROM DUAL;

CURRENT_USER()

Returns the name of the user identifier that is applicable for permission checking.

SELECT CURRENT_USER FROM DUAL;

DATABASE()

Returns the name of the database.

SELECT DATABASE() FROM DUAL;

GETCOMMITPROTOCOL()

Returns the commit protocol the transaction is using.

SELECT GETCOMMITPROTOCOL() FROM DUAL;
/* safe */

GETCONNECTIONID()

Returns the ID of the current database connection. Details about database connections are stored in the SYSTEM.CONNECTIONS table (see CONNECTIONS System Table Description).

SELECT GETCONNECTIONID() FROM DUAL;

GETEFFECTIVEPLATFORMVERSION()

Returns the effective version of the database. This is different than the NuoDB product release version. However, these two versions are closely related.

When new features are added to the database, in a new product release version, the effective version of the database is updated. All nodes must run at this same effective version.In a rolling upgrade, the effective version is automatically updated when all nodes in the database are capable of supporting that newer version.

Once the effective version of the database is updated, all nodes will begin operating at this newer version. If a node is not capable of supporting the current effective version of the database (i.e. cannot support all features of the database), then the node will not be allowed to join the domain.

SELECT GETEFFECTIVEPLATFORMVERSION() FROM DUAL;

GETNODEID()

Returns the unique ID of the Transaction Engine (TE) that serves the local connection. Details of system nodes are stored in the SYSTEM.NODES table (see NODES System Table Description).

SELECT GETNODEID() FROM DUAL;

GETPID()

Returns the unique operating system process ID (PID) of the TE that serves the local connection.

SELECT GETPID() FROM DUAL;

GETRELEASEVERSION()

Returns the current NuoDB release version

SELECT GETRELEASEVERSION() FROM DUAL;

GETTRANSACTIONID()

Returns the unique identifier for the current transaction.

SELECT GETTRANSACTIONID() FROM DUAL;

GETUPDATECOUNT()

Returns the number of rows that were affected by the last INSERT, UPDATE, DELETE or REPLACE statement. This function returns the same value that is returned by the JDBC Statement#getUpdateCount() function. Also, this is the value in the UPDATECOUNT column of the SYSTEM.LASTSTATEMENT table (See LASTSTATEMENT System Table Description).

A second select of this function, immediately following the first select, will return zero. When you select from the function that SELECT statement becomes the last statement executed. The UPDATECOUNT column in the SYSTEM.LASTSTATEMENT table equals zero for a SELECT statement.

SELECT GETUPDATECOUNT() FROM DUAL;

IS_MEMBER(role_string)

Indicates if the current user has been granted privileges specified by role_string:

  • Returns 1 if the current user has been granted privileges specified by role-string

  • Returns 0 if the current user has not been granted privileges specified by role-string

  • Returns NULL if the role is not a valid role

SELECT IS_MEMBER('qauser') FROM DUAL;

LAST_INSERT_ID()

Returns the last automatically generated ID for a column declared with GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY. This will return 0 for a new connection. If multiple rows are inserted with one statement, the ID of the first row is returned. The value returned is on a per-connection basis; it is the last ID generated for the client, and not necessarily the last ID inserted into the table.

SELECT LAST_INSERT_ID() FROM DUAL;

OPTIONAL_FIELD(column_name, default_value)

Returns the value defined by the column_name. If the column does not exist in the table being selected from then this will return the value defined by the default_value. This can be useful when doing upgrades to schema. Before new columns are added to a table the application code that references these new columns can be deployed without generating errors.

SELECT OPTIONAL_FIELD(<table_columnname>,'NEW') FROM <table_name>;

ROWNUM()

Returns a number indicating the order in which the row was selected from a table or set of joined rows. ROWNUM() is not supported in the WHERE, HAVING, and ON clauses. See SELECT. For a workaround to use the WHERE clause, see Known Issues.

SELECT ROWNUM() FROM <table_name>;

  • ROWNUM() support in the WHERE clause was added in NuoDB 5.0.2.

  • The LIMIT clause is recommended to select a limited number of records instead of ROWNUM().

SCOPE_IDENTITY()

Returns the last identity values that are generated in any table in the current session, restricted to values within the current scope. The current scope is a stored procedure, trigger, function, or batch. Two statements are in the same scope if they are in the same stored procedure, function, or batch. Whereas LAST_INSERT_ID() will return the last insert identity relative to the table, SCOPE_IDENTITY() will return the last identity relative to the scope. If multiple rows are inserted with one statement (batch), the ID of the last row is returned by SCOPE_IDENTITY().

SELECT SCOPE_IDENTITY() FROM DUAL;

USER()

Returns the name of the user identifier that is applicable for permission checking. USER is valid in any expression.

SELECT USER() FROM DUAL;

Examples

Example showing CURRENT_USER versus USER()

There is a difference between the USER() function and the pseudo-column CURRENT_USER. CURRENT_USER only has a predefined meaning when it’s selected from DUAL, but USER() is valid in any expression:

# USER() versus CURRENT_USER()
SELECT current_user FROM dual;
 [CURRENT_USER]
 ---------------
       DBA

SELECT user() FROM dual;
 [USER]
 -------
   DBA

# Work log example
CREATE TABLE work_log (tablename STRING, columnname STRING, change_desc STRING);
INSERT INTO work_log VALUES ('HOCKEY','NUMBER','Changed from integer to string');

SELECT user() AS changed_by, tablename, columnname, change_desc FROM work_log;
 CHANGED_BY  TABLENAME  COLUMNNAME           CHANGE_DESC
 ----------- ---------- ----------- ------------------------------
     DBA       HOCKEY     NUMBER    Changed from integer to string

SELECT current_user AS changed_by,tablename,columnname,change_desc FROM work_log;
 CHANGED_BY  TABLENAME  COLUMNNAME           CHANGE_DESC
 ----------- ---------- ----------- ------------------------------
     DBA       HOCKEY     NUMBER    Changed from integer to string

Example for IS_MEMBER

CREATE ROLE qauser;
SELECT IS_MEMBER('qauser') FROM dual;

[IS_MEMBER]
------------
     0

GRANT qauser TO dba;
SELECT IS_MEMBER('qauser') FROM dual;

[IS_MEMBER]
------------
     1

Example for LAST_INSERT_ID

# LAST_INSERT_ID Examples
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, name VARCHAR(10));
INSERT INTO t (name) VALUES ('User1');

SELECT * FROM t;

 ID  NAME
--- -----
 1  User1

SELECT LAST_INSERT_ID() FROM dual;

 [LAST_INSERT_ID]
-----------------
        1

# If multiple rows are inserted, returns the id of the first one.
INSERT INTO t (name) VALUES ('User2'), ('User3');

SELECT * FROM t;

 ID  NAME
--- -----
 1  User1
 2  User2
 3  User3

SELECT LAST_INSERT_ID() FROM dual;

 [LAST_INSERT_ID]
 -----------------
        2

Example showing LAST_INSERT_ID versus SCOPE_IDENTITY

CREATE TABLE t1 (id INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, name VARCHAR(10));
CREATE TABLE t2 (id INT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, name VARCHAR(10));
INSERT INTO t1 (name) VALUES ('greg');
INSERT INTO t2 (name) VALUES ('marcia'), ('peter'), ('jan'), ('bobby'), ('cindy');
SELECT LAST_INSERT_ID() FROM dual;

 [LAST_INSERT_ID]
 -----------------
         1

SELECT SCOPE_IDENTITY() FROM dual;

 [SCOPE_IDENTITY]
 -----------------
         5

Example for ROWNUM

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(10,10),(11,11),(12,12);
SELECT a, ROWNUM() FROM t1;

 A  [ROWNUM]
 -- ---------

  1     1
  2     2
  3     3
 10     4
 11     5
 12     6

Example for OPTIONAL_FIELD

CREATE TABLE my_table
(id INTEGER PRIMARY KEY
,description STRING);
INSERT INTO my_table VALUES
    (1,'row-1')
   ,(2,'row-2');
SELECT id,description FROM my_table;
 ID  DESCRIPTION
 --- ------------
  1     row-1
  2     row-2

/* Before schema Upgrade. Column SOURCE does not exist */
/*   For now, we can execute code and provide a default value */
SELECT id,description,OPTIONAL_FIELD(source,'Batch') FROM my_table;
 ID  DESCRIPTION  ['Batch']
 --- ------------ ----------
  1     row-1       Batch
  2     row-2       Batch

/* Schema upgrade */
ALTER TABLE my_table ADD source STRING;

/* Column SOURCE now exists, but has NULL value */
/*    The actual column value is returned */
SELECT id,description,OPTIONAL_FIELD(source,'batch') FROM my_table;
 ID  DESCRIPTION  MY_TABLE.SOURCE
 --- ------------ ----------------
  1     row-1          <null>
  2     row-2          <null>


UPDATE my_table SET source = CASE ID WHEN 1 then 'Batch' ELSE 'Online' END;

SELECT id,description,OPTIONAL_FIELD(source,'batch') FROM my_table;
 ID  DESCRIPTION  MY_TABLE.SOURCE
 --- ------------ ----------------
  1     row-1          Batch
  2     row-2          Online