Compressed SQL Statements with EMySQL API
Using the EMySQL API – Part 12
Foreword: In this part of the series I explain how to use EMySQL API to compress a SQL statement and send to the MySQL server.
By: Date Published: 24 Oct 2017
Introduction
Compressing an INSERT Statement
The following code compresses an INSERT statement and send to MySQL server:
const mysql = require('./server/Mysql.js');
con = new mysql.Connection("root", "secret", "localhost", 3306, function(err)
{
if (err)
console.log(err);
}, 'compress');
var db = `PetStore`;
con.selectDB(db, function(err)
{
if (err)
console.log(err);
else
console.log('database selected');
});
var insRowStr = `INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Pin','Peop','brancher','m','2017-04-21',NULL)`;
con.query(insRowStr, function(err)
{
if (err)
console.log(err);
else
console.log('row inserted');
});
con.close();
This code is similar to other code samples, except for the presence of the argument, 'compress' in the connection expression (above).
The following code compresses a SELECT statement and send:
const mysql = require('./server/Mysql.js');
con = new mysql.Connection("root", "secret", "localhost", 3306, function(err)
{
if (err)
console.log(err);
}, 'compress');
var db = `PetStore`;
con.selectDB(db, function(err)
{
if (err)
console.log(err);
else
console.log('database selected');
});
var selStr = `select * from pet where name='Pin'`;
con.query(selStr, function(err, result)
{
if (err)
console.log(err);
else
{
for (i=0; i<result.length; ++i)
{
tempStr = `${result[i].get('name')}, ${result[i].get('owner')}, ${result[i].get('species')}, ${result[i].get('sex')}, ${result[i].get('birth')}, ${result[i].get('death')}`
console.log(tempStr);
}
}
});
con.close();
Note the use of the 'compress' argument in the connection expression, which enables the SELECT statement to be compressed (before being sent over the network).
The following code compresses a DELETE statement and send:
const mysql = require('./server/Mysql.js');
con = new mysql.Connection("root", "secret", "localhost", 3306, function(err)
{
if (err)
console.log(err);
}, 'compress');
var db = `PetStore`;
con.selectDB(db, function(err)
{
if (err)
console.log(err);
else
console.log('database selected');
});
var insRowStr = `delete from pet where name='Pin'`;
con.query(insRowStr, function(err)
{
if (err)
console.log(err);
else
console.log('row deleted');
});
con.close();
This code is similar to other code samples, except for the presence of the argument, 'compress' in the connection expression. Any compression of a SQL statement, needs this argument.
Note: The API does not yet support a combination of INSERT, SELECT and DELETE.
That is it for this part of the series. We stop here and continue in the next part.
Chrys