Sample code for 30+ languages & platforms
SQL Server Requires Chilkat v11.0.0+

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

SQL Server
-- 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