SQL Server
SQL Server
Isabel Connect Create Access Token given Valid Refresh Token
See more Ibanity Examples
Create (refresh) an access token given a valid refresh token.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 -X POST https://api.ibanity.com/isabel-connect/oauth2/token \
-- --cert certificate.pem:qwertyuiop1 \
-- --key private_key.pem \
-- -H "Content-Type: application/x-www-form-urlencoded" \
-- -H "Accept: application/vnd.api+json" \
-- -H "Ibanity-Idempotency-Key: 94c5586e-e15e-4bae-a1fe-fdbefe1f11d3" \
-- -d grant_type=refresh_token \
-- -d refresh_token=valid_refresh_token \
-- -d client_id=valid_client_id \
-- -d client_secret=valid_client_secret
-- 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.)
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/my_ibanity_certificate.pfx', 'my_pfx_password'
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
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
RETURN
END
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'Path', '/isabel-connect/oauth2/token'
EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'grant_type', 'refresh_token'
-- Load the previously obtained refresh token.
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/isabel_refresh_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 @req
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'refresh_token'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'refresh_token', @sTmp0
-- Note: For sandbox testing, we literally want to use the strings
-- "valid_client_id", and "valid_client_secret".
-- For the live app, you would replace these with actual values.
EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_id', 'valid_client_id'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_secret', 'valid_client_secret'
EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Accept', 'application/vnd.api+json'
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
DECLARE @idempotencyKey nvarchar(4000)
EXEC sp_OAMethod @crypt, 'GenerateUuid', @idempotencyKey OUT
PRINT 'Ibanity-Idempotency-Key: ' + @idempotencyKey
EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Ibanity-Idempotency-Key', @idempotencyKey
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, 'https://api.ibanity.com/isabel-connect/oauth2/token', @req, @resp
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 @req
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
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 @resp, 'StatusCode', @respStatusCode OUT
PRINT 'Response Status Code = ' + @respStatusCode
IF @respStatusCode >= 400
BEGIN
PRINT 'Response Header:'
EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @resp
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)
-- Notice that we don't get a new refresh token. The original refresh token is used each time we
-- want to refresh. However, we get a new access token. This access token is to be used in Isabel API calls
-- until we need to refresh again.
-- {
-- "token_type": "Bearer",
-- "scope": "cloudconnect",
-- "expires_in": 1799,
-- "access_token": "access_token_1603365408"
-- }
-- 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 @token_type nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @token_type OUT, 'token_type'
DECLARE @scope nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @scope OUT, 'scope'
DECLARE @expires_in int
EXEC sp_OAMethod @jResp, 'IntOf', @expires_in OUT, 'expires_in'
DECLARE @access_token nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @access_token OUT, 'access_token'
-- Save this to a file so we can load in other examples to include the access token in the HTTP request.
EXEC sp_OAMethod @jResp, 'WriteFile', @success OUT, 'qa_data/tokens/isabel_access_token.json'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO