Hi everyone,

I personally am using IBO and retrieving generator values via IBO is
obviously not a problem. However, a client of ours now need to access our
database via the Firebird ODBC driver and I need to supply an easy way for
them to access generator values.

I thought of creating a "generic" stored procedure with which any the
generator values can be asked (however, Firebird doesn't seem to be happy
with ":" character since it doesn't form part of an SQL statement):

SET TERM ^ ;

CREATE PROCEDURE "GET_GENERATOR_VALUE"
(
GENERATOR_NAME VARCHAR(60)
)
RETURNS
(
GENERATOR_VALUE BIGINT
)
AS
BEGIN
GENERATOR_VALUE = GEN_ID(:GENERATOR_NAME, 0);
SUSPEND;
END
^

COMMIT ^
SET TERM ;^


Since the above stored procedure doesn't want to compile, I opted to go for
an IF statement that will return the generator value depending on the
generator name supplied through the GENERATOR_NAME variable.

Something like this:

SET TERM ^ ;

CREATE PROCEDURE "GET_GENERATOR_VALUE"
(
GENERATOR_NAME VARCHAR(60)
)
RETURNS
(
GENERATOR_VALUE BIGINT
)
AS
BEGIN
IF (AUTO_ID_NAME = 'GENERATOR_1_ID_GEN') THEN
BEGIN
AUTO_ID_VALUE = GEN_ID("GENERATOR_1_ID_GEN", 0);
END ELSE
IF (AUTO_ID_NAME = 'GENERATOR_2_ID_GEN') THEN
BEGIN
AUTO_ID_VALUE = GEN_ID("GENERATOR_2_ID_GEN", 0);
END ELSE
BEGIN
AUTO_ID_VALUE = 0;
END

SUSPEND;
END
^

COMMIT ^
SET TERM ;^

The above stored procedure works, but it's going to be a bit of a mission to
maintain in the future.

Are there any easier methods for the client's app to retrieve a generator
value via ODBC?

Thanks in advance!
Don Schoeman