SQL Server
SQL Server
Rabobank GET /v3/accounts
See more Rabobank Examples
Demonstrates a call to a Rabobank API. Specifically this makes a call to the "Accounts AIS sb" API to deliver all the consented payments accounts for a specific Rabobank customer.(This includes source code to for signing requests for PSD2 APIs)
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
-- 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 --request GET \
-- --url https://api-sandbox.rabobank.nl/openapi/sandbox/payments/account-information/ais/v3/accounts \
-- --header 'accept: application/json' \
-- --header 'authorization: Bearer REPLACE_BEARER_TOKEN' \
-- --header 'date: REPLACE_THIS_VALUE' \
-- --header 'digest: REPLACE_THIS_VALUE' \
-- --header 'psu-ip-address: OPTIONAL' \
-- --header 'signature: REPLACE_THIS_VALUE' \
-- --header 'tpp-signature-certificate: MIIDkDCCAnigA ... cwE6gUl0rMA==' \
-- --header 'x-ibm-client-id: Client ID' \
-- --header 'x-request-id: REPLACE_THIS_VALUE'
-- Load the previously fetched OAuth2 access token.
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/rabobank.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @jsonToken, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
-- This adds the "authorization: Bearer REPLACE_BEARER_TOKEN" header.
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- This is the TTP Signature certificate: The certificate used for signing the request.
DECLARE @sbTppCert int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbTppCert OUT
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'MIIDkDCCAnigAwIBAgIEWs3AJDANBgkqhkiG9w0BAQsFADCBiTELMAkGA1UEBhMC'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'TkwxEDAOBgNVBAgMB1V0cmVjaHQxEDAOBgNVBAcMB1V0cmVjaHQxETAPBgNVBAoM'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'CFJhYm9iYW5rMRwwGgYDVQQLDBNPbmxpbmUgVHJhbnNhY3Rpb25zMSUwIwYDVQQD'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'DBxQU0QyIEFQSSBQSSBTZXJ2aWNlcyBTYW5kYm94MB4XDTE4MDQxMTA3NTgyOFoX'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'DTIzMDQxMTA3NTgyOFowgYkxCzAJBgNVBAYTAk5MMRAwDgYDVQQIDAdVdHJlY2h0'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'MRAwDgYDVQQHDAdVdHJlY2h0MREwDwYDVQQKDAhSYWJvYmFuazEcMBoGA1UECwwT'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'T25saW5lIFRyYW5zYWN0aW9uczElMCMGA1UEAwwcUFNEMiBBUEkgUEkgU2Vydmlj'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'ZXMgU2FuZGJveDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBANoAjqGW'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'UgCIm2F+0sBSEwLal+T3u+uldLikpxHCB8iL1GD7FrRjcA+MVsxhvHly7vRsHK+t'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'QyMSaeK782RHpY33qxPLc8LmoQLb2EuiQxXj9POYkYBQ74qkrZnvKVlR3WoyQWeD'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'OXnSY2wbNFfkP8ET4ElwyuIIEriwYhab0OIrnnrO8X82/SPZxHwEd3aQjQ6uhiw8'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'paDspJbS5WjEfuwY16KVVUYlhbtAwGjvc6aK0NBm+LH9fMLpAE6gfGZNy0gzMDor'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'VNbkQK1IoAGD8p9ZHdB0F3FwkILEjUiQW6nK+/fKDNJ0TBbpgZUpY8bR460qzxKd'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'eZ1yPDqX2Cjh6fkCAwEAATANBgkqhkiG9w0BAQsFAAOCAQEAYL4iD6noMJAt63kD'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'ED4RB2mII/lssvHhcxuDpOm3Ims9urubFWEpvV5TgIBAxy9PBinOdjhO1kGJJnYi'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, '7F1jv1qnZwTV1JhYbvxv3+vk0jaiu7Ew7G3ASlzruXyMhN6t6jk9MpaWGl5Uw1T+'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'gNRUcWQRR44g3ahQRIS/UHkaV+vcpOa8j186/1X0ULHfbcVQk4LMmJeXqNs8sBAU'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'dKU/c6ssvj8jfJ4SfrurcBhY5UBTOdQOXTPY85aU3iFloerx7Oi9EHewxInOrU5X'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'zqqTz2AQPXezexVeAQxP27lzqCmYC7CFiam6QBr06VebkmnPLfs76n8CDc1cwE6g'
EXEC sp_OAMethod @sbTppCert, 'Append', @success OUT, 'Ul0rMA=='
EXEC sp_OAMethod @sbTppCert, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'tpp-signature-certificate', @sTmp0
-- ----------------------------------------------------------------
-- We're not going to add the psu-ip-address header in this example.
-- ----------------------------------------------------------------
-- X-Request-ID header...
-- Generate a UUID v4.
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
DECLARE @xRequestId nvarchar(4000)
EXEC sp_OAMethod @crypt, 'GenerateUuid', @xRequestId OUT
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'x-request-id', @xRequestId
-- ----------------------------------------------------------------
-- Date header...
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT
EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
DECLARE @dateHdrVal nvarchar(4000)
EXEC sp_OAMethod @dt, 'GetAsRfc822', @dateHdrVal OUT, 0
-- The desire date/time format is the "RFC822" format.
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Date', @dateHdrVal
-- ----------------------------------------------------------------
-- Digest header...
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'SHA256'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
-- A GET request has no HTTP request body. Therefore the payload is the empty string.
DECLARE @payload nvarchar(4000)
SELECT @payload = ''
DECLARE @payloadDigest nvarchar(4000)
EXEC sp_OAMethod @crypt, 'HashStringENC', @payloadDigest OUT, @payload
DECLARE @sbDigestHdrVal int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbDigestHdrVal OUT
EXEC sp_OAMethod @sbDigestHdrVal, 'Append', @success OUT, 'SHA-256='
EXEC sp_OAMethod @sbDigestHdrVal, 'Append', @success OUT, @payloadDigest
EXEC sp_OAMethod @sbDigestHdrVal, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'digest', @sTmp0
-- ----------------------------------------------------------------
-- The HTTP request needs to be signed for PSD2 API's...
-- Create the signing string (to use in signing the HTTP request).
-- See https://developer.rabobank.nl/signing-requests-psd2-apis
--
-- For "account information" HTTP requests, we must use the following headers in the signing string.
-- date
-- digest
-- x-request-id
DECLARE @sbStringToSign int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbStringToSign OUT
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'date: '
EXEC sp_OAMethod @sbStringToSign, 'AppendLine', @success OUT, @dateHdrVal, 0
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'digest: '
EXEC sp_OAMethod @sbDigestHdrVal, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sbStringToSign, 'AppendLine', @success OUT, @sTmp0, 0
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'x-request-id: '
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, @xRequestId
-- ----------------------------------------------------------------
-- Sign with our private key...
DECLARE @sbPrivKey int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPrivKey OUT
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQDaAI6hllIAiJth'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'ftLAUhMC2pfk97vrpXS4pKcRwgfIi9Rg+xa0Y3APjFbMYbx5cu70bByvrUMjEmni'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'u/NkR6WN96sTy3PC5qEC29hLokMV4/TzmJGAUO+KpK2Z7ylZUd1qMkFngzl50mNs'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'GzRX5D/BE+BJcMriCBK4sGIWm9DiK556zvF/Nv0j2cR8BHd2kI0OroYsPKWg7KSW'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, '0uVoxH7sGNeilVVGJYW7QMBo73OmitDQZvix/XzC6QBOoHxmTctIMzA6K1TW5ECt'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'SKABg/KfWR3QdBdxcJCCxI1IkFupyvv3ygzSdEwW6YGVKWPG0eOtKs8SnXmdcjw6'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'l9go4en5AgMBAAECggEAB8nsTqalwGIhFw8mbXuhNUFlGuek/arYLD6pv28swwQH'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, '7v0ZlxFUcCHF+iBl0PsDwZTZQ4ePtgGS6ehoLkWHCzb1lEv5E1YVG5qKNE2UUwRl'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'fIyPakO6AzyV/UF3uzq7C+/GuXGNTKZxKewg5yD/DCFvKoCOpxu9u36FyqP/hw0S'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'ADVlmp35/zoPDPZzu1j4FiCo0pJ9LwJcHxeJHopNAKDw9k6I4z/grskdgupsGzK2'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'BiGiQ/+wmmO68/6Xa6KWfpr1PQ6ODJHgzZsdGCVi6Ebaqlj6BbsYWxP6h3lrsGt+'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'LmHBaN2jCD6cDp+lihqFgnm8hfdv0lmbPilp71EDfwKBgQD6U8PBzZtN8yXm5WuS'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'NL+/8q5GjNmeTJBSo1gM6Y8vOT4QAE147LbVuVBDwyHPoSrNejePae6Q14PswjBy'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'T7B8DZ0OeQyGa3trrFg/ib7Vv4ZMvJqX9+WzBrzZsxTg7oCKHzmCR4vIRItKHH3z'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'Wnnhqlo8ic2AZ2O43cdJosbO1wKBgQDe8UODOLu0vnHohOKeUqF3w/ZOB2+83/js'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'YyUbSkzsGvHIwTjObuMUFTQvdMZ6IkIyJdfnDZIbvlBSD8tzL5iKFTNCK2nL1i4G'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'iFr0CYLaHAlhJ5GEbTrTMDoJeBPerZq83HPrSa/Wb0xO18QTWsoVQPFfPFbbcQyI'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, '9ryJ2iIDrwKBgQC6kuAefG46ZPVk6K2KZUJdgDUgZC52a75NuW0RAqszmUiGiJM1'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'g8ip9tq6BqAWrprGV0c93shusBKlzf5p1LdHXqYmeVY6gbWVhPipMrNHgN5KJ3BZ'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'v+w1yNnMsErpcxne2HL2hPjMJTpj3GSLkm2xIlTrNhIyl9ydlr7IRUhENQKBgQCv'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'i6HxbXa/90WSJTCcIcxqla8X+dsOCf3jhJ3vQy4Wq5C+1wZ35fCAG8Ifq/+so9Uj'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'z5CVqqXlmpF8TFuSs2OVNuRJsg14J4nOMwgLKIIUZAcurQ10DN5I9Kx+UEK1EFXL'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'aHsORdNjMfgQDO2jn9WHrr9gkg6CdB2+qyoCEfS+mQKBgBW08lcy9V5RzRWb/v/j'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'xsc7ovmgAhCJhDeV7dPbx4HbFeoQJlbA8g1thdcFlcatSGyNDbvNE1GPSd4NhkpR'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'Y6Hfv53kdEzjVkEtU8lUdL7HNVJqX7bU7oZlfbYcwxWQ1Gg8C1oLIAyEt71slQtd'
EXEC sp_OAMethod @sbPrivKey, 'Append', @success OUT, 'RiNYBRZTQe2F0wxbXnuUqLAw'
DECLARE @privKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT
EXEC sp_OAMethod @sbPrivKey, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @privKey, 'LoadPem', @success OUT, @sTmp0
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
RETURN
END
DECLARE @rsa int
EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
EXEC sp_OAMethod @rsa, 'UsePrivateKey', @success OUT, @privKey
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
RETURN
END
EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
DECLARE @b64Signature nvarchar(4000)
EXEC sp_OAMethod @sbStringToSign, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rsa, 'SignStringENC', @b64Signature OUT, @sTmp0, 'SHA256'
-- ----------------------------------------------------------------
-- Add the "signature" header.
--
-- The signature header will look like this:
-- signature: keyId="1523433508",algorithm="rsa-sha512",headers="date digest x-request-id",signature="y5o7gKxmfA6AT6...blE1A9Q=="
--
-- The keyId is the serial number of the certificate as defined in 'TPP-Signing-Certificate' header, the format should be Integer not hex.
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @sbTppCert, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @cert, 'SetFromEncoded', @success OUT, @sTmp0
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @sbSigHdrVal int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSigHdrVal OUT
EXEC sp_OAMethod @sbSigHdrVal, 'Append', @success OUT, 'keyId="'
EXEC sp_OAGetProperty @cert, 'SerialDecimal', @sTmp0 OUT
EXEC sp_OAMethod @sbSigHdrVal, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbSigHdrVal, 'Append', @success OUT, '",'
EXEC sp_OAMethod @sbSigHdrVal, 'Append', @success OUT, 'algorithm="rsa-sha256",'
EXEC sp_OAMethod @sbSigHdrVal, 'Append', @success OUT, 'headers="date digest x-request-id",'
EXEC sp_OAMethod @sbSigHdrVal, 'Append', @success OUT, 'signature="'
EXEC sp_OAMethod @sbSigHdrVal, 'Append', @success OUT, @b64Signature
EXEC sp_OAMethod @sbSigHdrVal, 'Append', @success OUT, '"'
EXEC sp_OAMethod @sbSigHdrVal, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'signature', @sTmp0
-- ----------------------------------------------------------------
-- Add remaining headers...
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'x-ibm-client-id', 'YOUR_APP_CLIENT_ID'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'accept', 'application/json'
-- ----------------------------------------------------------------
-- Add our certificate and key for mutual TLS
-- (provide a registered X509 client certificate during TLS handhake)
DECLARE @tlsCert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @tlsCert OUT
EXEC sp_OAMethod @tlsCert, 'LoadFromFile', @success OUT, 'qa_data/certs_and_keys/ING/example_client_tls.cer'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @tlsCert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbSigHdrVal
EXEC @hr = sp_OADestroy @tlsCert
RETURN
END
DECLARE @bdPrivKey int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdPrivKey OUT
EXEC sp_OAMethod @bdPrivKey, 'LoadFile', @success OUT, 'qa_data/certs_and_keys/ING/example_client_tls.key'
IF @success = 0
BEGIN
PRINT 'Failed to load example_client_tls.key'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbSigHdrVal
EXEC @hr = sp_OADestroy @tlsCert
EXEC @hr = sp_OADestroy @bdPrivKey
RETURN
END
-- The OAuth 2.0 client_id for these certificates is e77d776b-90af-4684-bebc-521e5b2614dd.
-- Please note down this client_id since you will need it in the next steps to call the API.
DECLARE @tlsPrivKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @tlsPrivKey OUT
EXEC sp_OAMethod @tlsPrivKey, 'LoadAnyFormat', @success OUT, @bdPrivKey, ''
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @tlsPrivKey, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbSigHdrVal
EXEC @hr = sp_OADestroy @tlsCert
EXEC @hr = sp_OADestroy @bdPrivKey
EXEC @hr = sp_OADestroy @tlsPrivKey
RETURN
END
-- Associate the private key with the certificate.
EXEC sp_OAMethod @tlsCert, 'SetPrivateKey', @success OUT, @tlsPrivKey
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @tlsCert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbSigHdrVal
EXEC @hr = sp_OADestroy @tlsCert
EXEC @hr = sp_OADestroy @bdPrivKey
EXEC @hr = sp_OADestroy @tlsPrivKey
RETURN
END
EXEC sp_OAMethod @http, 'SetSslClientCert', @success OUT, @tlsCert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbSigHdrVal
EXEC @hr = sp_OADestroy @tlsCert
EXEC @hr = sp_OADestroy @bdPrivKey
EXEC @hr = sp_OADestroy @tlsPrivKey
RETURN
END
-- ----------------------------------------------------------------
-- Finally, send the request...
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://api-sandbox.rabobank.nl/openapi/sandbox/payments/account-information/ais/v3/accounts', @sbResponseBody
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbSigHdrVal
EXEC @hr = sp_OADestroy @tlsCert
EXEC @hr = sp_OADestroy @bdPrivKey
EXEC @hr = sp_OADestroy @tlsPrivKey
EXEC @hr = sp_OADestroy @sbResponseBody
RETURN
END
DECLARE @jResp int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jResp OUT
EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
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, 'LastHeader', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbSigHdrVal
EXEC @hr = sp_OADestroy @tlsCert
EXEC @hr = sp_OADestroy @bdPrivKey
EXEC @hr = sp_OADestroy @tlsPrivKey
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
RETURN
END
-- Sample JSON response:
-- (Sample code for parsing the JSON response is shown below)
-- {
-- "accounts": [
-- {
-- "_links": {
-- "account": "/v3/accounts/dW9od2VIVUhGVypIZHVpaGRhdWR3OGRoY",
-- "balances": "/v3/accounts/dW9od2VIVUhGVypIZHVpaGRhdWR3OGRoY/balances",
-- "transactions": "/v3/accounts/dW9od2VIVUhGVypIZHVpaGRhdWR3OGRoY/transactions"
-- },
-- "currency": "EUR",
-- "iban": "NL05RABO0812836782",
-- "name": "Rosie Roy",
-- "resourceId": "dW9od2VIVUhGVypIZHVpaGRhdWR3OGRoY",
-- "status": "enabled"
-- }
-- ]
-- }
-- 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 @v_linksAccount nvarchar(4000)
DECLARE @v_linksBalances nvarchar(4000)
DECLARE @v_linksTransactions nvarchar(4000)
DECLARE @currency nvarchar(4000)
DECLARE @iban nvarchar(4000)
DECLARE @name nvarchar(4000)
DECLARE @resourceId nvarchar(4000)
DECLARE @status nvarchar(4000)
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'accounts'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @v_linksAccount OUT, 'accounts[i]._links.account'
EXEC sp_OAMethod @jResp, 'StringOf', @v_linksBalances OUT, 'accounts[i]._links.balances'
EXEC sp_OAMethod @jResp, 'StringOf', @v_linksTransactions OUT, 'accounts[i]._links.transactions'
EXEC sp_OAMethod @jResp, 'StringOf', @currency OUT, 'accounts[i].currency'
EXEC sp_OAMethod @jResp, 'StringOf', @iban OUT, 'accounts[i].iban'
EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'accounts[i].name'
EXEC sp_OAMethod @jResp, 'StringOf', @resourceId OUT, 'accounts[i].resourceId'
EXEC sp_OAMethod @jResp, 'StringOf', @status OUT, 'accounts[i].status'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbTppCert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @sbDigestHdrVal
EXEC @hr = sp_OADestroy @sbStringToSign
EXEC @hr = sp_OADestroy @sbPrivKey
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbSigHdrVal
EXEC @hr = sp_OADestroy @tlsCert
EXEC @hr = sp_OADestroy @bdPrivKey
EXEC @hr = sp_OADestroy @tlsPrivKey
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO