Olá pessoal!
Tudo bem ?

Neste post de hoje vou trazer algo muito legal, que é uma stored procedure Transact-SQL onde é possível consultar e rastrear as informações de encomendas e objetos utilizando dados do próprio site dos Correios no SQL Server. Para isso, vou utilizar o objeto MSXML2.ServerXMLHTTP e procedures OLE Automation para uma solução e o CLR para uma outra alternativa.

Isso é bem útil para empresas de e-Commerce que podem utilizar essa SP para integrar o rastreamento dos Correios a seus sistemas e exibir na tela o andamento da entrega de seus produtos.

Eu já havia mostrado uma solução bem parecida com essa no artigo Consultando o rastreamento de objetos dos Correios pelo SQL Server, mas essa solução parou de funcionar no dia 10/06, quando o Correios resolveu desativar o endereço que era executado no link http://websro.correios.com.br…, utilizado por vários sistemas de e-Commerce para fazer o rastreamento de objetos e no meu post anterior.

Com isso, precisei pensar em outra solução para fazer o rastreamento de objetos. Através de algumas buscas na internet, encontrei o serviço http://www.linkcorreios.com.br/, que permite fazer o rastreamento de forma bem precisa. Vamos ver como ele vai nos permitir rastrear objetos.

Como rastrear objetos utilizando OLE Automation

Uma forma bem simples e rápida de se fazer requisições HTTP Request pelo SQL Server é utilizando o recurso OLE Automation, que invoca bibliotecas e DLL’s externas para realizar vários tarefas pelo banco de dados. Veja neste link alguns outros posts sobre OLE Automation.

Antes de iniciarmos, vejamos alguns pré-requisitos para utilizar essa procedure:
– Habilitar o recurso OLE Automation no seu servidor SQL Server (veja como neste post)
– Função fncRemove_HTML para remover tags HTML de uma string varchar (veja mais neste post)
– Função fncSplitTexto para quebrar uma string em substrings utilizando um delimitador (veja mais neste post)

Um detalhe importante que observei no desenvolvimento dessa SP, é que se a quantidade de caracteres do XML de retornado pela procedure sp_OAGetProperty for superior a 4000 caracteres, o retorno da SP será NULL, uma vez que a procedure sp_OAGetProperty possui essa limitação de 4000 caracteres. Para contornar esse problema, ao invés de usar uma variável de OUTPUT para capturar os dados, eu os insiro em uma tabela temporária e depois trabalho com os dados. 🙂

Visualizar código-fonte da SP

Clique aqui para expandir
CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios]
    @Ds_Rastreamento [varchar](13)
AS
BEGIN


    SET NOCOUNT ON

        
    ------------------------------------------------------------------------
    -- RECUPERAÇÃO DAS INFORMAÇÕES
    ------------------------------------------------------------------------

    DECLARE 
        @obj INT,
        @Url VARCHAR(8000),
        @xml VARCHAR(MAX),
        @resposta VARCHAR(MAX)
        

    SET @Url = 'http://www.linkcorreios.com.br/?id=' + @Ds_Rastreamento


    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, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC sys.sp_OAMethod @obj, 'send'
 
 
    IF (OBJECT_ID('tempdb..#xml') IS NOT NULL) DROP TABLE #xml
    CREATE TABLE #xml ( 
        Ds_Dados VARCHAR(MAX)
    )

    INSERT INTO #xml(Ds_Dados)
    EXEC sys.sp_OAGetProperty @obj, 'responseText' --, @resposta OUT
    
    
    EXEC sys.sp_OADestroy @obj
 
 
    SELECT TOP 1 @resposta = Ds_Dados FROM #xml

    
    SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS
    
    ------------------------------------------------------------------------
    -- TRATAMENTO DOS DADOS
    ------------------------------------------------------------------------

    SET @xml = REPLACE(@xml, '</td><td>', ' | ')
    
    IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados
    SELECT
        ROW_NUMBER() OVER(ORDER BY Id) AS Linha,
        LTRIM(RTRIM(REPLACE(REPLACE(Palavra, CHAR(13), ''), CHAR(9), ''))) AS Palavra
    INTO
        #Dados
    FROM
        dbo.fncSplitTexto(dbo.fncRemove_HTML(@xml), CHAR(10))
    WHERE
        NULLIF(LTRIM(RTRIM(REPLACE(REPLACE(Palavra, CHAR(13), ''), CHAR(9), ''))), '') IS NOT NULL
        
    
    ------------------------------------------------------------------------
    -- RESULTADO FINAL
    ------------------------------------------------------------------------
    
    DECLARE 
        @Linha_Inicio INT = (SELECT Linha FROM #Dados WHERE Palavra = 'Data / Hora') + 2,
        @Linha_Fim INT = (SELECT Linha FROM #Dados WHERE Palavra LIKE '%Objeto atrasado? Problema na entrega?%')

    IF (OBJECT_ID('tempdb..#Dados_Final') IS NOT NULL) DROP TABLE #Dados_Final
    SELECT 
        ROW_NUMBER() OVER(ORDER BY Linha) AS Linha,
        Palavra AS Rastreamento,
        (Linha % 3) AS Grupo
    INTO 
        #Dados_Final
    FROM 
        #Dados
    WHERE 
        Linha >= @Linha_Inicio
        AND Linha < @Linha_Fim


    DECLARE @Tabela_Final TABLE (
        Dt_Rastreamento DATETIME,
        Ds_Rastreamento VARCHAR(255),
        Ds_Local VARCHAR(255)
    )

    DECLARE
        @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Dados_Final) / 3

    WHILE (@Contador < @Total)
    BEGIN
            
        INSERT INTO @Tabela_Final
        SELECT
            CONVERT(DATETIME, (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 1), 103) AS Dt_Rastreamento,
            (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 2) AS Ds_Rastreamento,
            REPLACE((SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 3), 'Local: ', '') AS Ds_Local

        SET @Contador += 1

    END


    SELECT * FROM @Tabela_Final

    
END

Como rastrear objetos utilizando CLR

Uma outra forma de conseguir realizar requisições HTTP pelo SQL Server, é utilizando o CLR, uma poderosa ferramenta que a Microsoft disponibiliza e que permite criar programas escritos em C# ou VB, utilizando todos os recursos do Microsoft .NET Framework e executá-los pelo banco de dados. Caso você ainda não conheça o CLR, dê uma lida neste link aqui e acompanhe todos os meus artigos utilizando o CLR neste link.

Pré-requisitos para utilizar essa procedure:
– Função fncRemove_HTML para remover tags HTML de uma string varchar (veja mais neste post)
– Função fncSplit_Texto para quebrar o HTML em linhas (veja mais neste post)
– Stored Procedure stpWs_Requisicao para realizar as requisições HTTP (veja mais neste post)

Visualizar código-fonte da SP

Clique aqui para expandir
CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios]
    @Ds_Rastreamento [varchar](13)
AS
BEGIN
 
    ------------------------------------------------------------------------
    -- RECUPERAÇÃO DAS INFORMAÇÕES
    ------------------------------------------------------------------------

    DECLARE 
        @obj INT,
        @Url VARCHAR(8000),
        @xml VARCHAR(MAX),
        @resposta VARCHAR(MAX)
        

    SET @Url = 'http://www.linkcorreios.com.br/?id=' + @Ds_Rastreamento


    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)
    

    SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS
    

    ------------------------------------------------------------------------
    -- TRATAMENTO DOS DADOS
    ------------------------------------------------------------------------

    SET @xml = REPLACE(@xml, '</td><td>', ' | ')

    
    IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados
    SELECT
        ROW_NUMBER() OVER(ORDER BY Id) AS Linha,
        LTRIM(RTRIM(REPLACE(Ds_Palavra, CHAR(13), ''))) AS Palavra
    INTO
        #Dados
    FROM
        CLR.dbo.fncSplit_Texto(CLR.dbo.fncRemove_HTML(@xml), CHAR(10), 1)
    WHERE
        NULLIF(REPLACE(Ds_Palavra, CHAR(13), ''), '') IS NOT NULL
    
    ------------------------------------------------------------------------
    -- RESULTADO FINAL
    ------------------------------------------------------------------------
    
    DECLARE 
        @Linha_Inicio INT = (SELECT Linha FROM #Dados WHERE Palavra = 'Data / Hora') + 2,
        @Linha_Fim INT = (SELECT Linha FROM #Dados WHERE Palavra LIKE '%Objeto atrasado? Problema na entrega?%')

    IF (OBJECT_ID('tempdb..#Dados_Final') IS NOT NULL) DROP TABLE #Dados_Final
    SELECT 
        ROW_NUMBER() OVER(ORDER BY Linha) AS Linha,
        Palavra AS Rastreamento,
        (Linha % 3) AS Grupo
    INTO 
        #Dados_Final
    FROM 
        #Dados
    WHERE 
        Linha >= @Linha_Inicio
        AND Linha < @Linha_Fim


    DECLARE @Tabela_Final TABLE (
        Dt_Rastreamento DATETIME,
        Ds_Rastreamento VARCHAR(255),
        Ds_Local VARCHAR(255)
    )

    DECLARE
        @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Dados_Final) / 3

    WHILE (@Contador < @Total)
    BEGIN
            
        INSERT INTO @Tabela_Final
        SELECT
            CONVERT(DATETIME, (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 1), 103) AS Dt_Rastreamento,
            (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 2) AS Ds_Rastreamento,
            REPLACE((SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 3), 'Local: ', '') AS Ds_Local

        SET @Contador += 1

    END


    SELECT * FROM @Tabela_Final
	

END

Resultado das SP’s

Ambas as Stored Procedures tem exatamente o mesmo retorno. Apenas o codificação das duas é que se difere. Uma vez que você escolher qual das duas abordagens quer seguir, o resultado da SP será assim:

É isso aí, pessoal!
Espero que tenham gostado desse post e deixem o seu comentário.

sql server tsql transact sql query rastreamento encomendas objetos correios

sql server tsql transact sql query rastreamento encomendas objetos correios