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

A system catalog table stores the information needed to identify and launch each of the external environments. The definition for this table is:

SYS.SYSEXTERNENV (
  object_id            unsigned bigint not null,
  name                 varchar(128)    not null,
  scope                char(1)         not null,
  supports_result_sets char(1)         not null,
  location             long varchar    not null,
  options              long varchar    not null,
  user_id              unsigned int
)

object_id A unique identifier that is generated by the database server.
name The name column identifies the name of the external environment or language. It is one of java, perl, php, clr, c_esql32, c_esql64, c_odbc32, or c_odbc64.
scope The scope column is either C for CONNECTION or D for DATABASE respectively. The scope column identifies if the external environment is launched as one-per-connection or one-per-database.
For one-per-connection external environments (like PERL, PHP, C_ESQL32, C_ESQL64, C_ODBC32, and C_ODBC64), there is one instance of the external environment for each connection using the external environment. For a one-per-connection, the external environment terminates when the connection terminates.

For one-per-database external environments (like JAVA and CLR), there is one instance of the external environment for each database using the external environment. Theone-per-database external environment terminates when the database is stopped.
supports_result_sets The supports_result_sets column identifies those external environments that can return result sets. All external environments can return result sets except PERL and PHP.
location The location column identifies the location on the database server computer where the executable/binary for the external environment can be found. It includes the executable/binary name. This path can either be fully qualified or relative. If the path is relative, then the executable/binary must be in a location where the database server can find it.
options The options column identifies any options required on the command line to launch the executable associated with the external environment. You should not modify this column.
user_id The user_id column identifies a user ID in the database that has DBA authority. When the external environment is initially launched, it must make a connection back to the database to set things up for the external environment's usage. By default, this connection is made using the DBA user ID, but if the database administrator prefers to have the external environment use a different user ID with DBA authority, then the user_id column in the SYS.SYSEXTERNENV table would indicate that different user ID instead. Typically, this column in SYS.SYSEXTERNENV is NULL and the database server, by default, uses the DBA user ID.

Another system catalog table stores the non-Java external objects. The table definition for this table is:

SYS.SYSEXTERNENVOBJECT (
  object_id    unsigned bigint not null,
  extenv_id    unsigned bigint not null,
  owner        unsigned int    not null,
  name         long varchar    not null,
  contents     long binary     not null,
  update_time  timestamp       not null
)

object_id A unique identifier that is generated by the database server.
extenv_id The extenv_id identifies the external environment type (as stored in SYS.SYSEXTERNENV).
owner The owner column identifies the creator/owner of the external object.
name The name column is the name of the external object as specified in the INSTALL EXTERNAL OBJECT statement.
contents The contents column contains the contents of the external object.
update_time The update_time column represents the last time the object was modified (or installed).

Deprecated options

With the introduction of the SYS.SYSEXTERNENV table, some Java-specific options have now been deprecated. These deprecated options are:

java_location
java_main_userid

Applications that have been using these options to identify which specific Java VM to use or which user ID to use for installing classes and other Java-related administrative tasks should use the ALTER EXTERNAL ENVIRONMENT statement instead to set the location and user_id values in the SYS.SYSEXTERNENV table for Java.

Locating external environments

The following SQL syntax allows you to set or modify the location of external environments in SYS.SYSEXTERNENV table.

ALTER EXTERNAL ENVIRONMENT environment-name 
     [ LOCATION location-string ]

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

COMMENT ON EXTERNAL ENVIRONMENT environment-name
  IS comment-string

Installing and using external objects

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