tcl-mysql-udf 0.2 and Win32 DLL binary

Last week, I released some code that would enable MySQL to evaluate Tcl scripts as a stored function, which I called tcl-mysql-udf. My friend Steve asked if I could prepare a Win32 DLL binary for him, so I worked on that tonight and am releasing version 0.2, along with the DLL:

• Source: tcl-mysql-udf-0.2.tar.gz (328K)
• Win32 DLL: tcl-mysql-udf-0.2.dll.zip (144K)

In order to play with this, you’ll need the following prerequisites installed:

• MySQL (I’m testing on 5.1.21-beta)
• Tcl (I’m testing on 8.4.15.0)

Inside the tcl-mysql-udf-0.2-dll.zip will be the file tcl-mysql-udf.dll. On MySQL 5.0.x, it expects it to reside in the Program Files\MySQL\MySQL Server 5.0\bin directory, so copy it there. On MySQL 5.1.x, however, it expects it to be in the Program Files\MySQL\MySQL Server 5.1\lib directory. Pay close attention to what version of MySQL you’re using and the correct directory to copy the DLL into.

Once you’ve got everything installed and copied to the right locations so far, connect to your MySQL database with your favorite MySQL client, and issue the following command (you only type what’s in bold):

mysql> CREATE FUNCTION TCL RETURNS STRING SONAME 'tcl-mysql-udf.dll';
Query OK, 0 rows affected (0.10 sec)

We can check what version of Tcl we’ve loaded this way:

mysql> SELECT TCL('info patchlevel') AS script;
+--------+
| script |
+--------+
| 8.4.15 |
+--------+
1 row in set (0.04 sec)

Here’s a goofy example of storing Tcl scripts in the database and having MySQL evaluate them:

mysql> CREATE TABLE code (
n INT NOT NULL AUTO_INCREMENT,
script TEXT NOT NULL,
PRIMARY KEY pk_code (n)
) ENGINE=MyISAM;
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO code (script) VALUES
('set x 123'),
('expr {$x + 432}'),
('clock format [clock seconds]'),
('incr x [clock seconds]'),
('expr {$x * rand()}');
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

So, we now have a table with five rows in it, each row containing a Tcl script. We can have MySQL evaluate those Tcl scripts like this:

mysql> SELECT n, TCL(script)
FROM code
ORDER BY n;

+---+---------------------------------------------------+
| n | TCL(script)                                       |
+---+---------------------------------------------------+
| 1 | 123                                               |
| 2 | 555                                               |
| 3 | Fri Aug 31 12:41:25 AM Eastern Daylight Time 2007 |
| 4 | 1188535408                                        |
| 5 | 6935485.39171                                     |
+---+---------------------------------------------------+
5 rows in set (0.05 sec)

So what, right? How about fetching HTML documents via HTTP, right from within MySQL?

mysql> CREATE TABLE urls (
n INT NOT NULL AUTO_INCREMENT,
url VARCHAR(255) NOT NULL,
PRIMARY KEY pk_urls (n)
) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO urls (url) VALUES
('http://dossy.org/'),
('http://aolserver.com/'),
('http://njgeeks.org/');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT n, url, TCL(
'package require http;'
'set url [lindex $args 0];'
'set token [http::geturl $url];'
'set data [http::data $token];'
'http::cleanup $token;'
'regexp -all -inline {<title>.*?</title>} $data',
url) AS script
FROM urls
ORDER BY n \G

*************************** 1. row ***************************
     n: 1
   url: http://dossy.org/
script: {<title>Dossy's Blog</title>}
*************************** 2. row ***************************
     n: 2
   url: http://aolserver.com/
script: <title>AOLserver</title>
*************************** 3. row ***************************
     n: 3
   url: http://njgeeks.org/
script: {<title>NJ Geeks | - New Jersey's IT Community</title>}
3 rows in set (1.54 sec)

This query uses the Tcl “http” package to fetch the documents specified by the url column and plucks out the <title> tag using a regular expression. The TCL() stored function takes a variable number of arguments, the first being the Tcl script to evaluate, followed by zero or more arguments that are placed into the $args Tcl variable. In this case, we pass in “url” so that on each row, we execute our Tcl script with the value from that row.

Be aware that this stored function is a security issue: allowing database users to execute arbitrary code has obvious risks, especially since that code will be executed as the user that the MySQL server is running as.

Hopefully this is enough to get you started and might even give you an idea as to how this could be useful to you in some way. If you have any questions, just leave them in the comments below.

Tags: MySQL, Tcl, Win32, stored function