Click here to Skip to main content
13,766,627 members
Click here to Skip to main content
Add your own
alternative version

Stats

15.8K views
266 downloads
14 bookmarked
Posted 20 Oct 2016
Licenced CPOL

Build Your Own Movie Database with SQL Server

, 24 Oct 2016
Rate this:
Please Sign up or sign in to vote.
Use the OLE Automation Procedures to retrieve data from a Web Service and parse the JSON response into a table format

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.

1143973/ole.png

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.

omdb

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.

1143973/movestoprocess.png

Once you entered all that information, you'll just need to run the following command:1143973/exec.png

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:

1143973/info.png

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.

1143973/simpletalk.png

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Aless Alessio
Software Developer (Senior)
Ireland Ireland
No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
QuestionError Msg50000 Pin
RobinPlomp25-Jan-17 4:26
memberRobinPlomp25-Jan-17 4:26 
AnswerRe: Error Msg50000 Pin
Aless Alessio27-Jan-17 7:36
memberAless Alessio27-Jan-17 7:36 
QuestionExecution failing Pin
KeithCorser3-Nov-16 17:34
memberKeithCorser3-Nov-16 17:34 
AnswerRe: Execution failing Pin
Aless Alessio3-Nov-16 23:56
memberAless Alessio3-Nov-16 23:56 
QuestionNice article Pin
Rahul_Biswas25-Oct-16 19:28
professionalRahul_Biswas25-Oct-16 19:28 
AnswerRe: Nice article Pin
Aless Alessio25-Oct-16 23:43
memberAless Alessio25-Oct-16 23:43 
GeneralVery interesting Pin
Jose Segarra24-Oct-16 0:08
memberJose Segarra24-Oct-16 0:08 
GeneralRe: Very interesting Pin
Aless Alessio24-Oct-16 5:19
memberAless Alessio24-Oct-16 5:19 
QuestionCode? Is there any? Pin
CurtisG21-Oct-16 8:18
professionalCurtisG21-Oct-16 8:18 
AnswerRe: Code? Is there any? Pin
Aless Alessio23-Oct-16 23:35
memberAless Alessio23-Oct-16 23:35 
GeneralRe: Code? Is there any? Pin
CurtisG24-Oct-16 7:34
professionalCurtisG24-Oct-16 7:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.181114.1 | Last Updated 24 Oct 2016
Article Copyright 2016 by Aless Alessio
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid