Introduction
In the last month, I have been dealing with the problem of calling a Web Service from SQL Server.
Why would I?
Fairness, mainly.
When all your applications are passing through the same Web Service that manages concurrency and audit, if a stored procedure writes directly onto the database, it is not really fair!
Background
SQL Server gives a big help with that, with the OLE Automation processes.
Basically, you have a set of objects and methods that help you in GETting, POSTing and PUTting data from SQL procedure to a Web Service
Isn't it just awesome?
I think it is, and it opens up a wide range of possibilities.
Here, there is the code for building a simple Movies Database, where you can enter the name of the movie that you are interested in, and all the information related to it will be read from the network and then written into the database.
Using the Code
To retrieve the movies data, I used the OMDb - Open Movie Database, that offers a full API to search and get all the Movies information we need from their database.
Just create a [Movies] Database and execute the scripts attached.
Those scripts will create a [MovieToProcess] table, where you can type in the list of movie titles you want information about. You may or may not know the year, it is important to know at least the movie name though.
Once you entered all that information, you'll just need to run the following command:
That stored procedure will scan all the movies you just entered, query the Open Movie Database and save all the information related to them.
All the information will be stored in the [MoviesInfo] table:
The OMDb offers a wider series of information, like Release Date, Director, Actors and Poster for example. You can expand the MoviesInfo table to include all that information.
With the scripts comes a very useful function (i.e.: parseJSON) reported by Phil Factor on SimpleTalk.
It allows to parse a Json string into a table format. This comes in very useful when dealing with a Response object from a Web Service.
So, what are you waiting for? Build up your own Movie Database.
