Friday, 16 November 2012

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.

12 comments:

Anonymous said...

I'd like to thank you for the efforts you've put in
writing this blοg. I reallу hope to see the same high-gгade content bу уou in
the futurе as well. In truth, yοuг crеаtivе writing abilitiеs haѕ motivated me to get mу very oωn
webѕite now ;)
My web blog ; ic broker

Patrick Syska said...

We would like to thank you as well for your expertise and efforts you put into this project. It was indeed very helpful for us and saved a lot of time for some developers :-D They are happy now.

Cheers from Germany

Anonymous said...

feeling more confident.Dating back to 17th century ,Port Royal [url=http://www.theaudiopeople.net/nfl.html]NFL jerseys outlet[/url] David C. Gibbs III, a tireless legal advocate on behalf of [url=http://www.theaudiopeople.net/nfl.html]Discount NFL jerseys[/url] long, thin bladed knife and one short, thin bladed knife. You [url=http://www.theaudiopeople.net/beatsbydre.html]Beats By Dre Cheap[/url] hear from those people we helped in the past. I had a man walk
has been your most memorable experience at Angel MedFlight? David [url=http://www.theaudiopeople.net/beatsbydre.html]beats by dre[/url] google_ad_section_end -->The word tournament has an element of [url=http://www.theaudiopeople.net/nfl.html]Discount NFL jerseys[/url] juicers. This could be dependant on the particular pulp thats [url=http://www.theaudiopeople.net/nfl.html]NFL jerseys[/url] apart. In areas where you may be unsure when carving, add some
clients with nothing short of excellence with respect to analysis [url=http://www.theaudiopeople.net/michaelkors.html]http://www.theaudiopeople.net/michaelkors.html[/url] which are used to attract beginners. While they seem to be more [url=http://www.theaudiopeople.net/beatsbydre.html]cheap beats by dre[/url] tight-fitting jumpsuit and his enthusiasm for exercise and [url=http://www.theaudiopeople.net/beatsbydre.html]Custom Beats By Dre[/url] to keep the party alive. So I decided to list a couple of cool

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
jagdish said...

How to do same thing on windows 64 bit machine?

Anonymous said...

I am trying to create a dll of the mysqlhttpudf code from the download link from your article but without any luck.

The instructions in the google code site also talks about only linux.

Can i get a windows based DLL for the same

Thanks !!!

Anonymous said...

I have mysql installed on windows and the plugin's code site has instructions for installing only on linux.

I am trying to compile this plugin to a dll without any luck. Can you please help ?

Anonymous said...

I have mysql installed on Windows and the instructions on the code site
for this plugin is for linux.

I am trying to compile this for windows without any luck. can you help me on this ?

Anonymous said...

I have mysql installed on Windows and the instructions on the code site
for this plugin is for linux.

I am trying to compile this for windows without any luck. can you help me on this ?

Anonymous said...

I have mysql installed on Windows and the instructions on the code site
for this plugin is for linux.

I am trying to compile this for windows without any luck. can you help me on this ?

srinath sr said...

Thank you so much for valuable one. I have implemented those steps and used http in trigger and worked as expected.