Broad Network


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

This is part 12 of my series, Using the EMySQL API. In the previous part of the series, it was the data value in a cell (column) of a database table that was compressed. That is expected when the EMySQL API (client) is in the same computer as the MySQL server. If both are in different computers and possibly distant apart, then the client (EMySQL API) should compress the whole SQL statement and send to the MySQL server. The MySQL server will decompress the SQL statement before carrying out the query. The compressed statement may still require a cell value to be compressed (before being inserted) - you will try that on your own. In this part of the series I explain how to use EMySQL API to compress a SQL statement and send to the MySQL server.

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

Compressing a SELECT Statement
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).

Compressing a DELETE Statement
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
BACK NEXT

Comments

Become the Writer's Follower
Send the Writer a Message