SQL Anywhere external environment support

SQL Anywhere includes support for six external runtime environments. These include embedded SQL and ODBC applications written in C/C++, and applications written in Java, Perl, PHP, or languages such as C# and Visual Basic that are based on the Microsoft .NET Framework Common Language Runtime (CLR).

SQL Anywhere has had the ability to call compiled native functions written in C or C++ for some time. However, when these procedures are run by the database server, the dynamic link library or shared object has always been loaded by the database server and the calls out to the native functions have always been made by the database server. The risk here is that if the native function causes a fault, then the database server crashes. Running compiled native functions outside the database server, in an external environment, eliminates these risks to the server.

The following is an overview of the external environment support in SQL Anywhere.

 Catalog tables
 Deprecated options
 SQL statements

Once an external environment is set up to be used on the database server, you can then install objects into the database and create stored procedures and functions that make use of these objects within the external environment. Installation, creation, and usage of these objects, stored procedures, and stored functions is very similar to the current method of installing Java classes and creating and using Java stored procedures and functions.

To add a comment for an external environment, you can execute:

COMMENT ON EXTERNAL ENVIRONMENT environment-name
  IS comment-string

To install a Perl or PHP external object (for example, a Perl script) from a file or an expression into the database, you would need to execute an INSTALL EXTERNAL OBJECT statement similar to the following:

INSTALL EXTERNAL OBJECT object-name-string
  [ update-mode ] 
  FROM { FILE file-path | VALUE expression }
  ENVIRONMENT environment-name 

To add a comment for an installed Perl or PHP external object, you can execute:

COMMENT ON EXTERNAL [ENVIRONMENT] OBJECT object-name-string 
  IS comment-string

To remove an installed Perl or PHP external object from the database, you would need to use a REMOVE EXTERNAL OBJECT statement:

REMOVE EXTERNAL OBJECT object-name-string

Once the external objects are installed in the database, they can be used within external stored procedure and function definitions (similar to the current mechanism for creating Java stored procedures and functions).

CREATE PROCEDURE procedure-name(...)
  EXTERNAL NAME '...' 
  LANGUAGE environment-name
CREATE FUNCTION function-name(...)
  RETURNS ... 
  EXTERNAL NAME '...'
  LANGUAGE environment-name

Once these stored procedures and functions are created, they can be used like any other stored procedure or function in the database. The database server, when encountering an external environment stored procedure or function, automatically launches the external environment (if it has not already been started), and sends over whatever information is needed to get the external environment to fetch the external object from the database and execute it. Any result sets or return values resulting from the execution are returned as needed.

If you want to start or stop an external environment on demand, you can use the START EXTERNAL ENVIRONMENT and STOP EXTERNAL ENVIRONMENT statements (similar to the current START JAVA and STOP JAVA statements):

START EXTERNAL ENVIRONMENT environment-name
STOP EXTERNAL ENVIRONMENT environment-name

For more information, see:


The CLR external environment
The ESQL and ODBC external environments
The Java external environment
The PERL external environment
The PHP external environment