SQL Server
SQL Server
Isabel Connect Get Account
See more Ibanity Examples
Get the details for a specific account.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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Implements the following CURL command:
-- curl -X GET https://api.ibanity.com/isabel-connect/accounts/93ecb1fdbfb7848e7b7896c0f2d207aed3d8b4c1 \
-- --cert certificate.pem:qwertyuiop1 \
-- --key private_key.pem \
-- -H "Authorization: Bearer access_token_1603365407" \
-- -H "Accept: application/vnd.api+json"
-- Other Chilkat examples for Ibanity show how to set the SSL client certificate using the .pfx.
-- This example will demonstrate using the PEM files.
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/pem/my_ibanity_certificate.pem'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @privKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT
EXEC sp_OAMethod @privKey, 'LoadEncryptedPemFile', @success OUT, 'qa_data/pem/my_ibanity_private_key.pem', 'my_pem_password'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
RETURN
END
EXEC sp_OAMethod @cert, 'SetPrivateKey', @success OUT, @privKey
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
RETURN
END
EXEC sp_OAMethod @http, 'SetSslClientCert', @success OUT, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
RETURN
END
-- Load the previously obtained access token.
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/isabel_access_token.json'
IF @success = 0
BEGIN
PRINT 'No existing access token.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
-- This causes the "Authorization: Bearer ***" header to be added to the HTTP request.
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
EXEC sp_OASetProperty @http, 'Accept', 'application/vnd.api+json'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'id', '93ecb1fdbfb7848e7b7896c0f2d207aed3d8b4c1'
DECLARE @jsonStr nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @jsonStr OUT, 'https://api.ibanity.com/isabel-connect/accounts/{$id}'
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
DECLARE @jResp int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jResp OUT
EXEC sp_OAMethod @jResp, 'Load', @success OUT, @jsonStr
EXEC sp_OASetProperty @jResp, 'EmitCompact', 0
PRINT 'Response Body:'
EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
PRINT @sTmp0
DECLARE @respStatusCode int
EXEC sp_OAGetProperty @http, 'LastStatus', @respStatusCode OUT
PRINT 'Response Status Code = ' + @respStatusCode
IF @respStatusCode >= 400
BEGIN
PRINT 'Response Header:'
EXEC sp_OAGetProperty @http, 'LastResponseHeader', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @jResp
RETURN
END
-- Sample JSON response:
-- (Sample code for parsing the JSON response is shown below)
-- {
-- "data": {
-- "attributes": {
-- "country": "BE",
-- "currency": "EUR",
-- "description": "current account",
-- "financialInstitutionBic": "KREDBEBB",
-- "holderAddress": "STREET NUMBER, ZIPCODE CITY",
-- "holderAddressCountry": "BE",
-- "holderName": "COMPANY",
-- "reference": "BE96153112434405",
-- "referenceType": "IBAN"
-- },
-- "id": "93ecb1fdbfb7848e7b7896c0f2d207aed3d8b4c1",
-- "type": "account"
-- }
-- }
-- Sample code for parsing the JSON response...
-- Use the following online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
DECLARE @dataAttributesCountry nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataAttributesCountry OUT, 'data.attributes.country'
DECLARE @dataAttributesCurrency nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataAttributesCurrency OUT, 'data.attributes.currency'
DECLARE @dataAttributesDescription nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataAttributesDescription OUT, 'data.attributes.description'
DECLARE @dataAttributesFinancialInstitutionBic nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataAttributesFinancialInstitutionBic OUT, 'data.attributes.financialInstitutionBic'
DECLARE @dataAttributesHolderAddress nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataAttributesHolderAddress OUT, 'data.attributes.holderAddress'
DECLARE @dataAttributesHolderAddressCountry nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataAttributesHolderAddressCountry OUT, 'data.attributes.holderAddressCountry'
DECLARE @dataAttributesHolderName nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataAttributesHolderName OUT, 'data.attributes.holderName'
DECLARE @dataAttributesReference nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataAttributesReference OUT, 'data.attributes.reference'
DECLARE @dataAttributesReferenceType nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataAttributesReferenceType OUT, 'data.attributes.referenceType'
DECLARE @dataId nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataId OUT, 'data.id'
DECLARE @dataType nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @dataType OUT, 'data.type'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @jResp
END
GO