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.

@@IDENTITY

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.

Examples

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); 
BEGIN 
DECLARE :V1 INTERGER; 
SET :V1 = :P1 + @@IDENTITY; 
DELETE FROM t1 WHERE t1.c1 = :V1; 
IF (@@ROWCOUNT = 0) THEN 
PRINT 'No row deleted'; 
ELSE 
PRINT CONVERT(@@ROWCOUNT, SQL_CHAR) + 
' rows deleted'; 
END IF; 
END; 

CALL TEST (@@IDENTITY) 

@@ROWCOUNT

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.

Examples

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(c2) VALUES (100, @@ROWCOUNT) 
SELECT * FROM t1 

SELECT @@ROWCOUNT 

Results:

2 

The first SELECT generates two rows and shows that the value of @@ROWCOUNT was 1 when it was used to insert a row. The second SELECT returns 2 as the value of @@ROWCOUNT, that is, after the first SELECT returned two rows.

Also see the example for @@IDENTITY.

@@SPID

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.

Example

SELECT @@SPID 

The example returns an integer identifier, for example 402.