MaxDB is a nice database but I’m missing some build-in functions, in particular a function to generate random values. All functions available in MaxDB are listed in the MaxDB manual. In the section MaxDB Library – Basic Information – Reference Manual – Functions: Overview you can find a complete list of build-in functions.
One can argue that a function to create random values is not needed inside a database. You do not need it, because you can use your programming language to generate random values. And it’s likely that your programming language comes with a sophisticated random number generator or has access to such one. For most applications it is true, that you can shift the job of generating a random value to the layer of application programming. But what if you want to use a stored procedure to generate random strings in order to populare tables with random data? We have seen some postings on PlanetMySQL that explain how it can be done with MySQL. Here’s one suggestion how to do it with MaxDB, based on a stored function I’ve created last year (german language posting)
Generating Random values
Generating random values is simple and complicated at once. Many algorihms exist to compute random values. Some are amazingly simple, but their drawback is that their randomness is not very good. Do you recall the password encryption function that was used in old MySQL versions? I still hear the teacher saying: “And now we show you what happens if someone without deeper knowledge of cryptography creates a cryptographic function”. The same is true for the approach I use to generate random numbers. Don’t use the suggested function if you really need random values! The function is OK for that for what it was written for: generate some random strings and numbers to populate a table with test data. But no more.
CREATE FUNCTION RANDOM RETURNS FIXED(38,37) AS
VAR randval FIXED(38,37);
SELECT ((134775818 * MICROSECOND(NOW()) + 387) MOD 231) / 230
INTO :randval
FROM DBADMIN.DUAL;
RETURN randval;
This is a simple stored function to generate random numbers in the range from 0…1. The function has the name “RANDOM”. It returns a fixed point numeric values with 38 digits in total. 37 out of the 38 digits are behind the decimal dot – FIXED(38, 37). The function is using the microsecond part of the current date as an input value – as a seed – for a very basic algorithm to generate random numbers.
Those of you who have checked the list of build-in functions of MaxDB might wonder why NOW() is not on the list. NOW() is not implemented by the MaxDB kernel. But NOW() is a function that belongs to the ODBC standard. MaxDB does support ODBC, SQL Studio is an ODBC application and thus I’m allowed to use NOW() in the SQL Studio. On the level of ODBC/SQLDBC the function NOW() gets mapped into something that the EBNF style statement syntax in the MaxDB manual calls an “extended_value_spec” . NOW() gets mapped into TIMESTAMP. You can think of TIMESTAMP as a symbolic constant in a programming lanugage. A symbolic constant is a placeholder that gets replaced by the current value it stands for whenever the constant is accessed. This mapping sounds confusing to you? Yes, it is a common pitfall not to remember that SQL Studio is an ODBC application and some things work differently than one might first expect. Thus you should always keep in mind that SQL Studio is an ODBC application.
However, the random number gets created by executing a SELECT … FROM DBADMIN.DUAL . Unlike in MySQL the FROM clause of the SELECT statement is not optional. In order to perform some calculations without the need to query an existing table, MaxDB allows you to query a pseudo table called DUAL. Whenever you use the SELECT … FROM … statement inside a routine, you have to tell MaxDB to which schema the table belongs that you are refering to (MySQL world: CREATE DATABASE = CREATE SCHEMA) . SELECT … FROM DBADMIN.DUAL means that I want to query the table DUAL of the schema DBADMIN. Make sure you adapt all SELECT statement shown in the examples to reflect any of your schemata.
Try out the newly created stored function. As I issue the SELECT statement not from inside a stored procedure or stored function I can use a simple … FROM DUAL here. I don’t need to specify the schema for the stored function, because it resides in my current schema.
SELECT RANDOM() FROM DUAL
Creating one random character
The function to create random numbers is the basis to create one random character and random strings (collections of characters). The following function can be used to create a random character in the range of a…z/A…Z . Regarding the function itself: I’d be happy to hear that I have missed a function/statement that would save all the awful sequence of IF-statements.
CREATE FUNCTION RANDOM_CHAR RETURNS CHAR(1) AS
VAR randval FIXED(38,37); randchar CHAR(1);
SET randchar = '';
SELECT RANDOM() INTO :randval FROM DBADMIN.DUAL;
IF randval < 0.03846 THEN randchar = 'a';
IF randval > 0.03846 AND randval < 0.07692 THEN randchar = 'b';
IF randval > 0.07692 AND randval < 0.11538 THEN randchar = 'c';
IF randval > 0.11538 AND randval < 0.15384 THEN randchar = 'd';
IF randval > 0.15384 AND randval < 0.19230 THEN randchar = 'e';
IF randval > 0.19230 AND randval < 0.23077 THEN randchar = 'f';
IF randval > 0.23077 AND randval < 0.26923 THEN randchar = 'g';
IF randval > 0.26923 AND randval < 0.30769 THEN randchar = 'h';
IF randval > 0.30769 AND randval < 0.34515 THEN randchar = 'i';
IF randval > 0.34515 AND randval < 0.38461 THEN randchar = 'j';
IF randval > 0.38461 AND randval < 0.42307 THEN randchar = 'k';
IF randval > 0.42307 AND randval < 0.46154 THEN randchar = 'l';
IF randval > 0.46154 AND randval < 0.50000 THEN randchar = 'm';
IF randval > 0.50000 AND randval < 0.53846 THEN randchar = 'n';
IF randval > 0.53846 AND randval < 0.57692 THEN randchar = 'o';
IF randval > 0.57692 AND randval < 0.61538 THEN randchar = 'p';
IF randval > 0.61538 AND randval < 0.65385 THEN randchar = 'q';
IF randval > 0.65385 AND randval < 0.69231 THEN randchar = 'r';
IF randval > 0.69231 AND randval < 0.73077 THEN randchar = 's';
IF randval > 0.73077 AND randval < 0.76923 THEN randchar = 't';
IF randval > 0.76923 AND randval < 0.80769 THEN randchar = 'u';
IF randval > 0.80769 AND randval < 0.84615 THEN randchar = 'v';
IF randval > 0.84615 AND randval < 0.88462 THEN randchar = 'w';
IF randval > 0.88462 AND randval < 0.92308 THEN randchar = 'x';
IF randval > 0.92308 AND randval < 0.96154 THEN randchar = 'y';
IF randval > 0.96154 THEN randchar = 'z';
SELECT RANDOM() INTO :randval FROM DBADMIN.DUAL;
IF randval < 0.5 THEN
SET randchar = UPPER(randchar);
RETURN randchar;
Generating a random string
That’s been it – almost. All you finally need to do to create random strings is to look up the syntax of a loop and how to concatenate strings. Here comes the code for a function that creates random strings with a length of 1…n, n<=4000.
CREATE FUNCTION RANDOM_STRING(len INT) RETURNS VARCHAR AS
VAR i INT; randstring VARCHAR(4000);
IF len > 4000 THEN SET len = 4000;
IF len < 0 THEN SET len = 1;
SET randstring = '';
SET i = 0;
WHILE i < len DO BEGIN
SET randstring = randstring || DBADMIN.RANDOM_CHAR();
SET i = i + 1;
END;
RETURN randstring;
MySQL users (< = 5.0.3) might be surprised to see VARCHAR(4000). MaxDB can store upto 8000 bytes in one VARCHAR column. In ASCII mode one char requires 1 byte to be stored, in Unicode mode 2 bytes are required per character. To be on the safe side and because 4000 is far more than I needed for test columns, I put a check in the function to sanitize out of bound function parameters.
Wrapping around a stored procedure or stored function around the RANDOM(), RANDOM_CHAR() and RANDOM_STRING() function calls to populare test tables is left over as an execercise to the reader. You should find all building-block in this posting: the basic syntax of a stored function, how to call the function, how to implement a loop, how to test for conditions and how to insert data in a table.
CREATE TABLE random_data_varchar(rand_varchar VARCHAR(50)) // INSERT INTO random_data_varchar(rand_varchar) VALUES (RANDOM_STRING(50)) // INSERT INTO random_data_varchar(rand_varchar) VALUES (RANDOM_STRING(50)) // SELECT * FROM random_data_varchar // DROP TABLE random_data_varchar
As usual, I’m happy to read your feedback in the MaxDB forum.