Joe Lennon

In this post, you will use the UTL_HTTP database package and the PL/JSON library to connect to the Twitter API and retrieve status updates from the Twitter microblogging service.

This post assumes you have access to an Oracle database with the UTL_HTTP package available (Oracle Database 10g Express Edition does) and that you have installed the PL/JSON library, a set of tools for working with JSON data in PL/SQL. For a guide to installing PL/JSON and getting to grips with the basics of the library, see this previous post.

The UTL_HTTP package is not usually granted to database users other than SYS, so the first step is to log on to your Oracle database as SYS and run the following command:

grant execute on utl_http to embosa;

Of course, be sure to replace “embosa” with your Oracle username. You should now have access to the UTL_HTTP package. Next, let’s verify that UTL_HTTP can connect to the Twitter API and return data from it. Execute the following PL/SQL block to do so:

declare
    req   utl_http.req;
    resp  utl_http.resp;
    value varchar2(32767);
begin
    req := utl_http.begin_request('http://api.twitter.com/1/statuses/public_timeline.json');
    resp := utl_http.get_response(req);
    loop
        utl_http.read_line(resp, value, true);
        dbms_output.put_line(substr(value, 1, 255));
    end loop;
    utl_http.end_response(resp);
exception
    when utl_http.end_of_body then
        utl_http.end_response(resp);
end;
/

This should return a response similar to the following:

[{"in_reply_to_user_id":67594046,"in_reply_to_status_id":null,"created_at":"Sat Feb 27 11:30:06 +0000 2010","favorited":false,"source":"web","geo":null,"in_reply_to_screen_name":"pemoutinho","truncated":false,"contributors":null,"user":{"geo_enabled":fals

Note that in the above example, the output has been trimmed down to 255 characters so that the DBMS_OUTPUT buffer would not overflow. In a moment you will parse the entire response as JSON and access individual properties using the PL/JSON library.

Now that you have verified that UTL_HTTP can connect to the Twitter API, let's parse the JSON and print out the status updates in a more meaningful format:

declare
    req            utl_http.req;
    resp           utl_http.resp;
    value          varchar2(32767);

    jsonArray     json_list;
    jsonObj        json;
begin
    begin
        req := utl_http.begin_request('http://api.twitter.com/1/statuses/public_timeline.json');
        resp := utl_http.get_response(req);
        utl_http.read_text(resp, value);
        utl_http.end_response(resp);
    exception
        when utl_http.end_of_body then
            utl_http.end_response(resp);
    end;

    jsonArray := json_list(value);
    dbms_output.put_line('---------------------------------------------------------------');
    for i in 1..jsonArray.count loop
        jsonObj := json.to_json(jsonArray.get_elem(i));
        dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'text'));
        dbms_output.put_line('Posted by ' ||json_ext.get_varchar2(jsonObj, 'user.screen_name'));
        dbms_output.put_line('Posted on ' ||json_ext.get_varchar2(jsonObj, 'created_at'));
        dbms_output.put_line('---------------------------------------------------------------');
    end loop;
end;
/

In this example, you get the entire response from the Twitter API using UTL_HTTP.READ_TEXT, and parse it using PL/JSON's json_list object (as Twitter's API is a JSON array containing several JSON objects). You then loop through each item in this list, extracting the text, user screen name and created at properties for each status update and outputting them. Each tweet is separated by a line of hyphens to make it easier to read. The final output should look something like the following:

---------------------------------------------------------------
Am reading this blog and a girl mentione Habana Outpost. Oh Brooklyn, sometimes I miss you. though I heard Habana was wack now...
Posted by thefashionbomb
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@0oo \u75b2\u308c\u3061\u3083\u3044\u307e\u3057\u305f\uff1f\u3061\u3087\u3063\u3068\u4f11\u307f\u307e\u3057\u3087\u3046\u304b
Posted by naoko_bot
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@papodebola sobre o horario dos jogos em sampa, uma frase que todo politico teme: Ninguem aguentaria 15 min de fama no Jornal Nacional 
Posted by ReeFzera
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@abogado_kaito \u308f\u304b\u308c\u3070\u3044\u3044\u306e\u3088\uff01
Posted by renkon_0
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
Just write something about Nicks cars:D
Posted by love_ncarter
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@Reema226: The book store?
Posted by amitgupta
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
\u3057\u304b\u3057TV\u306e\u30d6\u30e9\u30c7\u30a3\u30de\u30f3\u30c7\u30a4\u306f\u30d5\u30a1\u30eb\u30b3\u30f3\u5f31\u3044\u306a\u301c\u3002
Posted by kazuwitter
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
ya abisnya kamu rese di;iatin org tau   RT @ninomayday: yehee siapa suruh coba lagian maksa dasar lu yaang hahaha :p
Posted by karinutriansyah
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
\u3053\u3046\u3044\u3046\u3053\u3068\u77e5\u3063\u3066\u308b\u304b\u3089\u306a\u306b\uff1f\u3063\u3066\u601d\u3044\u307e\u3059\u3002\u81ea\u5206\u306e\u884c\u52d5\u3068\u306e\u9023\u9396\u3092\u8003\u3048\u3082\u3057\u306a\u3044\u3067\u3002\u3042\u3068\u81ea\u5206\u306e\u56fd\u306e\u8ca7\u56f0\u3082\u77e5\u3089\u306a\u3044\u306e\u306b\u304b\u308f\u3044\u305d\u3046\u3068\u304b\u4e2d\u9014\u534a\u7aef\u3059\u304e\u308b\u3068\u601d\u3044\u307e\u3059\u3002RT @gaitifujiyama \u65e5\u30c6\u30ec\u306e\u4e16\u754c\u4e00\u53d7\u3051\u305f\u3044\u6388\u696d\u3001\u6642\u305f\u307e\u3044\u3044\u4e8b\u3084\u308b\u3093\u3060\u3088\u306d\u3002\u4eca\u3001\u6b63\u306b\u305d\u308c\u3084\u3063\u3066\u3044\u308b
Posted by machuiii
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@_rica \uc544 \uc774\uac74 tiltshiftgen \uc774\ub77c\ub294 \uc571\uc758 \ud798\uc744 \ube4c\ub9b0 \uc870\uc791[..] \uc2e4\uc81c \uce74\uba54\ub77c\uc5d0\uc11c\ub294 \ud2f8\ud305 \ub80c\uc988\uac00 \uc774\ub7f0 \ubbf8\ub2c8\uc5b4\ucc98 \ud6a8\uacfc\ub97c \ub0b4\uc900\ub2e4\uace0 \ud558\ub354\uad70\uc694.
Posted by jstrane
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@micaelquental, fala maluco,,,
Posted by Jeankarllus
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
Ask me anything http://formspring.me/CassiGLAMOUR
Posted by Cassi_Hopeful
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
I killed a plant, and I disliked it!
Posted by Malantur
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
Can someone give me Ro's link to his page on here just with the @ so I get onto it since I can't do it on the search bar!? =/ x
Posted by Feehilys_Angelx
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
RT @terrycavanagh: Really excited by the concept behind Jason Rohrer's new game - check it out: http://sleepisdeath.net/
Posted by sonneveld
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@zzz_ral \u53ef\u611b\u3044\u3001\u3063\u3066\u3044\u3046\u304b\u30a8\u30ed\u3044\u3001\u30c9\u30a8\u30ed\u3060\u3088\u306d\uff01
Posted by chamber11
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@nano_limpid %\u8077\u306f\u5927\u5909\u3063\u3059\u306d\uff57\uff57\uff57\uff57\uff57
Posted by Syana
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
Aku suka perkataan cacu tadi sore...
Posted by masagakenaldema
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@dildillaa dih pede geelaaahhh hahha
Posted by satriavisabrina
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------
@555hamako \u3042\u307c\u30fc\u3093\u3068\u30ad\u30dc\u30f3\u30cc\u304c\u3054\u3063\u3061\u3083\u306b\u306a\u3063\u3066\u307e\u3059\u306d\u3048w\u3002
Posted by florestan854
Posted on Sat Feb 27 11:55:17 +0000 2010
---------------------------------------------------------------

You may notice that some tweets seem to be made up of gibberish with \u all over the place. These are Unicode characters and usually represent non-Latin characters such as Chinese characters, which Twitter stores as Unicode.

There you have it, in this post you have created a basic Twitter public timeline reader using PL/SQL, the UTL_HTTP package and the PL/JSON library. It should be relatively straightforward to build on this foundation to create a more complete Twitter client using PL/SQL. For more information about the Twitter API see the official Twitter API wiki.

In the third and final part of this series on creating applications that interact with an IBM DB2 pureXML database, you will use the popular PHP language to create scripts for publishing and syndicating the status updates in your database across the Web.

First you will allow users to create a Profile Badge in HTML, which permits them to generate a piece of HTML code to paste into their blog or Web site. This will pull the latest status updates from the pureXML database and present them in a pre-defined format. Users will be able to customize the design of the badge, choosing from a selection of sizes and color schemes, and define the number of updates to be displayed. Next, you will create a PHP script to publish the latest status updates in the database as an RSS Feed. Finally, you will create a script that presents all of the status updates in the database to the users with a Post to Twitter button alongside each update. When a user presses this button, the Twitter API will take the specified post and add the update to the Twitter stream of that user.

The pureXML® capabilities of IBM DB2® allow you to store XML natively in a database without modification, while Adobe® Flex® applications can read XML directly and populate Flex user interfaces. In this three-part article series, you will create a microblogging application that takes advantage of pureXML, Web services, and Adobe Flex; and even allows you to publish your microblogging updates on Twitter. In Part 1 of the series, you learned about Web Services and how they are enabled using DB2 pureXML as you created the microblog database and tested it. Part 2 tapped into Adobe Flex and ActionScript to create the user interface of your application. In this article, the final part of the series, you will learn how to use your pureXML Web Services to publish your microblog entries to an HTML page.

Read the article at http://www.ibm.com/developerworks/xml/library/x-db2mblog3/

In Part 2 of my developerWorks article series on creating a microblogging service, I show you how to connect to the Web services created in Part 1 from a Flex application. This application allows you to post new updates to your microblog database and see a list of previous updates.

The pureXML® capabilities of IBM DB2® allow you to store XML natively in a database without modification, while Adobe® Flex® applications can read XML directly and populate Flex user interfaces. In this three-part article series, you will create a microblogging application that takes advantage of pureXML, Web services, and Adobe Flex; and even allows you to publish your microblogging updates on Twitter. In Part 1 of the series, you learned about Web Services and how they are enabled using DB2 pureXML as you created the microblog database and tested it. In this article, Part 2 of the series, you will tap into Adobe Flex and ActionScript® to create the user interface of the application.

Read the article at http://www.ibm.com/developerworks/xml/library/x-db2mblog2/

In the first part of this three-part series on IBM DB2 pureXML, I show you how to get started with pureXML on a DB2 Express-C 9.5 database. You learn how to create and insert XML data into a relational table, and how to query that data using SQL, SQL/XML and XQuery. Next, you learn how to expose this data to applications using Web Services in IBM Data Studio. You create a database procedure that will insert data into the database, and this is also exposed as a Web Service. In Parts 2 and 3 you will learn how to take all of this and harness it in your applications, first in an Adobe Flex application for posting status updates, and then publishing profile badges an RSS feeds using PHP. You will also learn how to push your updates to Twitter using the Twitter API.

The pureXML® capabilities of IBM DB2® allow you to store XML natively in a database without modification, while Adobe Flex applications can read XML directly and populate Flex user interfaces. In this three-part article series, you will create a microblogging application that takes advantage of pureXML, Web services, and Adobe Flex; and even allows you to publish your microblogging updates on Twitter.

Read the article at http://www.ibm.com/developerworks/xml/library/x-db2mblog1/index.html

Many people (myself included) often post an update to Twitter when publishing a new blog post. WordPress (the blog software I use) and most other blogging platforms include a nice feature that allows you to schedule blog posts to be published at a particular date/time. Wouldn’t it be great if we could also schedule our tweets so that they are posted when our blog post is published? There are countless other potential uses for scheduled tweets. Maybe you’re launching a new product and want to announce it on Twitter on Monday morning at 9am, but are afraid you’ll get caught up on something else – set an automated Tweet and away you go.

Before I begin, I’ll start off by saying that this is a very simple example of scheduling tweets. It will give you a very simple introduction to the Twitter API, the curl tool and the Windows Task Scheduler. There is no pretty graphical user interface, and configuring your scheduled tweets is fairly manual to say the least. If you would like more features, why not incorporate them into your own little application? I’m certainly thinking of doing so. Also – I believe there is a WordPress plugin that will post a tweet when your blog post is published – if this fits your needs, great! Again – this is merely a simple example of what you can do with the Twitter API, it’s not intended to be the holy grail solution for scheduling tweets!

For the sake of this tutorial I am going to assume you’re using the Windows operating system. If you’re using Mac OS, Linux or another Unix variant, the technique used in this tutorial could be easily amended to work with cron. If enough people demand a Mac/Linux version of this tutorial, I’ll write one.

The first thing you’re going to need to do is download cURL. This command-line utility allows you to transfer files over a host of internet protocols, and is perfect for interacting with the Twitter API. It is free and open source, and is available for a wide range of platforms. Visit the cURL download page and download the No-SSL version provided by Daniel Stenburg. At the time of writing, the latest version available was 7.19.4. Once you have downloaded the ZIP file, extract the contents to c:\curl. If you need an archiving utility, download the excellent 7-Zip. That’s all there is to installing cURL! To test it out, open a command prompt (Start->Programs->Accessories->Command Prompt) and change to the curl directory (cd \curl). Enter the following command:

curl http://www.google.ie

This should spit back a ream of continuous HTML code. This is the HTML source for the Google.ie home page. What you see should be similar to the screenshot below:

Figure 1 - cURL'ing Google.ie

Now that we have verified that cURL is working, let’s see how we can use it to interact with the Twitter API. The Twitter API is what is known as a RESTful web service, meaning that it can be queryed using a URI, and will return data in a particular MIME type. Twitter can return data in the XML, JSON, RSS or Atom MIME types. Let’s start working with Twitter by retrieving the Public Timeline in RSS format. In your command prompt, issue the following command:

curl http://twitter.com/statuses/public_timeline.rss

This should bring back the RSS feed for the Twitter Public Timeline, as seen in the following screenshot:

Figure 2 - cURL'ing the Twitter Public Timeline

Great – but we’re not very interested in the public timeline are we? Let’s tell Twitter who we are and ask for the timeline of only the people we are following. This time, we will ask for a response in the JSON (JavaScript Object Notation) format. Please be sure to substitute your own Twitter username and password for the values below. And no, my Twitter password is not “password”!

curl -u joelennon:password http://twitter.com/statuses/friend_timeline.json

In this example, we are using HTTP authentication to tell Twitter who we are, and asking it to return a JSON representation of our friends’ timeline. For a sample of the output, see Figure 3 below:

Figure 3 - cURL'ing our Friends Timeline

At this point, you may be wondering what use all of the mumbo-jumbo we are receiving as a response is to us. In fact, in this tutorial, it’s not much use to us at all as we are only interested in updating Twitter. If we wanted to display our own, our friends or public tweets however, we would be able to parse this information and display it in a readable format. The Twitter API provides an array of methods for pulling back information like this, including:

• public_timeline
• friends_timeline
• user_timeline
• show
• replies
• friends
• followers
• many more…

If you would like to delve deeper into these methods, and the Twitter API in general, check out the REST API Documentation on the Twitter API Wiki. It has a host of information and examples on how to use the Twitter API. Now let’s get back to the tutorial!

Up until this point, we have concentrated mainly on retrieving data from Twitter. But we want to send updates to Twitter! Luckily, cURL allows us to neatly send POST data along with our HTTP request. To post an update, issue the following command in your command prompt:

curl -u joelennon:password -d status="Testing out using cURL for Twitter updates" http://twitter.com/statuses/update.xml

As previously, you will receive a response in the format you specified in the request (in this case, XML). This should look similar to the screenshot below:

Figure 4 - Posting a Twitter Update

But, more importantly, if you check out your Twitter page, you will see that your timeline has been updated with a new post – you guessed it, the one we just sent. To change the content of the post, simply change the text between the quote symbols in Listing 4 above. I think  you’ll agree, using the Twitter API with curl is pretty easy! Now let’s take things a step further and create a script that will post the update for us, so that we don’t have to issue the long curl command every time we want to post.

Open a text editor (Notepad will do fine) and add the command from Listing 4 above to it. Feel free to change the status text to something else! Now go to File -> Save As and save it as “twitter.bat” in the C:\curl directory. Be sure to include the quotes when you are saving the file, otherwise Notepad will probably try to save it as .txt file, and you’ll end up with a file named twitter.bat.txt instead!

Now go back to your command prompt and ensure that you have changed in to the C:\curl directory. Enter the following command:

twitter.bat

Hey presto, your Twitter update has been posted! While this is nice and short, it’s still a bit of a pain as we have to modify the twitter.bat file everytime to change the status text. Let’s fix that. Re-open twitter.bat in Notepad, and change the contents to the following:

@ECHO OFF
SET STATUS=%*
c:\curl\curl.exe -u joelennon:password -d status="%STATUS%" http://twitter.com/statuses/update.xml

We have changed the batch file so that it does not display the command each time it runs, and it sets a variable, STATUS to all the arguments entered when the command is executed. We then use this STATUS variable in our curl command instead of static text. This allows us to enter the status text we wish to update Twitter with when we run our script. This time, try running the command in Listing 7 below:

twitter.bat Passing arguments to the batch file

Well would you look at that, it’s submitted the Twitter update using the text we specified after the twitter.bat command! Take a step back and look at what you’ve just created – a Twitter updater client! Sure it’s basic, but it works! Now that we have the script to post our Twitter updates created, let’s look at how we can schedule it to automatically post an update at a specified date and time.

A feature of the Windows operating system that is often overlooked is the Task Scheduler. This allows you to create scheduled tasks that will run on a certain date and time. This tool has a GUI interface and a command-line interface for scheduling tasks, but it can be quite complex. Instead, we are going to use the at command, which is included with any NT-based Windows platform (NT/2000/XP/Vista/Server). This is a very basic and simple to use command-line scheduler.

I am writing this particular paragraph at 2:26pm. The line below will automatically send a tweet at 2:27pm. Change the time to the 24-hour value for a time that’s a few minutes into the future for you (otherwise it will send the next time 2:27pm comes around, and you might have to wait a long time to test it worked!)

at 14:27 cmd /c c:\curl\twitter.bat This is a scheduled tweet!

The at command is quite simple, but it has some nice features that allow you to schedule tasks to run at set intervals – for example every Monday at 9.00am. For further information, see this Microsoft Knowledgebase article on the at command. Note in the above example that we preceded our twitter script file with the command “cmd /c”. This might not mean anything to you, but it is required in order for the script to work, as we are running a batch file and not a regular executable. This basically tells the scheduler to start a command window, run the specified command and close the command window when done.

Congratulations, you now have a way of automating your tweets so that they are posted at a set date/time or even at a regular interval. It’s not the prettiest solution in the world, but it’s simple and you have probably learned a bit about the Twitter API (and batch files and the scheduling tasks!) in the process. If you have any questions or need some help with this, feel free to leave a comment and I’ll do my best to help you out!