IIUG Software Repository Files

From: Jonathan Leffler <johnl@informix.com>
To: software@iiug.org
Subject: Random number in a stored procedure
Date: Mon, 8 Dec 1997 11:32:34 -0800 (PST)

On Sun, 7 Dec 1997, Nils Myklebust wrote:

> I doubt this would work as there is no way of returning anything from
> a system call short of having an external program inserting it into
> the database. Would be still slower.

Agreed.

>
> billy@west.co.za ("Billy Wheeler") wrote:
> :On  6 Dec 97 at 8:28, Ed Schaefer wrote:
> :> Anybody out there written a stored procedure to return a random
> :> number. Any help is appreciated!
> :
> :Blunt, slow and ugly (just like me! :-) --
> :
> :SYSTEM("exit `echo $RANDOM`") -- (Needs ksh)
> :
> :I think. I'm not really sure how you return things to SPs. Guaranteed
> :to be slow due to the system call, I'm afraid.

Since that's the case, we need a proper random number generator.  On the
assumption that the C Standard committee knew roughly what they were doing
when they specified a random number generator in the ISO 9899:1990
standard, here's an implementation of that generator in two functions,
sp_setseed(0 and sp_random().  The range of returned values is 0..32767.
Note the comment about MOD.  That was in a 9.12.UC2 IUS system.

Yours,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>

-- @(#)$Id: random.spl,v 1.2 1997/12/08 19:31:44 johnl Exp $
--
-- Simple emulation of SRAND and RAND in SPL
-- Using random number generator suggested by C standard (ISO 9899:1990)

CREATE PROCEDURE sp_setseed(n INTEGER)
	DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
	LET seed = n;
END PROCEDURE;

CREATE PROCEDURE sp_random() RETURNING INTEGER;
	DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
	DEFINE d DECIMAL(20,0);
	LET d = (seed * 1103515245) + 12345;
	-- MOD function does not handle 20-digit values...  Dammit!!
	LET seed = d - 4294967296 * TRUNC(d / 4294967296);
	RETURN MOD(TRUNC(seed / 65536), 32768);
END PROCEDURE;