Some background:

Sybase uses the TDS protocol (Tabular Data Stream) to communicate between clients and servers. This protocol isn't limited to database client-server communication, and Sybase provides a library to build servers that speak TDS (the library is called Open Server).

These Open Servers can work in various modes: they can work as a gateway server sitting between the client and the real server, or they can provide specific functionality via remote procedure calls. A typical example of a gateway server is a connection pooling server, or a gateway allowing a Sybase client application to access another database (eg DB2, Oracle, etc.) A typical RPC server could be a stock quote server, where you could execute something like

exec stockquote...getQuote 'SY'
[download]

which would execute some C code on the remote server which presumably would fetch the latest quotes for the "SY" symbol from somewhere.

Writing a complete Open Server is non-trivial, however. Sybase provides a simpler mechanism with the XP server and what is known as Extended Stored Procedures (aka ESPs). The XP server was created because a lot of Sybase users wanted to be able to execute OS commands from Transact-SQL procedures or triggers. The xp_cmdshell ESP provides that functionality - this runs in a special Open Server called the XP server. This server is extensible - you can create your own ESPs by writing some C/C++ code, building a shared object, and installing it:

create proc xp_echo @msg varchar(255) as external filename "examples.s
+o"

exec xp_echo "this is a test"
[download]

I'm sure you can see where I'm going with this...

Calling Perl from Transact-SQL

So the next step is quite simple, really: plug a perl interpreter into an XP-compliant shared object, and then you can do something like this:

create proc xp_perl @module varchar(128), @p1 varchar(255), 
       @p2 varchar(255), .... @p20 varchar(255), ...
       as external file "xp_perl.so"

exec xp_perl "Sybase::ESP::FileDir", "/some/file/directory"
[download]

So borrowing on the mod_perl functionality, the Sybase::ESP::FileDir module could look like this:

package Sybase::ESP::FileDir;

use Sybase::ESP;

sub handler {
    my $esp = shift;

   my ($dir) = $esp->params;
   $esp->print("some string"); # same as T-SQL PRINT
   $esp->error(25001, "some error");  # same as T-SQL RAISERROR
   $esp->resultset([{ name => 'size', type => 'int' },
                    { name => 'filename', type => 'char', size => 255}
+ ]);

   if(!opendir(DIR, $dir)) {
       $esp->error(25001, "Can't open $dir: $!");
       return -6;
   }
   while(my $file = readdir(DIR)) {
       # ... get file size, etc.
       $esp->datarow($size, $file);
   }
   closedir(DIR);
   return 0;
}
[download]

The return code from the handler would be returned to the caller as the procedure return status.

Obviously this wouldn't be particularily fast (especially as Open Servers are multi-threaded and perl isn't quite ready for that yet, AFAIK), but it could open up a number of interesting possibilities (although I wonder if it's not a little bit of a solution looking for a problem :-).

I started tinkering with this yesterday, so there are no doubt quite a few issues that need to be resolved, not least of which is the security issue of allowing potentially arbitrary perl code to be run as the "sybase" user...

Michael