Wednesday, November 17, 2010

Chatting with the database

At APEXPosed in Brussels I attended a presentation by Lucas Jellema called "Reaching out from PL/SQL". It was all about leveraging the central position of the Oracle database in the application architecture. Of course the examples were accompanied by some fine demo's. One of them was sending and receiving email by the database: You could ask the database a question, and the database answered that one. Another one was sending instant messages from the database when some data was changed. Interesting stuff!
So I decided to combine those two: Why not chat with the database? Ask the database a question using an IM client and the database will (try to) answer to that question...

I started off with downloading and installing the Oracle-XMPP code (XMPP stands for Extensible Messaging and Presence Protocol). Because I needed my own XMPP-server, I also downloaded and installed OpenFire. One of the options when installing OpenFire is to use an Oracle Database for storing the data (using a OJDBC driver). In the OpenFire schema there is a table called ofoffline, used to store messages send to users that are offline. By defining a user (called 'oracle'), that is always offline, I can get "my" messages there. I also wrote a trigger on that table to handle the requests. And the result is someting like this:
How cool is that?
Of course you can (and should) modify the parsing of the query to make it more userfriendly - and not vulnerable to SQL-Injection. But you can chat with your database using this stuff!
Post a Comment