Call RESTful web services from MySQL

Hi,
I’m back after a long time without any post. Sorry for that, I was busy but I promise I will take time to write new posts and share precious things with you.
Recently, I went through a feature request : calling a RESTful web service from MySQL, using SQL. Why ? There are several reasons to do that, including :

• calling a geolocation web service in order to enrich some PII data,
• calling a data validation web service (bank, accounting, social, etc …),
• calling a SMS or EMAIL broker in order to send messages to people, according you have their PII in database. In that scenario, you could use a trigger that fire the RESTful call each time you have an incoming data (ie, a contact) into a given table,
• send a tweet …

After a quick review on internet, I discovered mysql-udf-http, which is a MySQL User Defined function for HTTP REST. You can find the package here on google code.
This UDF allows you to call any RESTful web service from SQL with HTTP methods : POST, GET, PUT, DELETE.
The process to use it is very simple, as you can see from the google code site.

1 – Install on Linux of course. Maybe you will have to change path below according to your setup.

ulimit -SHn 65535
wget http://curl.haxx.se/download/curl-7.21.1.tar.gz tar zxvf curl-7.21.1.tar.gz
cd curl-7.21.1/
./configure --prefix=/usr
make && make install
cd ../
echo "/usr/local/webserver/mysql/lib/mysql/" > /etc/ld.so.conf.d/mysql.conf
/sbin/ldconfig
wget http://mysql-udf-http.googlecode.com/files/mysql-udf-http-1.0.tar.gz tar zxvf mysql-udf-http-1.0.tar.gz
cd mysql-udf-http-1.0/
./configure --prefix=/usr/local/webserver/mysql --with-mysql=/usr/local/webserver/mysql/bin/mysql_config
make && make install
cd ../

2 – Create the UDF in MySQL.

create function http_get returns string soname 'mysql-udf-http.so';
create function http_post returns string soname 'mysql-udf-http.so';
create function http_put returns string soname 'mysql-udf-http.so';
create function http_delete returns string soname 'mysql-udf-http.so';

3 – Use it ! This is very simple.

SELECT http_get('<url>');
SELECT http_post('<url>', '<data>');
SELECT http_put('<url>', '<data>');
SELECT http_delete('<url>');

4 – Another example, using a trigger.

Here, the UDF is triggered each time a new row is added in the database and sends an email to the PII found in the row, given this row complies with some business rules.

CREATE TRIGGER `restful_trigger` BEFORE INSERT ON `customers` FOR EACH ROW BEGIN

DECLARE rtn_value varchar(1000) default ''; 

DECLARE dest_email varchar(100) default ''; 

SET @dest_email = NEW.EMAIL; 

SET @firstname = NEW.firstname; 

SET @message = 'Your message or your html template goes here'; 

-- Some business transformation rules here ...

CASE 

       WHEN NEW.xxx = 'xxx' THEN SET xxx = 'yyy';

-- etc ...

END CASE; 

-- Other business rules here to trigger or not the RESTful call ...

IF bla bla bla AND bla bla bla THEN

SELECT http_get(CONCAT ('http://REST_url_from_the_email_provider&NAME=',@firstname,'&MSG=',@message)) 

INTO @rtn_value; 

END IF;

-- Some rtn_value management code here ...

END;

Happy RESTful interactions with your MySQL … and don’t forget to have a visit on the UDF google code site for more infos and giving credits to this precious piece of software.