Evaluate Tcl scripts as a UDF in MySQL

In the previous blog entry, I mentioned evaluating Tcl scripts from within MySQL using the User-Defined Function (UDF) capability already in MySQL. For example, it would allow you to do this:

CREATE FUNCTION TCL RETURNS STRING SONAME 'libtcl-mysql-udf.so';

SELECT TCL('expr {2 + 2}');

Well, after a few hours of productive coding and hacking, I want to share tcl-mysql-udf-0.1.tar.gz with you.

I’ve done very limited testing on this and on very little sleep, so I wholly expect there to be really stupid mistakes in there–but, it does “work” on my personal Debian 4.0 Linux and MySQL 5.1.20 setup with Tcl 8.4.12. If you want to play around with this, I strongly urge you to read over the README included in the tarball, as it should have all the relevant bits of information necessary to get you started.

A quick demonstration of what’s currently possible with this version:

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

mysql> SELECT TCL('info commands') AS script \G
*************************** 1. row ***************************
script: tell socket subst open eof pwd glob list exec pid time eval lrange fblocked lsearch gets case lappend proc break variable llength return linsert error catch clock info split array if fconfigure concat join lreplace source fcopy global switch update close
1 row in set (0.00 sec)

Currently, Tcl errors result in the function returning NULL–I haven’t figured out how to propagate the error string back up, yet.

mysql> SELECT TCL('error foo') AS script;
+--------+
| script |
+--------+
| NULL   |
+--------+
1 row in set (0.01 sec)

Also, the maximum length string that the function can return is currently hard-coded to 255 characters.

mysql> SELECT LENGTH(TCL('string repeat x 1024')) AS script;
+--------+
| script |
+--------+
|    255 |
+--------+
1 row in set (0.07 sec)

I implemented the function as accepting a variable number of arguments, the first being the Tcl script, followed by values that get appended to the $args Tcl variable–as though it were a Tcl proc call, in a sense.

mysql> SELECT TCL('concat [llength $args] $args', 'abc', 123, NOW()) AS script;
+---------------------------------+
| script                          |
+---------------------------------+
| 3 abc 123 {2007-08-25 08:50:35} |
+---------------------------------+
1 row in set (0.05 sec)

If you’re familiar with Tcl and MySQL and aren’t afraid of compiling something and fooling around with it in your dev playground, I’d love to hear what you think of this code. Can you see any use for it? What else would it need to be able to do before it would be useful to you?

Tags: MySQL, Tcl, User-Defined Function, UDF