Azure Mobile Service: 500 Error but it's actually working?

Question

I've got an Azure Mobile Service with a custom API. I have tested this API in the past from iOS and it seems to work fine. I am now testing this API on Android. This is the API method in question:

exports.post = function(request, response) {
  var body   = request.body;
  var email  = body.email;
  var tables = request.service.tables;
  var users  = tables.getTable('User');
  users.where({ email: email }).read({
    success: function (userList) {
      if (userList.length === 0) {
        response.send(200, { Status: 'Error', Error: 'Email not found.' });
      } else {
        var user       = userList[0];
        var providerId = user.ObjectId;
        var accounts   = tables.getTable('Account');
        accounts.where({ User: providerId }).read({
          success: function (accountList) {
            if (accountList.length === 0) {
              response.send(200, { Status: 'Error', Error: 'Internal server error.' });
            } else {
              var account = accountList[0];
              var mssql   = request.service.mssql;
              var sql     = "EXEC [db].[usp_RequestPasswordReset] ?;";
              mssql.query(sql, [account.id], {
                success: function (results) {
                  console.log(results);
                  var codeRow = results[0];
                  if (codeRow == undefined) {
                    console.log("codeRow is undefined");
                  } else {
                    console.log(codeRow);
                  }
                  var code    = codeRow.Code;
                  response.send(200, { Status: 'Success', Message: 'Please check your email for further instructions.', Code: code });
                  sendEmail(email, user.Name, code);
                }
              });
            }
          }    
        });
      }
    }
  });
};

Now, sendEmail is a separate function that sends an email using Azure's SendGrid feature.

What is really perplexing me is that all of the code appears to be working fine.

1. The stored procedure executes just fine.
2. The database is updated exactly as I would expect.
3. The email comes through the SendGrid service exactly as expected.
4. The console.log messages that I have in the code display the expected values.

The only thing that is funky is that the call is returning a "500: Internal Server Error" error.

• This is true both in my Android client and also in the API log on the Azure Management Portal.
• The error message I am getting is telling me that var code = codeRow.Code; is trying to access 'Code' of 'undefined'. But it's not undefined.
• Going back and checking my iOS client against this produces the same results.
• Everything works fine except for the message returned to the user.
• To be clear, the error code is 500, not 200, since it's possible for my code to return an "Internal Server Error" message.
• Also, I am very sure that my mssql.query success block is firing, based on the console log messages and the outcome.

So, what gives?

Answer 1

mssql.query can call your callback more than once depending on what's in your stored procedure. You can define a variable outside your callback, e.g.

var callbackReceived = false;

and then in your callback, only send a response for the call that actually receives the updated record:

if (callbackReceived === false && results && results.length > 0) {

  callbackReceived = true;
  // continue as before
}

See also this question answered by one of the Azure developers: Azure mobile service custom API calling SQL SP multiple times