Invoking Stored Procedures from Azure Mobile Services

One of the questions I’m often asked is whether it’s possible to call SQL stored procedures from Azure Mobile Services. The answer is yes and it’s probably easier than you think. In case you don’t know, Azure Mobile Services is a way to very simply expose an HTTP service over data stored in an Azure SQL database. By default, Azure Mobile Services exposes the SQL tables directly as resources. So the HTTP methods GET, POST, PUT and DELETE will essentially be mapped to SQL operations on the underlying tables.

While this simple mapping mechanism is good for resource-oriented access to the tables, the logic to produce a usable Web API is often a bit more complex than that. Stored procedures can provide an interesting abstraction layer that allows us to use the efficiency of the SQL Server query engine to reduce round trips to and from Internet clients, for example. Or perhaps stored procedures might be used to hide normalization peculiarities from clients or perhaps to use advanced parameter handling logic. Whatever the case may be, it would be helpful from time to time to be able to invoke stored procedures from the HTTP API that Azure Mobile Services provides.

Let’s start by assuming that an Azure Mobile Service exists with some data that we would like to expose via a stored procedure. For the purposes of this example, my service is called MobileWeatherAlert which contains a backing table in an Azure SQL Database named [MobileWeatherAlert_db]. It’s really helpful that Azure Mobile Services uses schema separation in the underlying database to manage all of its data. That schema separation allows us to expose many separate middle-tier services from one common database if needed. So, in my weather database, there’s a schema called [MobileWeatherAlert] corresponding to the name of the service that it supports. For the purposes of this example, that schema contains a table called [Observation] which is used to collect weather data by [City]. Figure 1 shows a very simple stored procedure called [GetObservationsForCity] that I’d like to be able to call from the service API.

Figure 1 - A simple stored procedure to fetch weather observations for a given city.
Figure 1 – A simple stored procedure to fetch weather observations for a given city.

There are a number of places where this procedure might be invoked. For this example, I’ll implement a custom API in the mobile service called observation. Figure 2 shows the dialog in the Azure management console where the custom API will be created.

Figure 2 - Creating the custom observation API for the MobileWeatherAlert service.
Figure 2 – Creating the custom observation API for the MobileWeatherAlert service.

For this simple example, I’ll only implement the HTTP GET method in the API to invoke the stored procedure. For simplicity of the example, I’ll open up access to everyone to avoid having to pass any sort of credentials. Now I can add a bit of JavaScript to the API to make the stored procedure call. Figure 3 demonstrates adding that JavaScript to the API via the Azure management console.

Figure 3 – The JavaScript to invoke the GetObservationsForCity stored procedure with a URL-sourced city parameter.
Figure 3 – The JavaScript to invoke the GetObservationsForCity stored procedure with a URL-sourced city parameter.

Lines 1 through 9 in the script encompass the get function that will be invoked when the HTTP GET method is used to call the service. The parameters passed to the JavaScript function are the request and response objects. From the request object, line 2 shows how to obtain a reference to the mssql object which exposes a query function for making calls into the database. Line 3 demonstrates how to call the query function to execute the [GetObservationsForCity] stored procedure, passing a single parameter for the City by which to filter. It’s important to note here that the schema in which the stored procedure resides is not named in the EXEC call. This is counter-intuitive, in my opinion, and is likely to trip up novices as they experiment with this functionality. Since we are invoking the GET method for the MobileWeatherAlert service, there’s an implicit assumption used in the preparation of the SQL statement that objects will reside in a similarly-named database schema.

Notice also on Line 3 that the request object passed into the JavaScript function exposes a query property that conveniently contains an object named city which will be parsed directly from the URL. Figure 4 shows how that URL might be passed from PostMan, a really excellent Google Chrome plug in that allows the invocation of nearly any sort of HTTP-oriented web service or API.

Figure 4 - Calling the new API via PostMan to get weather observations for the city of RIchmond.
Figure 4 – Calling the new API via PostMan to get weather observations for the city of Richmond.

Finally, lines 4 through 6 of the JavaScript method, the success function that process the results of the SQL query logs the results and returns them to the caller with an HTTP 201 (OK) response. I’ve included a called to the console.log() function to show how easy it is to log just about anything when you’re debugging your JavaScript code in Azure Mobile Services. After invoking an API or custom resource method that logs something, check out the logs tab of the mobile service in the management console to see what got saved. Of course, you’ll want to do minimal logging in production but while you’re testing and debugging, the log is a valuable resource.

In studying the URL and its output in Figure 4, remember that the JavaScript for the observation API didn’t have to do any special parsing of the row set returned by SQL Server to produce this result. Simply returning that data from SQL Server caused the API to emit JavaScript Object Notation (JSON) which has arguably become the lingua franca of the Internet for expressing data.

In closing, I’ll share a couple of thoughts. If you’re interested in building a simple query interface on top of a mobile service, you don’t have to use stored procedures as shown here. Azure Mobile Services implements fairly rich OData support directly on table resources. With OData, filtering, sorting and pagination of SQL data are built in, so to speak. Also, the web way of doing services (sometimes called RESTful based on Dr. Roy Fielding’s dissertation and the HTTP standards that flowed from it) assume that we’ll use HTTP in the way it was intended: accessing and linking resources at a more basic level, using the HTTP methods GET, POST, PUT, and DELETE as a complete, fully-functional language for accessing those resources. Database people inherently understand and respect this access pattern better than many programmers working in traditional programming languages like C# and Java. After all, we’re accustomed to using four basic methods to manipulate data in our databases: SELECT, INSERT, UPDATE, and DELETE. Yet, as database people, we also know that giving software developers strict table-level access can cause all sorts of performance problems. For those situations, where you know that some complex database operation could be performed much more efficiently with a bit of T-SQL code, a stored procedure or a view may be just the prescription your developers need. Hopefully, this article has helped you understand how to invoke programmatic resources in a SQL Azure database and perhaps it will help you along the way to making the correct architectural choices in the design of your modern, data-driven web applications.

Earth Surface Distance in T-SQL

A few years ago, I was working on a project where I needed to calculate the distance between pairs of points on the Earth’s surface. With a bit of research, I found an implementation of the haversine formula written in Python on John D. Cook’s Standalone Numerical Code site. Given a pair of latitude and longitude values, John’s code produces a unit coefficient that can be multiplied by the radius of the sphere to yield the distance in whatever unit of measurement you might want.

Having the Python code was great but I needed a version of the algorithm in F#. After searching for some time and not finding an F# implementation, I decided to write one based on John’s Python version and put it back into the public domain. John published my F# code into his Standalone Numerical Code library here for everyone to use freely.

The exercise for today is to write the haversine formula in Transact-SQL. I’ll start by proposing a test. Between two points that are well-known and using Google Earth as the standard, I define the following variables.

Google Earth estimates that these two pairs of coordinates between Richmond, Virginia USA and São Paulo, Brazil are roughly 4,678 miles or 7,528 kilometers apart. Having no better standard handy, those will have to suffice for some testing later on. Next comes the heart of the haversine formula.

I recommend saving this as a user-defined function in your SQL repository called something like ArcUnitDistance. The reason for that naming is because the value that this calculation produces is the distance between the two points supplied on a surface that has a radius of one in whatever unit of measure you’re going to apply. Now it’s time to put the code to the test.

Commonly used values for the radius of the Earth when using the haversine formula are 3,960 miles or 6,373 kilometers. Of course, the Earth isn’t a sphere. It’s a spheroid so you may find other radius values that you trust more. To find the distance between my test points in those units of measurement, I simply need to multiply the @arcUnitDistance by the radius values. Then, it’s easy to calculate the skew from the expectation and print it out. The test yields these results:

Units Expected Calculated Skew
Kilometers 7527.806 7521.74343697558 0.00080535590641083
Miles 4677.562 4673.79632989539 0.000805049746985879

It seems that for the two test points at least, the skew based on Google Earth as the standard is about 8/100ths of one percent. Over the years, I’ve successfully adapted this haversine code to C#, JavaScript and Java, too. The haversine formula performs better for short distances than using a simple law of cosines-based formula for all sorts of reasons that scientists understand. However, it’s got a margin of error of up to 0.3% in some cases which can be too great for applications that require high precision.

If you want much greater accuracy when calculating distances on spheroids like planet Earth, you should check out the Vincenty Formula instead. It’s marginally more complex than haversine but yields typically better results. I hope you find this version of the haversine formula written in Transact-SQL useful for a variety of distance measurement applications.

Code Contracts Part 1 – Introduction

Series Redux

I discovered Microsoft Code Contracts several years ago and fell in love with the idea. Code Contracts have helped me to write better software since 2008 so I’d like to share them with you. I did an extensive blog series on this subject several years ago which was quite popular. However, in the years that have passed, the content got a bit stale and I’ve switched blogging platforms a couple of times. So I’m updating and offering this series once again. Please visit the series index if you would like to enjoy more articles like this one.

What is a contract?

We all have opportunities to deal with contracts now and again in our personal lives. Whether you’re leasing a car or buying a mobile phone, you’ll probably be signing a few contracts over the course of your life. But have you ever thought of the source code you write as a kind of contract? In fact, as software developers, we create contracts throughout the course each day simply by writing functions. Here’s a simple one written in C# that demonstrates the idea:

This simple method exposes a contract to which the two parties, the caller and the called function, must agree. If the Greet function could talk to its callers, it might say something like this about the contract:

Hello, my name is Greet. If you give me an array of strings, I'll give you an integer.

Because of the contract, callers can’t pass an array of integers to this function. Compilers for statically-typed languages won’t allow that. Similarly, if the Greet function tried to return something other than an integer, that would also be a violation of the contract and disallowed by the C# compiler. For the things that the C# compiler can do to enforce the agreement, we can safely call this exchange a kind of contract. However, as legally binding as this might seem, there are some lingering questions. For example:

  • Can the caller pass null (Nothing) for the args parameter?
  • Can the caller pass an empty array?
  • Can some of the strings in the array be empty or be null references?
  • Will any conversions be applied to the strings that affect valid input values?
  • Is there a limit on the length of the strings supplied?
  • How many strings can the array refer to?
  • Does the Greet function produce any noteworthy side effects in the object instance it may belong to?
  • Can the caller ignore the result of the function?
  • What range of integers can the function return to the caller?
  • Are any of the return values so-called sentinel values that have special meanings?
  • Are any exceptions potentially thrown?
  • If exceptions are thrown, are the promises about side effects still enforced? Or are there potentially different side effects when this occurs?

Well, it looks like we’re missing a few details. In a traditional, general purpose programming language like C#, the syntax doesn’t allow me to express all of the details to refine the contract more profoundly. A common approach to the ambiguities in C# might be to add some so-called guard code to the Greet function like this:

We call these kinds of defensive statements guarding because they act like a shield, keeping us from making obvious mistakes inside the function. By checking that the array isn’t null and that it contains at least one non-null element, we believe that the code in the remainder of Greet method will run cleanly. But the mechanism by which callers will be informed of their mistakes in passing bad parameters is the same. They will get exceptions at runtime which, as we know, probably won’t be handled all that well.

It would be much better to implement an exception avoidance policy instead. However, to do that, these guarding conditions need to be made known to callers before they construct their invocations. In contract language, we call these pre-conditions, meaning that they are standard terms and conditions for invoking the function correctly and that they should be pre-known because they will be applied before the body of the function runs.

Introducing Code Contracts

Microsoft Research has made available a set of tools called Code Contracts for strengthening the agreements between callers and called functions. These tools are not language specific so they work across all of the core .NET languages. In fact, if you have the .NET Framework 4.0 installed and are using Visual Studio 2010 (or a later version), you’ll find that after importing the System.Diagnostics.Contracts namespace, the following code compiles without installing any additional tools:

Pay attention to the use of the Contract class in place of the guard code I had in the last version of the Greet function shown before. The three invocations of the Requires method provide the pre-conditions that callers must meet to properly call the Greet function. The question that may be running through your mind at this point is, “If I were to violate any of those pre-conditions when calling Greet, would the compiler stop me?” The answer is no, at least not by default, which deserves a bit of explanation.

You see, the .NET 4.0 Framework includes the new namespace and the Contracts class as mentioned above, but until you install the Code Contracts for .NET tools from Visual Studio Gallery (perhaps via the Visual Studio Extensions and Updates Manager), calls to the Requires method and other members of the Contract class don’t do anything. These calls are essentially discarded during compilation if you don’t turn on the various features of Code Contracts. In this way, development computers that don’t have the Code Contracts tools installed can build and run code that includes these calls without errors. Not many research projects at Microsoft get to include optional classes in the .NET framework like. So Code Contracts must be an important piece of Microsoft’s future developer tooling plans.

After having installed the Code Contracts for .NET tools via Visual Studio's Extensions and Updates Manager.

Here’s a screenshot from my development computer after having installed the Code Contracts for .NET tools via the Visual Studio Extensions and Updates Manager. Of course, you can also download these tools directly from Visual Studio Gallery and install them yourself. No matter how you get them, you’ll probably need to restart Visual Studio after the installation if you have it open at the time.

After installing the Code Contracts tools, you’ll still not see any differences in the behavior of Visual Studio or the compilers for improper invocations of the Greet function. The pre-conditions will still seem to be doing nothing. This is by design. The Code Contract tools will be invisible until you turn specific features on. To do that, open the project properties for any project. Notice that there’s a new section (or tab) on the left side of the properties window called Code Contracts.

A new section appears in your project properties after installing the Code Contracts tools.

From the screenshot I’ve provided here, you can see that there are two major sections at the top of the Code Contracts project properties: runtime checking and static checking. I’ll show you how to use runtime Code Contracts later in this series. For now, if you’re following along by coding in Visual Studio, turn on the static checking feature, save the project settings and go back to the code.

Purple squigglies telling me that I've violated the contract.Because Code Contracts will be statically checking the code in the background, it may take some time before any indication appears that it has found some potential problems. Eventually, a squiggly purple line appears beneath a line of my code that improperly invokes the Greet method by passing null for the args parameter. In the sample I’ve shown here, I’ve positioned my mouse cursor over the line of squiggled code to reveal a message that says “CodeContracts: requires is false: args != null.” In fact, if you hovered the mouse over the previous line of squiggled code, you would see an even more ominous sounding message:

“CodeContracts: Invoking method ‘Main’ will always lead to an error. If this is wanted, consider adding Contract.Requires(false) to document it”

In other words, this program is always going to fail. That’s good to know. Of course, this was a contrived example but as we’ll see going forward in this series, Code Contracts can verify branches and assumptions across vast chains of function execution to find potential problems that you probably never realized were lurking in your software.

Ola! I have a better contract for you.

It would advisable at this point for you to stop and think about what this means for the way that you write software going forward. The basic pattern that you may have adopted for writing guard code still applies. Except now, those expressions of condition that used to be buried inside your functions can be pre-known by and visible to the callers of those functions. In the next article in this series, I’ll go deeper into the nuances of pre-conditions, showing you some of the more useful features of Code Contracts’ static verification engine.

Please visit the series index, if you would like to enjoy more articles like this one.

Automating Windows Defender Updates

If you have automatic Windows Updates turned off, you may have noticed that you also don’t get Windows Defender updates automatically. There are a lot of reasons why you may want to disable automatic updates. Perhaps you want to pick and choose the ones you accept. Or maybe you don’t want to smash through your data plan limits so you wait until you’re on another network to get your updates. Whatever your reasons are for disabling automatic Windows Updates, not getting up-to-date virus and malware signatures every day is an unfortunate side effect. However, you can force Windows Defender to update its signature file with the following command:

Try it out at a command prompt to see it in action and to verify that it works as advertised. On my computers, I simply created a new task in Task Scheduler that executes the command daily. Now, I can do my Windows Updates manually and receive Windows Defender malware signatures (effectively) automatically.