27.3.7.3 Working with Data and Metadata

Result Sets

The function print_result(), shown here, takes a result set (SqlResult) as input:

function print_result(result) {
  if (result.hasData()) {
    console.log(result.getColumnNames())
    console.log(result.getColumns())

    let row = result.fetchOne()

    while(row) {
      console.log(row.toArray())
      row = result.fetchOne()
    }
  } 
  else {
    console.log("Number of affected rows: " + result.getAffectedItemsCount())
    console.log("Last insert ID: " + result.getAutoIncrementValue())
  }

  while(result.nextResult()) {
    console.log("\nNext result set")
    print_result(result)
  }
}

If query is the text of a valid SQL statement, the function can be called like this in the body of a JavaScript stored procedure:

let stmt = session.sql(query);
let res = stmt.execute();

print_result(res);

print_result() prints its output to stdout. This includes the names of the columns in the result set. If the result set is not empty, the contents of each row are printed in the order obtained; otherwise, the function gets the number of rows affected by the statement and the value of the last inserted ID. Finally it checks for multiple result sets using nextResult(), and calls itself for the next result set if there is one.

Metadata

This section demonstrates how to obtain column metadata.

CREATE PROCEDURE jssp_simple_meta(IN query VARCHAR(250))
LANGUAGE JAVASCRIPT AS $$

  let stmt = session.sql(query)
  let result = stmt.execute()

  console.log(result.getColumnNames())

  let cols = result.getColumns()
  let cnt = result.getColumnCount()

  var out = 'COLUMN INFO:'

  for (var i=0; i<cnt; i++) {
    let col = cols[i]

    out += "\nColumn: " + col.getColumnName() + "(" + col.getColumnLabel() + ")"
    out += "; Schema: " + col.getSchemaName()
    out += "; Table: " + col.getTableName() + "(" + col.getTableLabel() + ")"
    out += "; Type: " + col.getType();
  }

  out += "\n"

  console.log(out);

  if (result.hasData()) {
    console.log("ROWS:")
    let row = result.fetchOne()
  
    while(row) {
      console.log(row.toArray())
      row = result.fetchOne()
    }
  }

$$;

Output:

mysql> SELECT mle_session_reset(); 
+------------------------------------------+
| mle_session_reset()                      |
+------------------------------------------+
| The session state is successfully reset. |
+------------------------------------------+
1 row in set (0.01 sec)

mysql> CALL jssp_simple_meta("
    ">   SELECT c.Name, c.LocalName, t.Name AS Capital, c.Population
    ">   FROM country c 
    ">   JOIN countrylanguage l 
    ">   ON c.Code=l.CountryCode 
    ">   JOIN city t 
    ">   ON c.Capital=t.ID
    ">   WHERE l.Language='Swedish'
    "> ");
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT mle_session_state('stdout')\G
*************************** 1. row ***************************
mle_session_state('stdout'): Name,LocalName,Capital,Population
COLUMN INFO:
Column: Name(Name); Schema: world; Table: country(c); Type: STRING
Column: LocalName(LocalName); Schema: world; Table: country(c); Type: STRING
Column: Name(Capital); Schema: world; Table: city(t); Type: STRING
Column: Population(Population); Schema: world; Table: country(c); Type: INT

ROWS:
Denmark,Danmark,København,5330000
Finland,Suomi,Helsinki [Helsingfors],5171300
Norway,Norge,Oslo,4478500
Sweden,Sverige,Stockholm,8861400

1 row in set (0.00 sec)

Error Handling

This section describes basic error handling in MySQL JavaScript stored programs, when using the SQL API.

SQL errors encountered during statement preparation or execution are thrown in JavaScript as exceptions where they can be handled using one or more try ... catch blocks, in which case execution proceeds. If the error is not handled in this way, stored procedure execution halts and produces the original SQL error that was encountered during the SQL query execution inside JavaScript.

Executing SHOW WARNINGS after a JavaScript stored procedure is executed returns the errors or warnings generated by the most recent statement executed inside the procedure.

Some errors cannot be handled in JavaScript. For example, if a query is aborted (CTRL-C), the stored program stops executing immediately and produces an error. Likewise, out of memory errors cannot be handled within JavaScript routines.

An SQL statement that causes errors that are not handled within the stored program passes them back to the client. To observe this, we create a stored procedure using the following SQL statement:

CREATE PROCEDURE jssp_simple_error(IN query VARCHAR(250))
LANGUAGE JAVASCRIPT AS $$
 let session = mysql.getSession()
 var result1 = session.sql("SELECT * FROM t_unknown;").execute()
$$;

Now we call jssp_simple_error(), passing to it a query against a table which we know not to exist, like this:

mysql> CALL jssp_simple_error("SELECT * FROM bogus");
ERROR 1146 (42S02): Table 'test.t_unknown' doesn't exist

You can choose to handle SQL errors in JavaScript instead, using try-catch syntax, like this:

CREATE PROCEDURE jssp_catch_errors(IN query VARCHAR(200))
LANGUAGE JAVASCRIPT AS $$
 try {
  var result = session.sql("SELECT * FROM bogus").execute()
 } catch (e) {
  console.error("\nJS Error:\n" + e.name + ":\n" + e.message)
 }
$$;

Here you can see the result when the query passed to jssp_catch_errors() is one which attempts to access a nonexistent table:

mysql> CALL jssp_catch_errors("SELECT * FROM bogus");
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT mle_session_state('stderr')\G
*************************** 1. row ***************************
mle_session_state('stderr'): 
JS Error: 
org.graalvm.polyglot.nativeapi.PolyglotNativeAPI$CallbackException: 
SQL-CALLOUT: Error code: 1146 Error state: 42S02 Error message: Table 'test.bogus' doesn't exist 

1 row in set (0.00 sec)