Movatterモバイル変換

The functionprint_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)  }}

Ifquery 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 tostdout. 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 usingnextResult(), and calls itself for the next result set if there is one.

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,PopulationCOLUMN INFO:Column: Name(Name); Schema: world; Table: country(c); Type: STRINGColumn: LocalName(LocalName); Schema: world; Table: country(c); Type: STRINGColumn: Name(Capital); Schema: world; Table: city(t); Type: STRINGColumn: Population(Population); Schema: world; Table: country(c); Type: INTROWS:Denmark,Danmark,København,5330000Finland,Suomi,Helsinki [Helsingfors],5171300Norway,Norge,Oslo,4478500Sweden,Sverige,Stockholm,88614001 row in set (0.00 sec)

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 moretry ... 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.

ExecutingSHOW 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 calljssp_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 tojssp_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)