SQL Server
SQL Server
Outlook List Contacts
See more Outlook Contact Examples
List Outlook ContactsChilkat 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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Use your previously obtained access token here: Get Outlook Contacts OAuth2 Access Token (Azure AD v2.0 Endpoint).
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/outlookContacts.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @jsonToken, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- Send the following GET:
-- GET https://graph.microsoft.com/v1.0/me/contacts
DECLARE @sbJson int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbJson OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://graph.microsoft.com/v1.0/me/contacts', @sbJson
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbJson
RETURN
END
DECLARE @statusCode int
EXEC sp_OAGetProperty @http, 'LastStatus', @statusCode OUT
PRINT 'Response status code = ' + @statusCode
IF @statusCode <> 200
BEGIN
EXEC sp_OAMethod @sbJson, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbJson
RETURN
END
DECLARE @jResp int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jResp OUT
EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbJson
EXEC sp_OASetProperty @jResp, 'EmitCompact', 0
EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Sample output:
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('4ee732c3-322e-4a6b-b729-2fd1eb5c6004')/contacts",
-- "value": [
-- {
-- "@odata.etag": "W/\"EQAAABYAAABUcG0qWqkmQYqWLHQataQxAAD0iYfT\"",
-- "id": "AAMkADYzZWE3YmZmLWU0YzgtNGNkZC04MGE1LWFiYTFlNTRlY2QwYQBGAAAAAAAu7cUXL5YOTrdsUIw7-v8FBwBUcG0qWqkmQYqWLHQataQxAAAAAAEOAABUcG0qWqkmQYqWLHQataQxAAD0sxexAAA=",
-- "createdDateTime": "2021-06-29T16:32:05Z",
-- "lastModifiedDateTime": "2021-06-29T16:32:06Z",
-- "changeKey": "EQAAABYAAABUcG0qWqkmQYqWLHQataQxAAD0iYfT",
-- "categories": [
-- ],
-- "parentFolderId": "AAMkADYzZWE3YmZmLWU0YzgtNGNkZC04MGE1LWFiYTFlNTRlY2QwYQAuAAAAAAAu7cUXL5YOTrdsUIw7-v8FAQBUcG0qWqkmQYqWLHQataQxAAAAAAEOAAA=",
-- "birthday": null,
-- "fileAs": "",
-- "displayName": "Pavel Bansky",
-- "givenName": "Pavel",
-- "initials": null,
-- "middleName": null,
-- "nickName": null,
-- "surname": "Bansky",
-- "title": null,
-- "yomiGivenName": null,
-- "yomiSurname": null,
-- "yomiCompanyName": null,
-- "generation": null,
-- "imAddresses": [
-- ],
-- "jobTitle": null,
-- "companyName": null,
-- "department": null,
-- "officeLocation": null,
-- "profession": null,
-- "businessHomePage": null,
-- "assistantName": null,
-- "manager": null,
-- "homePhones": [
-- ],
-- "mobilePhone": null,
-- "businessPhones": [
-- "+1 732 555 0102"
-- ],
-- "spouseName": null,
-- "personalNotes": "",
-- "children": [
-- ],
-- "emailAddresses": [
-- {
-- "name": "Pavel Bansky",
-- "address": "pavelb@fabrikam.onmicrosoft.com"
-- }
-- ],
-- "homeAddress": {},
-- "businessAddress": {},
-- "otherAddress": {}
-- },
-- ...
-- ]
-- }
DECLARE @odata_etag nvarchar(4000)
DECLARE @id nvarchar(4000)
DECLARE @createdDateTime nvarchar(4000)
DECLARE @lastModifiedDateTime nvarchar(4000)
DECLARE @changeKey nvarchar(4000)
DECLARE @parentFolderId nvarchar(4000)
DECLARE @birthday nvarchar(4000)
DECLARE @fileAs nvarchar(4000)
DECLARE @displayName nvarchar(4000)
DECLARE @givenName nvarchar(4000)
DECLARE @initials nvarchar(4000)
DECLARE @middleName nvarchar(4000)
DECLARE @nickName nvarchar(4000)
DECLARE @surname nvarchar(4000)
DECLARE @title nvarchar(4000)
DECLARE @yomiGivenName nvarchar(4000)
DECLARE @yomiSurname nvarchar(4000)
DECLARE @yomiCompanyName nvarchar(4000)
DECLARE @generation nvarchar(4000)
DECLARE @jobTitle nvarchar(4000)
DECLARE @companyName nvarchar(4000)
DECLARE @department nvarchar(4000)
DECLARE @officeLocation nvarchar(4000)
DECLARE @profession nvarchar(4000)
DECLARE @businessHomePage nvarchar(4000)
DECLARE @assistantName nvarchar(4000)
DECLARE @manager nvarchar(4000)
DECLARE @mobilePhone nvarchar(4000)
DECLARE @spouseName nvarchar(4000)
DECLARE @personalNotes nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @strVal nvarchar(4000)
DECLARE @name nvarchar(4000)
DECLARE @address nvarchar(4000)
DECLARE @odata_context nvarchar(4000)
EXEC sp_OAMethod @jResp, 'StringOf', @odata_context OUT, '"@odata.context"'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'value'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @odata_etag OUT, 'value[i]."@odata.etag"'
EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'value[i].id'
EXEC sp_OAMethod @jResp, 'StringOf', @createdDateTime OUT, 'value[i].createdDateTime'
EXEC sp_OAMethod @jResp, 'StringOf', @lastModifiedDateTime OUT, 'value[i].lastModifiedDateTime'
EXEC sp_OAMethod @jResp, 'StringOf', @changeKey OUT, 'value[i].changeKey'
EXEC sp_OAMethod @jResp, 'StringOf', @parentFolderId OUT, 'value[i].parentFolderId'
EXEC sp_OAMethod @jResp, 'StringOf', @birthday OUT, 'value[i].birthday'
EXEC sp_OAMethod @jResp, 'StringOf', @fileAs OUT, 'value[i].fileAs'
EXEC sp_OAMethod @jResp, 'StringOf', @displayName OUT, 'value[i].displayName'
EXEC sp_OAMethod @jResp, 'StringOf', @givenName OUT, 'value[i].givenName'
EXEC sp_OAMethod @jResp, 'StringOf', @initials OUT, 'value[i].initials'
EXEC sp_OAMethod @jResp, 'StringOf', @middleName OUT, 'value[i].middleName'
EXEC sp_OAMethod @jResp, 'StringOf', @nickName OUT, 'value[i].nickName'
EXEC sp_OAMethod @jResp, 'StringOf', @surname OUT, 'value[i].surname'
EXEC sp_OAMethod @jResp, 'StringOf', @title OUT, 'value[i].title'
EXEC sp_OAMethod @jResp, 'StringOf', @yomiGivenName OUT, 'value[i].yomiGivenName'
EXEC sp_OAMethod @jResp, 'StringOf', @yomiSurname OUT, 'value[i].yomiSurname'
EXEC sp_OAMethod @jResp, 'StringOf', @yomiCompanyName OUT, 'value[i].yomiCompanyName'
EXEC sp_OAMethod @jResp, 'StringOf', @generation OUT, 'value[i].generation'
EXEC sp_OAMethod @jResp, 'StringOf', @jobTitle OUT, 'value[i].jobTitle'
EXEC sp_OAMethod @jResp, 'StringOf', @companyName OUT, 'value[i].companyName'
EXEC sp_OAMethod @jResp, 'StringOf', @department OUT, 'value[i].department'
EXEC sp_OAMethod @jResp, 'StringOf', @officeLocation OUT, 'value[i].officeLocation'
EXEC sp_OAMethod @jResp, 'StringOf', @profession OUT, 'value[i].profession'
EXEC sp_OAMethod @jResp, 'StringOf', @businessHomePage OUT, 'value[i].businessHomePage'
EXEC sp_OAMethod @jResp, 'StringOf', @assistantName OUT, 'value[i].assistantName'
EXEC sp_OAMethod @jResp, 'StringOf', @manager OUT, 'value[i].manager'
EXEC sp_OAMethod @jResp, 'StringOf', @mobilePhone OUT, 'value[i].mobilePhone'
EXEC sp_OAMethod @jResp, 'StringOf', @spouseName OUT, 'value[i].spouseName'
EXEC sp_OAMethod @jResp, 'StringOf', @personalNotes OUT, 'value[i].personalNotes'
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'value[i].categories'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'value[i].imAddresses'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'value[i].homePhones'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'value[i].businessPhones'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
EXEC sp_OAMethod @jResp, 'StringOf', @strVal OUT, 'value[i].businessPhones[j]'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'value[i].children'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'value[i].emailAddresses'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'value[i].emailAddresses[j].name'
EXEC sp_OAMethod @jResp, 'StringOf', @address OUT, 'value[i].emailAddresses[j].address'
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbJson
EXEC @hr = sp_OADestroy @jResp
END
GO