Administrative and Utility Statements with EMySQL API

Using the EMySQL API – Part 5

Foreword: In this part of the series, I talk about Administrative and Utility Statements with the EMySQL API.

By: Chrysanthus Date Published: 28 Jul 2016

Introduction

This is part 5 of my series, Using the EMySQL API. In this part of the series, I talk about Administrative and Utility Statements with the EMySQL API. You should have read the previous parts of the series before reaching here, as this is a continuation.

Administrative Statements
Here I explain how to create a user and grant him a privilege. I also talk about the SHOW TABLES statement. The other administrative statements follow the examples here. I create the user “john”, with password, “smith”. After that I grant him the select privilege to the pet table of the database, PetStore. The following code segment does this:

    var crUser = `CREATE USER 'john' IDENTIFIED BY 'smit'`;
    con.query(crUser, function(err, OK)
        {
            if (err)
                console.log(err);
            else
                console.log(OK);
        });

OK here, is not a result set. It is a single element array, whose element is a map, with data confirming that the query went OK.

After creating the user, you can grant him the SELECT privilege for the pet table as follows:

    var selPriv = `GRANT SELECT ON pet TO 'john'`;
    con.query(selPriv, function(err, OK)
        {
            if (err)
                console.log(err);
            else
                console.log(OK);
        });

The granting of privileges does not necessarily have to be done at the same time the user is created; it can be done after.

The following script connects the user, “john” with password, “smith” to the server and selects the rows of the pet table where species = 'snake' OR species = 'bird':

const mysql = require('./server/Mysql.js');

    con = new mysql.Connection("john", "smith", "localhost", 3306, function(err)
        {
            if (err)
                console.log(err);
        });

    var db = `PetStore`;
    con.selectDB(db, function(err)
        {
            if (err)
                console.log(err);
            else
                console.log('database selected');
        });

    var selStr = `SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'`;
    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();

Remember, once you create a user and grant privileges, the effects are saved in the server; you do not have to create the same user and grant the same privileges the next time to connect.

After selecting the PetStore database, root (user) can go one to show all the tables in the database as follows:

    var selStr = `SHOW TABLES`;
    con.query(selStr, function(err, result)
        {
            if (err)
                console.log(err);
            else
                console.log(result);
        });

I tried the code in my computer and I had,

    [ Map { 'Tables_in_petstore' => 'pet' } ]

pet is the only table in the database, PetStore. That is correct.

The root user can also type SHOW DATABASES in place of SHOW TABLES to see the databases present in the server.

Utility Statements
I talk only about the DESCRIDE TableName statement here. This statement returns a special kind of result set, which present certain information about the table. Just try the following code as root, to see the output (after selecting the database - PetStore):

    var selStr = `DESCRIBE pet`;
    con.query(selStr, function(err, result)
        {
            if (err)
                console.log(err);
            else
                console.log(result);
        });

I tried the code in my computer and I had:

[ Map {
    'Field' => 'name',
    'Type' => 'varchar(20)',
    'Null' => 'YES',
    'Key' => 'NULL',
    'Default' => 'NULL',
    'Extra' => 'NULL' },
  Map {
    'Field' => 'owner',
    'Type' => 'varchar(20)',
    'Null' => 'YES',
    'Key' => 'NULL',
    'Default' => 'NULL',
    'Extra' => 'NULL' },
  Map {
    'Field' => 'species',
    'Type' => 'varchar(20)',
    'Null' => 'YES',
    'Key' => 'NULL',
    'Default' => 'NULL',
    'Extra' => 'NULL' },
  Map {
    'Field' => 'sex',
    'Type' => 'char(1)',
    'Null' => 'YES',
    'Key' => 'NULL',
    'Default' => 'NULL',
    'Extra' => 'NULL' },
  Map {
    'Field' => 'birth',
    'Type' => 'date',
    'Null' => 'YES',
    'Key' => 'NULL',
    'Default' => 'NULL',
    'Extra' => 'NULL' },
  Map {
    'Field' => 'death',
    'Type' => 'date',
    'Null' => 'YES',
    'Key' => 'NULL',
    'Default' => 'NULL',
    'Extra' => 'NULL' } ]

That is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Administrative and Utility Statements with EMySQL API

Using the EMySQL API – Part 5

Introduction

Related Links

Comments