Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  Working with Relational Tables

Pre-General Availability Draft: 2017-12-04

Chapter 6 Working with Relational Tables

Table of Contents     [+/-]

6.1 SQL CRUD Functions

This section explains how to use X DevAPI SQL CRUD functions to work with relational tables.

The following example code compares the operations previously shown for collections and how they can be used to work with relational tables using SQL. The simplified X DevAPI syntax is demonstrated using SQL in a Session and showing how it is similar to working with documents.

MySQL Shell JavaScript Code

// Working with Relational Tables
var mysqlx = require('mysqlx');

// Connect to server using a connection URL
var mySession = mysqlx.getNodeSession( {
  host: 'localhost', port: 33060,
  dbUser: 'mike', dbPassword: 's3cr3t!'} )
  
var myDb = mySession.getSchema('test');

// Accessing an existing table
var myTable = myDb.getTable('my_table');

// Insert SQL Table data
myTable.insert(['name', 'birthday', 'age']).
  values('Sakila', mysqlx.dateValue(2000, 5, 27), 16).execute();

// Find a row in the SQL Table
var myResult = myTable.select(['_id', 'name', 'birthday']).
  where('name like :name AND age < :age').
  bind('name', 'S%').bind('age', 20).execute();

// Print result
print(myResult.fetchOne());

MySQL Shell Python Code

# Working with Relational Tables
import mysqlx

# Connect to server using a connection URL
mySession = mysqlx.get_node_session( {
  'host': 'localhost', 'port': 33060,
  'dbUser': 'mike', 'dbPassword': 's3cr3t!'} )

myDb = mySession.get_schema('test')

# Accessing an existing table
myTable = myDb.get_table('my_table')

# Insert SQL Table data
myTable.insert(['name','birthday','age']) \
  .values('Sakila', mysqlx.date_value(2000, 5, 27), 16).execute()

# Find a row in the SQL Table
myResult = myTable.select(['_id', 'name', 'birthday']) \
  .where('name like :name AND age < :age') \
  .bind('name', 'S%') \
  .bind('age', 20).execute()

# Print result
print myResult.fetch_all()

Node.js JavaScript Code

// Working with Relational Tables
var mysqlx = require('mysqlx');

// Connect to server using a connection URL
mysqlx.getSession({
  host: 'localhost', port: 33060,
  dbUser: 'mike', dbPassword: 's3cr3t!'
}).then(function (session) {
  var db = session.getSchema('test');

  // Accessing an existing table
  var myTable = db.getTable('my_table');

  // Insert SQL Table data
  myTable.insert(['name', 'birthday', 'age']).
    values('Sakila', mysqlx.dateValue(2000, 5, 27), 16).execute();

  // Find a row in the SQL Table
  var myResult = myTable.select(['_id', 'name', 'birthday']).
    where('name like :name AND age < :age').
    bind('name', 'S%').bind('age', 20).execute(function (row) {
      console.log(row);
  });

C# Code

{
  // Working with Relational Tables

  // Connect to server using a connection
  var db = MySQLX.GetSession("server=localhost;port=33060;user=mike;password=s3cr3t!;")
	.GetSchema("test");

  // Accessing an existing table
  var myTable = db.GetTable("my_table");

  // Insert SQL Table data
  myTable.Insert("name", "age")
	.Values("Sakila", "19").Execute();

  // Find a row in the SQL Table
  var myResult = myTable.Select("_id, name, age")
	.Where("name like :name AND age < :age")
	.Bind(new { name = "S%", age = 20 }).Execute();

  // Print result
  PrintResult(myResult.FetchAll());
}

Java Code

// Working with Relational Tables
import com.mysql.cj.api.xdevapi.*;
import com.mysql.cj.xdevapi.*;

// Connect to server using a connection URL
XSession mySession = new XSessionFactory().getSession("mysqlx://localhost:33060/test?user=mike&password=s3cr3t!");
Schema db = mySession.getSchema("test");

// Accessing an existing table
Table myTable = db.getTable("my_table");

// Insert SQL Table data
myTable.insert("name", "birthday").values("Sakila", "2000-05-27").execute();

// Find a row in the SQL Table
RowResult myResult = myTable.select("_id, name, birthday")
  .where("name like :name AND age < :age")
  .bind("name", "S*").bind("age", 20).execute();

// Print result
System.out.println(myResult.fetchAll());

C++ Code

// Working with Relational Tables
#include <mysql_devapi.h>

// Connect to server using a connection URL
XSession mySession(33060, "mike", "s3cr3t!");

Schema myDb = mySession.getSchema("test");

// Accessing an existing table
Table myTable = myDb.getTable("my_table");

// Insert SQL Table data
myTable.insert("name", "birthday", "age")
       .values("Sakila", "2000-5-27", 16).execute();

// Find a row in the SQL Table
RowResult myResult = myTable.select("_id", "name", "birthday")
  .where("name like :name AND age < :age")
  .bind("name", "S%").bind("age", 20).execute();

// Print result
Row row = myResult.fetchOne();
cout << "     _id: " << row[0] << endl;
cout << "    name: " << row[1] << endl;
cout << "birthday: " << row[2] << endl;

User Comments
  Posted by Andy Fusniak on October 27, 2017
I've had no luck running most of the Node.js examples from any chapter in this document, with the exception of a small few that I had to modify. The code is all untested.

After having problems with Collections I decide I would try the Tables API. I couldn't get the Node 8.0.8 connector to work with relational tables running Mac OS X Mysql server 5.7.20-log

Confusingly some of the interface behaves asynchronously returning promises and some API calls work synchronously. When I tried to do an insert using an mysqlx.expr('NOW()') I got the error shown below. This appears to be way down the stack and yes I did read the code for the connector itself. Perhaps this library is being tested against Mysql 8?

I was quite excited when I first found the X Dev API Connectors, thinking the async nature would closely match Node.js, and that it would be supported by this programming language first, given that the Collections uses JSON objects and JSON and objects are native to JS.

I think this is in its early stages and needs better documentation. I would suggest the manual has separate sections for Java, Node, Python etc instead of having multiple code examples on the same page. I found it very hard to read.

I'm sure the X Dev API Connector for Node.js will be great someday, but not unless it gets comprehensive documentation with tested practical examples and tutorials. I've given up on it for now and going to use the npm mysql module and write Promise wrappers around callbacks.

_______

Error: Parse error unserializing protobuf message
at SqlResultHandler.ResponseHandler.(anonymous function) (node_modules/@mysql/xdevapi/lib/Protocol/ResponseHandler/ResponseHandler.js:111:19)
at Array.entry (node_modules/@mysql/xdevapi/lib/Protocol/ResponseHandler/ResponseHandler.js:86:36)
at WorkQueue.process (node_modules/@mysql/xdevapi/lib/WorkQueue.js:71:19)
at Client.handleServerMessage (node_modules/@mysql/xdevapi/lib/Protocol/Client.js:189:25)
at Client.handleNetworkFragment (node_modules/@mysql/xdevapi/lib/Protocol/Client.js:233:18)
at Socket.stream.on.data (node_modules/@mysql/xdevapi/lib/Protocol/Client.js:57:36)
at addChunk (_stream_readable.js:263:12)
at readableAddChunk (_stream_readable.js:250:11)
at Socket.Readable.push (_stream_readable.js:208:10)
at TCP.onread (net.js:597:20)
Sign Up Login You must be logged in to post a comment.