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

SQL #

Expanding the
capabilities of T-SQL

Home Features Benefits F.A.Q. Contact Us
Full Version Free Version Documentation

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

-- Documentation: Twitter Setup --

Setting up your Twitter Application:

Go to:
https://dev.twitter.com/
If you are not logged in, use the Sign in link at the top:
https://dev.twitter.com/login
Click on your icon drop-down (top menu, right side) and select My applications:
https://apps.twitter.com/
Click on the Create New App button on the right:
https://apps.twitter.com/app/new
Fill in:
1. 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. Website: This cannot be blank
Check the Yes, I agree checkbox below the Developer Rules of the Road and click the Create your Twitter application at the bottom.
You are now directed to your application page with the URL being:
https://apps.twitter.com/app/{your_application_id}
If you need to request xAuth access, this is the ApplicationID they will want.
Click on the Permissions tab.
Under Access, select the Read, Write and Access direct messages radio-button and click the Update settings button at the bottom. Note: you will get a warning if your Twitter account does not have a mobile number configured in your profile.
Click on the API Keys tab.
In the top section, Application settings, the first two items are the API key and API secret (previously named ConsumerKey and ConsumerSecret, respectively). These two values identify your application and are needed for ALL SQL# Twitter functions (whether you use xAuth or not). These values will change if you update the Access setting on the Permissions tab or if you click the Regenerate API keys button in the Application actions subsection. Make sure that Access level in Application settings shows Read, Write and Access direct messages and that you are using the current values for the API key and API secret.
Scroll down to the Your access token section. The first time you come to this section it will be empty outside of a Token actions subsection. Click the Create my access token button in the Token actions subsection.
Scroll down to the Your access token section. If there is no new information in this section then you might need to refresh your browser (it takes a few moments for Twitter to generate the info). The two values shown here Access token and Access token secret are the other two values you need for the SQL# Twitter functions (for most users). Verify that Access level in this section shows the same Read, Write and Access direct messages value that we used above in the Application settings section. If it shows Read only you will need to change the Access value under the Permissions tab and come back here to click on the Regenerate my access token button.

SQL# Twitter Examples:

In either case, run the following once:

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

Typical Usage:

DECLARE @APIKey NVARCHAR(100),
        @APISecret NVARCHAR(100),
        @AccessToken NVARCHAR(100),
        @AccessTokenSecret NVARCHAR(100)

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

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

SELECT @StatusID

Usage with xAuth:

DECLARE @APIKey NVARCHAR(100),
        @APISecret NVARCHAR(100),
        @AccessToken NVARCHAR(100),
        @AccessTokenSecret NVARCHAR(100),
        @UserName NVARCHAR(100),
        @Password NVARCHAR(100)

SELECT  @APIKey = 'aaaaaaaaaaa',
        @APISecret = 'bbbbbbbbbbb'

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

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

DECLARE @StatusID BIGINT

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

SELECT @StatusID