Visualizações: 5.213 views
Olá, pessoal!
Boa tarde!
Neste post, vou demonstrar mais uma vez a utilização de OLE Automation no SQL Server para consumir os dados de um serviço da Web. Dessa vez, vou consumir o serviço dos Correios para calcular prazo e valor do frete (muito utilizado pra e-Commerce).
Conhecendo o WebService dos Correios
Antes de iniciarmos a criação da Stored Procedure que irá consultar os dados, precisamos primeiro entender os parâmetros e flags do serviço dos Correios.
WebService Correios - Parâmetros de Entrada
| Campo | Tipo | Obrigatório | Descrição |
|---|---|---|---|
| nCdEmpresa | String | Não. O parâmetro tem que ser passado mesmo vazio. | Seu código administrativo junto à ECT. O código está disponível no corpo do contrato firmado com os Correios. |
| sDsSenha | String | Não. O parâmetro tem que ser passado mesmo vazio. | Senha para acesso ao serviço associada ao seu código administrativo. A senha inicial corresponde aos 8 primeiros dígitos do CNPJ informado no contrato. |
| nCdServico | String | Sim | Código do serviço |
| sCepOrigem | String | Sim | CEP de Origem sem hífen.Exemplo: 05311900 |
| sCepDestino | String | Sim | CEP de Destino sem hífen |
| nVlPeso | Decimal | Sim | Peso da encomenda incluindo sua embalagem. O peso deve ser informado em quilogramas. |
| nCdFormato | Int | Sim | Formato da encomenda (incluindo embalagem). 1 – Formato caixa/pacote / 2 – Formato rolo/prisma |
| nVlComprimento | Decimal | Sim | Comprimento da encomenda (incluindo embalagem) em centímetros. |
| nVlAltura | Decimal | Sim | Altura da encomenda (incluindo embalagem) em centímetros. |
| nVlLargura | Decimal | Sim | Largura da encomenda (incluindo embalagem) em centímetros. |
| nVlDiametro | Decimal | Sim | Diâmetro da encomenda (incluindo embalagem) em centímetros. |
| sCdMaoPropria | String | Sim | Indica se a encomenda será entregue com o serviço adicional mão própria. Valores possíveis: S ou N (S – Sim / N – Não) |
| nVlValorDeclarado | Decimal | Sim | Indica se a encomenda será entregue com o serviço adicional valor declarado. Neste campo deve ser apresentado o valor declarado desejado em Reais. |
| sCdAvisoRecebimento | String | Sim | Indica se a encomenda será entregue com o serviço adicional aviso de recebimento. Valores possíveis: S ou N (S – Sim / N – Não) |
| StrRetorno | String | Sim | Indica a forma de retorno da consulta. XML = Resultado em XML / Popup = Resultado em uma janela popup / <URL> = Resultado via post em uma página do requisitante |
WebService Correios - Parâmetros de Retorno
| Parâmetro de Saída | Descrição |
|---|---|
| Codigo | Código do Serviço de Entrega. |
| ValorValorDeclarado | Preço do serviço adicional Valor Declarado. |
| Valor | Preço total da encomenda em Reais incluindo os preços dos serviços opcionais. |
| ValorMaoPropria | Preço do serviço adicional Mão Própria. |
| ValorAvisoRecebimento | Preço do serviço adicional Aviso de Recebimento. |
| PrazoEntrega | Prazo estimado em dias para entrega do produto. Se o valor retornado for 0 (zero) indica que o prazo não foi retornado corretamente. |
| EntregaDomiciliar | Informa se a localidade informada possui entrega domiciliária. Se o prazo não for retornado corretamente o retorno deste parâmetro será vazio. |
| EntregaSabado | Informa se a localidade informada possui entrega domiciliária aos sábados. Se o prazo não for retornado corretamente o retorno deste parâmetro será vazio. |
| Erro | Os mesmos do Web Services. |
| MsgErro | Retorna a descrição do erro gerado. |
WebService Correios - Códigos de Serviço
| Códigos de Serviço | |
|---|---|
| 41106 | PAC sem contrato |
| 40010 | SEDEX sem contrato |
| 40045 | SEDEX a Cobrar sem contrato |
| 40126 | SEDEX a Cobrar com contrato |
| 40215 | SEDEX 10 sem contrato |
| 40290 | SEDEX Hoje sem contrato |
| 40096 | SEDEX com contrato |
| 40436 | SEDEX com contrato |
| 40444 | SEDEX com contrato |
| 81019 | e-SEDEX com contrato |
| 41068 | PAC com contrato |
| 40568 | SEDEX com contrato |
| 40606 | SEDEX com contrato |
| 81868 | (Grupo 1) e-SEDEX com contrato |
| 81833 | (Grupo 2) e-SEDEX com contrato |
| 81850 | (Grupo 3) e-SEDEX com contrato |
Criando a Stored Procedure para consultar os dados
Com o trecho de código abaixo, iremos criar a Stored Procedure que iremos utilizar para consultar os dados de valor do frete e prazo de entrega utilizando procedures OLE Automation para realizar a requisição GET e retornar o XML da Web, que iremos ler utilizando XQuery.
|
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 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
CREATE PROCEDURE dbo.stpConsulta_Frete_Correios ( @sCepOrigem VARCHAR(12), @sCepDestino VARCHAR(12), @nCdServico INT, @nVlPeso FLOAT = 0.1, @nCdFormato SMALLINT = 1, @nVlComprimento INT = 20, @nVlAltura INT = 5, @nVlLargura INT = 15, @nVlDiametro INT = 0, @CdMaoPropria CHAR(1) = 'n', @nVlValorDeclarado FLOAT = 0, @CdAvisoRecebimento CHAR(1) = 'n' ) AS BEGIN DECLARE @obj INT, @Url VARCHAR(500), @resposta VARCHAR(8000), @xml XML -- Recupera apenas os números do CEP de Origem DECLARE @startingIndex INT = 0 WHILE (1=1) BEGIN SET @startingIndex = PATINDEX('%[^0-9]%', @sCepOrigem) IF (@startingIndex <> 0) SET @sCepOrigem = REPLACE(@sCepOrigem, SUBSTRING(@sCepOrigem, @startingIndex, 1), '') ELSE BREAK END -- Recupera apenas os números do CEP de Destino SET @startingIndex = 0 WHILE (1=1) BEGIN SET @startingIndex = PATINDEX('%[^0-9]%', @sCepDestino) IF (@startingIndex <> 0) SET @sCepDestino = REPLACE(@sCepDestino, SUBSTRING(@sCepDestino, @startingIndex, 1), '') ELSE BREAK END SET @Url = 'http://ws.correios.com.br/calculador/CalcPrecoPrazo.aspx?' + 'sCepOrigem=' + @sCepOrigem + '&sCepDestino=' + @sCepDestino + '&nVlPeso=' + CAST(@nVlPeso AS VARCHAR(20)) + '&nCdFormato=' + CAST(@nCdFormato AS VARCHAR(20)) + '&nVlComprimento=' + CAST(@nVlComprimento AS VARCHAR(20)) + '&nVlAltura=' + CAST(@nVlAltura AS VARCHAR(20)) + '&nVlLargura=' + CAST(@nVlLargura AS VARCHAR(20)) + '&sCdMaoPropria=' + @CdMaoPropria + '&nVlValorDeclarado=' + CAST(@nVlValorDeclarado AS VARCHAR(20)) + '&sCdAvisoRecebimento=' + @CdAvisoRecebimento + '&nCdServico=' + CAST(@nCdServico AS VARCHAR(20)) + '&nVlDiametro=' + CAST(@nVlDiametro AS VARCHAR(20)) + '&StrRetorno=xml' 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 SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS SELECT @xml.value('(/Servicos/cServico/Codigo)[1]', 'bigint') AS Codigo_Servico, @xml.value('(/Servicos/cServico/Valor)[1]', 'varchar(20)') AS Valor_Frete, @xml.value('(/Servicos/cServico/PrazoEntrega)[1]', 'int') AS Prazo_Entrega, @xml.value('(/Servicos/cServico/ValorSemAdicionais)[1]', 'varchar(20)') AS Valor_Sem_Adicionais, @xml.value('(/Servicos/cServico/ValorMaoPropria)[1]', 'varchar(20)') AS Valor_Entrega_Em_Maos, @xml.value('(/Servicos/cServico/ValorAvisoRecebimento)[1]', 'varchar(20)') AS Valor_Aviso_Recebimento, @xml.value('(/Servicos/cServico/EntregaDomiciliar)[1]', 'varchar(20)') AS Entrega_Domiciliar, @xml.value('(/Servicos/cServico/EntregaDomiciliar)[1]', 'varchar(1)') AS Entrega_Domiciliar, @xml.value('(/Servicos/cServico/EntregaSabado)[1]', 'varchar(1)') AS Entrega_Sabado, @xml.value('(/Servicos/cServico/Erro)[1]', 'int') AS Codigo_Erro, @xml.value('(/Servicos/cServico/MsgErro)[1]', 'varchar(500)') AS Mensagem_Erro END |
Exemplos de uso
Realizando um teste simples, preenchendo só os parâmetros obrigatórios
Realizando um teste mais completo, preenchendo todos os parâmetros da SP
Chamando a SP, mas informando um CEP inválido
É isso aí, pessoal!
Até o próximo post!
Portuguese 
English
Olá Dirceu achei esse post otimo, realizei varios testes com webservice usando essa mesma lógica. A minha duvida fica com base em qual a vantagem ou desvantagem de desenvolver a comunicação do webservice via SQL ou via linguagem de programação como C# por exemplo? Aonde trabalho os desenvolvedores usam C# para a comunicação com diversos webservices e em alguns casos me parece ser até mais trabalhoso e oneroso atuar dessa maneira.
Thiago,
Boa noite e obrigado pela visita.
No caso desse exemplo, o webservice dos correios é bem simples e responde a requisições simples HTTP. Para esse tipo de situação, realizar consultas é bem mais simples mesmo pelo SQL server do que pelo C#, embora eu tenha até feito um post de como criar uma SP no c# e executar pelo SQL server (Usando o CLR) para realizar requisições post e get que fica até mais fácil do que usando OLE Automation.
Porém, quando você vai trabalhar com Webservices mais completos, que possuem WSDL, métodos de chamadas, retornos de tipos complexos, etc, isso não é possível de ser utilizado pelo SQL server puro. Nesses casos, você precisa de uma linguagem de programação para atuar nisso. Na empresa que eu trabalho, eu costumo criar a integração com o Webservice no visual studio, que já lê a URL do Webservice e gera as classes prontas em C# e encapsulo isso na minha biblioteca CLR, onde posso consultar o Webservice pelo SQL server e retornar como um XML ou mesmo já tratar o XML pelo próprio C# e retornar os dados como tabela.
Provavelmente nesse fim de semana vou criar uma post sobre isso 🙂