Consumindo JSON com Ole Automation Procedures no SQL Server 2016, alertando sobre seus episódios favoritos (Off-Topic)

Boa noite galera, tirei mais algumas noites para me divertir com SQL Server rs, continuando o assunto anterior sobre minhas séries favoritas, desenvolvi um script utilizando um pouco de ‘Ole automation procedures’ para ‘consumir’ uma ‘Web  API’ e retornar dados em formato ‘JSON’ com informações sobre minhas séries.

O objetivo é o seguinte:
Criar um job diário que consulte os próximos episódios a serem lançados e caso os mesmos estejam prestes a serem lançados, um e-mail de alerta será enviado. Calma amigos, eu sei que existem apps que executam este processo, no entanto como citei anteriormente, o foco aqui é diversão OK? Vamos aproveitar o tempo para aprender mais um pouco sobre SQL Server, let’s go.

Caso ainda não tenha lido o último o post onde eu listei minhas séries segue o link:

https://blogdojamal.wordpress.com/2017/01/09/utilizando-dense_rank-para-classificar-minhas-series-off-topic/

 

Primeiro passo para isso é liberar o ‘Ole automation’ nas configurações do SQL Server.

 

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Agent XPs’, 1;
GO
RECONFIGURE;
GO

Obs: Tome muito cuidado com quem terá acesso a executar essas procedures, assim como o ‘xp_cmdshell’ o ‘Ole Automation procedures’ é uma ferramenta poderosa que pode ser utilizada tanto para o ‘bem’ quanto para o ‘mal’.

Através do ‘Ole Automation procedures’ conseguimos ‘consumir’ um ‘web service’ ou uma ‘web api’ via ‘HTTP’ e retornar essas informações para o SQL Server.

Neste caso iremos consumir um ‘web api’ que nos retornará um ‘JSON’, como sabemos no SQL Server 2016 já conseguimos manipular esse formato de dados.

Vamos então para a primeira parte de códigos.

IF(OBJECT_ID(‘TMP_SERIES’)) IS NOT NULL
DROP TABLE TMP_SERIES

GO

CREATE TABLE TMP_SERIES (SERIENAME VARCHAR(200), TYPE VARCHAR(20), SEASON INT, EPISODENAME VARCHAR(200), EPISODE SMALLINT, AIRDATE DATE, AIRTIME TIME)
GO

IF(OBJECT_ID(‘SP_GETSERIES’)) IS NOT NULL
DROP PROCEDURE SP_GETSERIES

GO

CREATE PROCEDURE SP_GETSERIES(
@SERIENAME NVARCHAR(500)
)
AS BEGIN

DECLARE
@OBJ INT,
@URL VARCHAR(8000),
@JSON VARCHAR(MAX),
@JSON2 VARCHAR(MAX),
@RESPOSTA VARCHAR(8000),
@RESPOSTA2 VARCHAR(8000),
@IDSERIE INT
IF (OBJECT_ID(‘tempdb..#xml’) IS NOT NULL) DROP TABLE #xml
CREATE TABLE #xml (
Ds_Dados VARCHAR(MAX)
)

SET @URL = ‘http://api.tvmaze.com/search/shows?q=’+ @SerieName
EXEC sys.sp_OACreate @progid = ‘MSXML2.ServerXMLHTTP’, @objecttoken = @obj OUT, @context = 1
EXEC sys.sp_OAMethod @obj, ‘open’, NULL, ‘GET’, @URL, false
EXEC sys.sp_OAMethod @obj, ‘send’

INSERT INTO #xml(Ds_Dados)
EXEC sys.sp_OAGetProperty @obj, ‘responseText’
EXEC sys.sp_OADestroy @obj

SELECT @JSON = Ds_Dados from #xml

select @JSON = SUBSTRING(@JSON,2,LEN(@JSON) – 1)
select @IDSERIE = CAST(JSON_VALUE(@JSON,’$.show.id’) AS INT)

SET @Url = ‘http://api.tvmaze.com/shows/’+CAST(@IDSERIE AS VARCHAR(5))+’?embed=previousepisode’
EXEC sys.sp_OACreate @progid = ‘MSXML2.ServerXMLHTTP’, @objecttoken = @obj OUT, @context = 1
EXEC sys.sp_OAMethod @obj, ‘open’, NULL, ‘GET’, @Url, false
EXEC sys.sp_OAMethod @obj, ‘send’
EXEC sys.sp_OAGetProperty @obj, ‘responseText’, @resposta OUT
EXEC sys.sp_OADestroy @obj

SET @Url = ‘http://api.tvmaze.com/shows/’+CAST(@IDSERIE AS VARCHAR(5))+’?embed=nextepisode’
EXEC sys.sp_OACreate @progid = ‘MSXML2.ServerXMLHTTP’, @objecttoken = @obj OUT, @context = 1
EXEC sys.sp_OAMethod @obj, ‘open’, NULL, ‘GET’, @Url, false
EXEC sys.sp_OAMethod @obj, ‘send’
EXEC sys.sp_OAGetProperty @obj, ‘responseText’, @resposta2 OUT
EXEC sys.sp_OADestroy @obj

SET @JSON = @resposta

SET @JSON2 = @resposta2

INSERT INTO TMP_SERIES
select @SERIENAME SERIE,’PREVIOUS’ TYPE,
JSON_VALUE(@JSON,’$._embedded.previousepisode.season’) Season,
JSON_VALUE(@JSON,’$._embedded.previousepisode.name’) Episodename,
JSON_VALUE(@JSON,’$._embedded.previousepisode.number’) Episode,
JSON_VALUE(@JSON,’$._embedded.previousepisode.airdate’) AirDate,
JSON_VALUE(@JSON,’$._embedded.previousepisode.airtime’) Airtime
UNION ALL
select @SERIENAME SERIE,’NEXT’ TYPE,
ISNULL(JSON_VALUE(@JSON2,’$._embedded.nextepisode.season’) ,”) Season,
ISNULL(JSON_VALUE(@JSON2,’$._embedded.nextepisode.name’) ,”) Episodename,
ISNULL(JSON_VALUE(@JSON2,’$._embedded.nextepisode.number’) ,”) Episode,
ISNULL(JSON_VALUE(@JSON2,’$._embedded.nextepisode.airdate’),”) AirDate,
ISNULL(JSON_VALUE(@JSON2,’$._embedded.nextepisode.airtime’),”) Airtime

end

Criando a procedure ‘SP_GETSERIES’ onde recebe o nome da série como parâmetro e via ‘Ole automation procedures’ consome a ‘web api’ que retorna os dados em formato ‘JSON’.

Web API utilizada

http://www.tvmaze.com/api

O primeiro passo na procedure é através do nome da série informada buscar o seu ID no catálogo de séries para posteriormente utilizarmos esse ID na busca dos episódios.

Em ambas as chamadas do método ‘sp_OAGetProperty’ é retornado um texto no formato JSON, podemos validar se o formato retornado é válido através da função ‘ISJSON ()’ do SQL Server 2016.

Navegando pelo ‘JSON’ através da função ‘JSON_VALUE()’ conseguimos extrair os campos necessários para essa necessidade.

Para facilitar a visualização do JSON caso você não tenha nenhum editor que suporte esse formato, sugiro o site abaixo onde é disponibilizado a função de formatar o seu JSON, a visualização fica muito mais simples auxiliando a navegação pelo arquivo.

https://jsonformatter.curiousconcept.com/

 

[{“score”:22.526037,”show”:{“id”:82,”url”:”http://www.tvmaze.com/shows/82/game-of-thrones&#8221;,”name”:”Game of Thrones”,”type”:”Scripted”,”language”:”English”,”genres”:[“Drama”,”Adventure”,”Fantasy”],”status”:”Running”,”runtime”:60,”premiered”:”2011-04-17″,”schedule”:{“time”:”21:00″,”days”:[“Sunday”]},”rating”:{“average”:9.3},”weight”:6,”network”:{“id”:8,”name”:”HBO”,”country”:{“name”:”United States”,”code”:”US”,”timezone”:”America/New_York”}},”webChannel”:null,”externals”:{“tvrage”:24493,”thetvdb”:121361,”imdb”:”tt0944947″},”image”:{“medium”:”http://static.tvmaze.com/uploads/images/medium_portrait/53/132622.jpg&#8221;,”original”:”http://static.tvmaze.com/uploads/images/original_untouched/53/132622.jpg&#8221;},”summary”:”<p>Based on the bestselling book series <em>A Song of Ice and Fire</em> by George R.R. Martin, this sprawling new HBO drama is set in a world where summers span decades and winters can last a lifetime. From the scheming south and the savage eastern lands, to the frozen north and ancient Wall that protects the realm from the mysterious darkness beyond, the powerful families of the Seven Kingdoms are locked in a battle for the Iron Throne. This is a story of duplicity and treachery, nobility and honor, conquest and triumph. In the <em>\”Game of Thrones\”</em>, you either win or you die.</p>”,”updated”:1479078660,”_links”:{“self”:{“href”:”http://api.tvmaze.com/shows/82&#8243;},”previousepisode”:{“href”:”http://api.tvmaze.com/episodes/729575&#8243;},”nextepisode”:{“href”:”http://api.tvmaze.com/episodes/937256&#8243;}}}},{“score”:15.362018,”show”:{“id”:22602,”url”:”http://www.tvmaze.com/shows/22602/hip-hop-tribe-2-game-of-thrones&#8221;,”name”:”Hip Hop Tribe 2 : Game of Thrones”,”type”:”Variety”,”language”:”Korean”,”genres”:[],”status”:”Running”,”runtime”:90,”premiered”:”2016-11-02″,”schedule”:{“time”:””,”days”:[]},”rating”:{“average”:null},”weight”:0,”network”:{“id”:268,”name”:”jTBC”,”country”:{“name”:”Korea, Republic of”,”code”:”KR”,”timezone”:”Asia/Seoul”}},”webChannel”:null,”externals”:{“tvrage”:null,”thetvdb”:null,”imdb”:null},”image”:null,”summary”:””,”updated”:1480592283,”_links”:{“self”:{“href”:”http://api.tvmaze.com/shows/22602&#8243;},”previousepisode”:{“href”:”http://api.tvmaze.com/episodes/1002312&#8243;}}}}]

Acima um exemplo de formato JSON.

 

jsonformatted

Essa é a visualização com o site indicado, no qual você pode alcançar com alguns editores também.

 

series

A procedure ‘SP_GETSERIES’ inicialmente retornava esse result set, porém foi adaptado para a mesma trazer tudo em um só result set e armazenar em uma tabela temporária.

O interessante nesta ‘web api’ é que você não necessita passar o nome exato da série, caso passe por exemplo “Walking dead’,  ela entenderá.

Extraída as informações necessárias do ‘JSON’, inseridas em uma tabela temporária para consumir no próximo passo.

IF(OBJECT_ID(‘SP_LOADSERIES’)) IS NOT NULL
DROP PROCEDURE SP_LOADSERIES

GO

CREATE PROCEDURE SP_LOADSERIES
AS

TRUNCATE TABLE TMP_SERIES

DECLARE @NAME VARCHAR(200)
DECLARE CURSOR_SERIES CURSOR STATIC FOR
SELECT NOME FROM MinhasSeries WHERE Assistindo_Assistido = 1 AND PESO = 5 ORDER BY ID
OPEN CURSOR_SERIES
FETCH NEXT FROM CURSOR_SERIES INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC SP_GETSERIES @SerieName = @NAME

FETCH NEXT FROM CURSOR_SERIES INTO @NAME
END
CLOSE CURSOR_SERIES
DEALLOCATE CURSOR_SERIES
DECLARE @tableHTML NVARCHAR(MAX)

IF EXISTS (SELECT * FROM TMP_SERIES WHERE AIRDATE > GETDATE() AND DATEDIFF(DAY,GETDATE(),AIRDATE) = 3)
BEGIN

SET @tableHTML = N'<H1>Em 3 dias novos episódios</H1>’ + N'<table border=”1″>’ + N'<tr><th BGCOLOR=”#C0C0C0″ WIDTH=200 height=42>Serie</th><th BGCOLOR=”#C0C0C0″ WIDTH=200>Temporada</th> <th BGCOLOR=”#C0C0C0″ WIDTH=200>Nome Episodio</th> <th BGCOLOR=”#C0C0C0″ WIDTH=200>Numero Episodio</th> <th BGCOLOR=”#C0C0C0″ WIDTH=200>Dia / Hora Episodio</th></tr>’ +
CAST(
(SELECT ‘center’ AS ‘td/@align’,
’40’ AS ‘td/@HEIGHT’,td = CAST(SERIENAME AS NVARCHAR(200)) , ‘ ‘, ‘center’ AS ‘td/@align’ ,
’40’ AS ‘td/@HEIGHT’,td = CAST(SEASON AS NVARCHAR(200)) , ‘ ‘, ‘center’ AS ‘td/@align’ ,
’40’ AS ‘td/@HEIGHT’,td = CAST(EPISODENAME AS NVARCHAR(200)) , ‘ ‘, ‘center’ AS ‘td/@align’ ,
’40’ AS ‘td/@HEIGHT’,td = CAST(EPIdODE AS NVARCHAR(200)) , ‘ ‘, ‘center’ AS ‘td/@align’ ,
’40’ AS ‘td/@HEIGHT’,td = CAST(CAST(AIRDATE AS VARCHAR(20)) + ‘ : ‘ + SUBSTRING(CAST(AIRTIME AS VARCHAR(20)),0,6) AS NVARCHAR(200))
FROM
(
SELECT * FROM TMP_SERIES WHERE TYPE = ‘NEXT’ AND AIRDATE > GETDATE() AND DATEDIFF(DAY,GETDATE(),AIRDATE) = 3
)TAB FOR XML PATH(‘tr’) , TYPE ) AS VARCHAR(MAX)) + N'</table>’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Reginaldo Silva’,
@recipients = ‘reginaldo.silva27@gmail.com’,
@subject = ‘Em 3 dias novos episódios.’ ,
@body = @tableHTML,
@importance=’Normal’,
@body_format= ‘HTML’

END

IF EXISTS (SELECT * FROM TMP_SERIES WHERE AIRDATE > GETDATE() AND DATEDIFF(DAY,GETDATE(),AIRDATE) < 90)
BEGIN

SET @tableHTML = N'<H1>É HOJEEE novos episódios</H1>’ + N'<table border=”1″>’ + N'<tr><th BGCOLOR=”#C0C0C0″ WIDTH=200 height=42>Serie</th><th BGCOLOR=”#C0C0C0″ WIDTH=200>Temporada</th> <th BGCOLOR=”#C0C0C0″ WIDTH=200>Nome Episodio</th> <th BGCOLOR=”#C0C0C0″ WIDTH=200>Numero Episodio</th> <th BGCOLOR=”#C0C0C0″ WIDTH=200>Dia / Hora Episodio</th></tr>’ +
CAST(
(SELECT ‘center’ AS ‘td/@align’,
’40’ AS ‘td/@HEIGHT’,td = CAST(SERIENAME AS NVARCHAR(200)) , ‘ ‘, ‘center’ AS ‘td/@align’ ,
’40’ AS ‘td/@HEIGHT’,td = CAST(SEASON AS NVARCHAR(200)) , ‘ ‘, ‘center’ AS ‘td/@align’ ,
’40’ AS ‘td/@HEIGHT’,td = CAST(EPISODENAME AS NVARCHAR(200)) , ‘ ‘, ‘center’ AS ‘td/@align’ ,
’40’ AS ‘td/@HEIGHT’,td = CAST(EPIdODE AS NVARCHAR(200)) , ‘ ‘, ‘center’ AS ‘td/@align’ ,
’40’ AS ‘td/@HEIGHT’,td = CAST(CAST(AIRDATE AS VARCHAR(20)) + ‘ : ‘ + SUBSTRING(CAST(AIRTIME AS VARCHAR(20)),0,6) AS NVARCHAR(200))
FROM
(
SELECT * FROM TMP_SERIES WHERE TYPE = ‘NEXT’ AND AIRDATE > GETDATE() AND DATEDIFF(DAY,GETDATE(),AIRDATE) < 90
)TAB FOR XML PATH(‘tr’) , TYPE ) AS VARCHAR(MAX)) + N'</table>’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Reginaldo Silva’,
@recipients = ‘reginaldo.silva27@gmail.com’,
@subject = ‘HOJEEE novos episódios.’ ,
@body = @tableHTML,
@importance=’High’,
@body_format= ‘HTML’

END

Criada a procedure ‘SP_LOADSERIES’ para automatizar a busca dos episódios e enviar o alerta.

Através da tabela ‘MinhasSeries’ selecionando apenas as que tem peso 5, ou seja, as top séries, passando uma por uma através de um ‘CURSOR’ para a procedure ‘SP_GETSERIES’ a qual vai alimentando a tabela temporária ‘TMP_SERIES’, onde logo abaixo é utilizada para filtrar apenas os episódios que estão prestes a serem lançados. Será enviado um alerta via e-mail caso algum episódio estiver exatamente a 3 dias de ser lançado ou o mesmo seja lançado ainda hoje.

O resultado é formatado em HTML para uma melhor visualização do e-mail.

Caso não tenha configurado o ‘DatabaseMail’ o link abaixo pode te auxiliar:

https://www.oficinadanet.com.br/artigo/2007/configurar_o_database_mail_

 

USE [msdb]
GO

/****** Object: Job [GET TV SERIES] Script Date: 1/19/2017 10:21:36 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 1/19/2017 10:21:36 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’GET TV SERIES’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’DESKTOP-S9O6HKD\Reginaldo’,
@notify_email_operator_name=N’Alerta_BD’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [LOAD TEMP] Script Date: 1/19/2017 10:21:36 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’LOAD TEMP’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’EXEC SP_LOADSERIES’,
@database_name=N’DB_SERIES’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’DAILY’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20170119,
@active_end_date=99991231,
@active_start_time=120000,
@active_end_time=235959,
@schedule_uid=N’1725e3dc-3a34-4056-8727-6e1ce878277b’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Criado um JOB para executar diariamente ás 12h00 pm, apenas para executar a procedure ‘SP_LOADSERIES’.

 

jobexecution

Após executar o job com sucesso, logo receberemos um e-mail caso houver episódios prestes a serem lançados.

 

email2

Afim de simular o e-mail alteramos o parâmetro de 3 dias para 90 dias na busca dos episódios. E para surpresa dos fãs olha o que vem por aí ‘Prison Break’.

Nosso objetivo esta concluído, agora sempre que os episódios de nossas séries favoritas estiverem prestes a serem lançados nosso amigão SQL Server é quem irá nos alertar.

Bom galera é isso aí, espero que tenham gostado dessa brincadeira apesar do ‘Off Topic’ pois falamos de seriados, tem muito conteúdo legal para estudar, pegue os códigos e se divirta, a imaginação é o limite.

 

 

Referências:

Utilizando JSON no SQL Server 2016

https://msdn.microsoft.com/en-us/library/dn921897.aspx?f=255&MSPPError=-2147217396

Web API

http://www.tvmaze.com/api

Formatando JSON

https://jsonformatter.curiousconcept.com/

Ole Automation procedures

https://msdn.microsoft.com/en-us/library/ms191188.aspx

https://www.dirceuresende.com/banco-de-dados/sql-server/ole-automation/

Minhas séries

https://blogdojamal.wordpress.com/2017/01/09/utilizando-dense_rank-para-classificar-minhas-series-off-topic/

XML e JSON

https://www.infoq.com/br/news/2013/11/xml-json-performance

DatabaseMail

https://www.oficinadanet.com.br/artigo/2007/configurar_o_database_mail_

 

Reginaldo Silva

 

 

 

Anúncios

Um comentário sobre “Consumindo JSON com Ole Automation Procedures no SQL Server 2016, alertando sobre seus episódios favoritos (Off-Topic)

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s