Emacs SQL mode

Took me sometime to start working again with MySQL effectively. Again I had to find a decent clinet that allows me to edit a script and execute the commands from within the buffer for testing.

I looked into VIM plugin dbext, and it sucks!
I was not able to get it to easily get it to work, and after sometime finally it worked, but openning a file in VIM, and editing it (even non SQL), caused the screen to go dirty with garbage every where. C-L to redraw was not a help. I had to find something else !

So, emacs sql-mode comes with emacs 24.4.1 (on gentoo). So Let's open an sql script with emacs, then :

• M-x sql-mode
• M-x sql-mysql (fill in the values).
• M-x sql-set-product (type mysql or postgresql .... etc).
• M-x sql-set-sqli-buffer

Now, highlght the SQL statments, and C-c C-r or just C-c C-b to send the whole buffer. Enjoy !

StackOverflow: sql-set-sqli-buffer "there is no suitable sqli buffer"
Additional setup tips.

Database events - Part 2 - polling an events table with apache camel

The problem with approache discussed in part one is that it's good for one table. Assuming we have an ERPsystem with more than 1000+ tables, and we need t omonitor 300 of these tables. Would polling in this case be acceptable ?

The second approache still uses polling a single table. Let's call it "the events table". We will use triggers to log transactions to the events table. Each inserted record will have a boolean field "processed" indicating that it was read by apache camel . Our example is done with postgresql. However the idea is applicable to other relational database engines. The next SQL script is a simpliefied version of Audit Trigger. Save it in a file "postgresql.events.sql"

Now in order to use this script, we need to:

Load it

Create the events table

Monitor a table

Modify the java code from part 1 to read this table

The follwing script should handle this part.

Now all we need to do is to update our camel route to read from this table and possibly to update the field "processed" to true if we don't want to use the stateful scheduler in quarz2.

Database events

Database integration is not a straight forward task. Most of the time it's done using a script or a process that is invoked on schedule. Usually you want to do something when a data is changed. This can be done easily from the source code of the process modifying the data. However, this is not always possbile as it requires access to the source code, and skills in the technology used. This leaves us with very limited options.

In this series we discuss each method separately. We try to generalize and remain vendor neutral, however in some posts we will focus on postgresql specific functionality.

For each solution we will do a quick comparison table based on the following criteria

Criteria	description
Requires Polling
Scalable for many tables
Vendor neutral
Platform independent
Performance

1. Database events - Part 1 - Polling a table with Apache Camel
   
2. Database events - Part 2 - Polling an events table with apache camel