PERL External Environment

A Perl stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in Perl and the execution of the procedure or function takes place outside the database server (that is, within a Perl executable instance).

It should be noted that there is a separate instance of the Perl executable for each connection that uses Perl stored procedures and functions. This behavior is different from Java stored procedures and functions. For Java, there is one instance of the Java VM for each database rather than one instance per connection. The other major difference between Perl and Java is that Perl stored procedures do not return result sets, whereas Java stored procedures can return result sets.

There are a few prerequisites to using Perl in the database support:

Perl must be installed on the database server computer and the database server must be able to locate the Perl executable.

The DBD::SQLAnywhere driver must be installed on the database server computer.
On Windows, Microsoft Visual Studio must also be installed. This is a prerequisite since it is necessary for installing the DBD::SQLAnywhere driver.

In addition to the above prerequisites, the database administrator must also install the Perl External Environment module.

To install the external environment module (Windows):

Run the following commands from the SDK\PerlEnv subdirectory:
```
perl Makefile.PL
nmake
nmake install
```

To install the external environment module (UNIX):

Run the following commands from the sdk/perlenv subdirectory:
```
perl Makefile.PL
make
make install
```

Once the Perl external environment module has been built and installed, the Perl in the database support can be used.

To use Perl in the database, make sure that the database server is able to locate and start the Perl executable. Verify that this can be done by executing:

START EXTERNAL ENVIRONMENT PERL;

If the database server fails to start Perl, then the problem probably occurs because the database server is not able to locate the Perl executable. In this case, you should execute an ALTER EXTERNAL ENVIRONMENT statement to explicitly set the location of the Perl executable. Make sure to include the executable file name.

ALTER EXTERNAL ENVIRONMENT PERL 
  LOCATION 'perl-path';

For example:

ALTER EXTERNAL ENVIRONMENT PERL
  LOCATION 'c:\\Perl\\bin\\perl.exe';

Note that the START EXTERNAL ENVIRONMENT PERL statement is not necessary other than to verify that the database server can start Perl. In general, making a Perl stored procedure or function call starts Perl automatically.

Similarly, the STOP EXTERNAL ENVIRONMENT PERL statement is not necessary to stop an instance of Perl since the instance automatically goes away when the connection terminates. However, if you are completely done with Perl and you want to free up some resources, then the STOP EXTERNAL ENVIRONMENT PERL statement releases the Perl instance for your connection.

Once you have verified that the database server can start the Perl executable, the next thing to do is to install the necessary Perl code into the database. Do this by using the INSTALL statement. For example, you can execute the following statement to install a Perl script from a file into the database.

INSTALL EXTERNAL OBJECT 'perl-script' 
  NEW 
  FROM FILE 'perl-file'
  ENVIRONMENT PERL;

Perl code also can be built and installed from an expression, as follows:

INSTALL EXTERNAL OBJECT 'perl-script' 
  NEW
  FROM VALUE 'perl-statements'
  ENVIRONMENT PERL;

Perl code also can be built and installed from a variable, as follows:

CREATE VARIABLE PerlVariable LONG VARCHAR;
SET PerlVariable = 'perl-statements';
INSTALL EXTERNAL OBJECT 'perl-script' 
  NEW 
  FROM VALUE PerlVariable
  ENVIRONMENT PERL;

To remove Perl code from the database, use the REMOVE statement, as follows:

REMOVE EXTERNAL OBJECT 'perl-script'

To modify existing Perl code, you can use the UPDATE clause of the INSTALL EXTERNAL OBJECT statement, as follows:

INSTALL EXTERNAL OBJECT 'perl-script' 
  UPDATE 
  FROM FILE 'perl-file'
  ENVIRONMENT PERL 
 INSTALL EXTERNAL OBJECT 'perl-script' 
  UPDATE 
  FROM VALUE 'perl-statements'
  ENVIRONMENT PERL 
 SET PerlVariable = 'perl-statements';
INSTALL EXTERNAL OBJECT 'perl-script' 
  UPDATE 
  FROM VALUE PerlVariable
  ENVIRONMENT PERL

Once the Perl code is installed in the database, you can then create the necessary Perl stored procedures and functions. When creating Perl stored procedures and functions, the LANGUAGE is always PERL and the EXTERNAL NAME string contains the information needed to call the Perl subroutines and to return OUT parameters and return values. The following global variables are available to the Perl code on each call:

$sa_perl_return – This is used to set the return value for a function call.

$sa_perl_argN – where N is a positive integer [0 .. n]. This is used for passing the SQL arguments down to the Perl code. For example, $sa_perl_arg0 refers to argument 0, $sa_perl_arg1 refers to argument 1, and so on.

$sa_perl_default_connection – This is used for making server-side Perl calls.
$sa_output_handle – This is used for sending output from the Perl code to the database server messages window.

A Perl stored procedure can be created with any set of data types for input and output arguments, and for the return value. However, all non-binary data types are mapped to strings when making the Perl call while binary data is mapped to an array of numbers. A simple Perl example follows:

INSTALL EXTERNAL OBJECT 'SimplePerlExample' 
  NEW 
  FROM VALUE 'sub SimplePerlSub{ 
    return( ($_[0] * 1000) + 
            ($_[1] * 100) + 
            ($_[2] * 10) + 
            $_[3] ); 
  }'
  ENVIRONMENT PERL;

CREATE FUNCTION SimplePerlDemo( 
    IN thousands INT, 
    IN hundreds INT, 
    IN tens INT, 
    IN ones INT) 
  RETURNS INT
  EXTERNAL NAME '<file=SimplePerlExample> 
    $sa_perl_return = SimplePerlSub(
      $sa_perl_arg0, 
      $sa_perl_arg1, 
      $sa_perl_arg2, 
      $sa_perl_arg3)' 
  LANGUAGE PERL;

// The number 1234 should appear
SELECT SimplePerlDemo(1,2,3,4);

The following Perl example takes a string and writes it to the database server messages window:

INSTALL EXTERNAL OBJECT 'PerlConsoleExample'
  NEW
  FROM VALUE 'sub WriteToServerConsole { print $sa_output_handle $_[0]; }'
  ENVIRONMENT PERL;

CREATE PROCEDURE PerlWriteToConsole( IN str LONG VARCHAR) 
  EXTERNAL NAME '<file=PerlConsoleExample> 
    WriteToServerConsole( $sa_perl_arg0 )'
  LANGUAGE PERL;

// 'Hello world' should appear in the database server messages window
CALL PerlWriteToConsole( 'Hello world' );

To use server-side Perl, the Perl code must use the $sa_perl_default_connection variable. The following example creates a table and then calls a Perl stored procedure to populate the table:

CREATE TABLE perlTab(c1 int, c2 char(128));

INSTALL EXTERNAL OBJECT 'ServerSidePerlExample' 
  NEW 
  FROM VALUE 'sub ServerSidePerlSub 
    { $sa_perl_default_connection->do( 
        "INSERT INTO perlTab SELECT table_id, table_name FROM SYS.SYSTAB" ); 
      $sa_perl_default_connection->do( 
        "COMMIT" ); 
    }'
  ENVIRONMENT PERL;

CREATE PROCEDURE PerlPopulateTable() 
  EXTERNAL NAME '<file=ServerSidePerlExample> ServerSidePerlSub()' 
  LANGUAGE PERL;

CALL PerlPopulateTable();

// The following should return 2 identical rows
SELECT count(*) FROM perlTab 
UNION ALL 
SELECT count(*) FROM SYS.SYSTAB;