You are here: Reference Information > SQL Reference Information > SQL Functions and Operations > SQL System Information Functions

SQL System Information Functions

System Information Functions

Several functions are available that extract session and system information

Function Description Example Query Example Result
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; HOCKEY
CURRENT_USER() Returns the name of the user identifier that is applicable for permission checking. SELECT CURRENT_USER FROM DUAL; DBA
DATABASE() Returns the name of the database. SELECT DATABASE() FROM DUAL; test
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; INTEGER - unique identifier from the SYSTEM.CONNECTIONS table (CONNID), which represents the current database connection.
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; INTEGER - unique identifier that represents the effective version of the database.
GETNODEID() Returns the ID of the transaction engine of the local connection. Details of system nodes are stored in the SYSTEM.NODES table (see NODES System Table Description). SELECT GETNODEID() FROM DUAL; INTEGER - unique identifier from the SYSTEM.NODES table (ID), which represents the transaction engine node of the local connection
GETRELEASEVERSION() Returns the current NuoDB release version SELECT GETRELEASEVERSION() FROM DUAL; STRING
As an example, this is NuoDB Release 2.1:
2.1-2-8f7739a
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; INTEGER
Number of rows effected by the last SQL DML statement. If no rows are effected, it returns zero (0).
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
See Example below.
SELECT IS_MEMBER('qauser') FROM DUAL; INTEGER or NULL
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. See Example below. SELECT LAST_INSERT_ID() FROM DUAL; INTEGER - the last ID that was generated relative to the table
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. See Example below. SELECT OPTIONAL_FIELD(table_columnname,'NEW') FROM tablename; The data type returned is the data type of the column_name, if it exists. Otherwise, the data type returned is the data type of the default_value that is passed to the function.
ROWNUM() Returns a number indicating the order in which the row was selected from a table or set of joined rows. ROWNUM() can be used to limit the number of rows in the result set by specifying SELECT ... WHERE ROWNUM() < n. The first row number is always 1 and the row number is incremented after predicates are evaluated. SELECT...WHERE ROWNUM() > 1 returns no rows, because the first row number is always 1. See SELECT. SELECT ROWNUM() FROM TBL; INTEGER - the number of the row in the result set.
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(). See Example below. SELECT SCOPE_IDENTITY() FROM DUAL; INTEGER - the last ID that was generated relative to the scope
USER() Returns the name of the user identifier that is applicable for permission checking. USER is valid in any expression. See Example below. SELECT USER() FROM DUAL; DBA

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;
 ---
 DBA
 
SELECT user() FROM dual;    
 ---
 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;
 # error:  can't resolve field "CURRENT_USER" #

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 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
 
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 showing LAST_INSERT_ID versus SCOPE_IDENTITY

            CREATE TABLE t1 (id INT GENERATED ALWAYS NOT NULL PRIMARY KEY, name VARCHAR(10));
CREATE TABLE t2 (id INT GENERATED ALWAYS 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 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  SOURCE  
 --- ------------ ------- 
  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  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  SOURCE  
 --- ------------ ------- 
  1     row-1     Batch   
  2     row-2     Online