Connection-level variables  Comments

Chapter 3: SQL Language Elements

Global variables

Global variables have values set by Sybase IQ. 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 two @ signs preceding their names. For example, @@error is a global variable. Users cannot create global variables, and cannot update the value of global variables directly.

Some global variables, such as @@spid, hold connection-specific information and therefore have connection-specific values. Other variables, such as @@connections, have values that are common to all connections.

Global variable and special constants

The special constants such as CURRENT DATE, CURRENT TIME, USER, SQLSTATE, and so on are similar to global variables.

The following statement retrieves the value of the version global variable:

SELECT @@version

In procedures, 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;
END

In Embedded SQL, global variables can be selected into a host variable list.

List of global variables

Table 3-5 lists the global variables available in Sybase IQ.

Table 3-5: Sybase IQ global variables

Variable name

Meaning

@@error

Commonly used to check the error status (succeeded or failed) of the most recently executed statement. Contains 0 if the previous transaction succeeded; otherwise, 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

  • 0 The fetch statement completed successfully.

  • -1 The fetch statement resulted in an error.

  • -2 There is no more data in the result set.

This feature is the same as @@sqlstatus, except that it returns different values. It is for Microsoft SQL Server compatibility.

@@identity

The last value inserted into an Identity/Autoincrement column by an insert, load or update statement. @@identity is reset each time a row is inserted into a table. If a statement inserts multiple rows, @@identity reflects the Identity/Autoincrement value for the last row inserted. If the affected table does not contain an Identity/Autoincrement column, @@identity is set to 0. The value of @@identity is not affected by the failure of an insert, load, or update statement, or the rollback of the transaction that contained the failed statement. @@identity retains the last value inserted into an Identity/Autoincrement column, even if the statement that inserted that value fails to commit.

@@isolation

Current isolation level. @@isolation takes the value of the active level.

@@procid

Stored procedure ID of the currently executing procedure.

@@servername

Name of the current database server.

@@sqlstatus

Contains status information resulting from the last FETCH statement.

@@version

Version number of the current version of Sybase IQ.

Compatibility

Table 3-6 includes all Adaptive Server Enterprise global variables supported in Sybase IQ. Adaptive Server Enterprise global variables not supported by Sybase IQ are not included in the list. In contrast to Table 3-5, 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 might not be meaningful.

Table 3-6: ASE global variables supported in Sybase IQ

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, contains the name of the most recently used character set. Returns NULL in Sybase IQ.

@@client_csid

In Adaptive Server Enterprise, the client’s character set ID. Set to -1 if client character set has never been initialized; otherwise, contains the most recently used client character set ID from syscharsets. Returns -1 in Sybase IQ.

@@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 performing Adaptive Server Enterprise work since the last time Adaptive Server Enterprise was started. In Sybase IQ, returns 0.

@@error

Commonly used to check the error status (succeeded or failed) of the most recently executed statement. Contains 0 if the previous transaction succeeded; otherwise, 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 statement resets @@error, including PRINT statements or IF tests, so the status check must immediately follow the statement whose success is in question.

@@identity

In Adaptive Server Enterprise, the last value inserted into an IDENTITY column by an INSERT, LOAD, or SELECT INTO statement. @@identity is reset each time a row is inserted into a table. If a statement inserts multiple rows, @@identity reflects the IDENTITY value for the last row inserted. If the affected table does not contain an IDENTITY column, @@identity is set to 0. The value of @@identity is not affected by the failure of an INSERT or SELECT INTO statement, or the rollback of the transaction that contained the failed statement. @@identity retains the last value inserted into an IDENTITY column, even if the statement that inserted that value fails to commit.

@@idle

In Adaptive Server Enterprise, the amount of time, in ticks, that Adaptive Server Enterprise has been idle since the server was last started. In Sybase IQ, returns 0.

@@io_busy

In Adaptive Server Enterprise, the amount of time, in ticks, that Adaptive Server Enterprise has spent performing input and output operations since the server was last started. In Sybase IQ, returns 0.

@@isolation

Current isolation level of the connection. In Adaptive Server Enterprise, @@isolation takes the value of the active level.

@@langid

In Adaptive Server Enterprise, defines the local language ID of the language currently in use. In Sybase IQ, returns 0.

@@language

In Adaptive Server Enterprise, defines the name of the language currently in use. In Sybase IQ, returns “English”.

@@maxcharlen

In Adaptive Server Enterprise, maximum length, in bytes, of a character in the Adaptive Server Enterprise default character set. In Sybase IQ, returns 1.

@@max_ connections

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 Sybase IQ, 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 Sybase IQ, returns -1.

@@pack_received

In Adaptive Server Enterprise, number of input packets read by Adaptive Server Enterprise since the server was last started. In Sybase IQ, returns 0.

@@pack_sent

In Adaptive Server Enterprise, number of output packets written by Adaptive Server Enterprise since the server was last started. In Sybase IQ, returns 0.

@@packet_errors

In Adaptive Server Enterprise, number of errors that have occurred while Adaptive Server Enterprise was sending and receiving packets. In Sybase IQ, returns 0.

@@procid

Stored procedure ID of the currently executing procedure.

@@servername

Name of the local Adaptive Server Enterprise or Sybase IQ server.

@@spid

In Adaptive Server Enterprise, server process ID number of the current process. In Sybase IQ, 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:

  • 0 – the FETCH statement completed successfully.

  • 1 – the FETCH statement resulted in an error.

  • 2 – there is no more data in the result set.

@@thresh_hysteresis

In Adaptive Server Enterprise, change in free space required to activate a threshold. In Sybase IQ, returns 0.

@@timeticks

In Adaptive Server Enterprise, number of microseconds per tick. The amount of time per tick is machine-dependent. In Sybase IQ, returns 0.

@@total_errors

In Adaptive Server Enterprise, number of errors that have occurred while Adaptive Server Enterprise was reading or writing. In Sybase IQ, returns 0.

@@total_read

In Adaptive Server Enterprise, number of disk reads by Adaptive Server Enterprise since the server was last started. In Sybase IQ, returns 0.

@@total_write

In Adaptive Server Enterprise, number of disk writes by Adaptive Server Enterprise since the server was last started. In Sybase IQ, 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 Sybase IQ, returns -1.

@@version

Information on the current version of Adaptive Server Enterprise or Sybase IQ.



Copyright © 2006. Sybase Inc. All rights reserved. Comments

View this document as PDF