ASA SQL Reference
SQL Language Elements
Variables
Global variables have values set by the database server. For example, the global variable @@version has a value that is the current version number of the database server.
Global variables are distinguished from local and connection-level variables by having two @ signs preceding their names. For example, @@error and @@rowcount are global variables. Users cannot create global variables, and cannot update the values of global variables directly.
Some global variables, such as @@identity, hold connection-specific information, and so have connection-specific values. Other variables, such as @@connections, have values that are common to all connections.
The special constants (for example, CURRENT DATE, CURRENT TIME, USER, and SQLSTATE) are similar to global variables.
The following statement retrieves a value of the version global variable.
SELECT @@version
In procedures and triggers, global variables can be selected into a variable list. The following procedure returns the server version number in the ver parameter.
CREATE PROCEDURE VersionProc (OUT ver
VARCHAR(100))
BEGIN
SELECT @@version
INTO ver;
ENDIn Embedded SQL, global variables can be selected into a host variable list.
The following table lists the global variables available in Adaptive Server Anywhere
| Variable name | Meaning |
|---|---|
@@dbts |
A value of type TIMESTAMP representing the last generated value used for all columns defined with DEFAULT TIMESTAMP. |
@@error |
Commonly used to check the error status (succeeded or failed) of the most recently executed statement. It contains 0 if the previous transaction succeeded; otherwise, it contains the last error number generated by the system. A statement such as if @@error != 0 return causes an exit if an error occurs. Every SQL statement resets @@error, so the status check must immediately follow the statement whose success is in question. |
@@fetch_status |
Contains status information resulting from the last fetch statement. @@fetch_status may contain the following values
|
@@identity |
Last value inserted into any IDENTITY or DEFAULT AUTOINCREMENT column by an INSERT or SELECT INTO statement.
For a description, see @@identity global variable. |
@@isolation |
Current isolation level. @@isolation takes the value of the active level. |
@@procid |
Stored procedure ID of the currently executing procedure. |
@@rowcount |
Number of rows affected by the last statement. The value of @@rowcount should be checked immediately after the statement.
Inserts, updates, and deletes set @@rowcount to the number of rows affected. With cursors, @@rowcount represents the cumulative number of rows returned from the cursor result set to the client, up to the last fetch request. Unlike in Adaptive Server Enterprise, @@rowcount is not reset to zero by any statement which does not affect rows, such as an IF statement. |
@@servername |
Name of the current database server. |
@@sqlstatus |
Contains status information resulting from the last fetch statement. @@sqlstatus may contain the following values
|
@@version |
Version number of the current version of Adaptive Server Anywhere. |
The following list includes all Adaptive Server Enterprise global variables supported in Adaptive Server Anywhere. Adaptive Server Enterprise global variables not supported by Adaptive Server Anywhere are not included in the list. In contrast to the above table, this list includes all global variables that return a value, including those for which the value is fixed at NULL, 1, -1, or 0, and may not be meaningful.
| Global variable | Returns |
|---|---|
| @@char_convert | Returns 0. |
| @@client_csname | In Adaptive Server Enterprise, the client's character set name. Set to NULL if client character set has never been initialized; otherwise, it contains the name of the most recently used character set. Returns NULL in Adaptive Server Anywhere. |
| @@client_csid | In Adaptive Server Enterprise, the client's character set ID. Set to -1 if client character set has never been initialized; otherwise, it contains the most recently used client character set ID from syscharsets. Returns -1 in Adaptive Server Anywhere. |
| @@connections | The number of logins since the server was last started |
| @@cpu_busy | In Adaptive Server Enterprise, the amount of time, in ticks, that the CPU has spent doing Adaptive Server Enterprise work since the last time Adaptive Server Enterprise was started. In Adaptive Server Anywhere, returns 0. |
| @@error |
Commonly used to check the error status (succeeded or failed) of the most recently executed statement. It contains 0 if the previous transaction succeeded; otherwise, it contains the last error number generated by the system. A statement such as
if @@error != 0 returncauses an exit if an error occurs. Every statement resets @@error, including PRINT statements or IF tests, so the status check must immediately follow the statement whose success is in question. |
| @@identity |
Last value inserted into an IDENTITY column by an INSERT or SELECT INTO statement.
For a description, see @@identity global variable. |
| @@idle | In Adaptive Server Enterprise, the amount of time, in ticks, that Adaptive Server Enterprise has been idle since it was last started. In Adaptive Server Anywhere, returns 0. |
| @@io_busy | In Adaptive Server Enterprise, the amount of time, in ticks, that Adaptive Server Enterprise has spent doing input and output operations since it was last started. In Adaptive Server Anywhere, returns 0. |
| @@isolation | Current isolation level of the connection. In Adaptive Server Enterprise, @@isolation takes the value of the active level |
| @@langid | Returns a unique language ID for the language in use by the current connection. |
| @@language | Returns the name of the language in use by the connection. |
| @@maxcharlen | In Adaptive Server Enterprise, maximum length, in bytes, of a character in Adaptive Server Enterprise's default character set. In Adaptive Server Anywhere, returns 1. |
| @@max_ connections |
For the personal server, the maximum number of simultaneous connections that can be made to the server, which is 10.
For the network server, the maximum number of active clients (not database connections, as each client can support multiple connections). For Adaptive Server Enterprise, the maximum number of connections to the server. |
| @@ncharsize | In Adaptive Server Enterprise, average length, in bytes, of a national character. In Adaptive Server Anywhere, returns 1. |
| @@nestlevel | In Adaptive Server Enterprise, nesting level of current execution (initially 0). Each time a stored procedure or trigger calls another stored procedure or trigger, the nesting level is incremented. In Adaptive Server Anywhere, returns -1. |
| @@pack_received | In Adaptive Server Enterprise, number of input packets read by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0. |
| @@pack_sent | In Adaptive Server Enterprise, number of output packets written by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0. |
| @@packet_errors | In Adaptive Server Enterprise, number of errors that have occurred while Adaptive Server Enterprise was sending and receiving packets. In Adaptive Server Anywhere, returns 0. |
| @@procid | Stored procedure ID of the currently executing procedure. |
| @@rowcount | Number of rows affected by the last command. In Adaptive Server Enterprise @@rowcount is set to zero by any command which does not return rows, such as an IF statement; in Adaptive Server Anywhere, such statements to not reset @@rowcount. With cursors, @@rowcount represents the cumulative number of rows returned from the cursor result set to the client, up to the last fetch request. |
| @@servername | Name of the local Adaptive Server Enterprise or Adaptive Server Anywhere server. |
| @@spid | In Adaptive Server Enterprise, server process ID number of the current process. In Adaptive Server Anywhere, the connection handle for the current connection. This is the same value as that displayed by the sa_conn_info procedure. |
| @@sqlstatus |
Contains status information resulting from the last fetch statement. @@sqlstatus may contain the following values
|
| @@textsize | Current value of the SET TEXTSIZE option, which specifies the maximum length, in bytes, of text or image data to be returned with a select statement. The default setting is 32765, which is the largest bytestring that can be returned using READTEXT. The value can be set using the SET statement. |
| @@thresh_hysteresis | In Adaptive Server Enterprise, change in free space required to activate a threshold. In Adaptive Server Anywhere, returns 0. |
| @@timeticks | In Adaptive Server Enterprise, number of microseconds per tick. The amount of time per tick is machine-dependent. In Adaptive Server Anywhere, returns 0. |
| @@total_errors | In Adaptive Server Enterprise, number of errors that have occurred while Adaptive Server Enterprise was reading or writing. In Adaptive Server Anywhere, returns 0. |
| @@total_read | In Adaptive Server Enterprise, number of disk reads by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0. |
| @@total_write | In Adaptive Server Enterprise, number of disk writes by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0. |
| @@tranchained | Current transaction mode of the Transact-SQL program. @@tranchained returns 0 for unchained or 1 for chained. |
| @@trancount | Nesting level of transactions. Each BEGIN TRANSACTION in a batch increments the transaction count. |
| @@transtate | In Adaptive Server Enterprise, current state of a transaction after a statement executes. In Adaptive Server Anywhere, returns -1. |
| @@version | Information on the current version of Adaptive Server Enterprise or Adaptive Server Anywhere. |