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.
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).
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.
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:
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |