5.6 Batch Code Execution

As well as interactive code execution, MySQL Shell provides batch code execution from:

• A file loaded for processing.

• A file containing code that is redirected to the standard input for execution.

• Code from a different source that is redirected to the standard input for execution.

Tip

As an alternative to batch execution of a file, you can also control MySQL Shell from a terminal, see Section 5.8, “API Command Line Integration”.

In batch mode, all the command logic described at Section 5.2, “Interactive Code Execution” is not available, only valid code for the active language can be executed. When processing SQL code, it is executed statement by statement using the following logic: read/process/print result. When processing non-SQL code, it is loaded entirely from the input source and executed as a unit. Use the --interactive (or -i) command-line option to configure MySQL Shell to process the input source as if it were being issued in interactive mode; this enables all the features provided by the Interactive mode to be used in batch processing.

Note

In this case, whatever the source is, it is read line by line and processed using the interactive pipeline.

The input is processed based on the current programming language selected in MySQL Shell, which defaults to JavaScript. You can change the default programming language using the defaultMode MySQL Shell configuration option. Files with the extensions .js, .py, and .sql are always processed in the appropriate language mode, regardless of the default programming language.

This example shows how to load JavaScript code from a file for batch processing:

$> mysqlsh --file code.js

Here, a JavaScript file is redirected to standard input for execution:

$> mysqlsh < code.js

The following example shows how to redirect SQL code to standard input for execution on Linux platforms:

$> echo "show databases;" | mysqlsh --sql --uri user@192.0.2.20:33060

Note

To run this command on Windows platforms, you must remove the quotation marks surrounding the string in the echo command.

The --pym command line option is available to execute the specified Python module as a script in Python mode. The option works in the same way as Python's -m command line option.

Executable Scripts

On Linux you can create executable scripts that run with MySQL Shell by including a #! line as the first line of the script. This line should provide the full path to MySQL Shell and include the --file option. For example:

#!/usr/local/mysql-shell/bin/mysqlsh --file
print("Hello World\n");

The script file must be marked as executable in the filesystem. Running the script invokes MySQL Shell and it executes the contents of the script.

SQL Execution in Scripts

SQL query execution for X Protocol sessions normally uses the sql() function, which takes an SQL statement as a string, and returns a SqlExecute object that you use to bind and execute the query and return the results. This method is described at Using SQL with Session. However, SQL query execution for classic MySQL protocol sessions uses the runSql() function, which takes an SQL statement and its parameters, binds the specified parameters into the specified query and executes the query in a single step, returning the results.

If you need to create a MySQL Shell script that is independent of the protocol used for connecting to the MySQL server, MySQL Shell provides a session.runSql() function for X Protocol, which works in the same way as the runSql() function in classic MySQL protocol sessions. You can use this function in MySQL Shell only in place of sql(), so that your script works with either an X Protocol session or a classic MySQL protocol session. Session.runSql() returns a SqlResult object, which matches the specification of the ClassicResult object returned by the classic MySQL protocol function, so the results can be handled in the same way.

Note

Session.runSql() is exclusive to the MySQL Shell X DevAPI implementation in JavaScript and Python, and is not part of the standard X DevAPI.

To browse the query results, you can use the fetchOneObject() function, which works for both the classic MySQL protocol and X Protocol. This function returns the next result as a scripting object. Column names are used as keys in the dictionary (and as object attributes if they are valid identifiers), and row values are used as attribute values in the dictionary. Updates made to the object are not persisted on the database.

For example, this code in a MySQL Shell script works with either an X Protocol session or a classic MySQL protocol session to retrieve and output the name of a city from the given country:

var resultSet = mySession.runSql("SELECT * FROM city WHERE countrycode = ' AUT'");
var row = resultSet.fetchOneObject();
print(row['Name']);