SQL Server
SQL Server
Delete All Files
See more Google Drive Examples
Permanently deletes all files owned by the user without moving it to the trash.This example works by first getting a list of all fileIds, and then iterating over the list to delete each file.
See Google Drive Files delete for more information.
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
DECLARE @iTmp0 int
DECLARE @iTmp1 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
SELECT @success = 1
-- It requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- This example uses a previously obtained access token having permission for the
-- Google Drive scope.
DECLARE @gAuth int
EXEC @hr = sp_OACreate 'Chilkat.AuthGoogle', @gAuth OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @gAuth, 'AccessToken', 'GOOGLE-DRIVE-ACCESS-TOKEN'
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
-- Connect using TLS.
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'www.googleapis.com', 443, 1, @bAutoReconnect
-- Provide the authentication credentials (i.e. the access token)
EXEC sp_OAMethod @rest, 'SetAuthGoogle', @success OUT, @gAuth
-- Get 10 results per page for testing. (The default page size is 100, with a max of 1000.
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'pageSize', '10'
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
DECLARE @i int
DECLARE @numFiles int
-- Send the request for the 1st page.
DECLARE @jsonResponse nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponse OUT, 'GET', '/drive/v3/files'
DECLARE @pageNumber int
SELECT @pageNumber = 1
DECLARE @pageToken nvarchar(4000)
DECLARE @bContinueLoop int
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp1 OUT
SELECT @bContinueLoop = @iTmp0 and (@iTmp1 = 200)
DECLARE @fileId nvarchar(4000)
DECLARE @saFileIds int
EXEC @hr = sp_OACreate 'Chilkat.StringArray', @saFileIds OUT
WHILE @bContinueLoop = 1
BEGIN
PRINT '---- Page ' + @pageNumber + ' ----'
EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonResponse
EXEC sp_OAMethod @json, 'SizeOfArray', @numFiles OUT, 'files'
SELECT @i = 0
WHILE @i < @numFiles
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @fileId OUT, 'files[i].id'
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'files[i].name'
PRINT 'name: ' + @sTmp0
PRINT 'id: ' + @fileId
EXEC sp_OAMethod @saFileIds, 'Append', @success OUT, @fileId
SELECT @i = @i + 1
END
-- Get the next page of files.
-- If the "nextPageToken" is present in the JSON response, then use it in the "pageToken" parameter
-- for the next request. If no "nextPageToken" was present, then this was the last page of files.
EXEC sp_OAMethod @json, 'StringOf', @pageToken OUT, 'nextPageToken'
SELECT @bContinueLoop = 0
DECLARE @bHasMorePages int
EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @bHasMorePages OUT
IF @bHasMorePages = 1
BEGIN
EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'pageSize', '10'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'pageToken', @pageToken
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponse OUT, 'GET', '/drive/v3/files'
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp1 OUT
SELECT @bContinueLoop = @iTmp0 and (@iTmp1 = 200)
SELECT @pageNumber = @pageNumber + 1
END
END
-- Before actually deleting, check for errors...
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @saFileIds
RETURN
END
-- A successful response will have a status code equal to 200.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
PRINT 'response status text = ' + @sTmp0
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT 'response header: ' + @sTmp0
PRINT 'response JSON: ' + @jsonResponse
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @saFileIds
RETURN
END
-- OK, we have the full list of files. Delete each..
DECLARE @sbPath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT
EXEC sp_OAGetProperty @saFileIds, 'Count', @numFiles OUT
SELECT @i = 0
WHILE @i < @numFiles
BEGIN
EXEC sp_OAMethod @saFileIds, 'GetString', @fileId OUT, @i
EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
EXEC sp_OAMethod @sbPath, 'Clear', NULL
EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '/drive/v3/files/'
EXEC sp_OAMethod @sbPath, 'Append', @success OUT, @fileId
EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponse OUT, 'DELETE', @sTmp0
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @saFileIds
EXEC @hr = sp_OADestroy @sbPath
RETURN
END
-- A successful response will have a status code equal to 204 and the response body is empty.
-- (If not successful, then there should be a JSON response body with information..)
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 204
BEGIN
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
PRINT 'response status text = ' + @sTmp0
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT 'response header: ' + @sTmp0
PRINT 'response JSON: ' + @jsonResponse
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @saFileIds
EXEC @hr = sp_OADestroy @sbPath
RETURN
END
SELECT @i = @i + 1
PRINT @i + ': Deleted ' + @fileId
END
PRINT 'All Files Deleted.'
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @saFileIds
EXEC @hr = sp_OADestroy @sbPath
END
GO