27.3.6.1 Session Object

The Session object is always available as the session property of the global object. Session has the methods listed here:

• createSchema(String name): Creates a database having the specified name, and returns the corresponding Schema object.

  Introduced in MySQL 9.2.0.

• dropSchema(String name): Drops the database having the specified name. The return value is undefined.

  Introduced in MySQL 9.2.0.

• getDefaultSchema(): Returns a Schema corresponding to the default (current) database.

  Introduced in MySQL 9.2.0.

• getOption(String name): Gets the value of statement option name. Returns a string or a true-false value depending on the option type.

• getSchema(String name): Gets the Schema object having the supplied name if the corresponding schema exists, otherwise throwing an error.

  Introduced in MySQL 9.2.0.

• getSchemas(): Returns a list of all available Schema objects.

  Introduced in MySQL 9.2.0.

• prepare(String sql, {passResultToClient: Bool, charsetName: String}): Enables the execution of a prepared statements; takes an SQL statement and returns a PreparedStatement object.

  Calling this method is equivalent to executing a PREPARE statement in the mysql client.

• quoteName(String name): Returns name, after escaping it.

• runSql(String query[[, Array statementParameters], Options options]): Executes a query, with any options specified, and using an optional list of statement parameters; it returns an SqlResult.

• setOptions(Object options): Sets statement option defaults. Options not specified assume their default values.

• sql(String sql, {passResultToClient: Bool, charsetName: String, integerType: IntegerType}): Executes a simple SQL statement. Can also be used to provide attributes overriding the passResultlToClient and charsetName values set in the session. Returns an SqlExecute object.

  IntegerType consists of a JSON key-value pair whose key is IntegerType, and whose possible values and their effects are listed here:

  • mysql.IntegerType.BIGINT: Convert all MySQL integer values to JavaScript BigInt.

  • mysql.IntegerType.STRING: Convert all MySQL integer values to JavaScript String

  • mysql.IntegerType.UNSAFE_BIGINT: If the MySQL value is safe, convert it to JavaScript Number; otherwise convert it to JavaScript BigInt. If the value is safe, convert to JavaScript Number; otherwise convert to JavaScript String. This is the default behavior if no rule is specified.

  • mysql.IntegerType.UNSAFE_STRING: If the MySQL value is safe, convert it to JavaScript Number; otherwise convert it to JavaScript String. This is the default behavior if no rule is specified.

  The rule set by this value determines how MySQL integer values are converted to JavaScript by this SQL statement. Their names (less object references) correspond to those for the used with the integer_type key used with mle_set_session_state(). The default behavior is equivalent to having set mysql.IntegerType.UNSAFE_STRING, or called mle_set_session_state('{"integer_type":"STRING"}').

  This option can also be set for a given statement using runSQL() or prepare().

JavaScript transactional functions, supported in MySQL 9.2.0 and later, are also methods of Session. See Section 27.3.6.11, “JavaScript Transaction API” for descriptions and examples.

Accessing Session Variables from JavaScript

Beginning with MySQL 9.2.0, you can access MySQL session variables as properties of the Session object, as shown in this example:

Press CTRL+C to copy
 
mysql> SET @myvar = 27;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE get_session_var() LANGUAGE JAVASCRIPT
    -> AS $$
    $>     console.clear()
    $>     let the_var = session.myvar
    $>     
    $>     console.log("the_var: " + the_var)
    $>     console.log("typeof the_var: " + typeof the_var)
    $> $$;
Query OK, 0 rows affected (0.01 sec)

mysql> CALL get_session_var();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT mle_session_state("stdout")\G
*************************** 1. row ***************************
mle_session_state("stdout"): the_var: 27
typeof the_var: number

mysql> SET @myvar = "Something that is not 27";
Query OK, 0 rows affected (0.00 sec)

mysql> CALL get_session_var();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT mle_session_state("stdout")\G
*************************** 1. row ***************************
mle_session_state("stdout"): the_var: Something that is not 27
typeof the_var: string

1 row in set (0.00 sec)

You can also set session variables by accessing them in the same way, as shown here:

Press CTRL+C to copy
 
mysql> CREATE PROCEDURE set_session_var(IN x INT) LANGUAGE JAVASCRIPT
    -> AS $$
    $>   session.myvar = x
    $> $$;
Query OK, 0 rows affected (0.01 sec)

mysql> CALL set_session_var(72);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @myvar;
+--------+
| @myvar |
+--------+
|     72 |
+--------+
1 row in set (0.00 sec)

Session variables accessed as Session properties in JavaScript are created automatically if they do not already exist, as shown in this example:

Press CTRL+C to copy
 
mysql> CREATE PROCEDURE set_any_var(IN name VARCHAR, IN val INT)
    ->   LANGUAGE JAVASCRIPT
    -> AS $$
    $>   session[name] = val
    $> $$;
Query OK, 0 rows affected (0.00 sec)

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

mysql> CALL set_any_var("myvar", 25);
Query OK, 0 rows affected (0.01 sec)

mysql> CALL set_any_var("yourvar", 100);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @myvar, @yourvar;
+--------+----------+
| @myvar | @yourvar |
+--------+----------+
|     25 |      100 |
+--------+----------+
1 row in set (0.00 sec)

Rules for type conversion from MySQL session variables to JavaScript variables are shown in the following table:

MySQL type	JavaScript type	Comments
NULL	null	-
BIGINT	Number, String, or BigInt	Depends on session.sql() method integerType option value
DECIMAL	-	Error: Unsupported type
DOUBLE	Number	-
Binary string	Uint8Array	-
String	String	-

Rules for type conversion from JavaScript variables to MySQL session variables are shown in the following table:

JavaScript type	MySQL type	Comment
null or undefined	NULL	-
Boolean	BIGINT	-
Number	BIGINT, DECIMAL, or DOUBLE	-
Infinity, NaN, or Symbol	-	Error: Type cannot be used for session variables
String	string	-
BigInt	BIGINT	-
TypedArray or Float32Array	BINARY	-
Object	string	-
Array	string	-