SQL Server - Como rastrear encomendas e objetos dos correios (após desativação do WEBSRO)

Post Views 2,581 views

Neste artigo

Reading time 5 minutes

Hey Guys!
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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios]

@Ds_Rastreamento [varchar](13)

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 in this link here 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

CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios]

@Ds_Rastreamento [varchar](13)

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:

And that's it, folks!
Espero que tenham gostado desse post e deixem o seu comentário.

sql server tsql transact sql query rastreamento encomendas objetos correios

Francisco disse:

December 6, 2020 às 18:03

Boa noite Dirceu. Ótimo trabalho, rodei no servidor mas está retornando valor zero. Saberia me dar uma dica se estou fazendo algo errado no SQL server

Responder
Marcos F. Rodrigues disse:

August 29, 2019 às 12:31

Excelente, não conhecia nada pro SQL Server. muito bom. Já uso Link Correios alguns anos para enviar aos clientes da loja da minha esposa, eles também deixam colocar logo da loja para quem possui e-commerce. São realmente muito bons. Esses outros sites são lentos e caem. o LinkCorreios.com.br é incrível pois todo mundo cai e eles não. Será que poderias ensinar algo similar usando o http://www.supertrack.com.br para rastrear Fedex e JadLog?

Responder
- Dirceu Resende disse:
  
  September 18, 2019 às 23:00
  
  Estou adicionando à minha lista de postagens. Obrigado pela dica 🙂
  
  Responder
Paulo Chaves disse:

December 6, 2018 às 18:25

Oi Dirceu,
Gostei muito do seu artigo, eu já tinha feito algo parecido usando a api do site Link & Track (www.linketrack.com), eles devolvem a resposta em JSON, CSV OU XML o que facilita bastante a manipulação da resposta, ao invés de fazer scrapping de HTML.

Responder
- Dirceu Resende disse:
  
  December 7, 2018 às 07:55
  
  Boa dica, hein Paulo! Vou dar uma olhada e faço em artigo em breve sobre isso 😉
  Valeu!!
  
  Responder

Websro Correios | www.websro.com.br - Entrar

December 3, 2021

[…] SQL Server – Como rastrear encomendas e objetos dos … […]

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Como rastrear encomendas e objetos dos correios (após desativação do WEBSRO)

Como rastrear objetos utilizando OLE Automation

Visualizar código-fonte da SP

Como rastrear objetos utilizando CLR

Visualizar código-fonte da SP

Resultado das SP’s

sql server tsql transact sql query rastreamento encomendas objetos correios

You may also like...

6 Responses

Deixe uma respostaCancelar resposta

Como rastrear objetos utilizando OLE Automation

Visualizar código-fonte da SP

Como rastrear objetos utilizando CLR

Visualizar código-fonte da SP

Resultado das SP’s

sql server tsql transact sql query rastreamento encomendas objetos correios

You may also like...

SQL Server – Como transferir logins entre instâncias gerando backup de usuários, logins e permissões

Meet Power CLR: SQL Server Integration with APIs, Files, Webservices, FTP and more!

SQL Server – O que é operação bitwise e como armazenar vários valores na mesma coluna

6 Responses

Deixe uma respostaCancelar resposta