Using the EMySQL API Result Set

Using the EMySQL API – Part 4

Foreword: In this part of the series, I show you the basics on accessing the EMySQL API result set.

By: Chrysanthus Date Published: 28 Jul 2016

Introduction

This is part 4 of my series, Using the EMySQL API. In this part of the series, I show you the basics on accessing the EMySQL API result set. A result set is a special kind of database table. It is a feedback table. You should have read the previous parts of the series before reaching here, as this is a continuation.

Remember, you begin your script that will use the API with:

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

where the directory, server is the working directory, and has the API, MySQL.js.

The result Array Variable
The callback function in your script has the arguments:

    (err, result, fields)

err is usually a single element array, whose element is a map. The map has the error data. If result is present, then err is just false.

result is an array that has more than one element. Each element is a map. Each map has a row of feedback data.

I will talk about fields later.

Reading Row Contents
If you want to print out all the result array, you will type something like:

    con.query(queryStr, function(err, result)
        {
            if (err)
                console.log(err);
            else
                {
                 for (i=0; i<result.length; ++i)
                     {
                            console.log(result);
                     }
                }
        });

The array with its maps will be printed in some structural form.

Assuming that you want to print rows with all the columns in the pet table of the PetStore, you will type something like:

    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);
                     }
                }
        });

The number of feedback rows is the length of the result array. I tried this code and I had:

    Chirpy, Susan, bird, f, 2008-09-11, NULL
    Singer, Susan, bird, NULL, 2007-12-09, NULL
    Fat, Benny, snake, m, 2006-04-29, NULL

Accessing a Single Value
To access a single value for a particular row and column, the syntax is:

    result[rowNo].get('columnName')

Script Example for Selecting and Displaying
The following script selects and displays all the data in the pet table:

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

    con = new mysql.Connection("root", "secret", "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`;
    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();

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

Chrys

Broad Network

Related Articles

Using the EMySQL API Result Set

Using the EMySQL API – Part 4

Introduction

Related Links

Comments