Mega Code Archive
Using Firebird generators as variables
<![CDATA[
Title: Using Firebird generators as variables
Question: How to make and use variables outside transaction context in Firebird/Interbase.
Answer:
Single generator can hold 64-bit integer value.
Create generator for every variable you want.
For an array of 8 longint you should have eight generators names 'S1' ... 'S8'.
(You can adopt other naming convention, the principle is the same).
Two procedures GET_VAR and SET_VAR are the procedures that generalize access to
values of those generators.
These procedures make expanding long int array very easy, just add more generators and adjust boundary-check.
Values set up this way are immediately visible to all transactions (there is no need for refresh or commit).
Note: When setting up new value you should check if it has succeeded because the setting up of the generator is not an atomic operation, and another transaction can get into middle of an operation and provoke unpredictable result.
/* Definition for 8 generators to hold 8 integer variables */
CREATE GENERATOR S1;
CREATE GENERATOR S2;
CREATE GENERATOR S3;
CREATE GENERATOR S4;
CREATE GENERATOR S5;
CREATE GENERATOR S6;
CREATE GENERATOR S7;
CREATE GENERATOR S8;
/* Definition for the `RANGE_ERROR` exception for GET_VAR and SET_VAR procedures*/
CREATE EXCEPTION RANGE_ERROR 'Input parameter falls outside range boundaries';
/* Definition for the `GET_VAR` procedure : */
SET TERM ^ ;
CREATE PROCEDURE GET_VAR(
IND SMALLINT)
RETURNS(
VAR INTEGER)
AS
BEGIN
if ((IND1)or(IND8)) then exception RANGE_ERROR;
/* get the current value of generator IND */
execute statement 'SELECT GEN_ID(S'||IND||',0) FROM RDB$DATABASE'
into :VAR;
/* and return it */
suspend;
EXIT;
END^
SET TERM ; ^
/* Definition for the `SET_VAR` procedure : */
SET TERM ^ ;
CREATE PROCEDURE SET_VAR(
IND SMALLINT,
VAR INTEGER)
RETURNS(
VAR_NEW INTEGER)
AS
BEGIN
if ((IND1)or(IND8)) then exception range_error;
/* get current value of generator IND */
execute statement 'SELECT GEN_ID(S'||IND||',0) FROM RDB$DATABASE'
into :VAR_NEW;
/* if value should be changed then change it */
if (VARVAR_NEW) then begin
execute statement
'SELECT GEN_ID(S'||IND||','||VAR||'-GEN_ID(S'||IND||',0)) FROM RDB$DATABASE'
into :VAR_NEW;
end
/*
return new value for generator IND
Note: since last statement is not atomic, you should add check to
ensure that var_new is the same as var, in calling procedure
or here
*/
suspend;
EXIT;
END^
SET TERM ; ^
]]>