Here is a procedure that populates a table with 9-digit random numbers. It
catches collisions and limits the number allowed so it won't go on trying
for ever if there is a consistent problem. Note the discussion earlier in
this list about random number generators. The udf that comes out of the
box has a problem with its seed. I use my own UDF, there is another (I
think in fb_udf) that has a separate call to seed it.

SET TERM ^;
alter procedure INSERT_KEYS
(proj integer, number_of_keys integer)
returns (keys_created integer)
AS
declare variable i integer;
declare variable max_errors integer;
BEGIN
keys_created = 0;
i = number_of_keys;
max_errors = 100;
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;
when sqlcode -803 do begin
max_errors = max_errors - 1;
if (max_errors <= 0) then
exception RANDOM_DUPLICATES;
end
end
END ^
SET TERM ; ^

hth

Walter

At 05:36 PM 7/13/2004 +0000, you wrote:

>I have a generator attached to a primary key field on a certain
>table via a BEFORE INSERT trigger. I can get sequential values from
>this generator fine, but I was wondering if there was any way (using
>generators or not) to get 'random' values from the generator that,
>while 'random', won't conflict with any of the primary key ID values
>already in the table. ....basically it amounts to generating a
>random, unrepeated sequence for my primary key (or any other unique
>field for that matter).
>
>This would be useful to me because users of the system will be
>assigned a table that cooresponds to that unique ID, and there is a
>bit more security in giving them a random 5 digit ID
>(like '35464', '97483' for the next ID, etc) instead of a sequential
>ones like '10001', '10002'... I would like the level of security
>that nonsequential IDs bring, but we don't need enough security to
>warrant the entering of a seperate PIN.
>
>Is anything like this possible? If not, I might have to go that ID,
>PIN route instead.
>
>Thanks,
>
>Robby
>
>
>
>Yahoo! Groups Sponsor
>ADVERTISEMENT
>
>
>----------
>Yahoo! Groups Links
> * To visit your group on the web, go to:
> *
> <http://groups.yahoo.com/group/firebird-support/>http://groups.yahoo.com/g
> roup/firebird-support/
> *
> * To unsubscribe from this group, send an email to:
> *
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>f
> irebird-support-unsubscribe@yahoogroups.com
> *
> * Your use of Yahoo! Groups is subject to the
> <http://docs.yahoo.com/info/terms/>Yahoo! Terms of Service.

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