Alternative to RDB$GET_CONTEXT and RDB$SET_CONTEXT

Question

I need to convert SQL statements RDB$GET_CONTEXT and RDB$SET_CONTEXT from Firebird to Interbase so what I should use ?

Here is the complete Firebird SQL:

if (rdb$get_context('USER_TRANSACTION', 'Lock_Trigger') = '1') then exit;

Answer 1

These functions are more or less* shortcuts for accessing Firebird's key-value system table. You can imitate it with Global Temporary Tables

Use global temporary tables to allow an application to pass intermediate result sets from one section of an application to another section of the same application.

Creating a Global Temporary Table

A global temporary table is declared to a database schema via the normal

CREATE TABLE statement with the following syntax:
CREATE GLOBAL TEMPORARY TABLE table (<col_def> [, <col_def> | 
<tconstraint> ...])
[ON COMMIT {PRESERVE | DELETE} ROWS];

The first argument that you supply CREATE GLOBAL TEMPORARY TABLE is the temporary table name, which is required and must be unique among all table and procedure names in the database. You must also supply at least one column definition.

The ON COMMIT clause describes whether the rows of the temporary table are deleted on each transaction commit (ON COMMIT DELETE) or are left in place (ON COMMIT PRESERVE) to be used by other transactions in the same database attachment. If the ON COMMIT is not specified then the default behavior is to DELETE ROWS on transaction commit.

ON COMMIT DELETE GTT imitates *context('USER_TRANSACTION'...) while ON COMMIT PRESERVE imitates *context('USER_SESSION'...). Refer to Interbase DataDef manual for details.

So you can create GTT with fields (KEY, VALUE) and simply insert/update/select the values you need:

Get:
select Value from MY_GTT where Key = :key

Set:
select count(*) from MY_GTT where Key = :key into :Cnt;
if (:Cnt = 0) then
   insert into MY_GTT(Key, Value) values(:Key, :Value)
else
   update MY_GTT SET Value=:Value where Key = :key

Note that collisions are possible with COMMIT PRESERVE ROWS GTT's when reading/writing the same value from multiple transactions of the same attachment.

---

*I believe these functions also provide atomic type of operations to avoid read-write collisions.