SQL Server
SQL Server
SharePoint -- Get Server Form Digest Value
Demonstrates how to get a server form digest value to be placed in the X-RequestDigest HTTP request header for POST, PUT, MERGE, and DELETE requests. A form digest value is typically valid for 1800 seconds (i.e. 30 minutes). This example persists the value to a file, and only requests a new form digest value if the existing one is near expiration.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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- First, let's see if we already have a persisted form digest value
-- that hasn't yet expired.
DECLARE @fac int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
-- My example code (below) persists the form digest XML in this format:
--
-- <savedFormDigestValue>
-- <d:ExpireDateTime>2017-04-12T20:46:39Z</d:ExpireDateTime>
-- <d:FormDigestValue>0x3059FFB920651834540F3E6792EA73F5746B302E953FF4E808E485DB1E6C2836C7CF924644995F092453B02A94DE14A7962674B7B16780AF16EAFB8C246BCDC7,12 Apr 2017 17:08:22 -0000</d:FormDigestValue>
-- </savedFormDigestValue>
--
DECLARE @dtExpire int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtExpire OUT
DECLARE @dtNow int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtNow OUT
DECLARE @formDigestXmlFile nvarchar(4000)
SELECT @formDigestXmlFile = 'qa_data/sharepoint/savedFormDigestValue.xml'
EXEC sp_OAMethod @fac, 'FileExists', @iTmp0 OUT, @formDigestXmlFile
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, @formDigestXmlFile
-- Get the expire date/time
EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'd:ExpireDateTime'
EXEC sp_OAMethod @dtExpire, 'SetFromTimestamp', @success OUT, @sTmp0
-- Get the current date/time
EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
-- Get both times as Unix time values
DECLARE @tNow int
EXEC sp_OAMethod @dtNow, 'GetAsUnixTime', @tNow OUT, 0
DECLARE @tExpire int
EXEC sp_OAMethod @dtExpire, 'GetAsUnixTime', @tExpire OUT, 0
-- If tNow >= tExpire, then fall through.
-- Otherwise, just use the cached digest value.
IF @tNow < @tExpire
BEGIN
PRINT 'Cached digest value is not yet expired.'
EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'd:FormDigestValue'
PRINT 'X-RequestDigest: ' + @sTmp0
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dtExpire
EXEC @hr = sp_OADestroy @dtNow
RETURN
END
END
-- If we got to this point, the cached digest value either does not exist, or expired.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
-- If SharePoint Windows classic authentication is used, then set the
-- Login, Password, LoginDomain, and NtlmAuth properties.
EXEC sp_OASetProperty @http, 'Login', 'SHAREPOINT_USERNAME'
EXEC sp_OASetProperty @http, 'Password', 'SHAREPOINT_PASSWORD'
EXEC sp_OASetProperty @http, 'LoginDomain', 'SHAREPOINT_NTLM_DOMAIN'
EXEC sp_OASetProperty @http, 'NtlmAuth', 1
-- The more common case is to use SharePoint Online authentication (via the SPOIDCRL cookie).
-- If so, do not set Login, Password, LoginDomain, and NtlmAuth, and instead
-- establish the cookie as shown at SharePoint Online Authentication
-- When creating, updating, and deleting SharePoint entities, we'll need
-- to first get the server's form digest value to send in the X-RequestDigest header.
-- This can be retrieved by making a POST request with an empty body to
-- http://<site url>/_api/contextinfo and extracting the value of the
-- d:FormDigestValue node in the XML that the contextinfo endpoint returns.
-- Apparently, SharePoint needs an "Accept" request header equal to "application/xml",
-- otherwise SharePoint will return an utterly incomprehensible and useless error message.
DECLARE @savedAccept nvarchar(4000)
EXEC sp_OAGetProperty @http, 'Accept', @savedAccept OUT
EXEC sp_OASetProperty @http, 'Accept', 'application/xml'
-- Note: The last argument ("utf-8") is meaningless here because the body is empty.
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', 'https://SHAREPOINT_HTTPS_DOMAIN/_api/contextinfo', '', 'utf-8', 'application/xml', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dtExpire
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- Restore the default Accept header
EXEC sp_OASetProperty @http, 'Accept', @savedAccept
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
-- A response status code not equal to 200 indicates failure.
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response status code = ' + @iTmp0
PRINT 'Response body:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dtExpire
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
-- The response XML looks like this:
-- <?xml version="1.0" encoding="utf-8" ?>
-- <d:GetContextWebInformation xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml" m:type="SP.ContextWebInformation">
-- <d:FormDigestTimeoutSeconds m:type="Edm.Int32">1800</d:FormDigestTimeoutSeconds>
-- <d:FormDigestValue>0x3059FFB920651834540F3E6792EA73F5746B302E953FF4E808E485DB1E6C2836C7CF924644995F092453B02A94DE14A7962674B7B16780AF16EAFB8C246BCDC7,12 Apr 2017 17:08:22 -0000</d:FormDigestValue>
-- <d:LibraryVersion>15.0.4569.1000</d:LibraryVersion>
-- <d:SiteFullUrl>https://SHAREPOINT_HTTPS_DOMAIN</d:SiteFullUrl>
-- <d:SupportedSchemaVersions m:type="Collection(Edm.String)">
-- <d:element>14.0.0.0</d:element>
-- <d:element>15.0.0.0</d:element>
-- </d:SupportedSchemaVersions>
-- <d:WebFullUrl>https://SHAREPOINT_HTTPS_DOMAIN</d:WebFullUrl>
-- </d:GetContextWebInformation>
--
-- Cache the digest value, and also an expiration time. If this code is run again
-- before the digest expires, we'll just get it from the file.
DECLARE @xml2 int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml2 OUT
EXEC sp_OASetProperty @xml2, 'Tag', 'savedFormDigestValue'
EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'd:FormDigestValue'
EXEC sp_OAMethod @xml2, 'NewChild2', NULL, 'd:FormDigestValue', @sTmp0
DECLARE @timeoutInSec int
EXEC sp_OAMethod @xml, 'GetChildIntValue', @timeoutInSec OUT, 'd:FormDigestTimeoutSeconds'
PRINT 'Timeout in seconds = ' + @timeoutInSec
-- Convert this to an expire timestamp.
-- Let's make it expire 30 seconds prior to the actual timeout, just to be safe.
IF @timeoutInSec > 30
BEGIN
SELECT @timeoutInSec = @timeoutInSec - 30
END
EXEC sp_OAMethod @dtExpire, 'SetFromCurrentSystemTime', @success OUT
EXEC sp_OAMethod @dtExpire, 'AddSeconds', @success OUT, @timeoutInSec
EXEC sp_OAMethod @dtExpire, 'GetAsTimestamp', @sTmp0 OUT, 0
EXEC sp_OAMethod @xml2, 'NewChild2', NULL, 'd:ExpireDateTime', @sTmp0
-- Persist the digest and expire time to a file.
EXEC sp_OAMethod @xml2, 'SaveXml', @success OUT, @formDigestXmlFile
PRINT 'Here is the new form digest value:'
EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'd:FormDigestValue'
PRINT 'X-RequestDigest: ' + @sTmp0
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dtExpire
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @xml2
END
GO