Node.js calling a MySQL stored procedure multiple times in a loop

Question

I'm writing an application that lets you add visits (visit date, visit type, notes) to a case, via an Ajax POST call from a form. The visit creation functionality lets you add the same visit type and notes on several dates. So I end up with a visit object that has an array of dates in it, but the same notes and visit type. Because SQL isn't where I should be doing any looping, I want to do it in Node as I'll be able to handle any failures in the array or results returned from the individual SQL calls.

I tried setting up the procedure call so that it took an array of arrays in an array as a parameter as per here, but I couldn't get it to work, so am falling back to looping through.

The issue I'm having is with callbacks completing before I've got any results. Obviously it's because I don't understand callbacks enough and no amount of reading is making it any clearer, so I've ended up here to ask for help.

Below is the code that is executed. The visit object that is the parameter of the insertVisit function is the class as mentioned above with the array of dates.

this.insertVisit = function (req, res, visit)
{
    var insertVisit = new Visit();
    insertVisit = visit;

    var success = 0;
    var visitId = 0;

    //Split the visits into an array of individual dates
    var allVisits = insertVisit.visitDates.split(',');

    //Attemp to call insertVisits using a callback
    insertVisits(0, function(err){
        if( err ) {
          console.log('yeah, that insert didnt work: '+ err)
        }
    });

    console.log('finished');



    function insertVisits(v)
    {
        //Loop through all of the visits
        if (v < allVisits.length )
        {
            //Attempt to call the next function
            singleDate(allVisits[v], function(err)
            {

                if(err)
                {
                    console.log(err);
                }
                else
                {
                    //if everything is successful, insert the next individual date
                   allVisits[v + 1];
                }
            })
        }

    }


    function singleDate(singleVisitDate)
    {

    var query = 'CALL aau.sp_InsertVisit (?,?,?,?,?,?,?,@visitId,@success); SELECT @visitId, @success;';
    var parts = singleVisitDate.split('-');
    var formattedDate = new Date(parts[2], parts[1] - 1, parts[0]);  

    connection.init();

    //Everything runs fine up to here, but as soon as we go to the next line, the program
    //continues back at the end of the loop in the insertVisits function an exits the function.
    //At this point the below code executes asynchronously and inserts one of the dates before returning
    //and doesn't call any further dates.

    connection.acquire(function (err, con)
    {
        con.query(query,
        [
        insertVisit.caseId,
        formattedDate,
        parseInt(insertVisit.visitTypeId),               
        parseInt(insertVisit.visitStatusId),
        insertVisit.adminNotes,
        insertVisit.operatorNotes,
        insertVisit.isDeleted,
        visitId,
        success
        ]       


        , function (err, result)
        {
            if(err)
            {
                console.log(err);
            }
            else
            {
            con.release();
            res.write(JSON.stringify(result));
            }
        })
    })


}

So I'm trying to loop through each of the dates and call the stored procedure for each date and add the results to response using res.write.

This is a brand new project, so happy to rewrite it with promises or asynch/await. But any examples would be greatly appreciated of looping through multiple procedure calls

score 1 · Accepted Answer · answered Sep 04 '18 at 14:52

Ok,

So I looked at using async.eachSeries and managed to get it to work when I put the callback at the bottom of the 'tree'.

Hopefully this can be helpful to anyone else trying to run the same proc multiple times.

this.insertVisit = function (req, res, visit)
{
    var insertVisit = new Visit();
    insertVisit = visit;

    var success = 0;
    var visitId = 0;

    var allVisits = insertVisit.visitDates.split(',');

async.eachSeries(allVisits, function(singleVisitDate, callback) 
{
    var query = 'CALL aau.sp_InsertVisit (?,?,?,?,?,?,?,@visitId,@success); SELECT @visitId, @success;';
    var parts = singleVisitDate.split('-');
    var formattedDate = new Date(parts[2], parts[1] - 1, parts[0]);

    connection.init();
    connection.acquire(function (err, con)
    {
        con.query(query,
        [
        insertVisit.caseId,
        formattedDate,
        parseInt(insertVisit.visitTypeId),               
        parseInt(insertVisit.visitStatusId),
        insertVisit.adminNotes,
        insertVisit.operatorNotes,
        insertVisit.isDeleted,
        visitId,
        success
        ]       


        , function (err, result)
        {
            if(err)
            {
                console.log(err);
            }
            else
            {
            con.release();
            res.write(JSON.stringify(result));
            callback();
            }
        })
    })


},
function(err) 
{
    if(err)
    {
        console.log(err);
    }
    else
    {
    res.end();
    }
});

Node.js calling a MySQL stored procedure multiple times in a loop

1 Answers1