Archiving your timeline with Tweet-SQL

This TSQL script uses Tweet-SQL to archive all of the authenticating users tweets to a local SQL Server table. If you have a large number of Tweets to archive you may wish to break up the archiving by keeping track of the page number you are requesting. You can also modify the WAITFOR DELAY value to avoid hitting the API limit.

First create a table to hold our timeline....

   1:  CREATE TABLE TwitterArchive

   2:  (

   3:      created_at NVARCHAR(30),

   4:      id INT PRIMARY KEY,

   5:      [text] NVARCHAR(140),

   6:      source NVARCHAR(100),

   7:      truncated NVARCHAR(5),

   8:      in_reply_to_status_id INT,

   9:      in_reply_to_user_id INT,

  10:      favorited NVARCHAR(5),

  11:      status_Id INT,

  12:      statuses_Id INT                                            

  13:  );

This script will archive all of the authenticating users timeline a page at a time. Make adjustments to this script as your situation dictates.

   1:  DECLARE @xml XML,

   2:          @handle INT,

   3:          @page INT,

   4:          @optional NVARCHAR(10),

   5:          @rowcount TINYINT;

   6:   

   7:  SET @page = 1;

   8:   

   9:  -- Turn of resultsets from Tweet-SQL

  10:  EXEC dbo.tweet_cfg_resultset_send 0;

  11:   

  12:  -- Set the optional parameter to request page 1

  13:  SET @optional = '?page=' + CAST(@page AS NVARCHAR(4));

  14:  -- Get page 1 of your timeline

  15:  EXEC dbo.tweet_sts_user_timeline null, @optional, @xml OUTPUT;

  16:  SET @rowcount = 20;

  17:   

  18:  WHILE (@rowcount = 20) -- While we still have results to deal with

  19:  BEGIN

  20:      -- Prepare an xml document

  21:      EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

  22:   

  23:      -- Insert the page into a table

  24:      INSERT INTO dbo.TwitterArchive

  25:      (

  26:          created_at,

  27:          id,

  28:          [text],

  29:          source,

  30:          truncated,

  31:          in_reply_to_status_id,

  32:          in_reply_to_user_id,

  33:          favorited,

  34:          status_Id,

  35:          statuses_Id    

  36:      )

  37:      SELECT  created_at,

  38:              id,

  39:              [text],

  40:              source,

  41:              truncated,

  42:              in_reply_to_status_id,

  43:              in_reply_to_user_id,

  44:              favorited,

  45:              status_Id,

  46:              statuses_Id

  47:      FROM OPENXML (@handle, '/statuses/status', 2)

  48:      WITH

  49:      (

  50:          created_at NVARCHAR(30),

  51:          id INT,

  52:          [text] NVARCHAR(140),

  53:          source NVARCHAR(100),

  54:          truncated NVARCHAR(5),

  55:          in_reply_to_status_id INT,

  56:          in_reply_to_user_id INT,

  57:          favorited NVARCHAR(5),

  58:          status_Id INT,

  59:          statuses_Id INT

  60:      );

  61:   

  62:      -- Get the rowcount, when < 20 then we have the last page

  63:      SET @rowcount = @@ROWCOUNT;

  64:      -- Which page have we done?

  65:      PRINT 'Archived page ' + CAST(@page AS NVARCHAR(4));

  66:      -- destroy the xml document

  67:      EXEC sp_xml_removedocument @handle;

  68:      -- Increment the page count

  69:      SET @page = @page + 1;

  70:      -- Setup the optional parameter

  71:      SET @optional = '?page=' + CAST(@page AS NVARCHAR(4));

  72:      -- Wait for a bit...

  73:      WAITFOR DELAY '00:00:05';

  74:      -- Get the next page

  75:      EXEC dbo.tweet_sts_user_timeline null, @optional, @xml OUTPUT;

  76:  END

  77:   

  78:  -- Turn resultsets from Tweet-SQL back on as appropriate

  79:  EXEC dbo.tweet_cfg_resultset_send 1;

SQL Server Management Studio will return something like the below on completion.

200 OK: everything went awesome.

(20 row(s) affected)
Archived page 1
200 OK: everything went awesome.

(20 row(s) affected)
Archived page 2
200 OK: everything went awesome.

(20 row(s) affected)
Archived page 3
200 OK: everything went awesome.

(20 row(s) affected)
Archived page 4
200 OK: everything went awesome.

(20 row(s) affected)
Archived page 5
200 OK: everything went awesome.

(20 row(s) affected)
Archived page 6
200 OK: everything went awesome.

(20 row(s) affected)
Archived page 7
200 OK: everything went awesome.

(9 row(s) affected)
Archived page 8
200 OK: everything went awesome.

Blogged with the Flock Browser

Tweet-SQL a practical example

Here's the first in a series of quick and dirty TSQL scripts showing what you can do with the Tweet-SQL suite of procedures. This script uses several procedures in order to get the authenticating users followers and send a direct message to each of them.

   1:  DECLARE @xml XML,

   2:          @handle INT,

   3:          @twitter_user_id INT,

   4:          @twitter_username NVARCHAR(100),

   5:          @message NVARCHAR(140),

   6:          @msg_count INT;

   7:   

   8:  -- Set the message

   9:  SET @message = 'Hello, this is a test message';

  10:  SET @msg_count = 0;

  11:   

  12:  -- Turn of resultsets from Tweet-SQL

  13:  EXEC dbo.tweet_cfg_resultset_send 0;

  14:   

  15:  -- Get the followers for the authenticating user

  16:  EXEC dbo.tweet_usr_followers null, null, @xml OUTPUT;

  17:   

  18:  -- Create an xml document

  19:  EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

  20:   

  21:  -- Get all followers into a cursor

  22:  DECLARE userCursor CURSOR LOCAL FAST_FORWARD FOR SELECT id,

  23:                                                          [name]

  24:                                                   FROM OPENXML (@handle, '/users/user', 2)

  25:                                                   WITH

  26:                                                   (

  27:                                                      id INT,

  28:                                                      [name] NVARCHAR(100)

  29:                                                   );

  30:   

  31:  -- Open the cursor and fetch the first row

  32:  OPEN userCursor

  33:  FETCH NEXT FROM userCursor INTO @twitter_user_id,

  34:                                  @twitter_username;

  35:   

  36:  WHILE(@@FETCH_STATUS = 0)

  37:  BEGIN

  38:   

  39:      -- Send a message to each of our followers

  40:      EXEC dbo.tweet_msg_new @twitter_user_id, @message, null;

  41:      PRINT 'Sent message to twitter_user_id ' + CAST(@twitter_user_id AS NVARCHAR(10)) + ' twitter_username ' + @twitter_username;

  42:      -- Increment the sent message count

  43:      SET @msg_count = @msg_count + 1;

  44:      -- Pause so we don't hit the rate limit so fast

  45:      -- 5 seconds here, adjust as appropriate

  46:      WAITFOR DELAY '00:00:05';

  47:   

  48:      -- Get the next row

  49:      FETCH NEXT FROM userCursor INTO @twitter_user_id,

  50:                                      @twitter_username;

  51:  END

  52:   

  53:  -- Print out the sent message count

  54:  PRINT 'Sent a total of ' + CAST(@msg_count AS NVARCHAR(5)) + ' direct messages.'

  55:   

  56:  -- clean up

  57:  CLOSE userCursor;

  58:  DEALLOCATE userCursor;

  59:   

  60:  EXEC sp_xml_removedocument @handle;

  61:   

  62:  -- Turn resultsets from Tweet-SQL back on as appropriate

  63:  EXEC dbo.tweet_cfg_resultset_send 1;

On success the following is returned by SQL Server Management Studio...

200 OK: everything went awesome.
200 OK: everything went awesome.
Sent message to twitter_user_id 16855366 twitter_username rhystest
200 OK: everything went awesome.
Sent message to twitter_user_id 14370206 twitter_username rhyscampbell
Sent a total of 2 direct messages.

Blogged with the Flock Browser

Tweet-SQL: Working with public_timeline xml

Tweet-SQL allows you to use the Twitter API with TSQL.

Tweet-SQL includes the ability to work with resultsets in various ways; Relational Resultsets, a single columned XML result, or through OUTPUT parameters. These can be easily switched through using the Tweet-SQL Configuration Program or the supplied configuration stored procedures.

Relational Resultsets

This first example works from the following Tweet-SQL configuration...



Calling the public timeline is easy...



Don't like the first resultset appearing? No problem...



Fire up the Tweet-SQL Configuration program and change the "No Type Array" combo box as above, File > Save Config. Alternatively you could use the tweet_cfg_no_type_array stored procedure to achieve the same result. Now the first resultset will be omitted.



XML Resultsets

Maybe you would prefer to return the public timeline in a resultset as raw xml? In the Tweet-SQL Configuration program change the "Result Type" Combo Box as below. File > Save Config.



Now the following will be returned...



Click on the xml to view the entire document...



OUTPUT Parameters

OUTPUT parameters are always available for use but, If you prefer, you can work exclusively with OUTPUT parameters by changing the "Result Type" combo box to "No ResultSets".



The below screen-shot shows a simple of example of working with Tweet-SQL in this way.

Blogged with the Flock Browser