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

Constant Contact V3: Read every page of list members

See more Constant Contact Examples

Filter the contacts collection with lists and a list ID. To query multiple lists together, supply their comma-separated IDs. Follow the returned cursor links and report the total after the last page. The default contacts query excludes deleted records. A list member can still have unsubscribed email permission. Authorize the app first and use the same credential store.

Constant Contact developer guide

Chilkat SQL Server Downloads

SQL Server
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 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

    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'list', 'LIST_ID'
    DECLARE @url int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @url OUT

    EXEC sp_OAMethod @url, 'Append', @success OUT, 'https://api.cc.email/v3/contacts?lists={$list}&limit=50'
    DECLARE @page int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @page OUT

    DECLARE @total int
    SELECT @total = 0
    EXEC sp_OAGetProperty @url, 'Length', @iTmp0 OUT
    WHILE (@iTmp0 > 0)
      BEGIN
        EXEC sp_OAMethod @url, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', @sTmp0, @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 @url
            EXEC @hr = sp_OADestroy @page
            RETURN
          END
        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        EXEC sp_OAMethod @page, 'Load', @success OUT, @sTmp0
        DECLARE @count int
        EXEC sp_OAMethod @page, 'SizeOfArray', @count OUT, 'contacts'
        IF (@success <> 1) or (@count < 0)
          BEGIN

            PRINT 'Expected a page of contacts.'
            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 @url
            EXEC @hr = sp_OADestroy @page
            RETURN
          END
        DECLARE @i int
        SELECT @i = 0
        WHILE (@i < @count)
          BEGIN
            EXEC sp_OASetProperty @page, 'I', @i
            EXEC sp_OAMethod @page, 'StringOf', @sTmp0 OUT, 'contacts[i].contact_id'

            EXEC sp_OAMethod @page, 'StringOf', @sTmp1 OUT, 'contacts[i].email_address.address'
            PRINT @sTmp0 + '  ' + @sTmp1
            SELECT @i = @i + 1
          END
        SELECT @total = @total + @count
        EXEC sp_OAMethod @url, 'Clear', NULL
        EXEC sp_OAMethod @page, 'JsonTypeOf', @iTmp0 OUT, '_links.next.href'
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @page, 'StringOf', @sTmp0 OUT, '_links.next.href'
            EXEC sp_OAMethod @url, 'Append', @success OUT, @sTmp0
            --  The documented cursor link is relative to api.cc.email.
            EXEC sp_OAMethod @url, 'StartsWith', @iTmp0 OUT, '/v3/contacts?', 1
            IF @iTmp0 <> 1
              BEGIN

                PRINT 'Unexpected pagination link. Stopped before following it.'
                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 @url
                EXEC @hr = sp_OADestroy @page
                RETURN
              END
            EXEC sp_OAMethod @url, 'Prepend', @success OUT, 'https://api.cc.email'
          END
      END

    PRINT 'List members read: ' + @total

    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 @url
    EXEC @hr = sp_OADestroy @page


END
GO