File - Patch - SQLHTTP

File - Patch

Update Google Drive File fields using SQL Server

Prerequisites:

See SQLHTTP easy setup for Google
See API Call documentation for parameter values and other information
Create the stored procedure documented below

Stored Procedure:


CREATE PROCEDURE usp_Google_File_Patch(	
		@Profile nvarchar(100),
		@fileId nvarchar(MAX),
		@addParents nvarchar(MAX) = NULL,
		@convert bit = 0,
		@modifiedDateBehavior varchar(20) = NULL,
		@newRevision bit = 1,
		@ocr bit = 0,
		@ocrLanguage varchar(5) = NULL,
		@pinned bit = 0,
		@removeParents nvarchar(MAX) = NULL,
		@setModifiedDate bit = 0,
		@supportsTeamDrives bit = 0,
		@timedTextLanguage varchar(50) = NULL,
		@timedTextTrackName varchar(50) = NULL,
		@updateViewedDate bit = 1,
		@useContentAsIndexableText bit = 0,
		@Body varchar(MAX),
		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS

DECLARE @Text_convert varchar(5)
DECLARE @Text_newRevision varchar(5)
DECLARE @Text_ocr varchar(5)
DECLARE @Text_pinned varchar(5)
DECLARE @Text_setModifiedDate varchar(5)
DECLARE @Text_supportsTeamDrives varchar(5) 
DECLARE @Text_updateViewedDate varchar(5)
DECLARE @Text_useContentAsIndexableText varchar(5)

SET @Text_convert = 
	CASE 
		WHEN @convert = 0 THEN 'false'
		ELSE 'true' 
	END

SET @Text_newRevision = 
	CASE 
		WHEN @newRevision = 0 THEN 'false'
		ELSE 'true' 
	END

SET @Text_ocr = 
	CASE 
		WHEN @ocr = 0 THEN 'false'
		ELSE 'true' 
	END

SET @Text_pinned = 
	CASE 
		WHEN @pinned = 0 THEN 'false'
		ELSE 'true' 
	END

SET @Text_setModifiedDate = 
	CASE 
		WHEN @setModifiedDate = 0 THEN 'false'
		ELSE 'true' 
	END

SET @Text_supportsTeamDrives = 
	CASE
		WHEN @supportsTeamDrives = 0 THEN 'false'
		ELSE 'true'
	END

SET @Text_updateViewedDate = 
	CASE
		WHEN @updateViewedDate = 0 THEN 'false'
		ELSE 'true'
	END

SET @Text_useContentAsIndexableText = 
	CASE
		WHEN @useContentAsIndexableText = 0 THEN 'false'
		ELSE 'true'
	END

DECLARE @URL nvarchar(MAX)
DECLARE @QueryString nvarchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://www.googleapis.com/drive/v2'

EXEC SQLHTTP.net.UrlBuilder   @URL OUTPUT,
			      @Profile,
			      'files', @fileId

EXEC SQLHTTP.net.QueryStringBuilder     @QueryString OUTPUT,
					@Profile,
					'convert', @Text_convert,
					'addParents', @addParents,
					'modifiedDateBehavior', @modifiedDateBehavior,
					'newRevision', @Text_newRevision,
					'ocr', @Text_ocr,
					'ocrLanguage', @ocrLanguage,
					'pinned', @Text_pinned,
					'removeParents', @removeParents,
					'setModifiedDate', @Text_setModifiedDate,
					'supportsTeamDrives', @Text_supportsTeamDrives,
					'timedTextLanguage', @timedTextLanguage,
					'timedTextTrackName', @timedTextTrackName,
					'updateViewedDate', @Text_updateViewedDate,
					'useContentAsIndexableText', @Text_useContentAsIndexableText

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_Google_Auth_Header @Profile, @HTTPSessionID

EXEC SQLHTTP.net.HTTPRequest 	@HttpSessionID,
				@URL = @URL,
				@Method = 'PATCH',
				@Body = @Body,
				@ContentType = 'application/json',
				@StatusCode = @StatusCode OUTPUT,
				@StatusDescription = @StatusDescription OUTPUT,
				@Response = @Response OUTPUT
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

CREATE PROCEDURE usp_Google_File_Patch(

@Profile nvarchar(100),

@fileId nvarchar(MAX),

@addParents nvarchar(MAX) = NULL,

@convert bit = 0,

@modifiedDateBehavior varchar(20) = NULL,

@newRevision bit = 1,

@ocr bit = 0,

@ocrLanguage varchar(5) = NULL,

@pinned bit = 0,

@removeParents nvarchar(MAX) = NULL,

@setModifiedDate bit = 0,

@supportsTeamDrives bit = 0,

@timedTextLanguage varchar(50) = NULL,

@timedTextTrackName varchar(50) = NULL,

@updateViewedDate bit = 1,

@useContentAsIndexableText bit = 0,

@Body varchar(MAX),

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

DECLARE @Text_convert varchar(5)

DECLARE @Text_newRevision varchar(5)

DECLARE @Text_ocr varchar(5)

DECLARE @Text_pinned varchar(5)

DECLARE @Text_setModifiedDate varchar(5)

DECLARE @Text_supportsTeamDrives varchar(5)

DECLARE @Text_updateViewedDate varchar(5)

DECLARE @Text_useContentAsIndexableText varchar(5)

SET @Text_convert =

CASE

WHEN @convert = 0 THEN 'false'

ELSE 'true'

END

SET @Text_newRevision =

CASE

WHEN @newRevision = 0 THEN 'false'

ELSE 'true'

END

SET @Text_ocr =

CASE

WHEN @ocr = 0 THEN 'false'

ELSE 'true'

END

SET @Text_pinned =

CASE

WHEN @pinned = 0 THEN 'false'

ELSE 'true'

END

SET @Text_setModifiedDate =

CASE

WHEN @setModifiedDate = 0 THEN 'false'

ELSE 'true'

END

SET @Text_supportsTeamDrives =

CASE

WHEN @supportsTeamDrives = 0 THEN 'false'

ELSE 'true'

END

SET @Text_updateViewedDate =

CASE

WHEN @updateViewedDate = 0 THEN 'false'

ELSE 'true'

END

SET @Text_useContentAsIndexableText =

CASE

WHEN @useContentAsIndexableText = 0 THEN 'false'

ELSE 'true'

END

DECLARE @URL nvarchar(MAX)

DECLARE @QueryString nvarchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://www.googleapis.com/drive/v2'

EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT,

@Profile,

'files', @fileId

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'convert', @Text_convert,

'addParents', @addParents,

'modifiedDateBehavior', @modifiedDateBehavior,

'newRevision', @Text_newRevision,

'ocr', @Text_ocr,

'ocrLanguage', @ocrLanguage,

'pinned', @Text_pinned,

'removeParents', @removeParents,

'setModifiedDate', @Text_setModifiedDate,

'supportsTeamDrives', @Text_supportsTeamDrives,

'timedTextLanguage', @timedTextLanguage,

'timedTextTrackName', @timedTextTrackName,

'updateViewedDate', @Text_updateViewedDate,

'useContentAsIndexableText', @Text_useContentAsIndexableText

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_Google_Auth_Header @Profile, @HTTPSessionID

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'PATCH',

@Body = @Body,

@ContentType = 'application/json',

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)
DECLARE @Body varchar(MAX)
DECLARE @Profile nvarchar(100)

SET @Profile = 'My google'

SET @Body = '{
	"title": "new register file.pdf",
	"description": "Registration file for students"
}'

--Google API Bearer Token expires after an hour
EXEC usp_Google_Auth_Refresh @Profile

EXEC usp_Google_File_Patch
		@Profile = @Profile,
		@fileId = '1IYC6qAejm7mJy4SKNx1LdkrOzINJ16iG',
		@Body = @Body,  
		@Response = @Response OUTPUT, 
		@StatusCode = @StatusCode OUTPUT, 
		@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
	EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
	BEGIN
		
		DECLARE @X xml
		SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

		--The XPath syntax below was easily generated by executing the following commands:
		--EXEC SQLHTTP.net.XQueryHelper @X
		--and then executing: 
		--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject' --

		SELECT T.C.value(N'@kind', N'nvarchar(MAX)') AS [kind]
		      ,T.C.value(N'@title', N'nvarchar(MAX)') AS title
		      ,T.C.value(N'@id', N'nvarchar(MAX)') AS [id]
		      ,T.C.value(N'@description', N'nvarchar(MAX)') AS [description]
		      ,T.C.value(N'@fileSize', N'nvarchar(MAX)') AS [fileSize]
		FROM @X.nodes(N'/JsonObject') T(C)

	END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

DECLARE @Body varchar(MAX)

DECLARE @Profile nvarchar(100)

SET @Profile = 'My google'

SET @Body = '{

"title": "new register file.pdf",

"description": "Registration file for students"

--Google API Bearer Token expires after an hour

EXEC usp_Google_Auth_Refresh @Profile

EXEC usp_Google_File_Patch

@Profile = @Profile,

@fileId = '1IYC6qAejm7mJy4SKNx1LdkrOzINJ16iG',

@Body = @Body,

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

BEGIN

DECLARE @X xml

SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject' --

SELECT T.C.value(N'@kind', N'nvarchar(MAX)') AS [kind]

,T.C.value(N'@title', N'nvarchar(MAX)') AS title

,T.C.value(N'@id', N'nvarchar(MAX)') AS [id]

,T.C.value(N'@description', N'nvarchar(MAX)') AS [description]

,T.C.value(N'@fileSize', N'nvarchar(MAX)') AS [fileSize]

FROM @X.nodes(N'/JsonObject') T(C)

END

kind title id description fileSize

----------- ---------------------- ----------------- ------------------------------- ---------

drive#file new register file.pdf 1IYC6qAejm7mJy... Registration file for students 797635



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.