![]() |
Chilkat • HOME • Android™ • AutoIt • C • C# • C++ • Chilkat2-Python • CkPython • Classic ASP • DataFlex • Delphi DLL • Go • Java • Node.js • Objective-C • PHP Extension • Perl • PowerBuilder • PowerShell • PureBasic • Ruby • SQL Server • Swift • Tcl • Unicode C • Unicode C++ • VB.NET • VBScript • Visual Basic 6.0 • Visual FoxPro • Xojo Plugin
(SQL Server) Automatically Refresh Token for 401 UnauthorizedDemonstrates how to automatically refresh an access token (without user interaction) when the token expires and a 401 Unauthorized response is received.
-- 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. DECLARE @tokenFilePath nvarchar(4000) SELECT @tokenFilePath = 'qa_data/tokens/googleCalendar.json' -- Get our current access token. DECLARE @jsonToken int -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT IF @hr <> 0 BEGIN PRINT 'Failed to create ActiveX component' RETURN END DECLARE @success int EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, @tokenFilePath EXEC sp_OAMethod @jsonToken, 'HasMember', @iTmp0 OUT, 'access_token' IF @iTmp0 = 0 BEGIN PRINT 'No access token found.' EXEC @hr = sp_OADestroy @jsonToken RETURN END DECLARE @http int -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token' EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0 DECLARE @jsonResponse nvarchar(4000) EXEC sp_OAMethod @http, 'QuickGetStr', @jsonResponse OUT, 'https://www.googleapis.com/calendar/v3/users/me/calendarList' EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT IF @iTmp0 <> 1 BEGIN EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT IF @iTmp0 <> 401 BEGIN EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 PRINT '----' EXEC sp_OAGetProperty @http, 'LastResponseBody', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @jsonToken EXEC @hr = sp_OADestroy @http RETURN END -- The access token must've expired. -- Refresh the access token and then retry the request. DECLARE @oauth2 int -- Use "Chilkat_9_5_0.OAuth2" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.OAuth2', @oauth2 OUT EXEC sp_OASetProperty @oauth2, 'TokenEndpoint', 'https://www.googleapis.com/oauth2/v4/token' -- Replace these with actual values. EXEC sp_OASetProperty @oauth2, 'ClientId', 'GOOGLE-CLIENT-ID' EXEC sp_OASetProperty @oauth2, 'ClientSecret', 'GOOGLE-CLIENT-SECRET' -- Get the "refresh_token" EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'refresh_token' EXEC sp_OASetProperty @oauth2, 'RefreshToken', @sTmp0 -- Send the HTTP POST to refresh the access token.. EXEC sp_OAMethod @oauth2, 'RefreshAccessToken', @success OUT IF @success <> 1 BEGIN EXEC sp_OAGetProperty @oauth2, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @jsonToken EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @oauth2 RETURN END -- The response contains a new access token, but we must keep -- our existing refresh token for when we need to refresh again in the future. EXEC sp_OAGetProperty @oauth2, 'AccessToken', @sTmp0 OUT EXEC sp_OAMethod @jsonToken, 'UpdateString', @success OUT, 'access_token', @sTmp0 -- Save the new JSON access token response to a file. DECLARE @sbJson int -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbJson OUT EXEC sp_OASetProperty @jsonToken, 'EmitCompact', 0 EXEC sp_OAMethod @jsonToken, 'EmitSb', @success OUT, @sbJson EXEC sp_OAMethod @sbJson, 'WriteFile', @success OUT, @tokenFilePath, 'utf-8', 0 PRINT 'OAuth2 authorization granted!' EXEC sp_OAGetProperty @oauth2, 'AccessToken', @sTmp0 OUT PRINT 'New Access Token = ' + @sTmp0 -- re-try the original request. EXEC sp_OAGetProperty @oauth2, 'AccessToken', @sTmp0 OUT EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0 EXEC sp_OAMethod @http, 'QuickGetStr', @jsonResponse OUT, 'https://www.googleapis.com/calendar/v3/users/me/calendarList' EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT IF @iTmp0 <> 1 BEGIN EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @jsonToken EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @oauth2 EXEC @hr = sp_OADestroy @sbJson RETURN END END PRINT @jsonResponse PRINT '-----------------------------' EXEC @hr = sp_OADestroy @jsonToken EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @oauth2 EXEC @hr = sp_OADestroy @sbJson END GO |
© 2000-2025 Chilkat Software, Inc. All Rights Reserved.