title: Node.js calling a MySQL stored procedure multiple times in a loop
body: <p>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.</p>
  
  <p>I tried setting up the procedure call so that it took an array of arrays in an array as a parameter as per <a href="https://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js">here</a>, but I couldn't get it to work, so am falling back to looping through.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre class="lang-js prettyprint-override"><code>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 &lt; 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));
              }
          })
      })
  
  
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>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</p>