Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - Programming » SQL Anywhere Data Access APIs » SQL Anywhere external environment support

 

Overview of external environments

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. In the case of 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. In the case of one-per-database, the 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. In most cases, though, 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.

SQL statements

The following SQL syntax allows you to set or modify values in the SYS.SYSEXTERNENV table.

ALTER EXTERNAL ENVIRONMENT environment-name 
    [ USER user-name ]
    [ LOCATION location-string ]
  • environment-name   The environment name is an identifier representing the name of the environment in SYS.SYSEXTERNENV. It is one of PERL, PHP, JAVA, CLR, C_ESQL32, C_ESQL64, C_ODBC32, or C_ODBC64.

  • user-name   The user name string identifies a user in the database who 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 user-name would indicate the different user ID to be used. In most cases, this option need not be specified.

  • location-string   The location string 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.

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 an 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 
  • object-name-string   The object name string is the name by which the installed object is identified within the database.

  • update-mode   The update mode is either NEW or UPDATE. If the update mode is omitted, then NEW is assumed.

  • file-path   The file path is the location on the database server computer from where the object is installed.

  • environment-name   The environment name is one of JAVA, PERL, PHP, CLR, C_ESQL32, C_ESQL64, C_ODBC32, or C_ODBC64.

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

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

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

REMOVE EXTERNAL OBJECT object-name-string
  • object-name-string   The object name string is the same string that was specified in the corresponding INSTALL EXTERNAL OBJECT statement.

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
  • environment-name   The environment name is one of JAVA, PERL, PHP, CLR, C_ESQL32, C_ESQL64, C_ODBC32, or C_ODBC64.

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
  • environment-name   Environment name is one of JAVA, PERL, PHP, CLR, C_ESQL32, C_ESQL64, C_ODBC32, or C_ODBC64.

For more information, see: