Create an alerts system using XMPP, SMS, pureXML, and PHP

Develop a Euro currency exchange rate application with automatic updates

Thanks to the native XML support that pureXML® offers IBM DB2® database developers, you can load XML data directly into your database, freeing up development time to add functionality to your application. Follow along in this tutorial to import an XML file with Euro foreign exchange rates into an IBM DB2 database and use special XQuery and SQL/XML functions to split this XML into separate database rows. You will also create a PHP script that pulls down new rates from the European Central Bank (ECB) Web site each day. Then you will extend the script to send update alerts to a Google Talk user using the XMPP protocol, and to a cell phone by SMS text message using the Clickatell SMS gateway service. Finally, you will create a PHP script that generates a PNG (Portable Network Graphics) graph of this data.

Joe Lennon, Software developer, Freelance

Photo of Joe LennonJoe Lennon is a 24-year-old software developer from Cork, Ireland. Joe is author of the forthcoming Apress book Beginning CouchDB, and has contributed several technical articles and tutorials to IBM developerWorks. In his spare time, Joe likes to play football (soccer), tinker with gadgets and work on his Xbox 360 gamer score.



24 November 2009

Also available in Chinese

Before you start

This tutorial is aimed at Web application developers who want to develop database-driven applications that are powered by an IBM DB2 pureXML database. The reader should be familiar with basic PHP code and be able to work with the Windows command prompt in order to follow the tutorial. Some experience with database management systems and the SQL language is also required. By the end of this tutorial, you will learn how to create object-oriented PHP Web applications that take advantage of a pureXML database.

About this tutorial

Frequently used acronyms

  • API: Application programming interface
  • HTTP: Hypertext Transfer Protocol
  • RFC: Request for comments
  • RPC: Remote procedure call
  • SMS: Short Message Service
  • SOAP: Simple Object Access Protocol
  • TCP: Transmission Control Protocol
  • XML: Extensible Markup Language
  • XMPP: Extensible Messaging and Presence Protocol

IBM DB2 Express-C includes pureXML, a way to store XML data natively in a relational database table. pureXML allows the user to query, index, and manipulate this XML data. You can use a series of functions to work with a hybrid of relational and XML-based data in a seamless fashion. This enables the development of applications that utilize the strengths of both traditional relational database tables and XML data.

XML is now widely used as a portable data storage format, and you will frequently find public data and Web services providing access to their data in XML format. By using IBM DB2 Express-C you can develop applications that load this XML data directly into the database, and create applications that are driven by this data without having to convert it into relational columns. Of course, sometimes it makes sense to take this data and convert it, and in this tutorial you will see a mix of various techniques for working with XML in DB2.

In this tutorial, you will learn how to create an alerts system that polls the Euro foreign exchange rates from the European Central Bank Web site on a daily basis. If a new set of rates are found, the application will load this data into a DB2 database, and it will also send an alert in two ways: as a Google Talk (XMPP) instant message, and as an SMS text message to a cell phone. You will also learn how to import bulk XML data into a DB2 database, and in this case, you will load the entire history of Euro foreign exchange rates, dating all the way back to 1999, when the Euro currency was first introduced. Later in the tutorial, you will develop a PHP script that generates a chart of the maximum exchange rates for each month of the current year.

Prerequisites

To follow the steps in this tutorial, you will need to install the following software:

  • IBM DB2 Express-C 9.5
  • PHP 5.2
  • XMPPHP library

See Resources for the links to the download sites and to articles that walk you through the installation and configuration of the above software.

Project folder

This tutorial assumes that you store all of the source code in the folder C:\currency. If you have not already done so, create this folder now by opening Windows® Explorer and navigating to the root of the C: drive. In there, right-click and choose New>Folder. Name the folder currency. Alternatively, download the source code for this tutorial and extract the folder to your C: drive.

Configuring PHP

You need to make some changes to your PHP configuration file to follow this tutorial. In php.ini locate the line that reads something like error_reporting = E_ALL and change it to: error_reporting = E_ERROR.

Next, find the line similar to: display_errors = On and change it to read: display_errors = Off. Finally, scroll down the file until you find the section for PHP extensions. Many of these extensions will be disabled by default, with a semi-colon at the start of the line commenting out this particular feature. To enable features, simply remove the semi-colon at the start of the line. Now, find these lines in Listing 1 and make sure the semi-colon (;) at the beginning of each is removed.

Listing 1. Removing the semi-colons
extension = php_curl.dll
extension = php_gd2.dll
extension = php_mbstring.dll
extension = php_openssl.dll
extension = php_sockets.dll

Setting up the pieces

In this section, you'll download the XMPPHP library and set up the Google Talk and the Clickatell SMS gateway account.

The XMPPHP library

To communicate with the Google Talk instant messaging service, you will need to download the XMPPHP library, which will allow you to send messages to Google Talk users using PHP code. This file is distributed as a .tar.gz archive, so you will need an archiving utility that supports this file format, such as 7-Zip. Extract the contents of the archive to your desktop, and then copy the XMPPHP folder and paste it in your project folder at C:\currency. This library is included in the source code for this tutorial (see download), so if you use these files you do not need to download XMPPHP separately.

Google Talk accounts

To send Google Talk messages, you will need two Google Talk accounts, one for the script sending the message and another for the recipient receiving the message. If you have a Gmail account, you already have a Google Talk account. If not, see Resources for a link so you can sign up.

In addition to registering for two Google Talk accounts, you need to login to one of the accounts and add the other account as a contact. You can do this through Gmail or using the Google Talk client. You then need to log out of that account and log in to the other account where you will be asked to accept the chat invitation you just sent. Accept it, and the two accounts can now communicate with each other.

Clickatell SMS gateway account

To send SMS text message alerts, you need an account with the Clickatell SMS gateway service. In this section, you learn how to create and configure your Clickatell account so that you can later send text messages to your cell phone when you develop the alerts script.

Go to the Buy Now page on Clickatell's Web site (see Resources for a link). From here, you need to create a new Clickatell account. From the New Customers section, open the Select Product drop-down list and select Clickatell Central (API). This will take you to the Clickatell registration form, as in the Figure 1.

Figure 1. Clickatell registration form
Screen capture of Clickatell registration form to select product, set up your account, and enter personal information

In Step 1 of 4, the Clickatell Central product should be already selected. In Step 2 of 4 select the type of coverage that you want for your account. If you're unsure, choose the International option. Enter a username and password, and fill in the security code as shown in the image. Under Step 3 of 4, enter all fields marked with an asterisk (*). Checking Personal Use Only will hide company-related fields. Finally, check the I accept Clickatell's Terms and Conditions box and press the Continue button.

The final step is to activate your account and verify your identity. Clickatell will send an e-mail to the address you supplied on the previous page (see Figure 1) with a link that will activate your account. Click this link to view your to the My Account Login page, with your product, username, and client ID pre-populated, as in Figure 2.

Figure 2. Clickatell My Account Login
Screen capture of Clickatell My Account Login populated with your product, username, and client ID

Enter your password and click Login to log in to your account. You should now see a screen like the one in Figure 3.

Figure 3. Clickatell Central Home Page
Screen capture of Clickatell Central Home Page test message box and mobile number verification

In the Verify your Mobile Number box, check that the mobile number is correct and click the button Send Activation Code to send an SMS text message to your cell phone with a code to be used for activation. When you receive the message, enter the code into the box and press Verify Now. The next step is to register for the HTTP/S API. Click on the Manage My Products link in the top navigation section and, from the My Connections drop-down list, select HTTP. You should now see a screen like the one in Figure 4.

Figure 4. Clickatell HTTP API form
Screen capture of Clickatell HTTP API form

Enter Currency Alerts in the Name field and leave the rest of the fields as they are. (In Figure 4, the value in the Dial Prefix field is Ireland (353) and value in the Callback Type field is HTTPGET. Other fields are blank.) Now press Submit to set up the API. You will now see a confirmation screen, so press OK here to go back to the Manage my Products screen. You should now see the Currency Alerts connection, and your API ID listed alongside it, as shown in Figure 5. (Note: In the API ID field in the screen capture below, I intentionally blanked out my API ID value so you wouldn't try to use it—you will see your API ID when you submit your request.)

Figure 5. Clickatell HTTP API ID
Screen capture of Clickatell HTTP API ID

Now, test that the API works by sending a test message. You will need the following details to do so:

  • Your Clickatell username
  • Your Clickatell password
  • Your Clickatell HTTP API ID
  • The number you want to send the message to in international format, without the + symbol (for example, 353875555555)
  • The text you want to send (note that if you are using the free test credits, your message will be replaced by a Clickatell standard message. This does not occur with messages sent with paid-for credits)

Open up a new browser window and enter the following URL in the address bar (replacing the values with your own). For formatting purposes, the URL appears on multiple lines. To use, the URL is a single string of characters.

http://api.clickatell.com/http/sendmsg?user=xxxx&password=xxxxx
&api_id=xxxxxx&to=xxxxxx&text=xxxxxx

For the SMS alerts in the sample application you build in this tutorial to work, you will need to purchase some credits on the Clickatell Web site. If you don't want to do this, you can simply comment out the line that sends text messages. I will explain this in more detail at that point of the tutorial.

With all of the application's prerequisites installed and configured, you are now ready to create the database and build the foundation for the alerts application.


Building the foundation

In this section, you will learn how to fetch the XML file containing the entire exchange rate history for the Euro currency from the European Central Bank (ECB) Web site, and load this into an IBM DB2 database. You will then use the XMLTABLE function to split the large XML document into separate documents that will each be stored in its own row in the database, making it easier to query and improving query performance. Finally, you will create a database view that allows you to use normal SQL queries to retrieve data, whether it be stored in a relational column or as an attribute in an XML document.

Downloading the historic rates from ECB

Although the source code for this tutorial includes an XML file with the historic rates, it will be out of date by the time you read this. As a result, it makes sense to download the latest file from the ECB site directly (see Resources for a link). You can safely overwrite the file in the source code folder.

Right-click the link and choose Save Target As or Save Link As to download the file to your computer. Make sure to save it in the project folder at C:\currency. The file size was approximately 3 megabytes at the time of writing, although this will grow over time as more rates are added each and every business day.

Before you can import this XML file into a DB2 database, you need to create a comma-separated .del file that tells the database how to import the data. Open your favorite text editor and enter the following line in a new file: 1,"<XDS FIL='eurofxref-hist.xml'/>".

Save this file as currency.del in your project folder. If you use Notepad as your text editor, be sure to wrap the filename in double-quotes in the save dialog (as "currency.del") to prevent Notepad automatically appending the .txt extension to the filename.

Creating the currency database

The next step is to create a new DB2 database. To do this, open the DB2 Command Editor (Start>Programs>IBM DB2>[DBNAME]>Command Line Tools>Command Editor) and issue the following command: create database currency using codeset UTF-8 territory US.

Next, connect to this newly created database: connect to currency.

Before you can import the data into the database, you need to create a table to store the data in. This table will not be used by the application, it's merely an intermediary table that you load the XML document into. Issue the following command in Listing 2 to create the table.

Listing 2. Creating a table to import the XML file into
create table temp_rates (
    id int primary key not null,
    data xml
)

Importing the XML file into the database

Next, you will import the XML document into the database using the currency.del file you created earlier. Listing 3 will read the contents of this file, placing the value before the comma (1 in this case) into the id column of the temp_rates table. The <XDS> tag tells DB2 where to read the XML data file from and it will store this in the data column.

Listing 3. Importing XML into DB2
import from "C:\currency\currency.del" of del
    xml from "C:\currency"
insert into temp_rates;

If all goes according to plan, you should see a successful response similar to: SQL3149N "1" rows were processed from the input file. "1" rows were successfully inserted into the table. "0" rows were rejected.

It's worth verifying that the data was inserted as expected by executing the following SQL statement: select * from temp_rates. This should automatically switch to the Query Results tab, where you should see a single row with the ID 1 and the data column indicating that it contains XML content (see Figure 6).

Figure 6. Query results
Screen capture of Query results tab of the database

Under the data column you will see a button with three dots (...). Click on this button to open the XML Document Viewer window. In this window you should be able to browse through the XML representation of the exchange rate data you just imported (see Figure 7).

Figure 7. XML Document Viewer
Screen capture of Tree View tab and Attribute View in the XML Document Viewer

Having verified that the XML data is correctly imported into the database, you are now ready to process this data and split it so that it is stored in multiple rows. This will make it much easier (and faster) to query this data in your applications.

Splitting the data into separate database rows

Storing the entire document in a single row makes it difficult to query, and the entire XML document is rewritten every time you add data, which is far from ideal. It would be much nicer if you had a separate row for each date that the rates apply for. This makes querying the data easier and faster, and also allows new data to be inserted easily as a new row.

The first step in splitting this data is to create a new table which will hold the rates for the application (see Listing 4).

Listing 4. Creating a table to split the XML data into
create table rates (
    date date primary key not null,
    rates xml
)

Now, you need to split the large XML document into separate rows on this rates table. The command in Listing 5 uses the XMLTABLE function to do just that.

Listing 5. Splitting the data into separate rows
insert into rates(date, rates)
select x.date, x.rates
from temp_rates tr,
    xmltable('$d/*:Envelope/*:Cube/*:Cube'
    passing cast(tr.data as XML) as "d"
    columns
        date date path '@time',
        rates xml path 'document{.}'
    ) as x

This statement uses the XMLTABLE function to map the time attribute of each relevant <Cube> tag as the date for the rates in question, with that element and its child nodes stored in the rates XML field. To validate that this process worked, issue the following command: select * from rates.

This time the result should be much more attractive, as in Figure 8. Each date has its own row and its own XML document.

Figure 8. Results split over numerous rows
Screen capture of Query Results tab with results split over numerous rows

Click the more (...) button to show just the XML rates data for that particular date (see Figure 9).

Figure 9. XML data for a specific date
Screen capture of Tree View tab and Attribute View of XML data for a specific date

When you return to the Query Results tab, you mighty notice that only 100 rows are shown. Didn't you import data going back as far as 1999? Surely there should be over 2,000 rows if that were the case? Don't panic. The DB2 Command Editor only fetches 100 rows at a time by default. To fetch another 100 rows, click the Fetch More Rows button. If you want to check how many rows are in the table, use the following command: select count(*) from rates.

When I ran this command, there were 2,783 rows. For each business day that passes between the time of writing and the date you read this tutorial, one additional row should be added to the table.

Creating a relational view of XML data

The final database configuration step is to create a relational view of the XML data, which will make it much easier for applications to query the currency data. By creating this view, you will be able to use typical SQL queries to filter, order, and group results using aggregate functions, WHERE clauses, ORDER BY clauses, and GROUP BY clauses (see Listing 6).

Listing 6. SQL statement to create rates_view
create view rates_view(seq_no, date, currency, rate, doc)
as select x.seqno, r.date, x.currency, x.rate, x.doc
from rates r,
    xmltable('$d/*:Cube/*:Cube' passing r.rates as "d"
    columns
        seqno for ordinality,
        currency varchar(5) path '@currency',
        rate varchar(20) path '@rate',
        doc xml path 'document{.}'
    ) as x

Let's finish up this section by using some simple queries to verify that the view is working as expected. First, query for rates for a specific date: select * from rates_view where date = '2009-11-13'.

You should see 33 results in the Query Results tab, as in Figure 10.

Figure 10. Query Results for filtering rates by date
Screen capture of Query Results filtering rates by the date, 2009-11-13

It's worth noting at this point that there are no foreign exchange rate fluctuations on Saturdays and Sundays so if you are not getting any results when filtering by date, make sure that the date you are filtering by is not a Saturday or Sunday!

Next, query for only USD (United States Dollar) rates: select * from rates_view where currency = 'USD'.

You should now see an entry for USD for each business day. Although Command Editor only shows 100 rows at a time, there should be rows going back as far as January, 1999. Finally, let's execute a query that filters based on both date and currency: select * from rates_view where currency = 'USD' and date = '1999-12-30'.

This time, you should see a single row in the result set. As you can see, the Euro and US dollar were virtually in parity with one another at this time.

That concludes the database configuration. In the next section of this tutorial, you will create a script that fetches the rates for the current date and inserts a new row into the rates table if one does not already exist for the date in question. This script will then be expanded to send alerts using XMPP instant messages and SMS text messages.


Creating the update PHP script

In this section of the tutorial you will develop the heart of the sample application. This centers on a PHP script that will perform three primary functions:

  • Fetch today's rates from the ECB Web site (see Resources) and, if they have not already been added, insert them into the rates table in the currency database
  • Send a message to a Google Talk user using the XMPP protocol and the XMPPHP library
  • Send a message to a cell phone using the Clickatell SMS gateway service

You might be familiar with PHP programming as a server-side Web application language, but in this tutorial you will call the PHP scripts you create from the Windows command prompt and not through a Web browser.

To follow this section, your PHP installation folder will need to be on your Path. See Resources for a link to get more information.

Before you create the update.php script, you need to create a couple of helper PHP classes.

Creating helper PHP classes

All of the PHP scripts and classes you will create should be stored in the C:\currency folder. In this section, you will create two PHP classes: DB and GTalk, stored in the files db.php and gtalk.php, respectively. The DB class contains all the logic for connecting to your DB2 database, whereas the GTalk class deals with communicating with the Google Talk XMPP server.

First create the DB class, which should be saved as C:\currency\db.php (see Listing 7).

Listing 7. db.php
<?php
class DB {
    private $conn;

    function __construct() {
        $database = "currency";
        $hostname = "localhost";
        $port = 50000;
        $user = "USERNAME";
        $password = "PASSWORD";

        $db_connect_string = "DRIVER={IBM DB2 ODBC DRIVER};"
            . "DATABASE=$database;"
            . "HOSTNAME=$hostname;PORT=$port;PROTOCOL=TCPIP;"
            . "UID=$user;PWD=$password;";

        $this->conn = db2_connect($db_connect_string, '', '')

        if(!$this->conn) {
            die(db2_conn_errormsg($this->conn));
        }
    }

    function query($sql) {
        $result = db2_exec($this->conn, $sql);
        if(!$result) {
            die(db2_stmt_errormsg());
        } else {
            return $result;
        }
    }

    function get_row($result) {
        return db2_fetch_array($result)
    }
}
?>

Be sure to change the values of the $user and $password variables to match your own settings. This class defines a constructor, which connects to the DB2 currency database; a query function to execute SQL queries and a get_row function to retrieve a result row from a DB2 result set.

Next, create the GTalk class, and save it as C:\currency\gtalk.php. The code for this class is in Listing 8.

Listing 8. gtalk.php
<?php
require_once("XMPPHP/XMPP.php");
class GTalk {
    private $conn;

    function __construct() {
        $this->conn = new XMPPHP_XMPP('talk.google.com', 5222,
            'USERNAME', 'PASSWORD', 'xmpphp', 'gmail.com', 
            $printLog=false, $loglevel=0);
    }

    function connect() {
        try {
            $this->conn->connect();
            $this->conn->processUntil('session_start');
        } catch(XMPPHP_Exception $e) {
            die($e->getMessage());
        }
    }

    function disconnect() {
        try {
            $this->conn->disconnect();
        } catch(XMPPHP_Exception $e) {
            die($e->getMessage());
        }
    }

    function send_message($to, $msg) {
        $this->connect();
        try {
            $this->conn->message($to, $msg);
        } catch(XMPPHP_Exception $e) {
            die($e->getMessage());
        }
        $this->disconnect();
    }
}
?>

Again, you replace the USERNAME and PASSWORD values with your own account settings. With the PHP helper classes created, you can now create a script that will pull today's rates from the ECB Web site and insert them as a row in your rates table.

Updating the rates table with today's rates

You will now create a script that uses the curl extension to fetch the daily XML feed of Euro foreign exchange rates from the ECB Web site. You will use the SimpleXML features of PHP to filter out the sections of the XML file you require to insert the date and rates columns of the rates table. Before the rates are actually inserted into the database, the script will check that the rates are not already inserted to prevent duplicate entries from being stored.

Create a new file named update.php and save it in the C:\currency folder. The contents of this file are in Listing 9.

Listing 9. update.php
<?php
require_once("db.php");

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "http://www.ecb.europa.eu/stats
   /eurofxref/eurofxref-daily.xml");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$xml = curl_exec($ch);
curl_close($ch);

$doc = new SimpleXmlElement($xml, LIBXML_NOCDATA);
$today = $doc->Cube->Cube;
$todayAttr = $today->attributes();
$todayDate = $todayAttr['time'];
$rates = str_replace(array("\n", "\r", "\t"), '', $today->asXML());

$db = new DB;
$sql = "SELECT count(*) from rates "
    ."WHERE date = DATE('".$todayDate."')";
$result = $db->query($sql);
$row = $db->get_row($result);

if($row[0] == 0) {
    $insert_sql = "INSERT INTO rates(date, rates) "
        ."VALUES('".$todayDate."', '".$rates."')";
    $insert_result = $db->query($insert_sql);

    if(!$insert_result) {
        echo "An error occurred while adding rates to the "
            ."database.\n".db2_stmt_errormsg();
    }
} else {
    echo "There are already rates stored for today (or it is the "
        ."weekend!)";
}
?>

This script first retrieves the XML document from the ECB Web site for today's Euro foreign exchange rates. It then uses SimpleXML functions to parse this document as XML, picking out the time attribute to find the date that the document applies to and removing unnecessary XML nodes to leave the XML that is to be inserted into the rates table.

Next, the script connects to the database by creating an instance of the DB class and issuing a query which checks if there are any rows on the table for the date extracted from today's XML file. If the script finds no rows, it continues to insert a new row into the table, otherwise it prints an error saying that either the rates were already imported today or else the current date is a weekend day, when no new rates are available.

To execute the script, open up the Windows command prompt (Start>Programs>Accessories>Command Prompt). Issue the commands in Listing 10 to change to the C:\currency directory (you only need the second command if you are already on the C: drive).

Listing 10. Changing the directory
c:
cd \currency

To execute the update script, enter the following command: php update.php.

Assuming that the PHP installation folder is on your system's Path, this script should run. If there is a problem with your path, you might see a message saying that php is not a recognized command. If this happens, you need to place the PHP folder on your path (see Resources for details on how to do this).

If you downloaded the rates from the ECB Web site when you were loading historic data into the database at the beginning of this tutorial, you will probably see a message like the one in Figure 11.

Figure 11. Already rates for that date error
Error message that rates are already stored for a date (or it is a weekend) in Command prompt window

This basically means that the currency data for today has already been loaded into the database, so there's no need to insert a new row. I'm sure you want to test that the script is working, however, so back in DB2 Command Editor, enter the following statement to remove the latest entry from the database: delete from rates where date = (select max(date) from rates).

If you like, check for the newest date in the table to make sure that today's entry was deleted. Now, head back to your Windows command prompt window and re-enter that earlier command: php update.php.

This time around, your script should run and no messages should appear. Back in DB2 Command Editor, use the following statement to verify that the new set of rates were successfully inserted: select * from rates where date = (select max(date) from rates).

You should see a row replacing the row you deleted a few moments ago. Click the more (...) button in the rates column to verify that the XML was successfully imported from the ECB data file, as in Figure 12.

Figure 12. Latest rate imported from ECB file
Screen capture of Tree View tab and Attribute View that shows the latest rate imported from ECB file

Tip: There's a quick way to determine whether the data you are looking at was imported from the historic ECB file or the daily file. When importing using DB2, all namespace-related attributes are tagged on to the parent <Cube> node, such as xmlns and xmlns:gesmes. When you add the row using the PHP script, these namespaces are not present.

Next, you will expand the script to send an update to a Google Talk account using the XMPP protocol.

Sending messages to a Google Talk user through the XMPP protocol

In its present form, the sample application doesn't really do too much other than store a bunch of currency exchange rate data. Let's add some neat functionality to it by expanding the update script to send the latest Euro to US dollar exchange rate by instant message to a Google Talk account.

Google Talk is an example of an XMPP application. XMPP is a set of open XML technologies for real-time communication. Google Talk is a typical instant messaging platform, just like MSN Messenger, AIM, Yahoo Messenger, or ICQ. Thanks to the open source XMPPHP library, it is very easy to communicate with an XMPP service using PHP.

In this section you will need to modify the update.php script. The first change is that you need to import the GTalk class. Below the line require_once("db.php"); add the following line: require_once("gtalk.php");.

The next change you need to make is to add an else clause to the if block that checks if the $insert_result variable is false. This block should now look like Listing 11.

Listing 11. Updated if block in update.php
if(!$insert_result) {
    echo "An error occurred while adding rates to the "
        ."database.\n".db2_stmt_errormsg();
} else {
    $usd_sql = "SELECT rate FROM rates_view "
        ."WHERE date = '".$todayDate."' and currency = 'USD'";
    $usd_result = $db->query($usd_sql);
    $usd_row = $db->get_row($usd_result);
    $message = "[$todayDate] 1 Euro = ".$usd_row[0]." USD";

    $gtalk = new GTalk;
    $gtalk->send_message('user@gmail.com', $message);
}

Be sure to change the recipient (user@gmail.com above) to the Google Talk account you want to send the message to. If you try to run the update.php script again in the Command Prompt, you're likely to once again get the error that there are already rates for that date. Back in DB2 Command Editor, use the following statement (again!) to remove the latest entry: delete from rates where date = (select max(date) from rates).

Make sure that you are logged in to Google Talk or Gmail (if you're not, it will send you an e-mail with the message instead). Now go back to the Command Prompt window and enter the following command again: php update.php.

This time around you shouldn't get any errors, but rather a Google Talk chat session will open, as in Figure 13.

Figure 13. The update sent to Google Talk
Screen capture of a chat session that opens when the update sent to Google Talk

Pretty neat, huh? Next, you'll take this concept one step further and actually send an alert to a cell phone through SMS text message.

Sending SMS messages using the Clickatell SMS gateway

Earlier you learned how to set up an account with the Clickatell SMS gateway, a service that allows you to send SMS text messages to cell phones from your applications. The Clickatell HTTP/S API makes it really simple to send text messages, so jump right in and modify your script to do so!

Again, you need to edit the update.php file. This time, add the code directly beneath the code that sends the Google Talk message inside the else block from Listing 11. Listing 12 shows the code you need to add .

Listing 12. sending text messages in update.php
$sms_user = "USERNAME";
$sms_pass = "PASSWORD";    
$sms_api_id = "0000000";
$sms_to = "353875555555";
$sms_msg = urlencode($message);

$ch_sms = curl_init();
curl_setopt($ch_sms, CURLOPT_URL,
    "http://api.clickatell.com/http/sendmsg"
    ."?user=$sms_user&password=$sms_pass&api_id=$sms_api_id"
    ."&to=$sms_to&text=$sms_msg");
curl_exec($ch_sms);
curl_close($ch_sms);

Believe it or not, that's all the code required to send a text message using PHP and the curl extension! Again, be sure to replace the placeholder variable values with your actual settings. You'll find the API ID in your Clickatell account settings. Before you test the script, once again go ahead and delete the latest rate in DB2 Command Editor: delete from rates where date = (select max(date) from rates).

Back in the Windows Command Prompt, re-run the update script: php update.php.

This time, you will see a response from the Clickatell API with the ID of the message that was sent, as in Figure 14.

Figure 14. Response from the update.php script
Screen capture of command prompt window with response from the update.php script and message ID

More importantly, however, you should also receive an SMS text message on your cell phone, such as the message in Figure 15.

Figure 15. The SMS update viewed on a cell phone
Screen capture of the SMS update viewed on a cell phone with date, exchange rate, and sending phone

This only really scratches the surface of the capabilities of using the Clickatell API. Be sure to check out the Help section of the Clickatell Web site to find out more of the great things you can do with their gateway (see Resources for a link). Next, you'll schedule the script to run once a day, which will keep the foreign exchange rates up-to-date as time goes on automatically, sending you messages each business day with the day's Euro to USD exchange rate.

Scheduling the script to run automatically each day

The final piece of the puzzle for the currency update script is to schedule it to run each and every day. One of the lesser-known commands that can be used in Windows is the schtasks command. This relatively powerful tool allows you to schedule the execution of batch scripts and applications at a specified interval.

The first step in putting the script on the scheduler is to create a batch script that will execute it. Create a new file in your favorite text editor and add the following line to it: php update.php

Save this file as update.bat in the C:\currency folder. Now, enter the following command at the command prompt to add this batch file to the scheduler, requesting that it is run every day at 8 a.m. (Note that this command is all on one line): schtasks /create /tn "Currency Update" /tr c:\currency\update.bat /sc daily /st 08:00:00.

You might be prompted for your Windows password to confirm the task. When the task is created, you should see a message like the one in Figure 16.

Figure 16. Scheduled Task successfully created
Screen capture of command prompt response to a successfully created Scheduled Task

You can check what tasks are currently scheduled using the following command: schtasks.

If you want to actually go ahead and give your scheduled task a test run, you can use the following command: schtasks /run /tn "Currency Update".

That's it, the script should now run at 8 a.m. every day. Don't worry about Saturdays and Sundays (when the ECB file is not updated). The script will just detect that no new rates are available and it will not update or send alerts. Of course, it's worth mentioning that your computer needs to be switched on and logged in under the account that created the scheduled task in order for it to be executed. For the sake of ensuring that the update is performed every day, it might make more sense to run the script more frequently than once a day. You can find some links to useful resources on schtasks in the Resources of this tutorial.

The script you created in this section is relatively powerful given its fairly small number of lines of code. It polls the daily ECB exchange rates XML file for new rates, and, if it finds them, it first adds a new row to the currency DB2 database. Next, it sends a message to a Google Talk user with today's Euro-to-US dollar rate. Finally, it sends the same message to a cell phone through a SMS text message. All of this is done automatically each day thanks to the schtasks Windows command. In the next section of this tutorial, you will see how to use the GD2 image PHP library to generate a graph plotting some of the historic data in the currency database.


Graphically representing the currency data

Earlier in this tutorial, you loaded a large amount of historic currency data into the currency DB2 database. In this section, you'll see how to graphically represent this information by plotting it on a graph using the PHP GD2 image library. You will create a PHP script that can be run on the command line that retrieves the maximum Euro to US dollar exchange rate for each month of the current year and plots it on a graph. The graph is created as a PNG image and saved as graph.png in the project folder (C:\currency).

Creating the graph script

Let's get started with the graph script. In your text editor, create a new file and save it as graph.php in the C:\currency folder. The first thing that this script needs to do is connect to DB2 and pull down the data that is to be plotted on the graph. Add the code from Listing 13 to the script to do just that.

Listing 13. Retrieving max rates, grouped by month for current year
<?php
require_once("db.php");

$db = new DB;

$sql = "select MAX(rate), month(date), monthname(date), year(date) "     
."from rates_view where currency = 'USD' "
    ."and year(date) = year(current timestamp) "
    ."group by year(date),month(date),monthname(date),year(date) "
    ."order by month(date)";

$result = $db->query($sql);

$months = array();
$year = 0;
while($row = $db->get_row($result)) {
    $key = substr($row[2],0,3);
    $value = $row[0];
    $months[$key] = $value;
    $year = $row[3];
}
?>

This code retrieves the grouped monthly data from the currency DB2 database and adds it to a PHP array named $months. Now, modify it to plot this data on a graph. Add the code in Listing 14 just before the ?> in Listing 13.

Listing 14. Generating a bar chart in PHP
$width = 500;
$height = 400;
$hmargin = 30;
$vmargin = 30;

$chart_width = $width - ($hmargin * 2);
$chart_height = $height - ($vmargin * 2); 

$image = imagecreate($width, $height);

$month_width = 30;
$total_months = count($months);
$diff = ($chart_width - ($total_months * $month_width)) / 
    ($total_months +1);

$month_color = imagecolorallocate($image, 51, 102, 51);
$bg_color = imagecolorallocate($image, 235, 245, 235);
$text_color = imagecolorallocate($image, 0, 0, 0);
$out_bg_color = imagecolorallocate($image, 188, 222, 188);
$line_color = imagecolorallocate($image, 186, 220, 186);

imagefilledrectangle($image, 1, 1, $width - 2, $height - 2, 
    $out_bg_color);
imagefilledrectangle($image, $hmargin, $vmargin, $width - 1 - 
    $hmargin, $height - 1 - $vmargin, $bg_color);

$top = max($months)+(max($months)/10);
$gap = $chart_height / $top;

$hlines = 20;
$hgap = $chart_height / $hlines;

for($i=0;$i<=$hlines;$i++){
    $y = $height - $hmargin - $hgap * $i ;
    imageline($image, $hmargin, $y, $width - $hmargin, $y, 
        $line_color);
    $v = round(floatval($hgap * $i / $gap), 2);
    imagestring($image, 0, 5, $y-5, number_format($v, 2, '.', ','), 
        $text_color);
}

for($i=0;$i< $total_months; $i++){ 
    list($key,$value)=each($months); 
    $x1 = $vmargin + $diff + $i * ($diff+$month_width);
    $x2 = $x1 + $month_width; 
    $y1 = $vmargin + $chart_height - intval($value * $gap);
    $y2 = $height - $vmargin;
    imagestring($image, 0, 170, 10, "Max Monthly Values for Year "
        .$year, $text_color);
    imagestring($image, 0, $x1, $y1-10, $value, $text_color);
    imagestring($image, 0, $x1+7, $height-25, $key, $text_color);   
 imagefilledrectangle($image, $x1, $y1, $x2, $y2, $month_color);
}

imagepng($image, "graph.png");

This code uses the GD2 image library to generate a new image, 500 pixels wide and 400 pixels in height. In this image, it plots a series of bars, one for each month of the present year, with the y-axis showing the maximum Euro-to-dollar rate for the month in question.

Running the script and viewing the chart

In the Command Prompt window, execute the graph.php script with the following command: php graph.php.

This will create a new file in the C:\currency folder named graph.png. If you open this image file, it should look something like the image in Figure 17.

Figure 17. Result of the graph script
Screen capture of the result of the graph script: A bar graph of maximum rates per month in 2009

In this section, you learned how to plot historic data on a bar graph using the GD2 image library. You can create a wide range of graphs and images using this library. Of course, if you're looking for a more flexible option, you might look at some more powerful Flash or JavaScript charting libraries that allow you to create interactive and animated charts and graphs. But the GD2 library allows you to create visually impressive images without downloading external libraries or class files.

Suggested improvements

Although the sample application in this tutorial does quite a lot, you can easily expand it to perform a wide range of quite useful functions, such as:

  • Allow users to register for alerts (send messages to multiple users)
  • Allow users to select which currencies to receive alerts for
  • Allow users to define when to receive alerts
  • Create a Google Talk bot that allows users to request the current exchange rate for a particular currency by sending a message to the bot
  • Implement two-way SMS features that allow users to send a text message to a specified number, and they will receive an SMS in return with the exchange rate for the specified currency
  • Use a premium exchange rate data service to include foreign exchange data for currencies other than the Euro
  • Allow the user to select the currency for which they want to plot the exchange rate data on a graph
  • Create more graphs (pie charts, line graphs plotting historic currency data)
  • Create a search engine that allows users to find out what the exchange rates were on a particular date, or what date was a rate at its highest or lowest and so on
  • Create RSS feeds with the currency updates

Summary

In this tutorial you learned how to create an IBM DB2 database that stores Euro foreign exchange currency data in native XML format. You learned how to import a large XML document into the database using DB2's IMPORT command. You then split this data into separate rows using SQL/XML. Next, you created a script that polls the ECB daily rate XML file for new rates, before adding these rates in a new row in the database. Next, you expanded this script to send messages to a Google Talk user, and then to a cell phone through an SMS text message. Finally, you learned how to use the PHP GD2 image library to create a script that generates a bar chart of historic currency data.

This tutorial highlights the power of pureXML and how storing XML data natively in the database makes it easier to develop applications that rely on external XML data sources. With the sample application you built, you should have a strong base from which to develop a full-featured and highly useful alerts application.


Download

DescriptionNameSize
Alerts application source codecurrency.zip309KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into XML on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=XML, Information Management
ArticleID=449032
ArticleTitle=Create an alerts system using XMPP, SMS, pureXML, and PHP
publish-date=11242009