Hello everybody!
All right with you ?
In this quick post, I will once again demonstrate the use of OLE Automation and CLR procedures to consume information on the Web and bring it to our SQL Server database so that we can work with this information as needed. This time, I will show you how to consult the city, state, neighborhood and street information from an informed zip code, which is widely used today.
Since the API return contains JSON format data, I will use the SQL Server 2016 JSON_VALUE function. If you want to know a lot more about handling JSON strings and files in SQL Server 2016, check out my post SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).
This article is a complement to my post. How to query information from a zip code in SQL Server, where I demonstrated how to query data from a zip code using the Viacep.com.br API and that returned the data in XML format. I decided to make a new post demonstrating how to do this using another API and treating the data in JSON format.
Querying CEP Data
To perform this operation, I will use the great service of the page Bemean Postcode to see the zip code information.
To perform Web requests through SQL Server, you can use the solution using OLE Automation, which I will demonstrate below or using the Stored Procedure. stpWs_Requiring (my favorite form) from SQL CLR (C #), which I demonstrated in the post Performing POST and GET requests using CLR (C #) in SQL Server.
Querying CEP Information Using OLE Automation
Now I will demonstrate how to perform this query using OLE Automation. The routine is prepared to enable the feature on the instance if it is not enabled and deactivate again at the end of execution.
View source:
|
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 |
CREATE PROCEDURE dbo.stpConsulta_CEP_OLE ( @Nr_CEP VARCHAR(20) ) AS BEGIN -------------------------------------------------------------------------------- -- Habilitando o OLE Automation (Se não estiver ativado) -------------------------------------------------------------------------------- DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures') IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXECUTE SP_CONFIGURE 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE; END DECLARE @obj INT, @Url VARCHAR(255), @resposta VARCHAR(8000), @xml XML -- Recupera apenas os números do CEP DECLARE @startingIndex INT = 0 WHILE (1=1) BEGIN SET @startingIndex = PATINDEX('%[^0-9]%', @Nr_CEP) IF (@startingIndex <> 0) SET @Nr_CEP = REPLACE(@Nr_CEP, SUBSTRING(@Nr_CEP, @startingIndex, 1), '') ELSE BREAK END SET @Url = 'https://cep-bemean.herokuapp.com/api/br/' + @Nr_CEP EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT 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 SELECT JSON_VALUE(@resposta, '$.code') AS CEP, JSON_VALUE(@resposta, '$.address') AS Logradouro, JSON_VALUE(@resposta, '$.district') AS Bairro, JSON_VALUE(@resposta, '$.city') AS Cidade, JSON_VALUE(@resposta, '$.state') AS Estado -------------------------------------------------------------------------------- -- Desativando o OLE Automation (Se não estava habilitado antes) -------------------------------------------------------------------------------- IF (@Fl_Ole_Automation_Ativado = 0) BEGIN EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE; EXECUTE SP_CONFIGURE 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; END END |
Example of use:
|
1 2 |
EXEC dbo.stpConsulta_CEP_OLE @Nr_CEP = '29200260' -- varchar(20) |
Result:
Querying CEP Information Using the CLR
Now I will demonstrate how to perform this query using CLR (C #), which in my view is the best solution, although it is more complex to implement and requires knowledge in a programming language (C # or VB).
If you don't know what CLR is and would like to learn more about this great feature that can be used in SQL Server, see the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server. The CLR procedure source code stpWs_Requiring is available in the post Performing POST and GET requests using CLR (C #) in SQL Server.
View source:
|
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 |
CREATE PROCEDURE dbo.stpConsulta_CEP_CLR ( @Nr_CEP VARCHAR(20) ) AS BEGIN -- Recupera apenas os números do CEP DECLARE @startingIndex INT = 0 WHILE (1=1) BEGIN SET @startingIndex = PATINDEX('%[^0-9]%', @Nr_CEP) IF (@startingIndex <> 0) SET @Nr_CEP = REPLACE(@Nr_CEP, SUBSTRING(@Nr_CEP, @startingIndex, 1), '') ELSE BREAK END DECLARE @Url VARCHAR(500) = 'https://cep-bemean.herokuapp.com/api/br/' + @Nr_CEP, @resposta NVARCHAR(MAX); 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 = @resposta OUTPUT -- nvarchar(max) SELECT JSON_VALUE(@resposta, '$.code') AS CEP, JSON_VALUE(@resposta, '$.address') AS Logradouro, JSON_VALUE(@resposta, '$.district') AS Bairro, JSON_VALUE(@resposta, '$.city') AS Cidade, JSON_VALUE(@resposta, '$.state') AS Estado END |
Example of use:
|
1 2 |
EXEC dbo.stpConsulta_CEP_CLR @Nr_CEP = '29200290' -- varchar(20) |
Result:
That's it folks!
I hope you enjoyed this post.
Hug and see you next time.
Hello my friend good morning,
Thank you so much for your publications.
I want to take advantage of this stored procedure to fetch data in another web api,
but when changing the link and changing the parameter the result always returns null, I've done the test without setting any parameter just the full link of the web search api and the result always comes as null.
please some light !!!! kkkkkkkk
Hi Lucas, good night !! What URL are you trying to query?
Good morning my friend,
Follow Link
https://bibipecasapi.azurewebsites.net/api/v1/vehicle/plate/nwh0939?key=cf2a84247634431c958c563094d9850a&token=3a3dbe9ba144495cb286d82f4b3b8c8544da41253f0b42f2bd02d3d43994fbf3
where nwh0939 is the variable.
Grateful already