SQL Server Requires Chilkat v11.0.0+
SQL Server
Send POST to Bradesco Platform with Billing Ticket for Registration
See more HTTP Misc Examples
Sends a POST request to the Bradesco platform containing the JSON data of the Billing Ticket for registration.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- First build the JSON containing the data to be sent.
-- {
-- "nuCPFCNPJ":"12668",
-- "filialCPFCNPJ":"1",
-- "ctrlCPFCNPJ":"59",
-- "cdTipoAcesso":"2",
-- "idProduto":"9",
-- "nuNegociacao":"262200000000005577",
-- "nuCliente":"SEU_NUM_CLIENTE",
-- "dtEmissaoTitulo":"21.09.2016",
-- "dtVencimentoTitulo":"21.09.2016",
-- "vlNominalTitulo":"888888",
-- "cdEspecieTitulo":"2",
-- "nomePagador":"NOME DO PAGADOR",
-- "logradouroPagador":"ENDERECO DO PAGADOR",
-- "nuLogradouroPagador":"1145",
-- "complementoLogradouroPagador":"APTO 34",
-- "cepPagador":"5588",
-- "complementoCepPagador":"1",
-- "bairroPagador":"BAIRRO PAGADOR",
-- "municipioPagador":"MUNICIPIO PAGADOR",
-- "ufPagador":"SP",
-- "cdIndCpfcnpjPagador":"2",
-- "nuCpfcnpjPagador":"12668000159",
-- "endEletronicoPagador":"PAGADOR@BRADESCO.COM.BR",
-- "nomeSacadorAvalista":"NOME SACADOR AVALISTA",
-- "logradouroSacadorAvalista":"ENDERECO SACADOR AVALISTA",
-- "nuLogradouroSacadorAvalista":"5555",
-- "complementoLogradouroSacadorAvalista":"BLOCO 23",
-- "cepSacadorAvalista":"6182",
-- "complementoCepSacadorAvalista":"160",
-- "bairroSacadorAvalista":"BAIRRO SACADOR AVALISTA",
-- "municipioSacadorAvalista":"MUNICIPIO SACADOR AVALISTA",
-- "ufSacadorAvalista":"SP",
-- "cdIndCpfcnpjSacadorAvalista":"2",
-- "nuCpfcnpjSacadorAvalista":"12668000159",
-- "endEletronicoSacadorAvalista":"SACADOR@BRADESCO.COM.BR",
-- }
--
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'nuCPFCNPJ', '12668'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'filialCPFCNPJ', '1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'ctrlCPFCNPJ', '59'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'cdTipoAcesso', '2'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'idProduto', '9'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'nuNegociacao', '262200000000005577'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'nuCliente', 'SEU_NUM_CLIENTE'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'dtEmissaoTitulo', '21.09.2016'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'dtVencimentoTitulo', '21.09.2016'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'vlNominalTitulo', '888888'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'cdEspecieTitulo', '2'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'nomePagador', 'NOME DO PAGADOR'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'logradouroPagador', 'ENDERECO DO PAGADOR'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'nuLogradouroPagador', '1145'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'complementoLogradouroPagador', 'APTO 34'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'cepPagador', '5588'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'complementoCepPagador', '1'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'bairroPagador', 'BAIRRO PAGADOR'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'municipioPagador', 'MUNICIPIO PAGADOR'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'ufPagador', 'SP'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'cdIndCpfcnpjPagador', '2'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'nuCpfcnpjPagador', '12668000159'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'endEletronicoPagador', 'PAGADOR@BRADESCO.COM.BR'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'nomeSacadorAvalista', 'NOME SACADOR AVALISTA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'logradouroSacadorAvalista', 'ENDERECO SACADOR AVALISTA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'nuLogradouroSacadorAvalista', '5555'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'complementoLogradouroSacadorAvalista', 'BLOCO 23'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'cepSacadorAvalista', '6182'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'complementoCepSacadorAvalista', '160'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'bairroSacadorAvalista', 'BAIRRO SACADOR AVALISTA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'municipioSacadorAvalista', 'MUNICIPIO SACADOR AVALISTA'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'ufSacadorAvalista', 'SP'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'cdIndCpfcnpjSacadorAvalista', '2'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'nuCpfcnpjSacadorAvalista', '12668000159'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'endEletronicoSacadorAvalista', 'SACADOR@BRADESCO.COM.BR'
-- Load out PFX file
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/cert_test123.pfx', 'test123'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
EXEC sp_OAMethod @crypt, 'SetSigningCert', @success OUT, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
RETURN
END
-- Use SHA-256 within the signing..
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
-- Use no authenticated attributes
EXEC sp_OASetProperty @crypt, 'SigningAttributes', '{}'
EXEC sp_OASetProperty @json, 'EmitCompact', 1
DECLARE @sigBase64 nvarchar(4000)
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @crypt, 'OpaqueSignStringENC', @sigBase64 OUT, @sTmp0
EXEC sp_OAGetProperty @crypt, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
RETURN
END
-- Next, we need to send a POST to the following URL where the body of the POST
-- contains the binary PKCS7 signature (which embeds the JSON).
DECLARE @url nvarchar(4000)
SELECT @url = 'https://cobranca.bradesconetempresa.b.br/ibpjregistrotitulows/registrotitulohomologacao'
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
-- This is strange because apparently the server wants a "text/xml" Content-Type,
-- but the content in the body is not actually XML, it's binary PKCS7.
EXEC sp_OASetProperty @req, 'ContentType', 'text/xml'
EXEC sp_OAMethod @req, 'LoadBodyFromString', @success OUT, @sigBase64, 'utf-8'
-- We don't actually pass the URL, we set the path here, and the domain is passed below..
EXEC sp_OASetProperty @req, 'Path', '/ibpjregistrotitulows/registrotitulohomologacao'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpSReq', @success OUT, 'cobranca.bradesconetempresa.b.br', 443, 1, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response status code: ' + @iTmp0
PRINT 'Response body:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
END
GO