Consuming SQL Azure Data with the OData SDK for PHP
One of the interesting incubation projects that the SQL Azure team is working on is the SQL Azure OData Service. If that sentence makes no sense to you, then start by reading this short overview on the SQL Azure team blog: Introduction to Open Data Protocol and SQL Azure. I’ll paraphrase what I read in that post to inspire this post:
OData is a REST-based protocol [that]…is being used to expose data from a variety of sources, from relational databases like SQL Azure and file systems to content management systems and traditional websites…[C]lients across many platforms, ranging from ASP.NET, PHP, and Java websites to Microsoft Excel, PowerPivot, and applications on mobile devices, are finding it easy to access those vast data stores through OData...
The SQL Azure OData Service incubation (currently in SQL Azure Labs) provides an OData interface to SQL Azure databases that is hosted by Microsoft. … Another way to think about this is that SQL Azure OData Service provides a REST interface to your SQL Azure data.
So, in this post I’ll take a look at how to consume SQL Azure data using the OData SDK for PHP.
Note: The heart of this post gets at setting up the OData Service. Once you have that set up (actually, once you have any OData service set up), you can follow the directions in this post for installing and using the OData SDK for PHP: Retrieving Data with the OData SDK for PHP.
Creating a SQL Azure Server
The first thing you’ll need to do is create a SQL Azure server. If you follow the steps in the Create a SQL Azure Server section of this post, Getting Started with SQL Azure and PHP, you are almost ready to go. One thing you need to do (not mentioned in that post, but necessary if you want to create an OData Service) is to allow Microsoft Services through the firewall. On the Firewall Settings tab, check the Allow Microsoft Services access to this server box (and leave the default settings for the IP Address Range).
Creating a SQL Azure Database
Once you have a SQL Azure server, you’ll need a database (whose data we’ll expose as an OData feed). There are a number of ways to create a SQL Azure database (for example, you could execute a DDL script using the SQL Server Driver for PHP, as I did in the post I mentioned above), but here I’ll use the SQL Azure Migration Wizard. The SQL Azure Migration Wizard is a tool that you can use to move a local database into SQL Azure. I’ll use it to move the example Northwind database (which you can download here: http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en) to SQL Azure.
1. Download the SQL Azure Migration Wizard: http://sqlazuremw.codeplex.com/.
2. Launch SQLAzureMW.exe.
3. Under Analyze and Migrate, select SQL Database and click Next.
4. Connect to your local server.
5. Select the database you wish to migrate (Northwind in my case).
6. Select Script all database objects.
7. In the Script Wizard Summary pane, click Next.
8. Click Yes when asked if you are ready to generate the script. The wizard will create a script that we will then execute to create a database (with its data in tact) in SQL Azure.
9. When the wizard finishes creating the script, click Next.
10. In the next pane, you will connect to your SQL Azure server. You will need to replace SERVER with your server ID, replace UserName with the user name you used when creating the server, and supply the password for that user.
11. After you have connected to the server, click Create Database and supply the name of the database you want created.
12. Select the newly created database, and click Next.
13. Click Yes when asked to execute the script against the destination server.
Depending on the size of your database, it may take a few minutes for the script to execute.
Creating a SQL Azure OData Service
Once we have created a SQL Azure server and have a database on the server, we need to create an OData service for the database. To do this…
1. Go to http://sqlazurelabs.com and click on SQL Azure OData Service.
2. Supply your server, user, and password information, then click Connect.
3. After you connect, you will be able to choose the database for which you want to enable the OData service from a dropdown list. Be sure to check the Enable Odata box.
4. Enable anonymous access by choosing dbo from the Anonymous Access User dropdown. (I’ll save authenticated access for another post.)
You now have the endpoint for the Odata service. You can copy/paste that URL into your browser to start playing with it.
Accessing the OData Service with PHP
Once you have the OData Service set up, you can follow the instructions in this blog post for consuming the data with PHP: Retrieving Data with the OData SDK for PHP. (The URI that you will point the PHPDataSvcUtil.php tool at is the one at the bottom of the screenshot above.)
That’s it. Keep in mind that the SQL Server OData Service is still an incubation project. The SQL Azure team is looking for feedback, which you can provide by emailing SqlAzureLabs@microsoft.com or voting for it at www.mygreatsqlazureidea.com.
Thanks.
-Brian