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 currentSection 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 tofind() is followed by theexecute() function. CRUD database commands are only actually executed on the MySQL Server whenexecute() is called. However, when working with MySQL Shell interactively,execute() is implicitly called whenever you pressReturn on a statement. Then the results of the operation are fetched and displayed on-screen. The rules for when you need to callexecute() 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
Resultobjects, 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
mysqlandmysqlxmodules: -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 toexecute() is needed and the Result object is automatically printed.
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... 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.