Note - SYBASE specific functionality follows...

If you work with recent versions of Sybase or Oracle then I'm sure you've heard of JSPs (Java Stored Procedures - not Java Server Pages...).
Essentially the database engine includes a Java VM, letting you call java code from SQL. It's pretty neat, but for Sybase at least it's an add-on feature, and while Java is certainly a good programming language (no R-wars please!) it's not really my cup of tea.

Well the other day someone contacted me and asked why no-one had written any glue code to be able to use perl from a Sybase OpenServer application, and it got me thinking... (always a dangerous thing, that! :-)

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

Replies are listed 'Best First'.
Re: Perl in the database?
by VSarkiss (Monsignor) on Mar 20, 2002 at 17:38 UTC

    This sounds pretty nifty. I'd love to try playing with it. *mumble* years ago, I wrote an Open Server much as you describe: it retrieved stock quotes and prices from several different sources and presented them with a unified interface. (I say "I" but in fact I led a group of 5-10 developers.) Essentially we used TDS as a network transport protocol -- your post doesn't make this obvious, but TDS is a distributed protocol.

    One problem I see with this is TDS being tied to Sybase (and now Microsoft). The client for whom I wrote that Open Server is slowly moving away from Sybase -- for various reasons, not all of them technical. That includes slowly decomissioning all Open Servers. But if you're in a dedicated SQL Server shop, I agree it would be very nice to be able to generate result sets from Perl code.

    Are you planning on putting this on CPAN?

[reply]
      Are you planning on putting this on CPAN?

      I don't know yet - it depends on whether this actually works the way I hope it will.
      And you have to have the Open Server libraries, which most people won't have as they are not included in the default package from Sybase.

      Michael

[reply]
        Michael

        I would love an XP Server library running Perl!

        You could set up a sourceforge project for this, I think you'll find lots of help.

        If you use XP Server and a shared lib, you have everything you need, you don't need any additional libraries.

[reply]
        And you have to have the Open Server libraries, which most people won't have as they are not included in the default package from Sybase.

        I entered this thread a little late, but hopefully it is still active.

        Have you used the FreeTDS libraries? Will they provide the functionality you need? According to the FAQ on freetds.org, libtdssrv may provide some server capabilities.

        Of course, not being an Open Server programmer, I can't really evaluate whether it would be possible or not. It would sure be nice if FreeTDS developed to the point where a full Open Client/Open Server implementation were possible.

        -----
        "Computeri non cogitant, ergo non sunt"

[reply]
Re: Perl in the database?
by demerphq (Chancellor) on Mar 20, 2002 at 19:09 UTC
    Cool.

    Very cool.

    You do know that MS SQL server has similar functionality with perlscript ?(AS little Perl MS scripting language plug in.)

    It would be really cool to do this on Sybase too.

    Yves / DeMerphq
    ---
    Writing a good benchmark isnt as easy as it might look.

[reply]
      I wasn't aware that you could do this with MS-SQL - does this plug in to the server itself, or does it run as an external server, a la XP server?

      Michael

[reply]

        These are actually two different technologies. ActiveState provides an ActiveX scripting engine for Perl with their Windows distribution, called PerlScript. This allows you to use Perl in all the places that, for example, you can also use VBScript or JavaScript, including ASP, client-side web scripting (that is, if the client also has ActivePerl) and SQL Server Data Transformation Services.

        I've used Perl(Script) quite successfully within SQL Server DTS and love it. It allows me to do all sorts of things that you can't easily do with the default VBScript, such as Perl regexes and SMTP email. The best part is being able to use modules off of CPAN, where with VBScript you'd normally have to find and install an appropriate COM object.

        Now, SQL Server also implements extended stored procedures, such as xp_cmdshell, but my knowledge of them is somewhat limited. However, they sound similar to the Sybase versions. As far as I know, they can only currently be written in C/C++, and then should be compiled into a DLL.

        My colleagues and I have discussed something like this idea before, but we're Perl coders with only vague experience with C++, so it's never gone past the concept stage. Our main desire was to have Perl regexes within Transact SQL, but it would be great to have the complete functionality of Perl at hand.

        If you do develop a working way to call Perl from Sybase, please post the code, or explain how you've done it. It's quite possible it might also be applicable to SQL Server, due to its shared ancestry with Sybase.

        -jehuni

[reply]
Re: Perl in the database?
by knobunc (Pilgrim) on Mar 20, 2002 at 18:38 UTC

    Neat!

    For people looking for this in a free RDBMS you can build a perl interpreter into PostgreSQL and write stored procs and things in Perl.

    -ben

[reply]

Back to Meditations