Global Variables

Global Variables

Pervasive PSQL supports the following global variables:

•

•

•

•

Global variables are prefaced with two at signs, @@. All global variables are variables per connection. Each database connection has its own @@IDENTITY, @@ROWCOUNT, and @@SPID values.

This variable returns the value of the most recently inserted IDENTITY column value (IDENTITY or SMALLIDENTITY). The value is a signed integer value. The initial value is NULL.

This variable can only refer to a single column. If the target table includes more than one IDENTITY column, the value of this variable refers to the IDENTITY column that is the table’s primary key. If no such column exists, then the value of this variable refers to the first IDENTITY column in the table.

If the most recent insert was to a table without an IDENTITY column, then the value of @@IDENTITY is set to NULL.

SELECT @@IDENTITY

Returns NULL if no records have been inserted in the current connection, otherwise returns the IDENTITY column value of the most recently inserted row.

SELECT * FROM t1 WHERE @@IDENTITY = 12

Returns the most recently inserted row if it has an IDENTITY column value of 12. Otherwise, returns no rows.

INSERT INTO t1(c2) VALUES (@@IDENTITY)

Inserts the IDENTITY value of the last row inserted into column C2 of the new row.

UPDATE t1 SET t1.c1 = (SELECT @@IDENTITY) WHERE t1.c1 = @@IDENTITY + 10

Updates column C1 with the IDENTITY value of the last row inserted, if the value of C1 is 10 greater than the IDENTITY column value of the last row inserted.

UPDATE t1 SET t1.c1 = (SELECT NULL FROM t2 WHERE t2.c1 = @@IDENTITY)

Updates column C1 with the value NULL if the value of C1 equals the IDENTITY column value of the last row inserted.

The example below creates a stored procedure and calls it. The procedure sets variable V1 equal to the sum of the input value and the IDENTITY column value of the last row updated. The procedure then deletes rows from the table anywhere column C1 equals V1. The procedure then prints a message stating how many rows were deleted.

CREATE PROCEDURE TEST (IN :P1 INTEGER);

DECLARE :V1 INTERGER;

SET :V1 = :P1 + @@IDENTITY;

DELETE FROM t1 WHERE t1.c1 = :V1;

IF (@@ROWCOUNT = 0) THEN

PRINT 'No row deleted';

PRINT CONVERT(@@ROWCOUNT, SQL_CHAR) + ' rows deleted';

CALL TEST (@@IDENTITY)

This variable returns the number of rows that were affected by the most recent operation in the current connection. The value is an unsigned integer. The initial value is zero.

The @@ROWCOUNT variable is valid only when used after an INSERT, UPDATE, or DELETE statement.

SELECT @@ROWCOUNT

Returns zero if no records were affected by the previous operation in the current connection, otherwise returns the number of rows affected by the previous operation.

CREATE TABLE t1 (c1 INTEGER, c2 INTEGER)

INSERT INTO t1 (c1, c2) VALUES (100,200)

INSERT INTO t1(c1, c2) VALUES (300, @@ROWCOUNT)

SELECT @@ROWCOUNT

1 (in @@ROWCOUNT variable)

In line four, the @@ROWCOUNT variable has the value of 1 because the previous INSERT operation affected one row.

Also see the example for @@IDENTITY.

This variable returns an eight-byte integer value for the Pervasive PSQL connection. The integer is a combination of a time value and an incremental counter. This variable can be used to identify uniquely each Pervasive PSQL connection.

@@SESSIONID requires a connection to the database engine to return a value. If the connection to the database engine is lost, the variable cannot return an identifier.

SELECT @@SESSIONID

The example returns an integer identifier such as 26552653137523.

This variable (server process identifier) returns the identifier integer value of the system thread for the Pervasive PSQL connection.

If the connection to the database engine is lost, SPID cannot return an identifier. Instead, ODBC returns SqlState 08S01.

The example returns an integer identifier such as 402.