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

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

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

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

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");

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

# 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

Java does not currently support the quoteName() method.

C++ Code

#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.