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.
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 Interface”.
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
-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.
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
change the default programming language using the
defaultMode MySQL Shell configuration option.
Files with the extensions
.sql are always
processed in the appropriate language mode, regardless of the
default programming language.
shell> mysqlsh --file code.js
shell> mysqlsh < code.js
This example shows how to redirect SQL code to standard input for execution:
shell> echo "show databases;" | mysqlsh --sql --uri firstname.lastname@example.org:33060
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 query execution for X Protocol sessions normally uses
sql() function, which takes a 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 a 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
function for X Protocol, which works in the same way as
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
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
MySQL Shell 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']);