SQL# (SQLsharp) - Expanding the capabilities of T-SQL via SQL CLR (Documentation: Twitter Setup)

SQL #

Expanding the
capabilities of T-SQL

Home Features Benefits F.A.Q. Download Documentation Contact Us

SQL CLR Library , SQLCLR , CLR Routines , CLR Library , SQL Server CLR , Bulk Export , Regular Expressions , HTML Export , Generate Insert Statements

-- Documentation: Twitter Setup --

Setting up your Twitter Application:

Go to:
http://dev.twitter.com/
If you are not logged in, use the “Sign in” link at the top:
http://dev.twitter.com/login
Click on the “Your apps” link at the top:
http://dev.twitter.com/apps
Click on the “Register a new app” button on the right:
http://dev.twitter.com/apps/new
Fill in:
1. Application Name: This is what shows up when posting updates just underneath the text of the update, as in:
  Date and Time via _Application Name_
  This name needs to be unique within Twitter and if it is not you will be notified when you try to save the Application.
2. Description: You need to have at least 10 characters here.
3. Application Website: This cannot be blank
4. Application Type: Set this to “Client”
5. Default Access Type: Set this to “Read & Write”
Fill in the captcha words and Click the “Register Application” at the bottom. NOTE: If there is ANY error on your form, you will need to re-set the value of the “Application Type” back to “Client” as it will be set to “Browser” for some reason.
You are now directed to your application page with the URL being:
http://dev.twitter.com/apps/{your_application_id}
If you need to request xAuth access, this is the ApplicationID they will want.
Scroll down to the “OAuth 1.0a Settings” section. The first two items are the “ConsumerKey” and “ConsumerSecret”. These two values identify your application and are needed for ALL SQL# Twitter functions (whether you use xAuth or not).
Scroll up and click on the “My Access Token” button on the right. The two values shown here – “Access Token (oauth_token)” and “Access Token Secret (oath_token_secret)” – are the other two values you need for the SQL# Twitter functions (for most users).

SQL# Twitter Examples:

In either case, run the following once:

EXEC SQL#.SQLsharp_SetSecurity 2, 'SQL#.Twitterizer'

Typical Usage:

DECLARE @ConsumerKey NVARCHAR(100),
        @ConsumerSecret NVARCHAR(100),
        @AccessToken NVARCHAR(100),
        @AccessTokenSecret NVARCHAR(100)

SELECT  @ConsumerKey = 'aaaaaaaaaaa',
        @ConsumerSecret = 'bbbbbbbbbbb',
        @AccessToken = '9999999-ccccccccccc',
        @AccessTokenSecret = 'ddddddddddddddd'
		
DECLARE @StatusID BIGINT

SET @StatusID = SQL#.Twitter_Update(@ConsumerKey, @ConsumerSecret,
    @AccessToken, @AccessTokenSecret, 'test!!!!!', NULL, NULL, NULL)

SELECT @StatusID

Usage with xAuth:

DECLARE @ConsumerKey NVARCHAR(100),
        @ConsumerSecret NVARCHAR(100),
        @AccessToken NVARCHAR(100),
        @AccessTokenSecret NVARCHAR(100),
        @UserName NVARCHAR(100),
        @Password NVARCHAR(100)

SELECT  @ConsumerKey = 'aaaaaaaaaaa',
        @ConsumerSecret = 'bbbbbbbbbbb'

SELECT  @UserName = 'myusername',
        @Password = 'mypassword'

SELECT  @AccessToken = xauth.AccessToken,
        @AccessTokenSecret = xauth.AccessTokenSecret
FROM    SQL#.Twitter_xAuth(@ConsumerKey, @ConsumerSecret,
                           @UserName, @Password) xauth

DECLARE @StatusID BIGINT

SET @StatusID = SQL#.Twitter_Update(@ConsumerKey, @ConsumerSecret, @AccessToken,
    @AccessTokenSecret, 'test again!!!!!', NULL, NULL, NULL)

SELECT @StatusID