SQL Server
SQL Server
Isabel Connect List Transactions
See more Ibanity Examples
Get a list of transactions for an 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/transactions \
-- --cert certificate.pem:qwertyuiop1 \
-- --key private_key.pem \
-- -H "Authorization: Bearer access_token_1603365408" \
-- -H "Accept: application/vnd.api+json"
-- Ibanity provides the certificate + private key in PFX format. This example will use the .pfx instead of the pair of PEM files.
-- (It is also possible to implement using Chilkat with the PEM files, but PFX is easier.)
EXEC sp_OAMethod @http, 'SetSslClientCertPfx', @success OUT, 'qa_data/pfx/my_ibanity_certificate.pfx', 'my_pfx_password'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
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 @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}/transactions'
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 @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 @jsonToken
EXEC @hr = sp_OADestroy @jResp
RETURN
END
-- Sample JSON response:
-- (Sample code for parsing the JSON response is shown below)
-- {
-- "data": [
-- {
-- "attributes": {
-- "amount": 80000,
-- "counterpartAccountReference": "BE21210123456703",
-- "counterpartFinancialInstitutionBic": "GEBABEBB",
-- "counterpartName": "MYBESTCLIENT",
-- "endToEndId": "UNIQUE CODE CUSTOMER",
-- "executionDate": "2018-10-15T08:34:17.417Z",
-- "internalId": "UNIQUE CODE BANK",
-- "remittanceInformation": "123456789002",
-- "remittanceInformationType": "structured-be",
-- "status": "Booked",
-- "valueDate": "2018-10-15T08:52:43.962Z"
-- },
-- "id": "14e2bff5-e365-4bc7-bf48-76b7bcd464e9",
-- "type": "transaction"
-- },
-- {
-- "attributes": {
-- "amount": 40000,
-- "counterpartAccountReference": "BE58400101010179",
-- "counterpartFinancialInstitutionBic": "KREDBEBB",
-- "counterpartName": "MYGOODCLIENT",
-- "endToEndId": null,
-- "executionDate": "2018-10-15T08:34:17.417Z",
-- "internalId": null,
-- "remittanceInformation": "FREE COMMUNICATION",
-- "remittanceInformationType": "unstructured",
-- "status": "Booked",
-- "valueDate": "2018-10-15T08:52:43.962Z"
-- },
-- "id": "29b0f52e-a389-4ff8-88e1-cc30c12b789f",
-- "type": "transaction"
-- },
-- {
-- "attributes": {
-- "amount": -20000,
-- "counterpartAccountReference": "BE56300694353788",
-- "counterpartFinancialInstitutionBic": null,
-- "counterpartName": "MY SUPPLIER",
-- "endToEndId": null,
-- "executionDate": "2018-10-15T08:34:17.417Z",
-- "internalId": null,
-- "remittanceInformation": "999000000171",
-- "remittanceInformationType": "structured-be",
-- "status": "Booked",
-- "valueDate": "2018-10-15T08:52:43.962Z"
-- },
-- "id": "7d79a28e-b45d-4595-b98e-7c18871ee208",
-- "type": "transaction"
-- }
-- ],
-- "meta": {
-- "paging": {
-- "offset": 0,
-- "total": 3
-- }
-- }
-- }
-- 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 @attributesAmount int
DECLARE @attributesCounterpartAccountReference nvarchar(4000)
DECLARE @attributesCounterpartFinancialInstitutionBic nvarchar(4000)
DECLARE @attributesCounterpartName nvarchar(4000)
DECLARE @attributesEndToEndId nvarchar(4000)
DECLARE @attributesExecutionDate nvarchar(4000)
DECLARE @attributesInternalId nvarchar(4000)
DECLARE @attributesRemittanceInformation nvarchar(4000)
DECLARE @attributesRemittanceInformationType nvarchar(4000)
DECLARE @attributesStatus nvarchar(4000)
DECLARE @attributesValueDate nvarchar(4000)
DECLARE @id nvarchar(4000)
DECLARE @v_type nvarchar(4000)
DECLARE @metaPagingOffset int
EXEC sp_OAMethod @jResp, 'IntOf', @metaPagingOffset OUT, 'meta.paging.offset'
DECLARE @metaPagingTotal int
EXEC sp_OAMethod @jResp, 'IntOf', @metaPagingTotal OUT, 'meta.paging.total'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'data'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'IntOf', @attributesAmount OUT, 'data[i].attributes.amount'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesCounterpartAccountReference OUT, 'data[i].attributes.counterpartAccountReference'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesCounterpartFinancialInstitutionBic OUT, 'data[i].attributes.counterpartFinancialInstitutionBic'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesCounterpartName OUT, 'data[i].attributes.counterpartName'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesEndToEndId OUT, 'data[i].attributes.endToEndId'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesExecutionDate OUT, 'data[i].attributes.executionDate'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesInternalId OUT, 'data[i].attributes.internalId'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesRemittanceInformation OUT, 'data[i].attributes.remittanceInformation'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesRemittanceInformationType OUT, 'data[i].attributes.remittanceInformationType'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesStatus OUT, 'data[i].attributes.status'
EXEC sp_OAMethod @jResp, 'StringOf', @attributesValueDate OUT, 'data[i].attributes.valueDate'
EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'data[i].id'
EXEC sp_OAMethod @jResp, 'StringOf', @v_type OUT, 'data[i].type'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @jResp
END
GO