Thursday 23 October 2008

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

Sunday 19 October 2008

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

Wednesday 1 October 2008

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...

d9e7c582-8ff0-11dd-9154-8a5bc84f091e.png

Calling the public timeline is easy...

e3bc4884-8e6b-11dd-8741-8a5bc84f091e.png

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

f763af22-8ff0-11dd-9154-8a5bc84f091e.png

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.

7bdf50ca-8e6c-11dd-8741-8a5bc84f091e.png

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.

1bf8a798-8ff1-11dd-9154-8a5bc84f091e.png

Now the following will be returned...

13f587c6-8e6d-11dd-8741-8a5bc84f091e.png

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

1.png

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".

375b4e28-8ff1-11dd-9154-8a5bc84f091e.png

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

353ccefc-8e6e-11dd-8741-8a5bc84f091e.png

Blogged with the Flock Browser