En el post de hoy voy a traer algo realmente interesante, que es un procedimiento almacenado Transact-SQL donde es posible consultar y rastrear información sobre pedidos y objetos utilizando datos del propio sitio web de Correios en SQL Server. Para hacer esto, usaré el objeto MSXML2.ServerXMLHTTP y los procedimientos de automatización OLE para una solución y CLR para otra alternativa.
Esto es muy útil para empresas de comercio electrónico que pueden utilizar este SP para integrar el seguimiento de Correios en sus sistemas y mostrar el progreso de entrega de sus productos en la pantalla.
Ya había mostrado una solución muy similar a esta en el artículo. Consultar el seguimiento de objetos de Correios usando SQL Server, pero esta solución dejó de funcionar el 06/10, cuando Correios decidió desactivar la dirección que se ejecutaba en el enlace http://websro.correios.com.br…, utilizado por varios sistemas de comercio electrónico para rastrear objetos y en mi publicación anterior.
Como resultado, necesitaba pensar en otra solución para rastrear objetos. A través de algunas búsquedas en Internet, encontré el servicio. http://www.linkcorreios.com.br/, que le permite realizar un seguimiento con mucha precisión. Veamos cómo nos permitirá rastrear objetos.
Cómo rastrear objetos usando OLE Automation
Una forma muy sencilla y rápida de realizar solicitudes HTTP a través de SQL Server es mediante la función OLE Automation, que invoca bibliotecas externas y archivos DLL para realizar diversas tareas en la base de datos. Mirar en este enlace algunas otras publicaciones sobre OLE Automation.
Antes de comenzar, veamos algunos requisitos previos para utilizar este procedimiento.:
– Habilite la función OLE Automation en su servidor SQL Server (vea cómo en esta publicación)
– Función fncRemove_HTML para eliminar etiquetas HTML de una cadena varchar (ver más en esta publicación)
– Función fncSplitTexto para dividir una cadena en subcadenas usando un delimitador (ver más en esta publicación)
Un detalle importante que observé en el desarrollo de este SP es que si el número de caracteres en el XML que devuelve el procedimiento sp_OAGetProperty es mayor a 4000 caracteres, el retorno del SP será NULL, ya que el procedimiento sp_OAGetProperty tiene esta limitación de 4000 caracteres. Para solucionar este problema, en lugar de utilizar una variable de SALIDA para capturar los datos, la inserto en una tabla temporal y luego trabajo con los datos. 🙂
Ver el código fuente del SP
Haga clic aquí para ampliar
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
Cómo rastrear objetos usando CLR
Otra forma de realizar solicitudes HTTP a través de SQL Server es mediante el uso de CLR, una poderosa herramienta que Microsoft pone a disposición y que permite crear programas escritos en C# o VB, utilizando todos los recursos de Microsoft .NET Framework y ejecutándolos a través de la base de datos. Si aún no conoces CLR, léelo en este enlace aquí y sigue todos mis artículos usando CLR en este enlace.
Requisitos previos para utilizar este procedimiento:
– Función fncRemove_HTML para eliminar etiquetas HTML de una cadena varchar (ver más en esta publicación)
– Función fncSplit_Texto para dividir HTML en líneas (ver más en esta publicación)
– Procedimiento almacenado stpWs_Requisicao para realizar solicitudes HTTP (ver más en esta publicación)
Ver el código fuente del SP
Haga clic aquí para ampliar
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 de los SP
Ambos procedimientos almacenados tienen exactamente el mismo retorno. Sólo difiere la codificación de los dos. Una vez que elijas cuál de los dos enfoques quieres seguir, el resultado del SP será el siguiente:
¡Eso es todo, amigos!
Espero que te haya gustado este post y dejes tu comentario.
Comentários (0)
Carregando comentários…