hi firebirders!

<loose_thought>
how I wished I had the time, energy and know-how to put my
firebird realted php project into the public domain (in such
a way that people could actually make a decent stab at
evaluating it :-(
</loose_thought>

in the mean time.... today I have spent most of my time trying to
figure out how I can dynamically create GENERATORS (a process
that is triggered on a successful insert of a certain table of my
db). why would I want to do this? well I have a table that stores
'prizedraw' [marketing] actions that the client can add to (& edit)
via a CMS - according to local law the [3] winning prizedraw numbers
must be determined beforehand and the numbers deposited with a
sollicitor - the upshot being that entrants must be handed out prizedraw
numbers from a predefined range (in fact a start number is enough) and to avoid
any kind of race condition when handing out prizedraw numbers from
the predefined range I chose to use firebird GENERATORs (why re-invent the wheel
right).

so I had to create a trigger that would create a GENERATOR for
me when a new 'prizedraw' was inserted into the DB.... easier said than done!

the major help came from Ivan Presnosil via a page he wrote:
http://www.volny.cz/iprenosil/interbase/ip_ib_code_generator.htm#_code_creategen

I don't know Ivan personally but I get the impression that he has more
english languange documentation articles and tips about firebird under his name
than everybody else on the planet put together - thank you Ivan!

BUT I wasn't there yet - for 2 reasons:

1. the GEN_ID() function does not recognize variables as the first argument, which
means the following are symantically identical (i.e. you can only use hardcoded
names for the GENERATOR (at least when:

r = GEN_ID(MY_GEN, 1);
r = GEN_ID('MY_GEN', 1);

2. Once I had figured out how to get aroung the first problem I kept getting
type mismatchs for the return value of GEN_ID() and the variable I was stuffing
it into.


The solution to the first problem was to create (i.e. using concat'ing) a string
of SQL in my stored procedure and execute it with the 'EXECUTE STATEMENT ... INTO'

The solution to the second problem was to make the variable [that will store
the return value of GEN_ID()] of type BIG_INT (as opposed to INTEGER which what I had
it as).

In the end I had a stored proc whose body looks something like:

BEGIN
IF (NEW.start_number IS NOT NULL) THEN
BEGIN
-- create a generator name.
genname = 'GEN_PRIZEDRAW_' || NEW.webcode;
genstart = 0;
-- determine if a generator all ready exists by the given name
IF (NOT EXISTS(SELECT * FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = :genname)) THEN
BEGIN
-- create the generator
INSERT INTO RDB$GENERATORS (RDB$GENERATOR_NAME) VALUES (UPPER(:genname));

-- init the generator to the correct value.
vsql = 'SELECT GEN_ID(' || genname || ', ' || NEW.start_number || ' - GEN_ID(' || genname || ',0)) FROM
RDB$DATABASE';
EXECUTE STATEMENT :vsql INTO :genstart;

-- link the generator to the prizedraw [row]
UPDATE lc_prizedraw l SET l.generator_name=:genname WHERE l.lc_prizedraw_id=NEW.lc_prizedraw_id;
END
END
END