PDF (US Ltr)
- 1.4Mb
PDF (A4)
- 1.4Mb
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
Press CTRL+C to copyvar 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
Press CTRL+C to copyfrom 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
Press CTRL+C to copyvar 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
Press CTRL+C to copy// 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
Press CTRL+C to copy# 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
Press CTRL+C to copyimport 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
Press CTRL+C to copy#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.