SQL Server
SQL Server
Google People API - List the User's Contacts
See more Google People Examples
Gets a list of people in the user's contacts.Chilkat 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 assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- It is assumed we previously obtained an OAuth2 access token.
-- This example loads the JSON access token file
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/googlePeople.json'
IF @success <> 1
BEGIN
PRINT 'Failed to load googleContacts.json'
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://people.googleapis.com/v1/people/me/connections?personFields=names,addresses,emailAddresses', @sbResponseBody
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbResponseBody
RETURN
END
-- Sample JSON Response
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "connections": [
-- {
-- "resourceName": "people/c8871101035606120608",
-- "etag": "%EgkBAj0JQhBANy4aBAECBQciDERLSGdVTFNPbzNJPQ==",
-- "names": [
-- {
-- "metadata": {
-- "primary": true,
-- "source": {
-- "type": "CONTACT",
-- "id": "7b1c7b6409e718a0"
-- }
-- },
-- "displayName": "Chilkat Cloud",
-- "familyName": "Cloud",
-- "givenName": "Chilkat",
-- "displayNameLastFirst": "Cloud, Chilkat",
-- "unstructuredName": "Chilkat Cloud"
-- }
-- ],
-- "emailAddresses": [
-- {
-- "metadata": {
-- "primary": true,
-- "source": {
-- "type": "CONTACT",
-- "id": "7b1c7b6409e718a0"
-- }
-- },
-- "value": "support@***.com"
-- }
-- ]
-- },
-- {
-- "resourceName": "people/c7607335470312011517",
-- "etag": "%EgkBAj0JQhBANy4aBAECBQciDGZETUtHVTVMazI4PQ==",
-- "names": [
-- {
-- "metadata": {
-- "primary": true,
-- "source": {
-- "type": "CONTACT",
-- "id": "6992af4e0b2d36fd"
-- }
-- },
-- "displayName": "Matt Smith",
-- "familyName": "Smith",
-- "givenName": "Matt",
-- "displayNameLastFirst": "Smith, Matt",
-- "unstructuredName": "Matt Smith"
-- }
-- ],
-- "addresses": [
-- {
-- "metadata": {
-- "primary": true,
-- "source": {
-- "type": "CONTACT",
-- "id": "6992af4e0b2d36fd"
-- }
-- },
-- "formattedValue": "2222 E Foorest Ave\nWheaton, IL 60999\nUS",
-- "streetAddress": "2222 E Foorest Ave",
-- "city": "Wheaton",
-- "region": "IL",
-- "postalCode": "60999",
-- "country": "US",
-- "countryCode": "US"
-- }
-- ],
-- "emailAddresses": [
-- {
-- "metadata": {
-- "primary": true,
-- "source": {
-- "type": "CONTACT",
-- "id": "6992af4e0b2d36fd"
-- }
-- },
-- "value": "matt@***.com"
-- },
-- {
-- "metadata": {
-- "source": {
-- "type": "CONTACT",
-- "id": "6992af4e0b2d36fd"
-- }
-- },
-- "value": "admin@***.com"
-- }
-- ]
-- }
-- ],
-- "totalPeople": 2,
-- "totalItems": 2
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
DECLARE @resourceName nvarchar(4000)
DECLARE @etag nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @metadataPrimary int
DECLARE @metadataSourceType nvarchar(4000)
DECLARE @metadataSourceId nvarchar(4000)
DECLARE @displayName nvarchar(4000)
DECLARE @familyName nvarchar(4000)
DECLARE @givenName nvarchar(4000)
DECLARE @displayNameLastFirst nvarchar(4000)
DECLARE @unstructuredName nvarchar(4000)
DECLARE @value nvarchar(4000)
DECLARE @formattedValue nvarchar(4000)
DECLARE @streetAddress nvarchar(4000)
DECLARE @city nvarchar(4000)
DECLARE @region nvarchar(4000)
DECLARE @postalCode nvarchar(4000)
DECLARE @country nvarchar(4000)
DECLARE @countryCode nvarchar(4000)
DECLARE @totalPeople int
EXEC sp_OAMethod @json, 'IntOf', @totalPeople OUT, 'totalPeople'
DECLARE @totalItems int
EXEC sp_OAMethod @json, 'IntOf', @totalItems OUT, 'totalItems'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'connections'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @resourceName OUT, 'connections[i].resourceName'
EXEC sp_OAMethod @json, 'StringOf', @etag OUT, 'connections[i].etag'
SELECT @j = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'connections[i].names'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
EXEC sp_OAMethod @json, 'BoolOf', @metadataPrimary OUT, 'connections[i].names[j].metadata.primary'
EXEC sp_OAMethod @json, 'StringOf', @metadataSourceType OUT, 'connections[i].names[j].metadata.source.type'
EXEC sp_OAMethod @json, 'StringOf', @metadataSourceId OUT, 'connections[i].names[j].metadata.source.id'
EXEC sp_OAMethod @json, 'StringOf', @displayName OUT, 'connections[i].names[j].displayName'
EXEC sp_OAMethod @json, 'StringOf', @familyName OUT, 'connections[i].names[j].familyName'
EXEC sp_OAMethod @json, 'StringOf', @givenName OUT, 'connections[i].names[j].givenName'
EXEC sp_OAMethod @json, 'StringOf', @displayNameLastFirst OUT, 'connections[i].names[j].displayNameLastFirst'
EXEC sp_OAMethod @json, 'StringOf', @unstructuredName OUT, 'connections[i].names[j].unstructuredName'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'connections[i].emailAddresses'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
EXEC sp_OAMethod @json, 'BoolOf', @metadataPrimary OUT, 'connections[i].emailAddresses[j].metadata.primary'
EXEC sp_OAMethod @json, 'StringOf', @metadataSourceType OUT, 'connections[i].emailAddresses[j].metadata.source.type'
EXEC sp_OAMethod @json, 'StringOf', @metadataSourceId OUT, 'connections[i].emailAddresses[j].metadata.source.id'
EXEC sp_OAMethod @json, 'StringOf', @value OUT, 'connections[i].emailAddresses[j].value'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'connections[i].addresses'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
EXEC sp_OAMethod @json, 'BoolOf', @metadataPrimary OUT, 'connections[i].addresses[j].metadata.primary'
EXEC sp_OAMethod @json, 'StringOf', @metadataSourceType OUT, 'connections[i].addresses[j].metadata.source.type'
EXEC sp_OAMethod @json, 'StringOf', @metadataSourceId OUT, 'connections[i].addresses[j].metadata.source.id'
EXEC sp_OAMethod @json, 'StringOf', @formattedValue OUT, 'connections[i].addresses[j].formattedValue'
EXEC sp_OAMethod @json, 'StringOf', @streetAddress OUT, 'connections[i].addresses[j].streetAddress'
EXEC sp_OAMethod @json, 'StringOf', @city OUT, 'connections[i].addresses[j].city'
EXEC sp_OAMethod @json, 'StringOf', @region OUT, 'connections[i].addresses[j].region'
EXEC sp_OAMethod @json, 'StringOf', @postalCode OUT, 'connections[i].addresses[j].postalCode'
EXEC sp_OAMethod @json, 'StringOf', @country OUT, 'connections[i].addresses[j].country'
EXEC sp_OAMethod @json, 'StringOf', @countryCode OUT, 'connections[i].addresses[j].countryCode'
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @json
END
GO