Most MySQL data types are supported for MLE stored program input and output arguments, as well as for return data types. The data types are listed here:

Input arguments (IN and INOUT parameters) are automatically converted into JavaScript types based on the mapping shown in the following table:

Conversion to or from a MySQL integer whose value lies outside the range -(2⁵³-1) (-9007199254740991) to 2⁵³-1 (9007199254740991) is lossy. How conversion from MySQL integers to JavaScript is performed can be changed for the current session using mle_set_session_state(); the default behavior is equivalent to calling this function using UNSAFE_STRING as the value for integer_type. See the description of that function for more information.

SQL NULL is supported for all the types listed, and is converted to and from JavaScript null as required.

JavaScript (unlike SQL) is a dynamically typed language, which means that return types are known only at execution time. JavaScript return value and output arguments (OUT and INOUT parameters) are automatically converted back into the expected MySQL type based on the mappings shown in the following table:

Notes:

Passing a MySQL zero date (00-00-0000) or zero-in-date value (such as 00-01-2023) leads to the creation of an Invalid Date instance of Date. When passed a MySQL date which is invalid (for example, 31 February), MLE calls a JavaScript Date constructor with invalid individual date and time component values.

The MySQL TIME type is handled as a string, and is validated inside MySQL. See Section 13.2.3, “The TIME Type”, for more information.

Notes:

Conversion to and from MySQL ENUM and SET.  ENUM converts to a JavaScript String; SET converts to a JavaScript Set object, as shown in the following table:

The following table shows rules for converting a JavaScript type to a MySQL ENUM or SET type:

Additional notes

Conversion to and from MySQL DECIMAL and NUMERIC.  MySQL decimal types (DECIMAL and NUMERIC) are converted to either of JavaScript String or Number, depending on the value of session.options.decimalType (STRING or NUMBER, respectively). The default behavior is to convert such values to String.

To set this variable on the session level so that MySQL decimal types are converted to Number by default instead, call mle_set_session_state() like this:

mle_set_session_state("decimalType":"NUMBER")

The MLE stored program cache must be empty when this function is invoked; it is not empty, you can clear it using mle_session_reset(). See the description of mle_set_session_state() for more information.

To set the decimalType option within a JavaScript stored routine, use Session.setOptions(), as shown here:

session.setOptions('{"decimalType":"mysql.DecimalType.NUMBER"}')

This sets the default for conversion of MySQL decimal values to Number for the lifetime of the routine. Use mysql.DecimalType.STRING to make String the default.

Rules for conversion of JavaScript values to the MySQL DECIMAL type (or its alias NUMERIC) are shown in the following table:

The maximum which a decimal value can hold is determined by the precision and scale of DECIMAL(M, D), where M is the precision (maximum number of digits) in the range 1-65, and D is scale (the number of digits to the right of the decimal point, with the range 0-30. In addition, M must be greater than or equal to D. (See Section 13.1.3, “Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC”, for more information.)

In the event that a decimal value exceeds the range specified by DECIMAL(M, D) or cannot be stored within the constraints of DECIMAL(M, D), the behavior depends on the server SQL mode (see Section 7.1.11, “Server SQL Modes”), as follows:

Time zone support.  A JavaScript stored program uses the MySQL session timezone in effect at the time it is first invoked. This time zone remains in effect for this stored program for the duration of the session in the session.

Changing the MySQL session time zone is not automatically reflected in stored programs which have been used and thus are already cached. To make them use the new time zone, call mle_session_reset() to clear the cache; after this, stored programs use the new time zone.

Supported time zone types are listed here:

Range checks and invalid type conversion checks are performed following stored program execution. Casting is done inside JavaScript using type constructors such as Number() and String(); rounding to Integer is performed using Math.round().

An input argument (IN or INOUT parameter) named in a JavaScript stored program definition is accessible from within the routine body using the same argument identifier. Output arguments (INOUT and OUT parameters) are also available in JavaScript stored procedures. The same argument identifier can be used to set the value using the JavaScript assignment (=) operator. As with SQL stored procedure OUT arguments, the initial value is set to JavaScript null.

mysql> CREATE FUNCTION myfunc(x INT)
    ->   RETURNS INT LANGUAGE JAVASCRIPT AS
    -> $$
    $>   var x
    $>   
    $>   return 2*x
    $> $$
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT myfunc(10);
ERROR 6000 (HY000): MLE-Type> Cannot convert value 'NaN' to INT 
from MLE in 'myfunc(10)'

The JavaScript return statement should be used to return scalar values in stored functions. In stored procedures, this statement does not return a value, and merely exits the code block (this may or may not also exit the routine depending on program flow). return cannot be used to set stored procedure OUT or INOUT argument values; these must be set explicitly within the routine.

For information about accessing MySQL stored procedures and stored functions from within JavaScript stored routines, see Section 27.3.6.10, “Stored Routine API”.