EMySQL Multi-Statement and Stored Procedure

Using the EMySQL API – Part 9

Foreword: In this part of the series, I explain how to code multi-statement and stored procedure.

By: Chrysanthus Date Published: 16 Aug 2016

Introduction

This is part 9 of my series, Using the EMySQL API. In this part of the series, I explain how to code multi-statement and stored procedure. A multi-statement is more than one statement, sent in a query function; the statements are separated by semicolons. A stored procedure is a special kind of multi-statement that is saved in the MySQL server hard disk. You should have read the previous parts of the series before coming here, as this is a continuation.

Multi-Statement
The following is an example of a multi-statement coding (after selecting the database):

    var selSt = `SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; SELECT * FROM pet WHERE death IS NOT NULL`;
    con.query(selSt, function(err, result, field)
        {
            if (err)
                console.log(err);
            else
                {
                    console.log(field);
                    console.log(result);
                }
        });

There are two statements in the code. For the callback function (definition), if the first statement returns an error, then err will be set. Otherwise, result and field will each be a three dimensional structure. The outermost structure is an array. It either leads to a single element array of one map or an array of arrays. Each column index of the outermost structure leads to a result, from one of the multi-statements. If the result is OK, then you have an array of single cell array of one map (OK Map). If the result is an error, then you have an array of single cell array of one map, still (Error Map). If the result is a resultset (set of rows), then you have an array of array of maps, where each row is a map. You should try the above code, to appreciate this. The useful result of a stored procedure is similar in explanation.

For the field, each index of the outermost structure corresponds to an array of array. Each array of array gives the field properties of a resultset.

Stored Procedure
The following is a code example of a stored procedure (after selecting the database):

    var proceduSt = `CREATE PROCEDURE sampleProc (OUT parA DATE)
                        BEGIN
                            SELECT birth
                            FROM pet
                            WHERE name = 'Nelly'
                            INTO parA;
                        END`;
    con.query(proceduSt, function(err, OK)
        {
            if (err)
                console.log(err);
            else
                console.log(OK);
        });

    var setStr = `SET @val = NULL`;
    con.query(setStr, function(err, OK)
        {
            if (err)
                console.log(err);
            else
                console.log(OK);
        });

    var proceduCall = `CALL sampleProc(@val)`;
    con.query(proceduCall, function(err, result)
        {
            if (err)
                console.log(err);
            else
                {
                    console.log(result);
                }
        });

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

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

Chrys

Broad Network

Related Articles

EMySQL Multi-Statement and Stored Procedure

Using the EMySQL API – Part 9

Introduction

Related Links

Comments