--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 04:45 AM 19/01/2007, you wrote:
> >Hi,
> >I have a table (BROKERS) with a PK of domain D_PK of type Integer. I
> >also have a stored procedure that generates a PK value for this table,
> >as follows:
> >
> >CREATE PROCEDURE P_BROKER_ID returns (
> > BROKER_ID Integer)
> >AS
> >DECLARE VARIABLE DT1 TIMESTAMP;
> >DECLARE VARIABLE DT2 TIMESTAMP;
> >DECLARE VARIABLE DT DECIMAL(9,4);
> >DECLARE VARIABLE ID INTEGER;
> >begin
> > DT1 = CURRENT_TIMESTAMP;
> > DT2 = CAST('1.1.2005' AS TIMESTAMP);
> > DT = DT1 - DT2;
> > ID = CAST((DT * 24 * 60 * 60) AS INTEGER);
> > BROKER_ID = ID;
> >end
> >
> >in other words, the value of BROKER_ID is equal to the number of
> >elapsed seconds since 1 Jan 2005. I've tested this and it works. I
> >intend to have a BEFORE INSERT trigger that injects this value into
> >the PK of BROKERS, as follows (there IS a good reason why I am not
> >using an autoinc trigger/generator):
> >
> >CREATE TRIGGER BROKERS_NEW_ID FOR BROKERS ACTIVE BEFORE INSERT
> >POSITION 0 AS
> >
> >DECLARE VARIABLE N INTEGER;
> >BEGIN
> > EXECUTE PROCEDURE P_BROKER_ID(N);
> > IF (NEW.ID IS NULL) THEN
> > NEW.ID = N;
> >END;
> >
> >When I come to save this code I get the following error:
> >
> >ISC ERROR MESSAGE
> >invalid request BLR at offset 30
> >parameter mismatch for procedure P_BROKER_ID
> >
> >Has this got something to do with the types being different i.e.
> >Integer and D_PK, even though they are both integer types? What should
> >I do in this case?
>
> No, it has to do with trying to pass an input parameter to a
> procedure that is not defined to take any.
>
> Change this line
>
> EXECUTE PROCEDURE P_BROKER_ID(N);
>
> to
>
> EXECUTE PROCEDURE P_BROKER_ID
> returning_values(:N);
>
> And I plead with you to rethink using this number as a primary key,
> unless this is always going to be a stand-alone desktop database.
>
> ./heLen
>

Thanks, Helen (and to all others who gave their time to this little
problem)...
your suggestion works just fine. I'll have to dig further into "The
Firebird Book" :-) to find out more. On the matter you raised about
the PK value: this value "represents" the datetime that a Broker is
created. It only ever happens once during the lifetime of an
application. My intention was to merge databases some time in the
future, where all members of one Broker will remain attached to that
broker and no other broker. I woould like the PK to be an integer
rather than a GUID, which will guarantee uniqueness. Do you have any
other suggestions.
Ron