System Variables

Advantage SQL Engine

  Previous topic Next topic  

The Advantage Query Engine maintains two categories of system variables. The first category of system variable returns information about the current user connection or information about the current query handle. These variables are prefixed with the :: (double colons) followed by either "conn." or "stmt.", short hand for connection or statement (query handle) respectively. The second category of system variables are maintained by the SQL Script Execution Engine for the purpose of error handling. These variables are denoted by the double underscore-characters, "__", leading the variable name.

 

 

Server Level Variables

 

Variable Name

Description

::server.OldestActiveTxn

Timestamp. Returns the starting time of the oldest active transaction on the server. NULL is returned if there is no transaction active on the server.

 

 

Connection Level Variables

 

Variable Name

Description

::conn.Name

Character string. Returns the name of the current user connection. The connection name is unique for each connection and it is used by the SQL debugger.

::conn.Collation

Character String. Default collation for statements allocated on the current connection. This variable is settable. See SET <system variable>.

::conn.TransactionCount

Integer. Returns the nesting depth of the current transaction or 0 if one has not been started.

::conn.OperationCount

Integer. Returns the number of operations performed by the server for this connection.

 

 

Statement Level Variables

 

Variable Name

Description

::stmt.Name

Character string. Returns the name of the current query handle. The statement name is unique for each query handle and it stays unchanged as long as the query handle is valid regardless the number of individual statements executed on the query handle. This variable is used by the SQL debugger.

::stmt.UpdateCount

Returns the number of rows affected since the beginning of the current execution.

::stmt.TrigRecNo

Integer. Returns the record number of the row that led to the firing of the trigger. This variable is only meaningful when accessed inside a trigger script.

::stmt.TrigRowid

Char(18). Returns the ROWID of the row that led to the firing of the trigger. This variable is only meaningful when accessed inside a trigger script. ROWID is similar to record number but it is more useful in SQL statements and scripts.

::stmt.TrigName

Character String. Returns the name of the trigger object being fired. This variable is only meaningful when accessed inside of a trigger script.

::stmt.TrigTableName

Character String. Returns the name of the table object that is being updated and caused the trigger to fire. This variable is only meaningful when accessed inside of a trigger script.

::stmt.TrigEventType

Integer. Returns the type of event that led to the firing of the trigger. This variable is only meaningful when accessed inside of a trigger script. See CREATE TRIGGER.

::stmt.TrigType

Integer. Returns the type of trigger being fired. This variable is only meaningful when accessed inside of a trigger script. See CREATE TRIGGER.

::stmt.Collation

Character String. Collation language for sorting and comparing character data when executing SQL statements on this statement handle. This variable is settable. See SET <system variable>.

 

 

Script Error Handling Variables

The variables in this category are __errclass (String), __errcode (Integer), and __errtext (String). These three variables provide information about an exception in the script, either a raised exception or a runtime error. They are local to the current executing SQL script. They cannot be assigned values directly using assignment statements. Instead, their values are initialized when a RAISE statement is executed or when a runtime error is detected. If the exception originates from a RAISE statement, these three variables will be assigned the values provided by the RAISE statement. If the exception is caused by a runtime error, the __errclass variable will be initialized with string value "ADS_SCRIPT_EXCEPTION" and the __errcode and __errtext variables will contain the error code and error text that will be returned to the caller if the exception is not handled.