Hello people,
Good night!
In this post, I will demonstrate to you how to use the new SQL Server 2016 JSON handling feature, which is returning by a CLR (C #) procedure to return information from your favorite TV series. This is a cool and fun way to learn a little more about these two powerful SQL Server tools.
This idea was inspired by the post. Consuming JSON with Ole Automation Procedures in SQL Server 2016, Warning of Your Favorite Episodes (Off-Topic), from Reginaldo Silva's blog and decided to demonstrate this solution using the CLR.
If you do not know what is CLR, learn more by accessing the post Introduction to SQL Common Language Runtime (CLR) in SQL Server.
Creating the Stored Procedure for Web API Query
To perform queries to the TVMaze.com Web API (http://www.tvmaze.com/api), Reginaldo used OLE Automation procedures, while in this post I will use the procedure stpWs_Requisicao, written in C # to be used in SQLCLR.
Procedure source code stpWs_Request:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
using System.Data.SqlTypes; using System.Net; using System.IO; using System.Text; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpWs_Requisicao(SqlString Ds_Url, SqlString Ds_Metodo, SqlString Ds_Parametros, SqlString Ds_Codificacao, out SqlString Ds_Retorno_OUTPUT) { var parametros = (Ds_Parametros.IsNull) ? "" : Ds_Parametros.Value; var metodo = (Ds_Metodo.IsNull) ? "GET" : Ds_Metodo.Value; var url = Ds_Url.Value; var feedData = string.Empty; var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value; if (string.IsNullOrEmpty(encoding)) encoding = "UTF-8"; var request = (HttpWebRequest) WebRequest.Create(url); request.Method = metodo; if (metodo == "POST" && parametros.Length > 0) { var data = parametros; var dataStream = Encoding.UTF8.GetBytes(data); request.ContentLength = dataStream.Length; using (var newStream = request.GetRequestStream()) { newStream.Write(dataStream, 0, dataStream.Length); newStream.Close(); } } using (var response = (HttpWebResponse) request.GetResponse()) { using (var stream = response.GetResponseStream()) { if (stream != null) { using (var streamReader = new StreamReader(stream, Encoding.GetEncoding(encoding))) { feedData = streamReader.ReadToEnd(); } } } } Ds_Retorno_OUTPUT = feedData; } }; |
Understanding the SQL Server 2016 JSON_VALUE Function
Once the Stored Procedure has been created and published to your database, you can now create your series query procedure using the code below, which also uses the new SQL Server 2016 JSON_VALUE feature, which allows you to manipulate JSON strings natively. by the database.
Examples of using JSON_VALUE:
Creating the Stored Procedure to Search the Series
Once the CLR stpWS_Resquisition has been created and you already understand how the new JSON_VALUE function works, let's create the Stored Procedure that will return the information from your series.
Stored Procedure Source Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
CREATE PROCEDURE dbo.stpInformacoes_Serie ( @Nome_Serie NVARCHAR(MAX) ) AS BEGIN DECLARE @URL NVARCHAR(MAX), @Retorno1 NVARCHAR(MAX), @Retorno2 NVARCHAR(MAX), @Retorno3 NVARCHAR(MAX) SET @URL = 'http://api.tvmaze.com/search/shows?q=' + @Nome_Serie -- Faz uma consulta ao webservice para consultar as informações da série EXEC CLR.dbo.stpWs_Requisicao @Ds_Url = @URL, -- nvarchar(max) @Ds_Metodo = N'GET', -- nvarchar(max) @Ds_Parametros = N'', -- nvarchar(max) @Ds_Codificacao = N'UTF-8', -- nvarchar(max) @Ds_Retorno_OUTPUT = @Retorno1 OUTPUT -- nvarchar(max) -- Remove os caracteres [] do começo e fim da string SET @Retorno1 = SUBSTRING(@Retorno1, 2, LEN(@Retorno1) - 2) -- Recupera o ID da série DECLARE @Id_Serie INT = CAST(JSON_VALUE(@Retorno1,'$.show.id') AS INT) DECLARE @Serie NVARCHAR(100) = JSON_VALUE(@Retorno1,'$.show.name') -- Recupera informações do episódio anterior SET @Url = 'http://api.tvmaze.com/shows/' + CAST(@Id_Serie AS VARCHAR(5)) + '?embed=previousepisode' EXEC CLR.dbo.stpWs_Requisicao @Ds_Url = @URL, -- nvarchar(max) @Ds_Metodo = N'GET', -- nvarchar(max) @Ds_Parametros = N'', -- nvarchar(max) @Ds_Codificacao = N'UTF-8', -- nvarchar(max) @Ds_Retorno_OUTPUT = @Retorno2 OUTPUT -- nvarchar(max) -- Recupera informações do próximo episódio SET @Url = 'http://api.tvmaze.com/shows/' + CAST(@Id_Serie AS VARCHAR(5)) + '?embed=nextepisode' EXEC CLR.dbo.stpWs_Requisicao @Ds_Url = @URL, -- nvarchar(max) @Ds_Metodo = N'GET', -- nvarchar(max) @Ds_Parametros = N'', -- nvarchar(max) @Ds_Codificacao = N'UTF-8', -- nvarchar(max) @Ds_Retorno_OUTPUT = @Retorno3 OUTPUT -- nvarchar(max) -- Extrai as informações dos episódios com JSON_VALUE e guarda numa tabela IF (OBJECT_ID('tempdb..#Episodios') IS NOT NULL) DROP TABLE #Episodios CREATE TABLE #Episodios ( Ds_Serie NVARCHAR(100), Ds_Tipo VARCHAR(50), Nr_Temporada INT, Ds_Episodio NVARCHAR(100), Nr_Episodio INT, Dt_Episodio DATE, Hr_Episodio NVARCHAR(5) ) INSERT INTO #Episodios SELECT @Serie AS Ds_Serie , 'Anterior' AS Ds_Tipo, JSON_VALUE(@Retorno2, '$._embedded.previousepisode.season') AS Nr_Temporada, JSON_VALUE(@Retorno2, '$._embedded.previousepisode.name') AS Ds_Episodio, JSON_VALUE(@Retorno2, '$._embedded.previousepisode.number') AS Nr_Episodio, JSON_VALUE(@Retorno2, '$._embedded.previousepisode.airdate') AS Dt_Episodio, JSON_VALUE(@Retorno2, '$._embedded.previousepisode.airtime') AS Hr_Episodio UNION ALL SELECT @Serie AS Ds_Serie, 'Próximo' AS Ds_Tipo, ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.season'), '') AS Nr_Temporada, ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.name'), '') AS Ds_Episodio, ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.number'), '') AS Nr_Episodio, ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.airdate'), '') AS Dt_Episodio, ISNULL(JSON_VALUE(@Retorno3, '$._embedded.nextepisode.airtime'), '') AS Hr_Episodio SELECT * FROM #Episodios END |
Procedure usage examples:
A great tip that Reginaldo gave us is to use the site JSON Formatter & Validator to improve the JSON view returned by the Webservice query, as shown below.
Original JSON:
JSON Formatted:
That's it folks!
I hope you enjoyed the post.
If you want to create automated monitoring through a Job in SQL Server Agent, which will email you when an episode is coming up, check out the post I quoted from Reginaldo Silva's blog, which has this solution. over there.
Hug!
Good afternoon Dirceu.
Your post helped me a lot.
But I have a problem ...
What to do if the API return is truncated in the nvarchar (MAX) variable?
@Ds_Retorno_OUTPUT = @ Return1 OUTPUT - nvarchar (max)
Thankful.
Fernando Pancetti