2.4 Using SQL with Session

In addition to the simplified X DevAPI syntax of the Session object, the Session object has a sql() function that takes any SQL statement as a string.

The following example uses a Session to call an SQL Stored Procedure on the specific node.

MySQL Shell JavaScript Code

var mysqlx = require('mysqlx');

// Connect to server using a Session
var mySession = mysqlx.getSession('user:password@localhost');

// Switch to use schema 'test'
mySession.sql("USE test").execute();

// In a Session context the full SQL language can be used
mySession.sql("CREATE PROCEDURE my_add_one_procedure " +
  " (INOUT incr_param INT) " +
  "BEGIN " +
  "  SET incr_param = incr_param + 1;" +
  "END;").execute();
mySession.sql("SET @my_var = ?;").bind(10).execute();
mySession.sql("CALL my_add_one_procedure(@my_var);").execute();
mySession.sql("DROP PROCEDURE my_add_one_procedure;").execute();

// Use an SQL query to get the result
var myResult = mySession.sql("SELECT @my_var").execute();

// Gets the row and prints the first column
var row = myResult.fetchOne();
print(row[0]);

mySession.close();

MySQL Shell Python Code

from mysqlsh import mysqlx

# Connect to server using a Session
mySession = mysqlx.get_session('user:password@localhost')

# Switch to use schema 'test'
mySession.sql("USE test").execute()

# In a Session context the full SQL language can be used
sql = """CREATE PROCEDURE my_add_one_procedure
                                 (INOUT incr_param INT)
                                 BEGIN
                                         SET incr_param = incr_param + 1;
                                 END
                        """

mySession.sql(sql).execute()
mySession.sql("SET @my_var = ?").bind(10).execute()
mySession.sql("CALL my_add_one_procedure(@my_var)").execute()
mySession.sql("DROP PROCEDURE my_add_one_procedure").execute()

# Use an SQL query to get the result
myResult = mySession.sql("SELECT @my_var").execute()

# Gets the row and prints the first column
row = myResult.fetch_one()
print(row[0])

mySession.close()

Node.js JavaScript Code

var mysqlx = require('@mysql/xdevapi');
var session;

// Connect to server using a Low-Level Session
mysqlx
  .getSession('user:password@localhost')
  .then(function (s) {
    session = s;

    return session.getSchema('test');
  })
  .then(function () {
    return Promise.all([
      // Switch to use schema 'test'
      session.sql('USE test').execute(),
      // In a Session context the full SQL language can be used
      session.sql('CREATE PROCEDURE my_add_one_procedure' +
        ' (INOUT incr_param INT) ' +
        'BEGIN ' +
        '  SET incr_param = incr_param + 1;' +
        'END;').execute(),
      session.executeSql('SET @my_var = ?;', 10).execute(),
      session.sql('CALL my_add_one_procedure(@my_var);').execute(),
      session.sql('DROP PROCEDURE my_add_one_procedure;').execute()
    ])
  })
  .then(function() {
    // Use an SQL query to get the result
    return session.sql('SELECT @my_var').execute(function (row) {
      // Print result
      console.log(row);
    });
  });

C# Code

// Connect to server using a Session
var mySession = MySQLX.GetSession("server=localhost;port=33060;user=user;password=password;");

// Switch to use schema "test"
mySession.SQL("USE test").Execute();

// In a Session context the full SQL language can be used
mySession.SQL("CREATE PROCEDURE my_add_one_procedure " +
      " (INOUT incr_param INT) " +
      "BEGIN " +
      "  SET incr_param = incr_param + 1;" +
      "END;").Execute();
mySession.SQL("SET @my_var = 10;").Execute();
mySession.SQL("CALL my_add_one_procedure(@my_var);").Execute();
mySession.SQL("DROP PROCEDURE my_add_one_procedure;").Execute();

// Use an SQL query to get the result
var myResult = mySession.SQL("SELECT @my_var").Execute();

// Gets the row and prints the first column
var row = myResult.FetchOne();
Console.WriteLine(row[0]);

mySession.Close();

Python Code

# Connector/Python
from mysqlsh import mysqlx

# Connect to server using a Session
mySession = mysqlx.get_session('user:password@localhost')

# Switch to use schema 'test'
mySession.sql("USE test").execute()

# In a Session context the full SQL language can be used
sql = """CREATE PROCEDURE my_add_one_procedure
                                 (INOUT incr_param INT)
                                 BEGIN
                                         SET incr_param = incr_param + 1;
                                 END
                        """

mySession.sql(sql).execute()
mySession.sql("SET @my_var = ?").bind(10).execute()
mySession.sql("CALL my_add_one_procedure(@my_var)").execute()
mySession.sql("DROP PROCEDURE my_add_one_procedure").execute()

# Use an SQL query to get the result
myResult = mySession.sql("SELECT @my_var").execute()

# Gets the row and prints the first column
row = myResult.fetch_one()
print(row[0])

mySession.close()

Java Code

import com.mysql.cj.xdevapi.*;

// Connect to server on localhost
Session mySession = new SessionFactory().getSession("mysqlx://localhost:33060/test?user=user&password=password");

// Switch to use schema 'test'
mySession.sql("USE test").execute();

// In a Session context the full SQL language can be used
mySession.sql("CREATE PROCEDURE my_add_one_procedure " + " (INOUT incr_param INT) " + "BEGIN " + "  SET incr_param = incr_param + 1;" + "END")
        .execute();
mySession.sql("SET @my_var = ?").bind(10).execute();
mySession.sql("CALL my_add_one_procedure(@my_var)").execute();
mySession.sql("DROP PROCEDURE my_add_one_procedure").execute();

// Use an SQL query to get the result
SqlResult myResult = mySession.sql("SELECT @my_var").execute();

// Gets the row and prints the first column
Row row = myResult.fetchOne();
System.out.println(row.getInt(0));

mySession.close();

C++ Code

#include <mysqlx/xdevapi.h>

// Connect to server on localhost
string url = "mysqlx://localhost:33060/test?user=user&password=password";
Session mySession(url);

// Switch to use schema 'test'
mySession.sql("USE test").execute();

// In a Session context the full SQL language can be used
mySession.sql("CREATE PROCEDURE my_add_one_procedure "
              " (INOUT incr_param INT) "
              "BEGIN "
              "  SET incr_param = incr_param + 1;"
              "END;")
         .execute();
mySession.sql("SET @my_var = ?;").bind(10).execute();
mySession.sql("CALL my_add_one_procedure(@my_var);").execute();
mySession.sql("DROP PROCEDURE my_add_one_procedure;").execute();

// Use an SQL query to get the result
auto myResult = mySession.sql("SELECT @my_var").execute();

// Gets the row and prints the first column
Row row = myResult.fetchOne();
cout << row[0] << endl;

When using literal/verbatim SQL the common API patterns are mostly the same compared to using DML and CRUD operations on Tables and Collections. Two differences exist: setting the current schema and escaping names.