SQL Server
SQL Server
SharePoint -- Get File Metadata (File Size, Last-Modified Date/Time, etc.)
See more SharePoint Examples
Demonstrates how to get a file's metdata, which includes the file size, last-modified date/time, name, server relative URL, etc.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
DECLARE @iTmp2 int
DECLARE @iTmp3 int
DECLARE @iTmp4 int
DECLARE @iTmp5 int
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.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- 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
-- Indicate that we want a JSON reply
EXEC sp_OASetProperty @http, 'Accept', 'application/json;odata=verbose'
EXEC sp_OASetProperty @http, 'AcceptCharset', 'utf-8'
DECLARE @url nvarchar(4000)
SELECT @url = 'https://SHAREPOINT_HTTPS_DOMAIN/_api/web/GetFileByServerRelativeUrl(''/Documents/VCAC-document.docx'')'
DECLARE @jsonReply nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @jsonReply OUT, @url
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
RETURN
END
-- Examine the file's metadata in JSON format.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonReply
EXEC sp_OASetProperty @json, 'EmitCompact', 0
-- Make sure it was a success response, and that we really have metadata.
-- If it was an error response, then the JSON is error information..
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT '--------'
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'd.ServerRelativeUrl'
PRINT 'Server relative URL: ' + @sTmp0
DECLARE @fileSize int
EXEC sp_OAMethod @json, 'IntOf', @fileSize OUT, 'd.Length'
PRINT 'File Size = ' + @fileSize
DECLARE @lastmod int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @lastmod OUT
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'd.TimeLastModified'
EXEC sp_OAMethod @lastmod, 'SetFromTimestamp', @success OUT, @sTmp0
-- Once we have the CkDateTime object, we can get the date/time in all sorts of formats:
-- Get as a RFC822 GMT string:
DECLARE @bLocalTime int
SELECT @bLocalTime = 0
EXEC sp_OAMethod @lastmod, 'GetAsRfc822', @sTmp0 OUT, @bLocalTime
PRINT @sTmp0
-- Get as an RFC822 string in the local timezone.
-- (remember, the daylight savings that existed at the given time in the past is applied)
SELECT @bLocalTime = 1
EXEC sp_OAMethod @lastmod, 'GetAsRfc822', @sTmp0 OUT, @bLocalTime
PRINT @sTmp0
-- Get as a 32-bit UNIX time (local or GMT..)
-- The Unix time is number of seconds since the Epoch, 1970-01-01 00:00:00 +0000 (UTC).
DECLARE @unixTime int
EXEC sp_OAMethod @lastmod, 'GetAsUnixTime', @unixTime OUT, @bLocalTime
PRINT 'Unix time: ' + @unixTime
-- One can also get the as a "DtObj" object for accessing the individual
-- parts of the date/time, such as month, day, year, hour, minute, etc.
-- The DtObj can be obtained in the GMT or local timezone:
DECLARE @dtObj int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dtObj OUT
EXEC sp_OAMethod @lastmod, 'ToDtObj', NULL, @bLocalTime, @dtObj
EXEC sp_OAGetProperty @lastmod, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'This should never really happen!'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @lastmod
EXEC @hr = sp_OADestroy @dtObj
RETURN
END
EXEC sp_OAGetProperty @dtObj, 'Day', @iTmp0 OUT
EXEC sp_OAGetProperty @dtObj, 'Month', @iTmp1 OUT
EXEC sp_OAGetProperty @dtObj, 'Year', @iTmp2 OUT
EXEC sp_OAGetProperty @dtObj, 'Hour', @iTmp3 OUT
EXEC sp_OAGetProperty @dtObj, 'Minute', @iTmp4 OUT
EXEC sp_OAGetProperty @dtObj, 'Second', @iTmp5 OUT
PRINT @iTmp0 + '-' + @iTmp1 + '-' + @iTmp2 + ' ' + @iTmp3 + ':' + @iTmp4 + ':' + @iTmp5
-- -------------------------------------------------
-- The file's metadata look like this:
-- {
-- "d": {
-- "__metadata": {
-- "id": "Web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')",
-- "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')",
-- "type": "SP.File"
-- },
-- "Author": {
-- "__deferred": {
-- "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')/Author"
-- }
-- },
-- "CheckedOutByUser": {
-- "__deferred": {
-- "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')/CheckedOutByUser"
-- }
-- },
-- "ListItemAllFields": {
-- "__deferred": {
-- "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')/ListItemAllFields"
-- }
-- },
-- "LockedByUser": {
-- "__deferred": {
-- "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')/LockedByUser"
-- }
-- },
-- "ModifiedBy": {
-- "__deferred": {
-- "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')/ModifiedBy"
-- }
-- },
-- "Versions": {
-- "__deferred": {
-- "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')/Versions"
-- }
-- },
-- "CheckInComment": "",
-- "CheckOutType": 2,
-- "ContentTag": "{E2F05E75-B3EF-4826-8284-E572D3628A7D},9,10",
-- "CustomizedPageStatus": 0,
-- "ETag": "\"{E2F05E75-B3EF-4826-8284-E572D3628A7D},9\"",
-- "Exists": true,
-- "Length": "21082",
-- "Level": 2,
-- "MajorVersion": 0,
-- "MinorVersion": 3,
-- "Name": "VCAC-document.docx",
-- "ServerRelativeUrl": "/Documents/VCAC-document.docx",
-- "TimeCreated": "2016-03-04T12:38:01Z",
-- "TimeLastModified": "2017-01-16T04:44:31Z",
-- "Title": "",
-- "UIVersion": 3,
-- "UIVersionLabel": "0.3"
-- }
-- }
--
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @lastmod
EXEC @hr = sp_OADestroy @dtObj
END
GO