Monday 10 November 2008

Dealing with HTTP formatted dates with Tweet-SQL



Since my post about using Tweet-SQL to archive your Twitter timeline I noticed what a pain dealing with HTTP formatted dates is and how useful it would be to turn them into something a little more SQL Server friendly. So, I came up with a function that will allow this nasty date format to be handled a little more easily.

SELECT dbo.tweet_fnc_dateconvert('Tue Nov 04 22:33:39 +0000 2008');

Returns an ODBC canonical (with milliseconds) formatted datetime value, i.e.  2008-11-04 22:33:39.000

This function will be included in the upcoming release of Tweet-SQL.



Saturday 1 November 2008

Making friends with Tweet-SQL


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

 

200 OK: everything went awesome.

200 OK: everything went awesome.

Friends with Gemma

200 OK: everything went awesome.

Friends with Casper

200 OK: everything went awesome.

Friends with Lesley

200 OK: everything went awesome.

Friends with 如月にゃぶろーP

200 OK: everything went awesome.

Friends with RSS_Foot

200 OK: everything went awesome.

Friends with Ill Street Lounge

200 OK: everything went awesome.

Friends with 戰地記者

200 OK: everything went awesome.

Friends with zomgitseli

200 OK: everything went awesome.

Friends with Marzie

200 OK: everything went awesome.

Friends with Gaurav Mishra

200 OK: everything went awesome.

Friends with Ulises

200 OK: everything went awesome.

Friends with derrinyet

200 OK: everything went awesome.

Friends with kamma

200 OK: everything went awesome.

Friends with id:ultraist

200 OK: everything went awesome.

Friends with Jason_Cobb

200 OK: everything went awesome.

Friends with Mackyplanet

200 OK: everything went awesome.

Friends with おひょい

200 OK: everything went awesome.

Friends with drewmgriffin

200 OK: everything went awesome.

Friends with 極楽トンボ

200 OK: everything went awesome.

Friends with あおやゆい

 

I’ve ran this script twice under my twitter account and got 4 follow backs so far, a 10% return is pretty good. Konnichiha to my new Japanese friends, I’ll have to lean kanji characters! Once the twitter search api is merged with the main API, and plumbed into Tweet-SQL, it would be possible to target twitter users more effectively by  keyword, location and so on so your spamming may be a bit more excusable.  Expect twitter to knuckle down on this in the very near future as abuse of this will make twitter a pain to use!


Blogged with the Flock Browser