5.2 Interactive Code Execution

The default mode of MySQL Shell provides interactive execution of database operations that you type at the command prompt. These operations can be written in JavaScript, Python or SQL depending on the current Section 5.1, “Active Language”. When executed, the results of the operation are displayed on-screen.

As with any other language interpreter, MySQL Shell is very strict regarding syntax. For example, the following JavaScript snippet opens a session to a MySQL server, then reads and prints the documents in a collection:

var mySession = mysqlx.getSession('user:pwd@localhost');
var result = mySession.getSchema('world_x').getCollection('countryinfo').find().execute();
var record = result.fetchOne();
while(record){
  print(record);
  record = result.fetchOne();
}

As seen above, the call to find() is followed by the execute() function. CRUD database commands are only actually executed on the MySQL Server when execute() is called. However, when working with MySQL Shell interactively, execute() is implicitly called whenever you press Return on a statement. Then the results of the operation are fetched and displayed on-screen. The rules for when you need to call execute() or not are as follows:

• When using MySQL Shell in this way, calling execute() becomes optional on:

  • Collection.add()

  • Collection.find()

  • Collection.remove()

  • Collection.modify()

  • Table.insert()

  • Table.select()

  • Table.delete()

  • Table.update()

• Automatic execution is disabled if the object is assigned to a variable. In such a case calling execute() is mandatory to perform the operation.

• When a line is processed and the function returns any of the available Result objects, the information contained in the Result object is automatically displayed on screen. The functions that return a Result object include:

  • The SQL execution and CRUD operations (listed above)

  • Transaction handling and drop functions of the session objects in both mysql and mysqlx modules: -

    • startTransaction()

    • commit()

    • rollback()

    • dropSchema()

    • dropCollection()

    • ClassicSession.runSql()

Based on the above rules, the statements needed in the MySQL Shell in interactive mode to establish a session, query, and print the documents in a collection are as follows:

mysql-js> var mySession = mysqlx.getSession('user:pwd@localhost');
mysql-js> mySession.getSchema('world_x').getCollection('countryinfo').find();

No call to execute() is needed and the Result object is automatically printed.

Multiple-line Support

It is possible to specify statements over multiple lines. When in Python or JavaScript mode, multiple-line mode is automatically enabled when a block of statements starts like in function definitions, if/then statements, for loops, and so on. In SQL mode multiple line mode starts when the command \ is issued.

Once multiple-line mode is started, the subsequently entered statements are cached.

For example:

mysql-sql> \
... create procedure get_actors()
... begin
...   select first_name from sakila.actor;
... end
...

Note

You cannot use multiple-line mode when you use the \sql command with a query to execute single SQL statements while another language is active. The command only accepts a single SQL query on a single line.