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