Ping-SQL Sneak Peek

Posted on March 13, 2009, 7:16 pm, by Rhys, under Ping-SQL.

Ping-SQL allows you to play with the Ping.fm API with standard T-SQL in Microsoft SQL Server 2005 and above. Ping-SQL includes procedures to programmatically adjust configuration, replicate data to local tables and, of course, interact directly with the ping.fm API. Here’s a quick summary of the procedures currently in the suite…

Procedure	Classification	Comment
ping_cfg_debug	Configuration	Allows you to avoid posting test data to the ping.fm API when set to On / 1.
ping_cfg_license_key	Configuration	Change the Ping-SQL License key.
ping_cfg_no_rsp	Configuration	Removes any data table called “rsp” from resultsets.
ping_cfg_no_services	Configuration	Removes any data tables called “services” from resultsets.
ping_cfg_resultset_send	Configuration	Changes the way resultsets are presented.
ping_cfg_sys_debug	Configuration	Enables Ping-SQL application debugging.
ping_cfg_user_key	Configuration	Allows the user key used to authenticate with ping.fm to be changed.
ping_sys_systemServicesTable	Data Replication	Creates a local table containing the data returned from the system.services API method. This table is called ping_systemServicesTable. Any existing table with this name is dropped first.
ping_sys_userServicesTable	Data Replication	Creates a local table containing the data returned from the user.services API method. The table is called ping_userServicesTable. Any existing table with this name is dropped first.
ping_systemServices	Ping.fm API	Retuns a complete list of services supported by ping.fm.
ping_userKey	Ping.fm API
ping_userLatest	Ping.fm API	Returns the last 25 messages a user has posted through Ping.fm.
ping_userPost	Ping.fm API	Post a message to the users ping.fm services.
ping_userServices	Ping.fm API	Returns a list of services the user has configured in their ping.fm account.
ping_userTriggers	Ping.fm API	Returns a list of custom triggers.
ping_userValidate	Ping.fm API	Validates the configured user key.

So what can you do with Ping-SQL?

Update your Facebook status with Ping-SQL

^?View Code T-SQL

-- Update your facebook status with Ping-SQL
DECLARE @status VARCHAR(140),
		@optional VARCHAR(100);
 
-- Status message to post
SET @status = 'is posting my status to facebook';
-- Specify the service to post to here
SET @optional = 'service=facebook'
 
EXEC dbo.ping_userPost null, @status, @optional, null, null;

Post to Twitter with Ping-SQL

^?View Code T-SQL

-- Twitter microblogging with Ping-SQL
DECLARE @microblog VARCHAR(140),
		@optional VARCHAR(100);
 
-- Status message to post
SET @microblog = 'Drinking Coffee at home';
-- Specify the service to post to here
SET @optional = 'service=twitter'
 
EXEC dbo.ping_userPost null, @microblog, @optional, null, null;

Post to Blogger with Ping-SQL

^?View Code T-SQL

-- Post to Blogger with Ping-SQL
DECLARE @title VARCHAR(100),
		@blog VARCHAR(MAX),
		@optional VARCHAR(100);
 
-- Set the title of your blog
SET @title= 'Test Blog Title';
-- The blog text
SET @blog = 'Lorem ipsum dolor sit amet, consectetur 
adipisicing elit, sed do eiusmod tempor incididunt ut 
labore et dolore magna aliqua. Ut enim ad minim veniam, 
quis nostrud exercitation ullamco laboris nisi ut aliquip 
ex ea commodo consequat. Duis aute irure dolor in 
reprehenderit in voluptate velit esse cillum dolore eu 
fugiat nulla pariatur. Excepteur sint occaecat cupidatat 
non proident, sunt in culpa qui officia deserunt mollit 
anim id est laborum.';
 
-- Specify the service to post to here
-- and set the title tag
SET @optional = 'service=blogger&amp;title=' + @title;
 
-- Important @post_method must be set to blog!
EXEC dbo.ping_userPost 'blog', @blog, @optional, null, null;

Upload Photos to Flickr with Ping-SQL

^?View Code T-SQL

-- Upload a photo to Flickr!
DECLARE @title VARCHAR(140),
		@optional VARCHAR(100),
		@media_path VARCHAR(100);
 
-- Title of the Photo
SET @title = 'Pink Floyd Album Covers';
-- Specify the service to post to here
SET @optional = 'service=flickr';
-- Set the image path 
SET @media_path = 'C:\Users\Rhys\Pictures\pink_floyd_001.jpg';
 
EXEC dbo.ping_userPost null, @title, @optional, @media_path, null;

Ping-SQL is also capable of calling triggers that you have setup on your ping.fm account. In fact Ping-SQL should be capable of calling any of the services supported by ping.fm. Write T-SQL to play with Bebo, Blogger, BrightKite, Facebook, FriendFeed, MySpace, Plurk, Twitter, WordPress.com, Yahoo 360, Yammer and many more.