SQLCLR NEWS

Tuesday, 2 December 2008

Using Tweet-SQL to post multiple updates

Many Twitter users seem to post large numbers of updates to promote news, events, or their websites. Doing this manually would be rather time consuming. So here's how easy it is to do with Tweet-SQL...

First lets create a database specifically for this purpose.

-- Create a db called twitter

CREATE DATABASE twitter;

Now create a table and insert some Tweets

USE twitter;

-- Create a table to hold our tweets

CREATE TABLE TwitterPosts

(

id INT PRIMARY KEY IDENTITY(1, 1),

tweet VARCHAR(140) NOT NULL

);

-- Post some links to YouTube videos of

-- Peter Sellers in the Pink Panther films

INSERT INTO TwitterPosts

(

)

SELECT 'Does your dog bite? http://uk.youtube.com/watch?v=SXn2QVipK2o'

UNION ALL

SELECT 'Not now Kato! http://uk.youtube.com/watch?v=c_Jb2Vd4BJY'

UNION ALL

SELECT 'The lift fart... http://uk.youtube.com/watch?v=ViYclyAkkDg'

UNION ALL

SELECT 'The bath scene... http://uk.youtube.com/watch?v=AtLbsyx-1f4'

UNION ALL

SELECT 'The opening to "The Pink Panther"... http://uk.youtube.com/watch?v=HhHwnrlZRus'

UNION ALL

SELECT 'The famous Theme music performed by a live band... http://uk.youtube.com/watch?v=jBupII3LH_Q'

UNION ALL

SELECT 'Outakes... http://uk.youtube.com/watch?v=h0vW5O42cFA'

UNION ALL

SELECT 'The Revenge of the Pink Panther... http://uk.youtube.com/watch?v=r1cbl0zwFws'

UNION ALL

SELECT 'The Pink Panther Strikes Again... http://uk.youtube.com/watch?v=1SG4xb_o8BM'

UNION ALL

SELECT 'Can Steve Martin compare? http://uk.youtube.com/watch?v=iUCDhvbQFmU';

Here's the TSQL script that makes posting your table of Tweets easy...

-- Setup variables for tweeting

DECLARE @tweet VARCHAR(140),

@id INT;

-- Turn off resultsets from Tweet-SQL

EXEC dbo.tweet_cfg_resultset_send 0;

-- Setup a cursor

DECLARE tweetCursor CURSOR LOCAL FAST_FORWARD FOR SELECT id

FROM dbo.TwitterPosts;

-- Open the cursor and get the first row

OPEN tweetCursor;

FETCH NEXT FROM tweetCursor INTO @id;

WHILE (@@FETCH_STATUS = 0)

BEGIN

-- Get the Tweet to post

SET @tweet = (SELECT tweet FROM dbo.TwitterPosts WHERE id = @id);

-- Tweet!

EXEC dbo.tweet_sts_update @tweet, null;

PRINT 'Posted tweet with id = ' + CAST(@id AS VARCHAR(4)) + ', "' + @tweet + '".';

-- Wait for a while to avoid hitting the API limit

-- One minute delay here

WAITFOR DELAY '00:01';

-- Get the next row

FETCH NEXT FROM tweetCursor INTO @id;

END

-- Clean up

CLOSE tweetCursor;

DEALLOCATE tweetCursor;

-- Turn on resultsets from Tweet-SQL as appropriate

EXEC dbo.tweet_cfg_resultset_send 1;

That's it! If all goes well SQL Server Management Studio will return the following...

200 OK: everything went awesome.
Posted tweet with id = 1, "Does your dog bite? http://uk.youtube.com/watch?v=SXn2QVipK2o".
200 OK: everything went awesome.
Posted tweet with id = 2, "Not now Kato! http://uk.youtube.com/watch?v=c_Jb2Vd4BJY".
200 OK: everything went awesome.
Posted tweet with id = 3, "The lift fart... http://uk.youtube.com/watch?v=ViYclyAkkDg".
200 OK: everything went awesome.
Posted tweet with id = 4, "The bath scene... http://uk.youtube.com/watch?v=AtLbsyx-1f4".
200 OK: everything went awesome.
Posted tweet with id = 5, "The opening to "The Pink Panther"... http://uk.youtube.com/watch?v=HhHwnrlZRus".
200 OK: everything went awesome.
Posted tweet with id = 6, "The famous Theme music performed by a live band... http://uk.youtube.com/watch?v=jBupII3LH_Q".
200 OK: everything went awesome.
Posted tweet with id = 7, "Outakes... http://uk.youtube.com/watch?v=h0vW5O42cFA".
200 OK: everything went awesome.
Posted tweet with id = 8, "The Revenge of the Pink Panther... http://uk.youtube.com/watch?v=r1cbl0zwFws".
200 OK: everything went awesome.
Posted tweet with id = 9, "The Pink Panther Strikes Again... http://uk.youtube.com/watch?v=1SG4xb_o8BM".
200 OK: everything went awesome.
Posted tweet with id = 10, "Can Steve Martin compare? http://uk.youtube.com/watch?v=iUCDhvbQFmU".

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