(SQL Server) Get Individual Photo Info

Assuming we have the ID of a Photo, this example demonstrates how to retrieve the photo information and parse the JSON.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example assumes a previously obtained an access token
    DECLARE @oauth2 int
    -- Use "Chilkat_9_5_0.OAuth2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.OAuth2', @oauth2 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.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 @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

    -- Assumes we've already obtained a Photo ID.
    DECLARE @photoId nvarchar(4000)
    SELECT @photoId = '10210199026347451'

    DECLARE @sbPath int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT

    EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '/v2.7/'
    EXEC sp_OAMethod @sbPath, 'Append', @success OUT, @photoId

    -- Select the fields we want.
    -- This example will select many of the possible fields.
    -- See https://developers.facebook.com/docs/graph-api/reference/photo/
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'fields', 'id,album,can_delete,can_tag,from,height,width,images,link,name,name_tags,picture,place,target'

    DECLARE @responseJson nvarchar(4000)
    EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson 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 @oauth2
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @sbPath
        RETURN
      END

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Load', @success OUT, @responseJson

    -- Show the JSON in human-readable format.
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- A sample response is shown below.
    -- Demonstrate how to parse values from the JSON.

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'album.name'
    PRINT 'Album name: ' + @sTmp0
    DECLARE @canDelete int
    EXEC sp_OAMethod @json, 'BoolOf', @canDelete OUT, 'can_delete'

    PRINT 'Can Delete: ' + @canDelete

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'from.name'
    PRINT 'From Name: ' + @sTmp0
    DECLARE @height int
    EXEC sp_OAMethod @json, 'IntOf', @height OUT, 'height'
    DECLARE @width int
    EXEC sp_OAMethod @json, 'IntOf', @width OUT, 'width'


    PRINT 'Dimensions: ' + @width + 'x' + @height

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'images[0].source'
    PRINT 'First Image Source: ' + @sTmp0

    -- A sample JSON response is shown here.  
    -- { 
    --   "id": "10210199026347451",
    --   "album": { 
    --     "created_time": "2009-10-19T00:06:46+0000",
    --     "name": "Timeline Photos",
    --     "id": "1237223526054"
    --   },
    --   "can_delete": true,
    --   "can_tag": true,
    --   "from": { 
    --     "name": "Matt Smith",
    --     "id": "10224048320139890"
    --   },
    --   "height": 120,
    --   "width": 120,
    --   "images": [
    --     { 
    --       "height": 120,
    --       "source": "https:\/\/scontent.xx.fbcdn.net\/v\/t1.0-9\/14462791_10210199026347451_7830642117574407060_n.jpg?oh=a7f9ed10cf9cd81a82adeb541c60e2e2&oe=58ABB195",
    --       "width": 120
    --     }
    --   ],
    --   "link": "https:\/\/www.facebook.com\/photo.php?fbid=10210199026347451&set=a.1237223526054.2038240.1093202869&type=3",
    --   "name": "Ignore my posts -- I'm doing some testing for Facebook related programming...",
    --   "picture": "https:\/\/scontent.xx.fbcdn.net\/v\/t1.0-9\/14462791_10210199026347451_7830642117574407060_n.jpg?oh=a7f9ed10cf9cd81a82adeb541c60e2e2&oe=58ABB195"
    -- }
    -- 

    EXEC @hr = sp_OADestroy @oauth2
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @sbPath
    EXEC @hr = sp_OADestroy @json


END
GO