SQL Server

Read a Single Facebook Post

See more Facebook Examples

Demonstrates how to read the contents of a single Facebook post. A post is an individual entry in a profile's feed. The profile could be a user, page, app, or group. SQL Server

-- 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
    -- 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 assumes a previously obtained an access token
    DECLARE @oauth2 int
    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
    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 = 0
      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

    -- This example assumes a post id was already retrieved.
    -- For example, it could've been retrieved by reading the user's feed:
    -- See Parsing the Facebook User Feed for code showing how to parse the JSON feed content.

    DECLARE @postId nvarchar(4000)
    SELECT @postId = '10224048320139890_10210156138515282'

    DECLARE @sbPath int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT

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

    -- Select the fields we want.
    -- This example will select almost all the possible fields.
    -- See https://developers.facebook.com/docs/graph-api/reference/post/
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'fields', 'id,message,created_time,caption,description,from,link,name,object_id,picture,place,privacy,properties,shares,source,status_type,story,targeting,to,type,updated_time,with_tags'

    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 = 0
      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
    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 JSON response is shown here.  
    -- { 
    --   "id": "12345678901234567_12345678900000004",
    --   "message": "Ignore my posts -- I'm doing some testing for Facebook related programming...",
    --   "created_time": "2016-09-29T20:46:18+0000",
    --   "from": { 
    --     "name": "John Doe",
    --     "id": "12345678901234567"
    --   },
    --   "link": "https:\/\/www.facebook.com\/photo.php?fbid=10210199026247451&set=a.1237223526054.2038240.1094202869&type=3",
    --   "object_id": "10210139026347451",
    --   "picture": "https:\/\/scontent.xx.fbcdn.net\/v\/t1.0-9\/14462791_10210199026647451_7830642117574407060_n.jpg?oh=a7f9ed10ce9cd81a82adeb541c60e2e2&oe=58ABB195",
    --   "privacy": { 
    --     "allow": "",
    --     "deny": "",
    --     "description": "Public",
    --     "friends": "",
    --     "value": "EVERYONE"
    --   },
    --   "status_type": "added_photos",
    --   "type": "photo",
    --   "updated_time": "2016-09-29T20:46:18+0000"
    -- }

    -- This is the code to parse some fields in the JSON response.

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'type'
    PRINT 'type: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'message'
    PRINT 'message: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'id'
    PRINT 'id: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'link'
    PRINT 'link: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'privacy.description'
    PRINT 'privacy descripton: ' + @sTmp0

    DECLARE @dtime int
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtime OUT

    DECLARE @bLocalTime int
    SELECT @bLocalTime = 1
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'created_time'
    EXEC sp_OAMethod @dtime, 'SetFromTimestamp', @success OUT, @sTmp0
    DECLARE @dt int
    EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dt OUT

    EXEC sp_OAMethod @dtime, 'ToDtObj', NULL, @bLocalTime, @dt

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp1 OUT

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
    PRINT @iTmp0 + '/' + @iTmp1 + '/' + @iTmp2 + '  ' + @iTmp3 + ':' + @iTmp4

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


END
GO