SQLCLR NEWS: November 2008

A friend of mine helping out with the testing of Tweet-SQL asked how he could add new friends using the supplied procs. He pointed me at a couple of products, one free and the other a whopping US$50, that allow you to add new friends to your twitter account.

http://www.download.com/Twitter-FriendAdder/3000-2650_4-10878011.html

http://www.twitteradder.com/index.php

Obviously this is something that spammers will love and can be easily abused. This article on mashtable illustrates this…

http://mashable.com/2008/04/14/twitter-spam-out-of-control/

I’m sure we’ve all had this happen on our twitter accounts. I for one was thrilled when “Leather Sofa World” decided to follow me… but I can see the attraction that marketeers may have to this. So here’s how you can do it with Tweet-SQL….

DECLARE @xml XML,

@handle INT,

@twitter_user_id INT,

@twitter_username NVARCHAR(100);

-- Turn of resultsets from Tweet-SQL

EXEC dbo.tweet_cfg_resultset_send 0;

-- Get the public timeline, we’re making friends with these guys

EXEC dbo.tweet_sts_public_timeline @xml OUTPUT;

-- Create an xml document

EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

DECLARE userCursor CURSOR LOCAL FAST_FORWARD FOR SELECT id,

[name]

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

WITH

(

id INT,

[name] NVARCHAR(100)

);

-- Open the cursor and get the first result

OPEN userCursor;

FETCH NEXT FROM userCursor INTO @twitter_user_id,

@twitter_username;

-- Loop

WHILE (@@FETCH_STATUS = 0)

BEGIN

-- Making friends has never been so easy (or impersonal)

EXEC dbo.tweet_frd_create @twitter_user_id, null;

PRINT 'Friends with ' + @twitter_username;

-- Get the next row

FETCH NEXT FROM userCursor INTO @twitter_user_id,

@twitter_username;

END

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

EXEC dbo.tweet_cfg_resultset_send 1;

-- Clean up

CLOSE userCursor;

DEALLOCATE userCursor;

EXEC sp_xml_removedocument @handle;

Tweet-SQL will return something like the below once complete