(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 ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

// 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 @sTmp0 nvarchar(4000)
    -- 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_9_5_0.FileAccess', @fac OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml OUT

    DECLARE @success int

    -- 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_9_5_0.CkDateTime', @dtExpire OUT

    DECLARE @dtNow int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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_9_5_0.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 sp_OAMethod @http, 'PostXml', @resp OUT, 'https://SHAREPOINT_HTTPS_DOMAIN/_api/contextinfo', '', 'utf-8'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      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
        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 @resp

        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
        RETURN
      END

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
    EXEC @hr = sp_OADestroy @resp

    -- 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_9_5_0.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 @xml2


END
GO