Documentation Home
X DevAPI User Guide
Download this Manual
PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb


2.6 Dynamic SQL

A quoting function exists to escape SQL names and identifiers. Session.quoteName() escapes the identifier given in accordance to the settings of the current connection.

Note

The quoting function must not be used to escape values. Use the value binding syntax of Session.sql() instead; see Section 2.4, “Using SQL with Session” for some examples.

MySQL Shell JavaScript Code

Press CTRL+C to copy
function createTestTable(session, name) { // use escape function to quote names/identifier quoted_name = session.quoteName(name); session.sql("DROP TABLE IF EXISTS " + quoted_name).execute(); var create = "CREATE TABLE "; create += quoted_name; create += " (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)"; session.sql(create).execute(); return session.getCurrentSchema().getTable(name); } var mysqlx = require('mysqlx'); var session = mysqlx.getSession('user:password@localhost:33060/test'); var default_schema = session.getDefaultSchema().name; session.setCurrentSchema(default_schema); // Creates some tables var table1 = createTestTable(session, 'test1'); var table2 = createTestTable(session, 'test2');

MySQL Shell Python Code

Press CTRL+C to copy
def createTestTable(session, name): # use escape function to quote names/identifier quoted_name = session.quote_name(name) session.sql("DROP TABLE IF EXISTS " + quoted_name).execute() create = "CREATE TABLE " create += quoted_name create += " (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)" session.sql(create).execute() return session.get_current_schema().get_table(name) from mysqlsh import mysqlx session = mysqlx.get_session('user:password@localhost:33060/test') default_schema = session.get_default_schema().name session.set_current_schema(default_schema) # Creates some tables table1 = createTestTable(session, 'test1') table2 = createTestTable(session, 'test2')

Node.js JavaScript Code

Press CTRL+C to copy
var mysqlx = require('mysqlx'); function createTestTable(session, name) { var create = 'CREATE TABLE '; create += name; create += ' (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)'; return session .sql('DROP TABLE IF EXISTS ' + name) .execute() .then(function () { return session.sql(create).execute(); }); } var session; mysqlx .getSession({ user: 'user', password: 'password' }) .then(function (s) { session = s; return session .sql('use myschema') .execute() }) .then(function () { // Creates some tables return Promise.map([ createTestTable(session, 'test1'), createTestTable(session, 'test2') ]) }) .then(function () { session.close(); }) });

C# Code

Press CTRL+C to copy
var session = MySQLX.GetSession("server=localhost;port=33060;user=user;password=password;"); session.SQL("use test;").Execute(); session.GetSchema("test"); // Creates some tables var table1 = CreateTestTable(session, "test1"); var table2 = CreateTestTable(session, "test2");
Press CTRL+C to copy
private Table CreateTestTable(Session session, string name) { // use escape function to quote names/identifier string quoted_name = "`" + name + "`"; session.SQL("DROP TABLE IF EXISTS " + quoted_name).Execute(); var create = "CREATE TABLE "; create += quoted_name; create += " (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)"; session.SQL(create).Execute(); return session.Schema.GetTable(name); }

Python Code

Press CTRL+C to copy
# Connector/Python def createTestTable(session, name): # use escape function to quote names/identifier quoted_name = session.quote_name(name) session.sql("DROP TABLE IF EXISTS " + quoted_name).execute() create = "CREATE TABLE " create += quoted_name create += " (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)" session.sql(create).execute() return session.get_current_schema().get_table(name) from mysqlsh import mysqlx session = mysqlx.get_session('user:password@localhost:33060/test') default_schema = session.get_default_schema().name session.set_current_schema(default_schema) # Creates some tables table1 = createTestTable(session, 'test1') table2 = createTestTable(session, 'test2')

Java Code

Press CTRL+C to copy
Java does not currently support the quoteName() method.

C++ Code

Press CTRL+C to copy
#include <mysqlx/xdevapi.h> // Note: The following features are not yet implemented by // Connector/C++: // - DataSoure configuration files, // - quoteName() method. Table createTestTable(Session &session, const string &name) { string quoted_name = string("`") + session.getDefaultSchemaName() + L"`.`" + name + L"`"; session.sql(string("DROP TABLE IF EXISTS") + quoted_name).execute(); string create = "CREATE TABLE "; create += quoted_name; create += L"(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)"; session.sql(create).execute(); return session.getDefaultSchema().getTable(name); } Session session(33060, "user", "password"); Table table1 = createTestTable(session, "test1"); Table table2 = createTestTable(session, "test2");

Code that uses X DevAPI does not need to escape identifiers. This is true for working with collections and for working with relational tables.