Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  ...  /  Using SQL with NodeSession

Pre-General Availability Draft: 2017-12-04

2.5.1 Using SQL with NodeSession

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

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

MySQL Shell JavaScript Code

// Connect to server using a NodeSession
var mySession = mysqlx.getNodeSession('mike:s3cr3t!@localhost');

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

// In a NodeSession 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

import mysqlx

# Connect to server using a NodeSession
mySession = mysqlx.get_node_session('mike:s3cr3t!@localhost')

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

# In a NodeSession 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('mysqlx');

// Connect to server using a Low-Level Session
mysqlx.getSession('root:s3kr3t@localhost').then(function(session) {
  return session.getSchema('test');
}).then(function(session) {
  return Promise.all([
    // Switch to use schema 'test'
    session.executeSql("USE test").execute(),
    // In a Session context the full SQL language can be used
    session.executeSql("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.executeSql("CALL my_add_one_procedure(@my_var);").execute(),
    session.executeSql("DROP PROCEDURE my_add_one_procedure;").execute()
  ]).then(function() {
    // Use an SQL query to get the result
    return session.executeSql("SELECT @my_var");
  }).then(function(my_result) {
    // Print result
    console.log(my_result);
  });
});

C# Code

// Connect to server using a NodeSession
var mySession = MySQLX.GetNodeSession("server=localhost;port=33060;user=mike;password=s3cr3t!;");

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

// In a NodeSession 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();

Java Code

import com.mysql.cj.api.xdevapi.*;
import com.mysql.cj.xdevapi.*;

// Connect to server on localhost
NodeSession mySession = new XSessionFactory().getNodeSession("mysqlx://localhost:33060/test?user=mike&password=s3cr3t!");

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

// In a NodeSession 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 <mysql_devapi.h>

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

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

// In a NodeSession 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

As mentioned previously, literal or verbatim SQL can only be issued when connected to one node. Only the NodeSession class includes a function sql(). The XSession class does not because it encapsulates a connection to many nodes. When connected to multiple MySQL Servers, additional tasks arise over the single node case. Among these tasks is failover. The standard query language allows users to establish a stateful connection. Transparently failing over a stateful connection is a difficult and complex task. The X DevAPI XSession class implements a significant subset of the SQL language through API calls. The API calls offered do not allow building a connection state. This is a key enabler for automatic and transparent failover. Future versions of the X DevAPI might support literal SQL when working with the XSession class. Note that you are not prevented from using the full power of SQL but literal or verbatim SQL is only available with NodeSession based connections.

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
Sign Up Login You must be logged in to post a comment.