System Functions

Wed, 03/04/2009 - 20:02 |  hdanzl

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 V3 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
‹ Current Timestamp FunctionupPredicates ›
Print this chapter |  Login or register to post comments |  Send to friend |  PDF version |  metadata Tags:
Home | Site Contents | Documentation | NexusDB Manual V4 | SQL Reference | SQL Functions

Powered by Drupal and Drupal Theme created with Artisteer.