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 External Language Plug-ins 1.1 C Language Plug-in 1.2 Java Language Plug-in 1.3 LegacyUDF Language Plug-in 1.4 Perl Language Plug-in 1.5 XML-RPC Language Plug-in 2 TO DO 3 Project Authors

External Language Plug-ins

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

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

C Language Plug-in

Status: planning

Requirements:

• cinvoke library

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

LegacyUDF Language Plug-in

Status: finished but not tested

Note:

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

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 |
+-------------------+

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)

TO DO

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

Project Authors

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