MySQL Forge

Resources for the MySQL Community

Category: Software Preview

ProjectPage External Language Stored Procedures

This project is a possible implementation of WorkLog#820: External Language Stored Procedures

Contents 1 Documentation 2 Source Downloads 3 External Language Plug-ins 3.1 C Language Plug-in 3.2 Java Language Plug-in 3.3 LegacyUDF Language Plug-in 3.4 Perl Language Plug-in 3.5 XML-RPC Language Plug-in 4 TO DO 5 Project Authors

[edit] Documentation

• Slides used at Users Conference 2008 Image:External Language Stored Procedures UC 2008.pdf
• Users Conference 2008 Presentation Video http://technocation.org/content/video%3A-external-language-stored-procedures

[edit] Source Downloads

Current development tree:

• https://code.launchpad.net/~starbuggers/sakila-server/mysql-5.1-wl820
• Checkout: bzr branch http://bazaar.launchpad.net/~starbuggers/sakila-server/mysql-5.1-wl820

Legacy download

• http://cyberkinetica.homeunix.net/mysql-devel/

Source tarballs and patches are automatically generated at midnight PST.

Please do not link to actual files as they may not exist after next update.

Freshmeat Project Page

• http://freshmeat.net/projects/udfng/

[edit] External Language Plug-ins

The following external language plug-ins are currently operational, planned or in development:

• C
• Java
• LegacyUDF
• Perl
• XML-RPC

[edit] C Language Plug-in

Status: planning

Requirements:

• cinvoke library

[edit] Java Language Plug-in

Status: working

Requirements:

• Supported JDK: Apple, Sun, IBM, Blackdown

Note:

• may obsolete WorkLog#2651 - "New" UDF -- Finish mapping of existing java objects to JNI classes

[edit] LegacyUDF Language Plug-in

Status: finished but not tested

Note:

• may obsolete WorkLog#2974 - Convert UDF to use Plugin API

[edit] Perl Language Plug-in

Status: working

Requirements:

• Perl 5.8 with 'multiplicity', see link
• DBI and DBD::mysql, see link

Sample stored Perl routine:

sub test1()
{
  my $dsn= "DBI:mysql:test";
  my $dbh= DBI->connect($dsn, undef, undef) or
      die "Failed to obtain connection";
  # Putting a WRITE lock on this table before calling this routine
  # will not cause deadlock because this INSERT will occur
  # within the same thread context.
  $dbh->do("INSERT INTO t1 (txt) VALUES ('hello world')");
  return 0;
}

Example:

mysql> CREATE TABLE test.t1 (
   seq INT NOT NULL AUTO INCREMENT PRIMARY KEY,
   txt VARCHAR(128));
mysql> CREATE PROCEDURE test.test1()
   LANGUAGE Perl MODIFIES SQL DATA
   EXTERNAL NAME 'MyDBIExample::test1';
mysql> LOCK TABLE test.t1 WRITE;
mysql> CALL test.test1();
mysql> UNLOCK TABLES;
mysql> SELECT * FROM test.t1;
+-----+-------------+
| seq | txt         |
+-------------------+
|   1 | hello world |
+-------------------+

[edit] XML-RPC Language Plug-in

Status: working

Requirements:

• xmlrpc-c library

Example:

mysql> create function xml_get_state(id int) returns text
-> no sql language xmlrpc external name
-> 'xmlrpc://betty.userland.com/RPC2;examples.getStateName';
Query OK, 0 rows affected (0.00 sec)

mysql> select xml_get_state(40);
+-------------------+
| xml_get_state(40) |
+-------------------+
| South Carolina |
+-------------------+
1 row in set (0.42 sec)

[edit] TO DO

• Other script languages: Lua, Ruby, Python?
• Table functions

[edit] Project Authors

• Eric Herman <eric@mysql.com>
• Antony Curtis <antony.curtis@ieee.org>