SQL Server Requires Chilkat v11.0.0+
SQL Server
Constant Contact V3: Restore a deleted contact to a list
See more Constant Contact Examples
Look up the deleted contact by email, then restore it by adding its existing ID to a chosen list. Constant Contact documents this membership activity as a restoration method. It changes only membership, preserves the saved profile, and makes no email-permission claim. Select the intended destination list explicitly; previous memberships were removed by deletion. Authorize the app first and use the same credential store.
Constant Contact developer guide
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
DECLARE @iTmp1 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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
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, 'access_token'
IF @iTmp0 <> 1
BEGIN
PRINT 'The saved authorization has no access token.'
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
EXEC sp_OAMethod @tokens, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
DECLARE @query int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @query OUT
EXEC sp_OASetProperty @query, 'HttpVerb', 'GET'
EXEC sp_OAMethod @query, 'AddParam', NULL, 'email', 'reader+newsletter@example.com'
EXEC sp_OAMethod @query, 'AddParam', NULL, 'status', 'deleted'
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, 'https://api.cc.email/v3/contacts', @query, @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF (@success <> 1) or (@iTmp0 <> 200)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Expected HTTP 200. Received ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 401
BEGIN
PRINT 'Refresh the saved access token or 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 @resp
EXEC @hr = sp_OADestroy @query
RETURN
END
DECLARE @matches int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @matches OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @matches, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Expected a JSON response.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
RETURN
END
EXEC sp_OAMethod @matches, 'SizeOfArray', @iTmp0 OUT, 'contacts'
EXEC sp_OAMethod @matches, 'JsonTypeOf', @iTmp1 OUT, 'contacts[0].deleted_at'
IF (@iTmp0 <> 1) or (@iTmp1 <> 1)
BEGIN
PRINT 'Expected one deleted contact for this email. No restoration submitted.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
RETURN
END
EXEC sp_OAMethod @matches, 'JsonTypeOf', @iTmp0 OUT, 'contacts[0].contact_id'
EXEC sp_OAMethod @matches, 'StringOfEquals', @iTmp1 OUT, 'contacts[0].contact_id', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'Missing contacts[0].contact_id in the response.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
RETURN
END
DECLARE @body int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @body OUT
EXEC sp_OAMethod @matches, 'StringOf', @sTmp0 OUT, 'contacts[0].contact_id'
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'source.contact_ids[0]', @sTmp0
EXEC sp_OAMethod @body, 'UpdateString', @success OUT, 'list_ids[0]', 'DESTINATION_LIST_ID'
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://api.cc.email/v3/activities/add_list_memberships', @body, 'application/json', @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
IF (@success <> 1) or (@iTmp0 < 200) or (@iTmp1 >= 300)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Could not start the membership activity. HTTP ' + @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 @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @body
RETURN
END
DECLARE @job int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @job OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @job, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Expected a JSON response.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @body
EXEC @hr = sp_OADestroy @job
RETURN
END
EXEC sp_OAMethod @job, 'JsonTypeOf', @iTmp0 OUT, 'activity_id'
EXEC sp_OAMethod @job, 'StringOfEquals', @iTmp1 OUT, 'activity_id', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'Missing activity_id in the response.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @body
EXEC @hr = sp_OADestroy @job
RETURN
END
DECLARE @activityId nvarchar(4000)
EXEC sp_OAMethod @job, 'StringOf', @activityId OUT, 'activity_id'
PRINT 'Submitted activity ID: ' + @activityId
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'job', @activityId
-- Poll briefly. A longer-running job can be checked later using the same ID.
DECLARE @attempt int
SELECT @attempt = 0
WHILE (@attempt < 5)
BEGIN
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://api.cc.email/v3/activities/{$job}', @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF (@success <> 1) or (@iTmp0 <> 200)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Status check returned HTTP ' + @iTmp0 + '. Keep the activity ID and check again later.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @body
EXEC @hr = sp_OADestroy @job
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @job, 'Load', @success OUT, @sTmp0
EXEC sp_OAMethod @job, 'JsonTypeOf', @iTmp0 OUT, 'state'
IF (@success <> 1) or (@iTmp0 <> 1)
BEGIN
PRINT 'Missing activity state. Keep the activity ID.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @body
EXEC @hr = sp_OADestroy @job
RETURN
END
DECLARE @state nvarchar(4000)
EXEC sp_OAMethod @job, 'StringOf', @state OUT, 'state'
IF (@state <> 'initialized') and (@state <> 'processing')
BEGIN
PRINT 'Activity state: ' + @state
DECLARE @i int
SELECT @i = 0
EXEC sp_OAMethod @job, 'SizeOfArray', @iTmp0 OUT, 'activity_errors'
WHILE (@i < @iTmp0)
BEGIN
EXEC sp_OASetProperty @job, 'I', @i
EXEC sp_OAMethod @job, 'StringOf', @sTmp0 OUT, 'activity_errors[i]'
PRINT @sTmp0
SELECT @i = @i + 1
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 @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @body
EXEC @hr = sp_OADestroy @job
RETURN
END
SELECT @attempt = @attempt + 1
IF @attempt < 5
BEGIN
EXEC sp_OAMethod @http, 'SleepMs', NULL, 2000
END
END
PRINT 'Still processing. Recheck the activity ID before submitting another request.'
EXEC @hr = sp_OADestroy @vault
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @tokens
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @query
EXEC @hr = sp_OADestroy @matches
EXEC @hr = sp_OADestroy @body
EXEC @hr = sp_OADestroy @job
END
GO