Performing Multiple SQL Queries with queryRaw in Azure Mobile Services Scripts

I was recently working on a sample application where I wanted to perform a few SQL queries directly against my Mobile Services’ SQL Database.  This is easy enough to do with the MSSQL module provided as part of the scripting runtime.  You can see an example of doing this here:

This does nearly exactly what you think it does.  It takes the query text, mixes it with the parameters and executes it against the database connected to our Mobile Service.  The tricky thing here is that if you put multiple queries in your SQL statement (here I have TWO update statements) your call back handler actually get’s called twice!  This means that if you had your script like the one above and you respond to the calling application using request.respond, you’ll end up seeing an error in the logs that looks like this:

Respond cannot be called more than once.

From the client side, you wouldn’t notice anything.  The client gets the initial response that says “we updated the tables successfully” so it will continue on happy.  even though that is true, we really want to only respond back to our client when we’re done on the server side.  We can do that by altering our code to keep track of how many queries we’re performing:

Here, we’re creating a var named responseCount which we use to track the number of callbacks we’ve received.  In each callback, we’re incrementing that variable and checking to see if we’ve had at least one callback already.  If we were doing more than 2 queries, we’d have to update that number as well.  One issue we could run into is that the error block is done the same way as our success block.  So if the first query fails and the second query is successful, we’ll respond to the calling application that we were successful.  We’d probably want to keep track and tell the calling application that there was an issue if this occurred. 

Summary

It’s easy enough to execute a custom SQL query against your database in a Mobile Services script.  It’s also quite easy to execute multiple queries in a single call to the database.  If we do perform multiple queries we do need to plan out how we’re going to appropriately reply to the calling application.  We could alternatively call a stored procedure that would handle doing the updates (where we could also use a transaction) or we could execute the queries one at a time if we wanted to (with the second query being called in the first’s success block).