Documentation Home
X DevAPI User Guide
Download this Manual
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb
EPUB - 0.7Mb
HTML Download (TGZ) - 0.6Mb
HTML Download (Zip) - 0.7Mb


X DevAPI User Guide  /  Working with Relational Tables

Beta Draft: 2017-03-17

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
Sign Up Login You must be logged in to post a comment.