27.3.4 JavaScript Stored Program Data Types and Argument Handling

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:

• Integer: All variants and aliases of MySQL integer data types are supported, including TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

  SIGNED and UNSIGNED are supported for all these types.

  BOOL and SERIAL are also supported, and treated as integer types.

• String: The CHAR, VARCHAR, TEXT, and BLOB string types are supported.

  These types are supported as in the MySQL server with the following exceptions:

  1. String argument and return types must use the utf8mb4 character set; use of any other character set for these raises an error. This restriction applies to argument and return type declarations; the server attempts to convert argument values using other character sets to utfmb4 whenever necessary, as with SQL stored programs.

  2. The maximum supported length for a LONGTEXT value is 1073741799 characters.

  Support for BLOB types includes support for BINARY and VARBINARY.

  The MySQL JSON data type is also supported.

• Floating point: FLOAT and DOUBLE are supported along with their aliases. REAL is also treated as floating point, but UNSIGNED FLOAT and UNSIGNED DOUBLE are deprecated in MySQL, and are not supported by MLE.

• Temporal types: DATE, DATETIME, and TIMESTAMP are supported, and are converted to JavaScript Date values. TIME values are treated as strings; YEAR values are treated as numbers.

  The first time a given JavaScript stored procedure is executed, it is associated with the current MySQL session time zone, and this time zone continues to be used by the stored program, even if the MySQL session time zone is changed concurrently, for the duration of the MLE component session, or until mle_session_reset() is invoked. More more information, see Time zone support, later in this section.

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

Table 27.1 Type Conversion: MySQL to JavaScript

MySQL Type	JavaScript Type
TINYINT, SMALLINT, MEDIUMINT, INT, or BOOL	Number
BIGINT or SERIAL	BigInteger
FLOAT or DOUBLE	Number
CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT	String
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, BINARY, or VARBINARY	Uint8Array
DATE, DATETIME, or TIMESTAMP	Date
TIME	String
YEAR	Number

Conversion to or from a MySQL integer whose value lies outside the range -(2⁵³-1) (-9007199254740991) to 2⁵³-1 (9007199254740991) is lossy.

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:

Table 27.2 Type Conversion: JavaScript to MySQL

From JavaScript Type	To MySQL TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, BOOLEAN, or SERIAL	To MySQL CHAR or VARCHAR	To MySQL FLOAT or DOUBLE	To MySQL TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT	To MySQL MySQL TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, BINARY, VARBINARY
Boolean	Cast to Integer	Convert to string; check whether length of result is within expected range	Cast to Float	If JavaScript Boolean true: convert to “true”; If JavaScript Boolean false: convert to “false”	Error
Number	Round value to Integer; check whether value is out of range [a] [b] [c]	Convert to String; check whether length of result is within expected range	Retain value; check whether this is out of range [a] [b]	Convert to String; check whether length of result is within expected range	Error
BigInteger	Retain value; check whether out of range [a] [b]	Convert to String; check whether length of result is within expected range	Cast to Float; check whether result is out of range [d]	Convert to String; check whether length of result is within expected range	Error
String	Parse as number and round to Integer; check for out value of range	Retain value; check whether length is within range	Parse value to Float, check for value out of range values [d]	Use existing string value; check whether length of string is within expected range	Error
Symbol or Object	Raise invalid type conversion error	Convert to String; check whether length of result is within expected range	Raise invalid type conversion error	Convert to String; check whether length of result is within expected range [e]	Error
Array	Raise invalid type conversion error	Convert to String; check whether length of result is within expected range	Raise invalid type conversion error	Convert to String; check whether length of result is within expected range [e]	Convert to byte array; check whether result is within expected size
null or undefined	NULL	NULL	NULL	NULL	NULL

^[a] JavaScript Infinity and -Infinity are treated as out-of-range values.

^[b] JavaScript NaN raises an invalid type conversion error.

^[c] This is done using Math.round().

^[d] A non-numeric value raises an invalid type conversion error

^[e] Maximum supported string length is 1073741799

Table 27.3 Type Conversion: JavaScript Dates to MySQL

JavaScript Type	MySQL DATE	MySQL DATETIME, TIMESTAMP	MySQL YEAR
null or undefined	NULL	NULL	NULL
Date	Retain value as is, rounding off any time part to the closest second.	Keep value as is.	Extract year from the Date
Type convertible to JavaScript Date (formatted string)	Cast value to JavaScript Date and handle accordingly	Cast value to JavaScript Date and handle accordingly	If value contains 4-digit year, use it.
Type not convertible to JavaScript Date	Invalid type conversion error	Invalid type conversion error	If value contains 4-digit year, use it.

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.

Table 27.4 Type Conversion: MySQL JSON to JavaScript

MySQL JSON Type	JavaScript Type
NULL, JSON NULL	null
JSON OBJECT	Object
JSON ARRAY	Array
JSON BOOLEAN	Boolean
JSON INTEGER, JSON DOUBLE, JSON DECIMAL	Number
JSON STRING	String [a]
JSON DATETIME, JSON DATE, JSON TIME	String
JSON BLOB, JSON OPAQUE	String

^[a] A MySQL JSON string, when converted to a Javascript string, becomes unquoted.

Table 27.5 Type Conversion: JavaScript to MySQL JSON

JavaScript Type	MySQL JSON Type
null, undefined	NULL
Boolean	Error [a]
Number	Error [a]
String	Can be parsed as JSON: JSON string, JSON object, or JSON array Cannot be parsed as JSON: Error 'null': JSON null
BigInt	Error [b]
Object	JSON object or error (see text following table)
Array	JSON array
Symbol	Inside an object: ignored Inside an array: JSON null Scalar value: Error

^[a] A value within a container such as a JSON array or JSON object is converted (loss of precision is possible for Number values). A scalar value throws an error.

^[b] JavaScript BigInt values cannot be converted to MySQL JSON; attempting to perform such a conversion always raises an error, regardless of whether the value is inside a container or not.

It may or may not be possible to convert a Javascript Object to MySQL JSON, depending on how toJSON() is implemented for the object in question. Some examples are listed here:

• The toJSON() method of the JavaScript Date class converts a Date to a string having invalid JSON syntax, thus throwing a conversion error.

  For the Set class, toJSON() returns "{}" which is a valid JSON string.

  For JSON-like objects, toJSON() returns a valid JSON string.

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:

• Time zone offsets from UTC, such as +11:00 or -07:15.

• Timezones defined in the IANA time zone database are supported, with the exception of configurations using leap seconds. For example, Pacific/Nauru, Japan, and MET are supported, while leap/Pacific/Nauru and right/Pacific/Nauru are not.

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.

Caution

You should not override program arguments using let, var, or const inside JavaScript stored programs. Doing so turns them into variables which are local to the program, and makes any values passed into the program using the same-named parameters inaccessible.

Example:

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.