(SQL Server) Microsoft Graph -- List Users (Automatic OAuth2 Client Credentials)

Simplify OAuth2 client credentials by using Chilkat to automatically obtain the necessary access token. This example demonstrates the technique with the Microsoft Graph API's "List Users" request.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Instead of directly sending a POST request to obtain an OAuth2 access token, 
    -- you can provide Chilkat with the necessary information to automatically fetch 
    -- the token using the Client Credentials flow. During the first HTTP request, 
    -- Chilkat will obtain the token if it doesn't have one yet, and then use it for 
    -- all subsequent requests. When the token is expired or about to expire, Chilkat 
    -- will automatically obtain a new one, eliminating the need for your application 
    -- to manage token expiration and renewal manually. This approach also reduces the 
    -- number of HTTP requests by utilizing valid tokens as long as possible.

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_secret', 'CLIENT_SECRET'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_id', 'CLIENT_ID'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'token_endpoint', 'https://login.microsoftonline.com/TENANT_ID/oauth2/v2.0/token'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'scope', 'https://graph.microsoft.com/.default'

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- Provide the information to let Chilkat automatically obtain the access token.
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    DECLARE @sbResponse int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT

    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://graph.microsoft.com/v1.0/users', @sbResponse
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponse
        RETURN
      END

    EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbResponse
    EXEC sp_OASetProperty @json, 'EmitCompact', 0


    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    PRINT 'Status code = ' + @iTmp0
    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
      END

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample output
    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users",
    --   "value": [
    --     {
    --       "@odata.id": "https://graph.microsoft.com/v2/6d8ddd66-68d1-43b0-af5c-e31b4b7dd5cd/directoryObjects/fca490d8-5918-4201-8079-c5dcbeafcdc9/Microsoft.DirectoryServices.User",
    --       "businessPhones": [
    --       ],
    --       "displayName": "Joe Sample",
    --       "givenName": "Joe",
    --       "jobTitle": null,
    --       "mail": null,
    --       "mobilePhone": null,
    --       "officeLocation": null,
    --       "preferredLanguage": null,
    --       "surname": "Sample",
    --       "userPrincipalName": "admin_chilkatsoft.com#EXT#@adminchilkatsoft.onmicrosoft.com",
    --       "id": "fca490d8-5918-4201-8079-c5dcbeafcdc9"
    --     }
    --   ]
    -- }

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    DECLARE @odata_id nvarchar(4000)

    DECLARE @displayName nvarchar(4000)

    DECLARE @givenName nvarchar(4000)

    DECLARE @jobTitle nvarchar(4000)

    DECLARE @mail nvarchar(4000)

    DECLARE @mobilePhone nvarchar(4000)

    DECLARE @officeLocation nvarchar(4000)

    DECLARE @preferredLanguage nvarchar(4000)

    DECLARE @surname nvarchar(4000)

    DECLARE @userPrincipalName nvarchar(4000)

    DECLARE @id nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @odata_context nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @odata_context OUT, '"@odata.context"'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'value'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @odata_id OUT, 'value[i]."@odata.id"'
        EXEC sp_OAMethod @json, 'StringOf', @displayName OUT, 'value[i].displayName'
        EXEC sp_OAMethod @json, 'StringOf', @givenName OUT, 'value[i].givenName'
        EXEC sp_OAMethod @json, 'StringOf', @jobTitle OUT, 'value[i].jobTitle'
        EXEC sp_OAMethod @json, 'StringOf', @mail OUT, 'value[i].mail'
        EXEC sp_OAMethod @json, 'StringOf', @mobilePhone OUT, 'value[i].mobilePhone'
        EXEC sp_OAMethod @json, 'StringOf', @officeLocation OUT, 'value[i].officeLocation'
        EXEC sp_OAMethod @json, 'StringOf', @preferredLanguage OUT, 'value[i].preferredLanguage'
        EXEC sp_OAMethod @json, 'StringOf', @surname OUT, 'value[i].surname'
        EXEC sp_OAMethod @json, 'StringOf', @userPrincipalName OUT, 'value[i].userPrincipalName'
        EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'value[i].id'
        SELECT @j = 0
        EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'value[i].businessPhones'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbResponse


END
GO