SQL Server

Get Google Account Info from OAuth2 Access Token

See more Google APIs Examples

In the Google OAuth2 authorization flow, an application can know the email address of the Google account used to authorize access by requesting and processing user information from the Google UserInfo endpoint after obtaining an access token. SQL Server

-- 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.

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Implements the following CURL command:

    -- curl -H "Authorization: Bearer YOUR_ACCESS_TOKEN" https://www.googleapis.com/oauth2/v3/userinfo

    -- Use the following online tool to generate HTTP code from a CURL command
    -- Convert a cURL Command to HTTP Source Code

    -- Adds the "Authorization: Bearer YOUR_ACCESS_TOKEN" header.
    EXEC sp_OASetProperty @http, 'AuthToken', 'YOUR_ACCESS_TOKEN'

    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.googleapis.com/oauth2/v3/userinfo', @sbResponseBody
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @jResp int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jResp OUT

    EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jResp, 'EmitCompact', 0


    PRINT 'Response Body:'
    EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @http, 'LastStatus', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END

    -- Sample JSON response:
    -- (Sample code for parsing the JSON response is shown below)

    -- {
    --   "sub": "112233445678901234567",
    --   "name": "John Doe",
    --   "given_name": "John",
    --   "family_name": "Doe",
    --   "profile": "https://plus.google.com/112233445678901234567",
    --   "picture": "https://lh3.googleusercontent.com/a-/AOh14GiU-EXAMPLE",
    --   "email": "johndoe@example.com",
    --   "email_verified": true,
    --   "locale": "en"
    -- }

    -- Sample code for parsing the JSON response...
    -- Use the following online tool to generate parsing code from sample JSON:
    -- Generate Parsing Code from JSON

    DECLARE @v_sub nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @v_sub OUT, 'sub'
    DECLARE @name nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'name'
    DECLARE @given_name nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @given_name OUT, 'given_name'
    DECLARE @family_name nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @family_name OUT, 'family_name'
    DECLARE @profile nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @profile OUT, 'profile'
    DECLARE @picture nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @picture OUT, 'picture'
    DECLARE @email nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @email OUT, 'email'
    DECLARE @email_verified int
    EXEC sp_OAMethod @jResp, 'BoolOf', @email_verified OUT, 'email_verified'
    DECLARE @locale nvarchar(4000)
    EXEC sp_OAMethod @jResp, 'StringOf', @locale OUT, 'locale'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO