How to TWEET from a SQL CLR Stored Procedure

Here’s another SQL Server 2005 geek out moment, a CLR SPROC that tweets to Twitter. Big shoutout to Danny Battison for sharing the C# code to post to Twitter. This is what got me started on the C# side of things.  Also, you can skip all my ramblings here and just download code here and fire it up.  The zip file contains all the source code, the compiled assembly file, and install.sql that shows you how to hook this up.

Being the SQL junky that I am, I was interested in trying out SQL Server’s new CLR Stored Procedures. A CLR sproc is a stored procedure that is able to use .net code that you’ve compiled into an assembly file. For you classic ASP heads out there, think of the ASP page being the sproc, and the .net assembly being your COM object ( cringe, let’s talk about classic ASP ). While there are plenty of great articles on writing CLR stored procedures, I’m going to breeze through the code that makes up this project.

First make a .net class library that will be compiled into an assembly file.

using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Net;
using System.IO;
using System.Text.RegularExpressions;

/// <summary>
/// This assembly will be used by a SQL2005 SPROC to communicate
/// with twitter.com
/// </summary>
public sealed class tweetsproc
{
    /*
     * TWITTER CODE BORROWED FROM :
     *  http://www.dreamincode.net/code/snippet2556.htm
     *
     * A function to post an update to Twitter programmatically
     * Author: Danny Battison
     * Contact: gabehabe@hotmail.com
     */

    /// <summary>
    /// Post an update to a Twitter acount
    /// </summary>
    /// <param name="username">The username of the account</param>
    /// <param name="password">The password of the account</param>
    /// <param name="tweet">The status to post</param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "PostTweet")]
    //public static void PostTweet( string username, string password, string tweet)
    public static void PostTweet(   SqlString username,
                                    SqlString password,
                                    SqlString tweet)
    {
        try
        {
            // encode the username/password
            string user = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(username.ToString() + ":" + password.ToString()));
            // determine what we want to upload as a status
            byte[] bytes = System.Text.Encoding.ASCII.GetBytes("status=" + tweet.ToString());

            // Create a WebPermission.
            WebPermission myWebPermission1 = new WebPermission();

            // Allow Connect access to the specified URLs.
            myWebPermission1.AddPermission(NetworkAccess.Connect,new Regex("http://www\.twitter\.com/.*",
              RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Singleline));

            myWebPermission1.Demand();

            // connect with the update page
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://twitter.com/statuses/update.xml");

            // set the method to POST
            request.Method = "POST";
            request.ServicePoint.Expect100Continue = false; // thanks to argodev for this recent change!
            // set the authorisation levels
            request.Headers.Add("Authorization", "Basic " + user);
            request.ContentType = "application/x-www-form-urlencoded";
            // set the length of the content
            request.ContentLength = bytes.Length;

            // set up the stream
            Stream reqStream = request.GetRequestStream();
            // write to the stream
            reqStream.Write(bytes, 0, bytes.Length);
            // close the stream
            reqStream.Close();

            // Let's get the Response from Twitter
            var webresp = request.GetResponse();
            // Let's read the Response
            var sread = new StreamReader( webresp.GetResponseStream() );

            // Use SqlContext to return data to the QueryAnalyzer results window
            SqlContext.Pipe.Send( sread.ReadToEnd() );

        }
        catch (Exception exc)
        {
            // send error back
            SqlContext.Pipe.Send(exc.Message);
        }
    }
}

Here’s the app.config for this assembly.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.web>
    <trust level="Full" processRequestInApplicationTrust="true" originUrl="" />
  </system.web>
</configuration>

Once you build this project, you should have your assembly ( tweetsproc.dll ) which will be used by your CLR Sproc. Now it’s time to do some SQL server work.

Enable CLR access for SQL server

EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO

Create the SQL Assembly

CREATE ASSEMBLY tweetsproc_clr_assembly from 'C:UsersericDesktopblogtweetsproc.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

Create your SPROC

CREATE PROC tweetsproc_tweet(	@username as nvarchar(50),
								@password as nvarchar(50),
								@tweet as nvarchar(140)
							)
AS
	-- [Assembly Name].[Class Name].[CLR function Name]
	EXTERNAL NAME tweetsproc_clr_assembly.tweetsproc.PostTweet
GO

Tweet from a sproc

EXEC tweetsproc_tweet 'TwitterUsername', 'TwitterPassword', 'Hey @ericfickes, I''m tweeting from my database too!'

Running this sproc returns the XML response from Twitter.

Twitter response from tweet sproc

Tweetsproc returns the full Twitter response

That’s one sample CLR SPROC in the bank!  Feel free to download this code and try it out yourself.  I’d love to get some feedback on anybody looking to use this for real.  While tweeting from a stored procedure probably isn’t a hot topic for anybody, this is a nice teaser for what you can do with CLR sprocs now.

Download code here.

Inside this zip you’ll find this.

  • install.sql is everything you need to install this on your database
  • tweetsproc.dll is the twitter assembly used by the sproc
  • tweetsproc folder is the .net class library project
Contents of tweetsproc.zip

Everything you need to get TWEETING from a sproc

Advertisements

From joke to AdobeMAX in 6 months

Hey, we should jam! Yeah, we totally should. No seriously, we should jam sometime. We can play bad 80s cover songs and play under the name April Fools.

That’s the gist of how my current band formed. A handful of computer programmers who used to play in bands decided it was time to get back on the music horse. At first we had five or so people interested in jamming and seeing where things go. A few jams later and we were down to the power trio that is now known as The Compilers.

The name of the group didn’t come around until a month or so after we had been jamming as the April Fool’s.  All three of us are internet programmers who work with Adobe’s Flash platform, as well as contribute to our local Adobe community.  About the time we officially became a power trio we were kicking around the idea of becoming the house band for our local user group meetings ( RMAUG ).  In the process of laughing this one out, we were asked by RMAUG to play an upcoming Adobe technical event that was happening in two weeks.  That technical event was the “Next Generation Flex & Coldfusion Tour“.

At first we were hesitant booking a show when we didn’t have a real band, or enough material to play a show.  Long story short, we decided to play the show, decided to name the band The Compilers, and decided we should probably integrate some sort of tech into this show.  We didn’t set out wanting to mix technology with music, it just seemed appropriate for this first gig to do something technical.  That technical music nugget is now known as Tweet Jam, our smash hit that plays tweets from @thecompiler‘s Twitter timeline while the band plays underneath the robot voice.

That first show was fun, funny, and the beginning of an interesting musical project.  In the last six months we’ve played four shows, and we gave a presentation at the 360|MAX unconference at Adobe MAX 2009 in Los Angeles, CA.  This really isn’t a lot of gigs, but considering we started this band in June, it’s been quite a whirlwind of musical and technical action.  Jun has built at least three versions of the TweetJam application, as well as integrated his midi foot controller with TheCompilers Flex player.  I’ve gotten pretty comfortable playing iPhone, made a few Flash movies for our set, and started looking into sound generation with Sonoflash.  Scottie has been the mad scientist behind the green velvet curtain.  Drummer man, electric drummer man, sound man, idea man, recorder man, crazy Brazillian ghord instrument player.

It’s so great to be back in a band.  These last six months have been a blast, and it was all started from a few jokes.  Speaking of jokes, below you will find two flash movies that I built for our 360|MAX presentation, as well as our talk.  The concept of melding tech with music is new to us, so this talk was a bit off the cuff, but it was still a great time.

360|MAX assets

It’s a Laser! – My first experiment with the Sonoflash library.  Adjust your laser properties then click shoot.  Each slider has a keyboard listener.  Attack = A, Release = R, and Frequency = F.

Click to launch the laser maker

Click to launch the laser maker

By Note – This sample is the beginning of layering sounds and controlling notes and octaves.  This one can play the major notes C, D, E, F, G, A, B using your keyboard and the play button.  The octave controller on the right is powered by mouseover, 1 is low and 9 is high.  The sounds also have keyboard listeners.  Laser = 1 and Ping = 2.  Note, the event listeners don’t kick in until after you’ve clicked a button, so it may be quirky at first.  I built this on the plane ride out to LA and couldn’t figure that one out.

Click to launch this madness

Click to launch this madness

360|MAX presentation : “Geek Rock with the Flash Platform”

http://vimeo.com/moogaloop.swf?clip_id=7271593&server=vimeo.com&show_title=1&show_byline=0&show_portrait=0&color=c9ff23&fullscreen=1

@thecompilers at 360 MAX from Eric Fickes on Vimeo.

411 on The Compilers

Facebook : http://www.facebook.com/pages/The-Compilers/110943240897

MySpace : http://www.myspace.com/compilers

Twitter : http://twitter.com/thecompilers

My first DestroyTwitter theme

Currently my favorite Twitter desktop client is DestroyTwitter.  It doesn’t have all of the same features as TweetDeck and Twhirl, but it is light, snappy, and fun to use.

One of the killer features is the online Theme Builder.

DestoryTwitter Theme Builder

DestoryTwitter Theme Builder

I created a simple green theme called DtGreen.

DestroyTwitter theme DtGreen

DestroyTwitter theme DtGreen

Update Twitter status from an HTML form

UPDATE 5.28 : As you probably guessed I’ve been slacking on this, but I’d like to think of it more of just being temporarily roadblocked.  I did a little work on the HTML client and added the nice character count and everything, but can’t seem to figure out how to send the Basic Authentication header via Javascript.  I tapped into the XmlHttpRequest as well as a JQuery lib, but just couldn’t get it going.  As of now my html twitter client does tweet, but you’ll get the login prompt from twitter.com.  This is not good IMO which is why I haven’t posted anything yet.  I’ve since changed my focus to my AIR Yammer client, and probably adding Twitter support to that.  For anybody interested, here’s my latest HTML twitter client in case you’d like to fix it up, or even use it for that matter.  If you can make this work, please drop me a note, I’d love to see how you did it.

Having a hard time getting the Basic Authentication header working in JS

Having a hard time getting the Basic Authentication header working in JS

UPDATE : When I was first messing with this little form, I didn’t realize I had already authenticated with Twitter and thought the hidden fields were in fact doing their jobs by passing the username and password.  Turns out that’s not the case.  When you submit this form, the status will be sent, but you will be presented with a username and password dialog from Twitter.  After logging, your status will be sent.  I know have a goal of figuring out how to make this simple HTML form work with Twitter.  Got a feeling I’ll have to do some javascript magic with OAuth.  Once I figure this out, this post will be updated with the latest html.

@ericfickes

Here’s an easy way to update your Twitter status using a simple HTML form.  Be sure to replace { TWITTER USERNAME }and{ TWITTER PASSWORD } with your information prior to using this form.


&lt;form action=&quot;http://twitter.com/statuses/update.xml&quot; method=&quot;POST&quot; enctype=&quot;application/x-www-form-urlencoded&quot;&gt;

&lt;!-- set your Twitter username and password here --&gt;
&lt;input type=&quot;hidden&quot; name=&quot;user&quot; value=&quot;{ TWITTER USERNAME }&quot; /&gt;
&lt;input type=&quot;hidden&quot; name=&quot;pswd&quot; value=&quot;{ TWITTER PASSWORD }&quot; /&gt;

status &lt;input type=&quot;text&quot; name=&quot;status&quot; maxlength=&quot;140&quot; /&gt;
&lt;input type=&quot;submit&quot; value=&quot; tweet! &quot; /&gt;
&lt;/form&gt;

10 .9 . 8 – A website becomes a man

Tonight is the next step in this year’s internet experiment.  As you can see, I have upgraded the html page to a rhobust, synergy of bleeding edge, web two point oh! technologies.  Ipso Facto, el WordPress-o.

This was the year I decided to bring it to the internets.  Since I write code for a living, it’s been an endless dream to built the perfect CMS to power my name dot com.  The constant struggle over which technology to use, how simple or complex to make it, and the endless ‘oh yeah’s.  So I decided to roll old school and made one large html page.  That was fun for a while, but I’m done the “webmaster” thing.  I’m very excited and impressed with WordPress.  Now if I can only figure out the rest of the system.  So many themes, so many widgets, so little time.

So now it’s live, and on to the dribble.  After the jump?

Been checking out Fuser lately.  I had mistaken them for another Feed Aggregator, but turns out they do email and social.  Gotta say it’s starting to grow on me.  Another part of this year’s internet experiment was to dive back into the scene through RSS and the major social sites.  I was cooking for a while, but started to get data burnout.  I had to cut down on the input.

Since then I’ve steared clear of aggregators.  I’ve gotten off RSS for Twitter and haven’t looked back.  While I don’t see myself using Fuser everyday, I can see myself using it.  It’s starting to grow on me now that I know it’s not a Flex app.  Now I’m actually digging the BeOS-ish bubbly design.  When I found the Exchange integration, that’s when I knew I’d be coming back.  

Can’t wait to see what new site I’ll find tomorrow.

Three more social networks you can find me on

Signed up with three new micro blog social whatchamacallits this week.

  1. posterous.com
  2. plurk.com
  3. identi.ca

Posterous

is cool because I can finally blog via email. I know Flickr has had this since forever, but you had to use that
cryptic email addy that nobody can remember. With Posterous, you just send an email to post@posterous.com.  First you can auto-register just by emailing that addy. Or, sign up at posterous.com,
confirm your email, and then start posting via the same email you registerred with. So, I like this concept, but the rest of it seems pretty standard blah blah.

Plurk

is a cool new approach to micro-blogging : timeline, and karma. I haven’t taken the time to really figure this
site out yet, but I have signed up. This is really the first micro-blog site that has caught my attention since Twitter.  The concept of a timeline and karma are enough to sell me on Plurk. Just check it out already.

Identi.ca

really just seems like a free marketing site to me, but who knows. Besides the cost, I like the big post box at the top of the page.  Other than that, I don’t see what’s so great about it other than it’s fresh and new.

The next thing I’m looking forward to getting into is sleep, but I think that’s a little farther away.
I have the requirements already : burning eyes, daily grumpitude, and lack of motivation around doing most things.

Of course, it may just be gas.