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

Banco de Dados CLR Integrações OLE Automation SQL Server clr correios ole automation sql sql server