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