SQL Server
SQL Server
Twitter - Get Follower IDs
Demonstrates how to get a list of follower IDs for a Twitter user. If the number of followers is more than can be returned in a single response, this example will use cursors to page through the followers.This example is deprecated and no longer valid.
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 initial setup, which involves setting the OAuth1 properties and connecting
-- to api.twitter.com, is only required once at the beginning. Once connected, the same
-- object instance may be re-used, and if necessary, it will automatically reconnect
-- as needed.
-- Assume we've previously obtained an access token and saved it to a JSON file..
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/tokens/twitter.json'
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
DECLARE @oauth1 int
EXEC @hr = sp_OACreate 'Chilkat.OAuth1', @oauth1 OUT
EXEC sp_OASetProperty @oauth1, 'ConsumerKey', 'TWITTER_CONSUMER_KEY'
EXEC sp_OASetProperty @oauth1, 'ConsumerSecret', 'TWITTER_CONSUMER_SECRET'
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'oauth_token'
EXEC sp_OASetProperty @oauth1, 'Token', @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'oauth_token_secret'
EXEC sp_OASetProperty @oauth1, 'TokenSecret', @sTmp0
EXEC sp_OASetProperty @oauth1, 'SignatureMethod', 'HMAC-SHA1'
EXEC sp_OAMethod @oauth1, 'GenNonce', @success OUT, 16
EXEC sp_OAMethod @rest, 'SetAuthOAuth1', @success OUT, @oauth1, 0
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'api.twitter.com', 443, 1, @bAutoReconnect
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @oauth1
RETURN
END
-- This ends the initial setup...
-- ----------------------------------------------------------------------
-- This Twitter user has about 77.5K followers..
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'screen_name', 'MarcusMiller959'
DECLARE @jsonResponse int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT
EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
-- Get the 1st page of results using a cursor of "-1".
DECLARE @sbNextCursor int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbNextCursor OUT
EXEC sp_OAMethod @sbNextCursor, 'SetString', @success OUT, '-1'
DECLARE @pageNum int
SELECT @pageNum = 1
DECLARE @caseSensitive int
SELECT @caseSensitive = 0
DECLARE @bContinue int
SELECT @bContinue = 1
-- Get a maximum of 5 pages (of 5000 ids each).
WHILE @bContinue = 1
BEGIN
EXEC sp_OAMethod @sbNextCursor, 'ContentsEqual', @iTmp0 OUT, '0', @caseSensitive
IF @iTmp0 = 1
BEGIN
-- This will cause the loop to exit..
SELECT @bContinue = 0
END
ELSE
BEGIN
-- Adds or replaces the query param.
EXEC sp_OAMethod @sbNextCursor, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'cursor', @sTmp0
-- Get the next page of follower IDs
DECLARE @resp nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @resp OUT, 'GET', '/1.1/followers/ids.json'
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @oauth1
EXEC @hr = sp_OADestroy @jsonResponse
EXEC @hr = sp_OADestroy @sbNextCursor
RETURN
END
EXEC sp_OAMethod @jsonResponse, 'Load', @success OUT, @resp
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @oauth1
EXEC @hr = sp_OADestroy @jsonResponse
EXEC @hr = sp_OADestroy @sbNextCursor
RETURN
END
-- Show the number of IDs returned in this
DECLARE @numIds int
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @numIds OUT, 'ids'
PRINT 'Page ' + @pageNum + ', Number of Ids: ' + @numIds
-- Show the 1st 10 ids in this page.
DECLARE @i int
SELECT @i = 0
WHILE @i < @numIds
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'I', @i
EXEC sp_OAMethod @jsonResponse, 'StringOf', @sTmp0 OUT, 'ids[i]'
PRINT ' ' + @i + ': ' + @sTmp0
SELECT @i = @i + 1
IF @i > 10
BEGIN
-- Force the loop to exit.
SELECT @i = @numIds
END
END
SELECT @pageNum = @pageNum + 1
IF @pageNum > 5
BEGIN
SELECT @bContinue = 0
END
ELSE
BEGIN
EXEC sp_OAMethod @jsonResponse, 'StringOf', @sTmp0 OUT, 'next_cursor_str'
EXEC sp_OAMethod @sbNextCursor, 'SetString', @success OUT, @sTmp0
END
END
END
-- A successful JSON response for the 1st page looks like this:
-- {
-- "ids": [
-- 3140496044,
-- 793204773751324672,
-- 789951187781050369,
-- 763520773587922945,
-- ...
-- 15031286,
-- 2668251246,
-- 3751659443
-- ],
-- "next_cursor": 1531680438812851153,
-- "next_cursor_str": "1531680438812851153",
-- "previous_cursor": 0,
-- "previous_cursor_str": "0"
-- }
-- The output of this program looks like this:
-- Page 1, Number of Ids: 5000
-- 0: 931953350
-- 1: 786708055
-- 2: 560845700
-- 3: 3140496044
-- 4: 793204773751324672
-- 5: 789951187781050369
-- 6: 763520773587922945
-- 7: 793143274059988992
-- 8: 793139683412762624
-- 9: 1588222783
-- 10: 703821370778451968
-- Page 2, Number of Ids: 5000
-- 0: 15031286
-- 1: 2668251246
-- 2: 3751659443
-- 3: 3324584493
-- 4: 2440214809
-- 5: 1322335441
-- 6: 4439178393
-- 7: 4911573711
-- 8: 720792880080560128
-- 9: 720805087124267008
-- 10: 172926330
-- Page 3, Number of Ids: 5000
-- 0: 93867176
-- 1: 2992946183
-- 2: 2825296077
-- 3: 3784572861
-- 4: 2150051321
-- 5: 2460881603
-- 6: 4128849341
-- 7: 2234697931
-- 8: 2379418164
-- 9: 3425171542
-- 10: 325759186
-- Page 4, Number of Ids: 5000
-- 0: 22793412
-- 1: 3347750489
-- 2: 316923043
-- 3: 2481719196
-- 4: 3363591905
-- 5: 3238116492
-- 6: 58467130
-- 7: 3015182362
-- 8: 2985342719
-- 9: 3095965720
-- 10: 17505957
-- Page 5, Number of Ids: 5000
-- 0: 2541434684
-- 1: 140022957
-- 2: 134845054
-- 3: 772810508
-- 4: 16979294
-- 5: 2320540225
-- 6: 105439442
-- 7: 2796744529
-- 8: 251128801
-- 9: 350229758
-- 10: 2683994716
--
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @oauth1
EXEC @hr = sp_OADestroy @jsonResponse
EXEC @hr = sp_OADestroy @sbNextCursor
END
GO