SQL Server
SQL Server
Facebook Download all Photos to Local Files
See more Facebook Examples
Demonstrates how to download all of one's Facebook photos to a local filesystem directory. This sample code keeps a local cache to avoid re-downloading the same photos twice. The program can be run again after a time, and it will download only photos that haven't yet been downloaded.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 example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- This example will use a local disk cache to avoid re-fetching the same
-- photo id after it's been fetched once.
DECLARE @fbCache int
EXEC @hr = sp_OACreate 'Chilkat.Cache', @fbCache OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- The cache will use 1 level of 256 sub-directories.
EXEC sp_OASetProperty @fbCache, 'Level', 1
-- Use a directory path that makes sense on your operating system..
EXEC sp_OAMethod @fbCache, 'AddRoot', NULL, 'C:/fbCache'
-- This example assumes a previously obtained an access token
DECLARE @oauth2 int
EXEC @hr = sp_OACreate 'Chilkat.OAuth2', @oauth2 OUT
EXEC sp_OASetProperty @oauth2, 'AccessToken', 'FACEBOOK-ACCESS-TOKEN'
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
-- Connect to Facebook.
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'graph.facebook.com', 443, 1, 1
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @fbCache
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @rest
RETURN
END
-- Provide the authentication credentials (i.e. the access key)
EXEC sp_OAMethod @rest, 'SetAuthOAuth2', @success OUT, @oauth2
-- There are two choices:
-- We can choose to download the photos the person is tagged in or has uploaded
-- by setting type to "tagged" or "uploaded".
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'type', 'uploaded'
-- To download all photos, we begin with an outer loop that iterates over
-- the list of photo nodes in pages. Each page returned contains a list of
-- photo node ids. Each photo node id must be retrieved to get the download URL(s)
-- of the actual image.
-- I don't know the max limit for the number of records that can be downloaded at once.
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'limit', '100'
-- Get the 1st page of photos ids.
-- See https://developers.facebook.com/docs/graph-api/reference/user/photos/ for more information.
DECLARE @responseJson nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson OUT, 'GET', '/v2.7/me/photos'
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @fbCache
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @rest
RETURN
END
DECLARE @photoJson int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @photoJson OUT
DECLARE @saPhotoUrls int
EXEC @hr = sp_OACreate 'Chilkat.StringArray', @saPhotoUrls OUT
DECLARE @sbPhotoIdPath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPhotoIdPath OUT
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Load', @success OUT, @responseJson
DECLARE @i int
DECLARE @photoId nvarchar(4000)
DECLARE @imageUrl nvarchar(4000)
-- Get the "after" cursor.
DECLARE @afterCursor nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @afterCursor OUT, 'paging.cursors.after'
EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
WHILE @iTmp0 = 1
BEGIN
PRINT '-------------------'
PRINT 'afterCursor = ' + @afterCursor
-- For each photo id in this page...
SELECT @i = 0
DECLARE @numItems int
EXEC sp_OAMethod @json, 'SizeOfArray', @numItems OUT, 'data'
WHILE @i < @numItems
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @photoId OUT, 'data[i].id'
PRINT 'photoId = ' + @photoId
-- We need to fetch the JSON for this photo. Check to see if it's in the local disk cache,
-- and if not, then get it from Facebook.
DECLARE @photoJsonStr nvarchar(4000)
EXEC sp_OAMethod @fbCache, 'FetchText', @photoJsonStr OUT, @photoId
EXEC sp_OAGetProperty @fbCache, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
-- It's not locally available, so get it from Facebook..
EXEC sp_OAMethod @sbPhotoIdPath, 'Clear', NULL
EXEC sp_OAMethod @sbPhotoIdPath, 'Append', @success OUT, '/v2.7/'
EXEC sp_OAMethod @sbPhotoIdPath, 'Append', @success OUT, @photoId
EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'fields', 'id,album,images'
PRINT 'Fetching photo node from Facebook...'
-- This REST request will continue using the existing connection.
-- If the connection was closed, it will automatically reconnect to send the request.
EXEC sp_OAMethod @sbPhotoIdPath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @photoJsonStr OUT, 'GET', @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 @fbCache
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @photoJson
EXEC @hr = sp_OADestroy @saPhotoUrls
EXEC @hr = sp_OADestroy @sbPhotoIdPath
EXEC @hr = sp_OADestroy @json
RETURN
END
-- Add the photo JSON to the local cache.
EXEC sp_OAMethod @fbCache, 'SaveTextNoExpire', @success OUT, @photoId, '', @photoJsonStr
END
-- Parse the photo JSON and add the main photo download URL to saPhotoUrls
-- There may be multiple URLs in the images array, but the 1st one is the largest and main photo URL.
-- The others are smaller sizes of the same photo.
EXEC sp_OAMethod @photoJson, 'Load', @success OUT, @photoJsonStr
EXEC sp_OAMethod @photoJson, 'StringOf', @imageUrl OUT, 'images[0].source'
EXEC sp_OAGetProperty @photoJson, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 1
BEGIN
-- Actually, we'll add a small JSON document that contains both the image ID and the URL.
DECLARE @imgUrlJson int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @imgUrlJson OUT
EXEC sp_OAMethod @imgUrlJson, 'AppendString', @success OUT, 'id', @photoId
EXEC sp_OAMethod @imgUrlJson, 'AppendString', @success OUT, 'url', @imageUrl
EXEC sp_OAMethod @imgUrlJson, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @saPhotoUrls, 'Append', @success OUT, @sTmp0
PRINT 'imageUrl = ' + @imageUrl
END
SELECT @i = @i + 1
END
-- Prepare for getting the next page of photos ids.
-- We can continue using the same REST object.
-- If already connected, we'll continue using the existing connection.
-- Otherwise, a new connection will automatically be made if needed.
EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'type', 'uploaded'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'limit', '20'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'after', @afterCursor
-- Get the next page of photo ids.
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson OUT, 'GET', '/v2.7/me/photos'
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @fbCache
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @photoJson
EXEC @hr = sp_OADestroy @saPhotoUrls
EXEC @hr = sp_OADestroy @sbPhotoIdPath
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @imgUrlJson
RETURN
END
EXEC sp_OAMethod @json, 'Load', @success OUT, @responseJson
EXEC sp_OAMethod @json, 'StringOf', @afterCursor OUT, 'paging.cursors.after'
END
PRINT 'No more pages of photos.'
-- Now iterate over the photo URLs and download each to a file.
-- We can use Chilkat HTTP. No Facebook authorization (access token) is required to download
-- the photo once the URL is known.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
-- We'll cache the image data so that if run again, we don't re-download the same image again.
DECLARE @numUrls int
EXEC sp_OAGetProperty @saPhotoUrls, 'Count', @numUrls OUT
SELECT @i = 0
DECLARE @urlJson int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @urlJson OUT
DECLARE @fac int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
WHILE @i < @numUrls
BEGIN
EXEC sp_OAMethod @saPhotoUrls, 'GetString', @sTmp0 OUT, @i
EXEC sp_OAMethod @urlJson, 'Load', @success OUT, @sTmp0
EXEC sp_OAMethod @urlJson, 'StringOf', @photoId OUT, 'id'
EXEC sp_OAMethod @urlJson, 'StringOf', @imageUrl OUT, 'url'
-- Check the local cache for the image data.
-- Only download and save if not already cached.
EXEC sp_OAMethod @fbCache, 'FetchFromCache', @imageBytes OUT, @imageUrl
EXEC sp_OAGetProperty @fbCache, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
-- This photo needs to be downloaded.
DECLARE @sbImageUrl int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbImageUrl OUT
EXEC sp_OAMethod @sbImageUrl, 'Append', @success OUT, @imageUrl
-- Let's form a filename..
DECLARE @extension nvarchar(4000)
SELECT @extension = '.jpg'
EXEC sp_OAMethod @sbImageUrl, 'Contains', @iTmp0 OUT, '.gif', 0
IF @iTmp0 = 1
BEGIN
SELECT @extension = '.gif'
END
EXEC sp_OAMethod @sbImageUrl, 'Contains', @iTmp0 OUT, '.png', 0
IF @iTmp0 = 1
BEGIN
SELECT @extension = '.png'
END
EXEC sp_OAMethod @sbImageUrl, 'Contains', @iTmp0 OUT, '.tiff', 0
IF @iTmp0 = 1
BEGIN
SELECT @extension = '.tiff'
END
EXEC sp_OAMethod @sbImageUrl, 'Contains', @iTmp0 OUT, '.bmp', 0
IF @iTmp0 = 1
BEGIN
SELECT @extension = '.bmp'
END
DECLARE @sbLocalFilePath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbLocalFilePath OUT
EXEC sp_OAMethod @sbLocalFilePath, 'Append', @success OUT, 'C:/Photos/facebook/uploaded/'
EXEC sp_OAMethod @sbLocalFilePath, 'Append', @success OUT, @photoId
EXEC sp_OAMethod @sbLocalFilePath, 'Append', @success OUT, @extension
EXEC sp_OAMethod @http, 'QuickGet', @imageBytes OUT, @imageUrl
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @fbCache
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @photoJson
EXEC @hr = sp_OADestroy @saPhotoUrls
EXEC @hr = sp_OADestroy @sbPhotoIdPath
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @imgUrlJson
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @urlJson
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @sbImageUrl
EXEC @hr = sp_OADestroy @sbLocalFilePath
RETURN
END
-- We've downloaded the photo image bytes into memory.
-- Save it to the cache AND save it to the output file.
EXEC sp_OAMethod @fbCache, 'SaveToCacheNoExpire', @success OUT, @imageUrl, '', @imageBytes
EXEC sp_OAMethod @sbLocalFilePath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @fac, 'WriteEntireFile', @success OUT, @sTmp0, @imageBytes
EXEC sp_OAMethod @sbLocalFilePath, 'GetAsString', @sTmp0 OUT
PRINT 'Downloaded to ' + @sTmp0
END
SELECT @i = @i + 1
END
PRINT 'Finished downloading all Facebook photos!'
EXEC @hr = sp_OADestroy @fbCache
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @photoJson
EXEC @hr = sp_OADestroy @saPhotoUrls
EXEC @hr = sp_OADestroy @sbPhotoIdPath
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @imgUrlJson
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @urlJson
EXEC @hr = sp_OADestroy @fac
EXEC @hr = sp_OADestroy @sbImageUrl
EXEC @hr = sp_OADestroy @sbLocalFilePath
END
GO