SQL Server Requires Chilkat v11.0.0+
SQL Server
Constant Contact V3: Find a contact by email address
See more Constant Contact Examples
Look up an exact email address across contact states, including deleted contacts. Pass the address as a query parameter so a literal plus sign survives URL encoding. Read the stored contact ID, email permission, and deletion marker. An empty contacts array means no match. 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 @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', 'all'
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 @reply int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @reply OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, '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 @reply
RETURN
END
DECLARE @count int
EXEC sp_OAMethod @reply, 'SizeOfArray', @count OUT, 'contacts'
IF @count < 0
BEGIN
PRINT 'The reply contained no contacts array.'
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 @reply
RETURN
END
IF @count = 0
BEGIN
PRINT 'No contact has that email address.'
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 @reply
RETURN
END
DECLARE @i int
SELECT @i = 0
WHILE (@i < @count)
BEGIN
EXEC sp_OASetProperty @reply, 'I', @i
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'contacts[i].contact_id'
PRINT 'Contact ID: ' + @sTmp0
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'contacts[i].email_address.permission_to_send'
PRINT 'Email permission: ' + @sTmp0
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'contacts[i].deleted_at'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'contacts[i].deleted_at'
PRINT 'Deleted at: ' + @sTmp0
END
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 @reply
END
GO