Views: 5.053 views
Hello people!
Good afternoon!
In this post, I will demonstrate once again the use of OLE Automation SQL Server to consume data from a Web service. This time, I will consume the Post Office service to calculate shipping time and value (widely used for e-Commerce).
Knowing the Post Office WebService
Before we start creating the Stored Procedure that will query the data, we must first understand the parameters and flags of the Post Office service.
WebService Post Office - Input Parameters
| Field | Type | Required | Description |
|---|---|---|---|
| nCdCompany | String | No. The parameter must be passed even empty. | Your administrative code with ECT. The code is available in the body of the contract signed with the Post Office. |
| sDsPassword | String | No. The parameter must be passed even empty. | Password for access to the service associated with your administrative code. The initial password corresponds to the first 8 CNPJ digits entered in the contract. |
| nCdService | String | Yes | Service Code |
| sCepOrigin | String | Yes | Source ZIP Code without hyphen.Example: 05311900 |
| sCepDestination | String | Yes | Destination ZIP Code without hyphen |
| nVlWeight | Decimal | Yes | Order weight including its packaging. Weight should be reported in kilograms. |
| nCdFormat | Int | Yes | Order format (including packaging). 1 - Box / Package Format / 2 - Roll / Prism Format |
| nVlLength | Decimal | Yes | Order length (including packaging) in centimeters. |
| nVlHeight | Decimal | Yes | Order height (including packaging) in centimeters. |
| nVlWidth | Decimal | Yes | Order width (including packaging) in centimeters. |
| nVlDiameter | Decimal | Yes | Order diameter (including packaging) in centimeters. |
| sCdMaoPropria | String | Yes | Indicates whether the order will be delivered with the additional service by hand. Possible values: Y or N (Y - Yes / N - No) |
| nVlValueDeclarado | Decimal | Yes | Indicates whether the order will be delivered with the additional service stated value. In this field the desired declared value in Reais must be presented. |
| sCdWarningReceiving | String | Yes | Indicates whether the order will be delivered with the additional acknowledgment service. Possible values: Y or N (Y - Yes / N - No) |
| StrReturn | String | Yes | Indicates how to return the query. XML = Result in XML / Popup = Result in a popup window / = Result via post on a requestor's page |
WebService Post Offices - Return Parameters
| Output Parameter | Description |
|---|---|
| Code | Delivery Service Code. |
| ValueValueDeclared | Price of additional service Declared Value. |
| Value | Total price of the order in Reais including the prices of optional services. |
| ProperMoonValue | Price of additional own-hand service. |
| AmountWarningReceiving | Price of additional service Notice of Receipt. |
| DeadlineDelivery | Estimated time in days for product delivery. If the returned value is 0 (zero) it indicates that the deadline was not returned correctly. |
| Home Delivery | Informs if the informed location has home delivery. If the deadline is not returned correctly the return of this parameter will be empty. |
| DeliverySaturday | Informs if the informed locality has home delivery on Saturdays. If the deadline is not returned correctly the return of this parameter will be empty. |
| Errö | Same as Web Services. |
| MsgError | Returns the description of the generated error. |
WebService Post Office - Service Codes
| Service Codes | |
|---|---|
| 41106 | PAC without contract |
| 40010 | SEDEX without contract |
| 40045 | SEDEX Charge without contract |
| 40126 | SEDEX Collect on Contract |
| 40215 | SEDEX 10 without contract |
| 40290 | SEDEX Today without contract |
| 40096 | SEDEX with contract |
| 40436 | SEDEX with contract |
| 40444 | SEDEX with contract |
| 81019 | e-SEDEX with contract |
| 41068 | PAC with contract |
| 40568 | SEDEX with contract |
| 40606 | SEDEX with contract |
| 81868 | (1 Group) e-SEDEX with contract |
| 81833 | (2 Group) e-SEDEX with contract |
| 81850 | (3 Group) e-SEDEX with contract |
Creating the Stored Procedure to Query the Data
With the code snippet below, we will create the Stored Procedure that we will use to query freight value and lead time data using procedures. OLE Automation to perform the GET request and return the XML from the web, which we will read using 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 |
Examples of use
Performing a simple test, filling in only the required parameters
Performing a more complete test, filling all SP parameters
Calling SP, but entering an invalid zip code
That's it folks!
Until the next post!
Hi Dirceu I found this post great, I performed several tests with webservice using this same logic. My question is what is the advantage or disadvantage of developing webservice communication via SQL or via C # programming language for example? Where I work Developers use C # to communicate with various webservices, and in some cases it seems to me to be even more laborious and costly to do so.
Thiago,
Good evening and thanks for stopping by.
For this example, the mail webservice is very simple and responds to simple HTTP requests. For this kind of situation, querying is much simpler even by SQL server than by C #, although I have even made a post on how to create an SP in C # and execute it from SQL server (using CLR) to perform post requests and get that is even easier than using OLE Automation.
However, when you are going to work with more complete Webservices, which have WSDL, call methods, complex type returns, etc., this cannot be used by pure SQL server. In such cases, you need a programming language to do this. In the company I work for, I usually create Webservice integration in visual studio, which already reads the Webservice URL and generates the C # ready classes and encapsulates it into my CLR library, where I can query Webservice through SQL server and return as an XML or even already treat XML by C # itself and return the data as a table.
Probably this weekend I will create a post about it 🙂