| 1 |
----------------------------------------- |
| 2 |
Generic user and system context variables |
| 3 |
----------------------------------------- |
| 4 |
|
| 5 |
Function: |
| 6 |
New built-in functions give access to some information about current |
| 7 |
connection and current transaction. Also they provide means to associate |
| 8 |
and retrieve user context data with transaction or connection. |
| 9 |
|
| 10 |
Author: |
| 11 |
Nickolay Samofatov <nickolay at broadviewsoftware dot com> |
| 12 |
|
| 13 |
Format: |
| 14 |
RDB$SET_CONTEXT( <namespace>, <variable>, <value> ) |
| 15 |
RDB$GET_CONTEXT( <namespace>, <variable> ) |
| 16 |
|
| 17 |
Declared as: |
| 18 |
DECLARE EXTERNAL FUNCTION RDB$GET_CONTEXT |
| 19 |
VARCHAR(80), |
| 20 |
VARCHAR(80) |
| 21 |
RETURNS VARCHAR(255) FREE_IT; |
| 22 |
|
| 23 |
DECLARE EXTERNAL FUNCTION RDB$SET_CONTEXT |
| 24 |
VARCHAR(80), |
| 25 |
VARCHAR(80), |
| 26 |
VARCHAR(255) |
| 27 |
RETURNS INTEGER BY VALUE; |
| 28 |
|
| 29 |
Usage: |
| 30 |
|
| 31 |
RDB$SET_CONTEXT and RDB$GET_CONTEXT set and retrieve current value for the |
| 32 |
context variables. Namespace name identifies a group of context variables with |
| 33 |
similar properties. Access rules such as the fact if variables may be read and |
| 34 |
written to and by whom are determined by namespace which they belong to. |
| 35 |
|
| 36 |
Namespace and variable names are case-sensitive. |
| 37 |
|
| 38 |
RDB$GET_CONTEXT retrieves current value of a variable. If variable does not |
| 39 |
exist in namespace return value for the function is NULL. |
| 40 |
|
| 41 |
RDB$SET_CONTEXT sets a value for specific variable. Function returns value of |
| 42 |
1 if variable existed before the call and 0 otherwise. To delete variable from |
| 43 |
context set its value to NULL. |
| 44 |
|
| 45 |
Currently, there is a fixed number of pre-defined namespaces you may use. |
| 46 |
|
| 47 |
USER_SESSION namespace offers access to session-specific user-defined |
| 48 |
variables. You can define and set value for variable with any name in this |
| 49 |
context. USER_TRANSACTION namespace offers the same possibilities for |
| 50 |
individual transactions. |
| 51 |
|
| 52 |
SYSTEM namespace provides read-only access to the following variables. |
| 53 |
|
| 54 |
Variable name Value |
| 55 |
------------------------------------------------------------------------------ |
| 56 |
NETWORK_PROTOCOL | The network protocol used by client to connect. Currently |
| 57 |
| used values: "TCPv4", "TCPv6", "WNET", "XNET" and NULL |
| 58 |
| |
| 59 |
WIRE_COMPRESSED | Compression status of current connection. |
| 60 |
| If connection is compressed - returns "TRUE", if it is |
| 61 |
| not compressed - returns "FALSE". |
| 62 |
| If connection is embedded - returns NULL. |
| 63 |
| |
| 64 |
WIRE_ENCRYPTED | Encryption status of current connection. |
| 65 |
| Value is the same as for compression status above. |
| 66 |
| |
| 67 |
CLIENT_ADDRESS | The wire protocol address and port number of remote client |
| 68 |
| represented as string. Value is IP address concatenated with |
| 69 |
| port number using the '/' separator character. Value is |
| 70 |
| returned for TCPv4 and TCPv6 protocols only, for all other |
| 71 |
| protocols NULL is returned |
| 72 |
| |
| 73 |
CLIENT_HOST | The wire protocol host name of remote client. Value is |
| 74 |
| returned for all supported protocols |
| 75 |
| |
| 76 |
CLIENT_PID | Process ID of remote client application |
| 77 |
| |
| 78 |
CLIENT_PROCESS | Process name of remote client application |
| 79 |
| |
| 80 |
DB_NAME | Canonical name of current database. It is either alias |
| 81 |
| name if connectivity via file names is not allowed or |
| 82 |
| fully expanded database file name otherwise. |
| 83 |
| |
| 84 |
ISOLATION_LEVEL | Isolation level for current transaction. Returned values |
| 85 |
| are "READ COMMITTED", "CONSISTENCY", "SNAPSHOT" |
| 86 |
| |
| 87 |
TRANSACTION_ID | Numeric ID for current transaction. Returned value is the |
| 88 |
| same as of CURRENT_TRANSACTION pseudo-variable |
| 89 |
| |
| 90 |
LOCK_TIMEOUT | Lock timeout value specified for current transaction |
| 91 |
| |
| 92 |
READ_ONLY | Returns "TRUE" if current transaction is read-only and |
| 93 |
| "FALSE" otherwise |
| 94 |
| |
| 95 |
SESSION_ID | Numeric ID for current session. Returned value is the |
| 96 |
| same as of CURRENT_CONNECTION pseudo-variable |
| 97 |
| |
| 98 |
CURRENT_USER | Current user for the connection. Returned value is the |
| 99 |
| same as of CURRENT_USER pseudo-variable |
| 100 |
| |
| 101 |
CURRENT_ROLE | Current role for the connection. Returned value is the |
| 102 |
| same as of CURRENT_ROLE pseudo-variable |
| 103 |
| |
| 104 |
ENGINE_VERSION | Engine version number, e.g. "2.1.0" (since V2.1) |
| 105 |
|
| 106 |
Notes: |
| 107 |
To prevent DoS attacks against Firebird Server you are not allowed to have |
| 108 |
more than 1000 variables stored in each transaction or session context. |
| 109 |
|
| 110 |
Example(s): |
| 111 |
|
| 112 |
create procedure set_context(User_ID varchar(40), Trn_ID integer) as |
| 113 |
begin |
| 114 |
RDB$SET_CONTEXT('USER_TRANSACTION', 'Trn_ID', Trn_ID); |
| 115 |
RDB$SET_CONTEXT('USER_TRANSACTION', 'User_ID', User_ID); |
| 116 |
end; |
| 117 |
|
| 118 |
create table journal ( |
| 119 |
jrn_id integer not null primary key, |
| 120 |
jrn_lastuser varchar(40), |
| 121 |
jrn_lastaddr varchar(255), |
| 122 |
jrn_lasttransaction integer |
| 123 |
); |
| 124 |
|
| 125 |
CREATE TRIGGER UI_JOURNAL FOR JOURNAL BEFORE INSERT OR UPDATE |
| 126 |
as |
| 127 |
begin |
| 128 |
new.jrn_lastuser = rdb$get_context('USER_TRANSACTION', 'User_ID'); |
| 129 |
new.jrn_lastaddr = rdb$get_context('SYSTEM', 'CLIENT_ADDRESS'); |
| 130 |
new.jrn_lasttransaction = rdb$get_context('USER_TRANSACTION', 'Trn_ID'); |
| 131 |
end; |
| 132 |
|
| 133 |
execute procedure set_context('skidder', 1); |
| 134 |
|
| 135 |
insert into journal(jrn_id) values(0); |
| 136 |
|
| 137 |
commit; |