At 08:05 PM 7/03/2007, Don Schoemann wrote:

>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,

No, like this it won't, because a generator is a metadata object and
a generator name is an object identifier. As you're probably aware
already, you can't pass metadata objects as parameters to SPs.

In any case, it's not a sensible thing to do, to return the *current*
generator value to an application (as you are trying to do
here). There is nothing the user could safely do with it except read
it. What did you want from this?

You haven't said what you are using for your database engine but, if
it is Firebird, version 1.5 or higher, you can get the next value via
a SP using EXECUTE STATEMENT. The SQL for the SP would be:

CREATE PROCEDURE GET_GENERATOR_VALUE
(
GENERATOR_NAME VARCHAR(31)
)
RETURNS
(
GENERATOR_VALUE BIGINT
)
AS
declare variable stmt varchar(60) = '';
BEGIN
if (generator_name is not null) then
begin
stmt = 'select gen_id (' || generator_name || ', 1) from rdb$database';
execute statement (stmt) into :generator_value;
SUSPEND;
end
END
^

You haven't said how or why your ODBC user would be accessing the
generator but, if it's some kind of ad hoc query capability then you
would want to build in some check to discover if the generator exists
(a query on RDB$GENERATORS); or else put in some error handling to
pick up the error and return some useful message to the caller (if it
is a human).

Helen