Fear friends,

I am new at Firebird, udfs and stored procs, and using the rand function
from ib_udf in a stored procedure defined as follows:

SET TERM ^;
alter procedure INSERT_KEYS
(proj integer, number_of_keys integer)
returns (keys_created integer)
AS
declare variable i integer;
BEGIN
keys_created = 0;
i = number_of_keys;
while (i > 0) do
begin
insert into answersheet (PROJECT_NUMBER, ANSWER_NUMBER)
values (proj, Rand() * (999999999 - 100000000) + 100000000);
keys_created = keys_created + 1;
i = i - 1;
end
END ^
SET TERM ; ^

The idea is to create a set of random 9-digit values (cardinality =
number_of_keys) for answer_number. When I call this function with
number_of_keys = 1 it works fine, but setting number_of_keys >= 2 I get a
key violation. Primary key of answersheet relation is (project_number;
answer_number).

It looks like the udf function Rand is not getting called again but is
using a cached return value, or else it is being called with the same seed
(according to the documentation in ib_udf.sql "random number generator is
seeded using the current time").

Source for rand (from
http://cvs.sourceforge.net/viewcvs.py/*checkout*/firebird/interbase/extlib/i
b_udf.c?rev=1.3) is:

double EXPORT IB_UDF_rand() { srand( (unsigned)time (NULL)); return
((float) rand() / (float) RAND_MAX); }

Any ideas how to fix or work around this problem?

TIA

Walter
/*----------------
C. Walter Ogston
ogstoncw@... Kalamazoo, Michigan
*/