SQL Server Requires Chilkat v11.0.0+
SQL Server
Constant Contact V3: Refresh a PKCE access token
See more Constant Contact Examples
Run desktop authorization first. Refresh near the saved expiry or after a 401. Reuse the original client ID and redirect URI and save any rotated refresh token with the new access token. Serialize refreshes for the same account so two processes cannot overwrite the newest grant. If the grant is invalid, authorize again.
Constant Contact developer guide
Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @iTmp1 int
DECLARE @iTmp2 int
DECLARE @sTmp0 nvarchar(4000)
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
DECLARE @vault int
EXEC @hr = sp_OACreate 'Chilkat.Secrets', @vault OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @vault, 'Location', 'local_manager'
EXEC sp_OASetProperty @vault, 'AutoSplit', 1
DECLARE @entry int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @entry OUT
DECLARE @success int
EXEC sp_OAMethod @entry, 'UpdateString', @success OUT, 'appName', 'ConstantContactV3'
EXEC sp_OAMethod @entry, 'UpdateString', @success OUT, 'service', 'OAuth2'
EXEC sp_OAMethod @entry, 'UpdateString', @success OUT, 'username', 'my-account'
DECLARE @tokens int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @tokens OUT
DECLARE @loaded int
EXEC sp_OAMethod @vault, 'GetSecretJson', @loaded OUT, @entry, @tokens
IF @loaded <> 1
BEGIN
PRINT 'Run the desktop authorization example using this account and store.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
RETURN
END
EXEC sp_OAMethod @tokens, 'JsonTypeOf', @iTmp0 OUT, 'refresh_token'
EXEC sp_OAMethod @tokens, 'JsonTypeOf', @iTmp1 OUT, 'client_id'
EXEC sp_OAMethod @tokens, 'JsonTypeOf', @iTmp2 OUT, 'redirect_uri'
IF (@iTmp0 <> 1) or (@iTmp1 <> 1) or (@iTmp2 <> 1)
BEGIN
PRINT 'The saved PKCE grant is incomplete. Authorize again.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
RETURN
END
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1
DECLARE @form int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @form OUT
EXEC sp_OASetProperty @form, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @form, 'ContentType', 'application/x-www-form-urlencoded'
EXEC sp_OAMethod @form, 'AddHeader', NULL, 'Accept', 'application/json'
EXEC sp_OAMethod @form, 'AddParam', NULL, 'grant_type', 'refresh_token'
EXEC sp_OAMethod @tokens, 'StringOf', @sTmp0 OUT, 'client_id'
EXEC sp_OAMethod @form, 'AddParam', NULL, 'client_id', @sTmp0
EXEC sp_OAMethod @tokens, 'StringOf', @sTmp0 OUT, 'redirect_uri'
EXEC sp_OAMethod @form, 'AddParam', NULL, 'redirect_uri', @sTmp0
EXEC sp_OAMethod @tokens, 'StringOf', @sTmp0 OUT, 'refresh_token'
EXEC sp_OAMethod @form, 'AddParam', NULL, 'refresh_token', @sTmp0
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
DECLARE @sent int
EXEC sp_OAMethod @http, 'HttpReq', @sent OUT, 'https://authz.constantcontact.com/oauth2/default/v1/token', @form, @resp
DECLARE @reply int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @reply OUT
DECLARE @parsed int
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @parsed OUT, @sTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF (@sent <> 1) or (@iTmp0 <> 200)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Token refresh failed. HTTP status ' + @iTmp0
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp0 OUT, 'error', 'invalid_grant', 1
IF @iTmp0 = 1
BEGIN
PRINT 'The refresh grant is no longer valid. Authorize again.'
END
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'access_token'
EXEC sp_OAMethod @reply, 'IntOf', @iTmp1 OUT, 'expires_in'
IF (@iTmp0 <> 1) or (@iTmp1 <= 0)
BEGIN
PRINT 'Token response is missing an access token or lifetime.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp0 OUT, 'access_token', '', 1
IF @iTmp0 = 1
BEGIN
PRINT 'The returned access token is empty.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'refresh_token'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp0 OUT, 'refresh_token', '', 1
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'refresh_token'
EXEC sp_OAMethod @tokens, 'UpdateString', @success OUT, 'refresh_token', @sTmp0
END
END
DECLARE @now int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @now OUT
EXEC sp_OAMethod @now, 'SetFromCurrentSystemTime', @success OUT
EXEC sp_OAMethod @now, 'GetAsTimestamp', @sTmp0 OUT, 0
EXEC sp_OAMethod @tokens, 'UpdateString', @success OUT, 'obtained_at_utc', @sTmp0
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OAMethod @tokens, 'UpdateString', @success OUT, 'access_token', @sTmp0
EXEC sp_OAMethod @reply, 'IntOf', @iTmp0 OUT, 'expires_in'
EXEC sp_OAMethod @tokens, 'UpdateInt', @success OUT, 'expires_in', @iTmp0
DECLARE @saved int
EXEC sp_OAMethod @vault, 'UpdateSecretJson', @saved OUT, @entry, @tokens
IF @saved <> 1
BEGIN
PRINT 'Could not save the current token record. Check the credential store.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @now
RETURN
END
EXEC sp_OAMethod @tokens, 'IntOf', @iTmp0 OUT, 'expires_in'
PRINT 'Authorization saved. Access-token lifetime in seconds: ' + @iTmp0
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @now
END
GO