Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  Connection and Session Concepts  /  Using SQL with Session

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('root: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;
Note

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.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.