The following table shows the special system functions supported in NexusDB SQL.

 

Syntax

 

Data type

 

Notes

 

USER

VARCHAR

Returns the user name associated with the current session.

CURRENT_USER

VARCHAR

Equivalent to USER.

SESSION_USER

VARCHAR

Equivalent to USER.

SYSTEM_ROW#

DWORD

The function can only be defined as the first select column in query specifications, and will assign a sequential (zero-based) row-id to the rows in the result set. Cannot be used inside other functions.

NEWGUID

GUID

Returns a generated GUID value.

ROWSREAD

 

Returns the number of rows in the result set of a SELECT statement executed prior to calling ROWSREAD.

ROWSAFFECTED

 

Returns the number of rows affected by a data-change statement executed prior to calling ROWSAFFECTED.

ERROR_MESSAGE

VARCHAR

Returns the last error message from the system. The function can only be used in the CATCH clause of the TRY statement.

 

 

Examples

 

1)

The following example selects all appointments made by the current user:



 


SELECT *


FROM orders


WHERE takenBy = CURRENT_USER



 

2)

The following example retrieves a unique row-id and the customer name:



 


SELECT SYSTEM_ROW# AS row_id, customer_name


FROM customers



 

3)

The following example uses the NEWGUID function to assign a value to the documentID column:



 


INSERT INTO documents ( documentID, title )


VALUES ( NEWGUID, 'NexusDB features' )



 

4)

The following example shows how ERROR_MESSAGE is used in the TRY statement:



 


START TRANSACTION;


TRY




DROP TABLE garbage;


COMMIT;




CATCH POSITION( 'Unable to open table' IN ERROR_MESSAGE ) <> 0




ROLLBACK;


SIGNAL ERROR_MESSAGE;




END;



 

 


 

Conformance

 

SQL:2003 standard

 

NexusDB extensions

-

 

-

-

-

-

-

Core SQL

 

SYSTEM_ROW#

NEWGUID

ROWSREAD

ROWSAFFECTED

ERROR_MESSAGE