Debian Codegraph @

doc/sql.extensions/README.context_variables2

raw
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;