Documentation Home
X DevAPI User Guide
Download this Manual

Pre-General Availability Draft: 2017-06-23

2.5.3 Dynamic SQL

A quoting function exists to escape SQL names and identifiers. NodeSession.quoteName() escapes the identifier given in accordance to the settings of the current connection. The escape function must not be used to escape values. Use the value bind syntax of NodeSession.sql() instead.

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.getNodeSession('mike:s3cr3t!@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)

import mysqlx

session = mysqlx.get_node_session('mike:s3cr3t!@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

function createTestTable(session, name) {

  // use escape function to quote names/identifier
  quoted_name = session.quoteName(name);

  session.executeSql("DROP TABLE IF EXISTS " + quoted_name).execute();

  var create = "CREATE TABLE ";
  create += quoted_name;
  create += " (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)";

  session.executeSql(create).execute();

  return session.getCurrentSchema().getTable(name);
}

var mysqlx = require('mysqlx');

mysqlx.getNodeSession({
  dataSourceFile: 'mysqlxconfig.json', app: 'myapp',
  dbUser: 'mike', dbPassword: 's3cr3t!'
}).then(function (session) {

  session.executeSql("use myschema").execute();
  var default_schema = session.getDefaultSchema().name;
  session.setCurrentSchema(default_schema);

  // Creates some tables
  var table1 = createTestTable(session, 'test1');
  var table2 = createTestTable(session, 'test2');

  session.close();
});

C# Code

var session = MySQLX.GetNodeSession("server=localhost;port=33060;user=mike;password=s3cr3t!;");

session.SQL("use test;").Execute();
session.GetSchema("test");

// Creates some tables
var table1 = CreateTestTable(session, "test1");
var table2 = CreateTestTable(session, "test2");

Java Code

Java does not currently support the quoteName() method.

C++ Code

#include <mysql_devapi.h>

// Note: The following features are not yet implemented by
// Connector/C++:
// - DataSoure configuration files,
// - quoteName() method.

Table createTestTable(NodeSession &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);
}

NodeSession session(33060, "mike", "s3cr3t!");

Table table1 = createTestTable(session, "test1");
Table table2 = createTestTable(session, "test2");

Users of the X DevAPI do not need to escape identifiers. This is true for working with collections and for working with relational tables.


User Comments
Sign Up Login You must be logged in to post a comment.