Data Manipulation Statements and EMySQL API

Using the EMySQL API – Part 3

Foreword: In this part of the series, I show you how data manipulation statements can be used with the EMySQL API.

By: Chrysanthus Date Published: 28 Jul 2016

Introduction

This is part 3 of my series, Using the EMySQL API. In this part of the series, I show you how data manipulation statements can be used with the EMySQL API. It is simple: type your SQL statement as a string without the ending semicolon, as argument to the query() function. You should have read the previous parts of the series before reaching here, as this is a continuation.

Examples of Data Manipulation Statements
There are a good number of data manipulation statements. The main data manipulation statements are the SELECT, INSERT and UPDATE statements. I will use these statements to manipulate the pet table already created in the PetStore database, in the previous part of the series. You use the other data manipulation statements in a similar way, as I use these three: as first argument to the query function. You should try all the code samples in this series.

The query()  Function Syntax
The query function syntax is:

    con.query(`SQLStr`, callbackFn(){});

where con is the connection object. callbackFn(){} is for handling error and result. Note the use of backticks in the query string.

Inserting a Row
After connecting to the MySQL server and selecting the database, you can insert a row into a table in the database as the following code shows:

    var insRowStr = `INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Nail','Mary','hamster','f','2009-03-30',NULL)`;
    con.query(insRowStr, function(err)
        {
            if (err)
                console.log(err);
            else
                console.log('row insterted');
        });

If you have more than one row to insert, call the query() function more than once.

Loading Data from Text file into a Table
You can create a text file called, pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns are listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), use NULL values. To represent these in your text file, use \N (backslash followed by capital-N).

The text file to create and load is:

Boun John cat f 2003-02-04 \N
Claws Susan cat m 2004-03-17 \N
Buffy Harold dog f 1999-05-13 \N
Fang Benny dog m 2000-08-27 \N
Bows Diane dog m 1989-08-31 2005-07-29
Chirpy Susan bird f 2008-09-11 \N
Singer Susan bird \N 2007-12-09 \N
Fat Benny snake m 2006-04-29 \N

The white spaces between values in a row should be achieved by pressing the tab key on the keyboard.

The basic statement syntax to load the file values into their corresponding positions in the database table, from a directory in the server is:

    LOAD DATA INFILE 'Drive:/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n'

The portion, “LINES TERMINATED BY '\r\n'” depends on the text editor you are using. For some editors you have to omit it.

I prepared the above file and saved in the server directory (localhost). The following code segment was used to copy the data into the table.

    var loStr = `LOAD DATA INFILE 'C:/server/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n'`;
    con.query(loStr, function(err, feedBack)
        {
            if (err)
                console.log(err);
            else
                console.log(feedBack);
        });

I tried the code and I had the following for successful loading:

[ Map {
    'bodyLength' => 55,
    'seqNo' => 1,
    'okNo' => 0,
    'affectedRows' => 4,
    'last_insert_id' => 0,
    'noOfWarnings' => 12032,
    'info' => 'Records: 8  Deleted: 0  Skipped: 0  Warnings: 0' } ]

feedback is a single element array whose element is a map. err is also a single element array whose element is a map.

Updating
The following code updates the row of the pet whose name is, Bows.

    var updStr = `UPDATE pet SET birth = '1999-08-31' WHERE name = 'Bows'`;
    con.query(updStr, function(err, feedBack)
        {
            if (err)
                console.log(err);
            else
                console.log(feedBack);
        });

I tried the code and I had:

[ Map {
    'bodyLength' => 48,
    'seqNo' => 1,
    'okNo' => 0,
    'affectedRows' => 1,
    'last_insert_id' => 0,
    'noOfWarnings' => 10240,
    'info' => 'Rows matched: 1  Changed: 1  Warnings: 0' } ]

Selecting
The following code segment selects rows where species = 'snake' OR species = 'bird' :

    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)
                       {
                           console.log(`${result[i].get('name')}, ${result[i].get('owner')}, ${result[i].get('species')}, ${result[i].get('sex')}, ${result[i].get('birth')}, ${result[i].get('death')}`);
                       }
                }

        });

Here, result is an array consisting of maps. I tried the 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

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

Chrys

Broad Network

Related Articles

Data Manipulation Statements and EMySQL API

Using the EMySQL API – Part 3

Introduction

Related Links

Comments