SQL Server Requires Chilkat v11.0.0+
SQL Server
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
--
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