(SQL Server) Facebook Download all Photos to Local Files

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 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 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_9_5_0.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_9_5_0.OAuth2', @oauth2 OUT

    EXEC sp_OASetProperty @oauth2, 'AccessToken', 'FACEBOOK-ACCESS-TOKEN'

    DECLARE @rest int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Rest', @rest OUT

    -- Connect to Facebook.
    DECLARE @success int
    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_9_5_0.JsonObject', @photoJson OUT

    DECLARE @saPhotoUrls int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringArray', @saPhotoUrls OUT

    DECLARE @sbPhotoIdPath int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbPhotoIdPath OUT

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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_9_5_0.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_9_5_0.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_9_5_0.JsonObject', @urlJson OUT

    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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_9_5_0.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_9_5_0.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