27.3.9 JavaScript Stored Program Examples

This section contains examples illustrating a number of different aspects of using JavaScript programs under various circumstances.

The following example demonstrates the use of a JavaScript stored function with table column values. First we define a stored function gcd() which finds the greatest common denominator of two integers, shown here:

mysql> CREATE FUNCTION gcd(a INT, b INT) 
    -> RETURNS INT NO SQL LANGUAGE JAVASCRIPT AS
    -> $mle$
    $>   let x = Math.abs(a)
    $>   let y = Math.abs(b)
    $>   while(y) {
    $>     var t = y
    $>     y = x % y
    $>     x = t
    $>   }
    $>   return x
    $> $mle$
    -> ;
Query OK, 0 rows affected (0.01 sec)

We can test the stored function, like this:

mysql> SELECT gcd(75, 220), gcd(75, 225);
+--------------+--------------+
| gcd(75, 220) | gcd(75, 225) |
+--------------+--------------+
|            5 |           75 |
+--------------+--------------+
1 row in set (0.00 sec)

Next we create a table t1 with two integer columns and populate it with a few rows, like this:

mysql> CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES ROW(12,70), ROW(17,3), ROW(81,9);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> TABLE t1;
+------+------+
| c1   | c2   |
+------+------+
|   12 |   70 |
|   17 |    3 |
|   81 |    9 |
+------+------+
3 rows in set (0.00 sec)

Now we can select from t1, using the gcd() function with the column values serving as argument values in the function call, as shown here:

mysql> SELECT c1, c2, gcd(c1, c2) AS G
    -> FROM t1
    -> WHERE gcd(c1, c2) > 1
    -> ORDER BY gcd(c1, c2);
+----+----+---+
| c1 | c2 | G |
+----+----+---+
| 12 | 70 | 2 |
| 81 |  9 | 9 |
+----+----+---+
8 rows in set (0.01 sec)

An argument value that is not of the specified type is coerced to the correct type when possible, as shown here:

mysql> SELECT gcd(500.3, 600), gcd(500.5, 600);
+-----------------+-----------------+
| gcd(500.3, 600) | gcd(500.5, 600) |
+-----------------+-----------------+
|             100 |               3 |
+-----------------+-----------------+
1 row in set (0.01 sec)

Rounding of floating point values to integers is accomplished using Math.round(); in this case, 500.3 is rounded down to 500, but 500.5 is rounded up to 501.

Next, we create a simple JavaScript stored procedure using a CREATE PROCEDURE statement that includes an OUT parameter for passing the current date and time in a human-readable format to a user variable. Since we are not certain how long this representation is, we use VARCHAR(25) for the parameter's type.

mysql> CREATE PROCEDURE d1 (OUT res VARCHAR(25))
    -> LANGUAGE JAVASCRIPT
    -> AS
    -> $$
    $>   let d = new Date().toString()
    $>   res = d
    $> $$
    -> ;
Query OK, 0 rows affected (0.01 sec)

We can now test the stored procedure, first verifying that the user variable @today has not yet been set to any value, like this:

mysql> SELECT @today;
+----------------------+
| @today               |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.01 sec)

mysql> CALL d1(@today);
ERROR 1406 (22001): Data too long for column 'res' at row 1

The procedure is syntactically valid, but the data type of the INOUT parameter (res) does not allow for a sufficient number of characters; rather than truncating the value, the stored program rejects it. Since it is not possible to alter procedure code in place, we must drop the procedure and re-create it; this time we try doubling the length specified for the INOUT parameter:

mysql> DROP PROCEDURE d1;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE PROCEDURE d1 (OUT res VARCHAR(50))
    -> LANGUAGE JAVASCRIPT
    -> AS
    -> $$
    $>   let d = new Date().toString()
    $>   res = d
    $> $$
    -> ;
Query OK, 0 rows affected (0.01 sec)

Now we can repeat the test, like this:

mysql> SELECT @today;
+----------------------+
| @today               |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.01 sec)

Prior to invoking the updated procedure with CALL, the value of @today remains unset, since the original version of d1() did not execute successfully. The updated version runs successfully, and we see afterwards that, this time, the value of the user variable is set as expected:

mysql> CALL d1(@today);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @today;
+-----------------------------------------+
| @today                                  |
+-----------------------------------------+
| Mon Oct 30 2023 20:47:29 GMT+0000 (GMT) |
+-----------------------------------------+
1 row in set (0.00 sec)

Note

The value that you obtain from running this example is likely to differ to some extent from what is shown here, since the exact representation of dates is dependent upon your system locale, and possibly other settings. See the documentation for the JavaScript Date object for more information.

The next example demonstrates the use of a JavaScript stored function in a trigger.

First we create a table t2 containing three integer columns, like this:

mysql> CREATE TABLE t2 (c1 INT, c2 INT, c3 INT);
Query OK, 0 rows affected (0.04 sec)

Now we can create a trigger on this table. This must be done using a CREATE TRIGGER statement written in the usual way using SQL (see Section 27.4, “Using Triggers”), but it can make use of stored routines written in JavaScript, such as the js_pow() function shown earlier in this section.

mysql> delimiter //
mysql> CREATE TRIGGER jst BEFORE INSERT ON t2
    -> FOR EACH ROW
    -> BEGIN
    ->   SET NEW.c2 = js_pow(NEW.c1, 2);
    ->   SET NEW.c3 = js_pow(NEW.c1, 3);
    -> END;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql>

This trigger acts when a row is inserted into t2, taking the value inserted into the first column and inserting the square of this value into the second column, and its cube into the third. We test the trigger by inserting a few rows into the table; since the only value that is not thrown away is that which we supply for column c1, we can simply use NULL for each of the remaining two columns, as shown here:

mysql> INSERT INTO t2 
    -> VALUES 
    ->   ROW(1, NULL, NULL), 
    ->   ROW(2.49, NULL, NULL), 
    ->   ROW(-3, NULL, NULL),
    ->   ROW(4.725, NULL, NULL);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Since the function invoked by the trigger was written in JavaScript, JavaScript rounding rules apply, so that 2.49 is rounded down to 2, and 4.75 is rounded up to 5. We can see that this is the case when we check the result using a TABLE statement:

mysql> TABLE t2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    4 |    8 |
|   -3 |    9 |  -27 |
|    5 |   25 |  125 |
+------+------+------+
4 rows in set (0.00 sec)