Transbase
Stored Procedures and User-Defined Functions
Version V8.4
Copyright © 2022 Transaction Software GmbH
ALL RIGHTS RESERVED.
While every precaution has been taken in the preparation of this document, the publisher assumes no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein.
2022-11-30
Table of Contents
- Introduction
- 1. Getting Started
- 2. SQL Extensions Reference
- 2.1. CREATE EXTERNAL Statement
- 2.2. ALTER EXTERNAL Statement
- 2.3. ALTER EXTERNAL OPTION Statement
- 2.4. DROP EXTERNAL Statement
- 2.5. CREATE PROCEDURE/FUNCTION Statement
- 2.6. ALTER PROCEDURE/FUNCTION Statement
- 2.7. CALL Statement
- 2.8. DROP PROCEDURE / FUNCTION Statement
- 2.9. GRANT USAGE Statement
- 2.10. REVOKE USAGE Statement
- 3. Implementation Guide - Java
- 3.1. System Architecture
- 3.2. Transactions
- 3.3. Java Type Mappings
- 3.4. Parameter Modes
- 3.5. User-defined Type Mapping
- 3.6. Method Resolution
- 3.7. Null Values
- 3.8. Security Manager
- 3.9. Class Loading
- 3.10. Pattern for a Stored Procedure
- 3.11. Parameter Values
- 3.12. Pattern for a User-Defined Value Function
- 3.13. Pattern for a User-Defined Table Function
- 3.14. Pattern for Generic Table Functions
- 3.15. Static Variables
- 3.16. Java Wrappers
- 3.17. Remote Method Debugging
- 3.18. Evaluating Debug Output
- 4. Implementation Guide - Native Libraries
- 5. Built-In Functions
- 6. Database Client APIs
- A. Data Dictionary Extensions
- B. Abbreviations
List of Tables
- 3.1. Java Type Mapping
- 5.1. Built-In Native Functions
- 5.2. Built-In Java Functions
- A.1. sysexternal
- A.2. sysexternalmethod
- A.3. sysexternalmethodparam
- A.4. sysexternalpriv
- B.1. Abbreviations
Introduction
Stored Procedures (STP) and User-Defined Functions (UDF) enable any Transbase user to extend server side functionality by providing user-defined routines explicitly callable via SQL statements or implicitly by event driven database triggers. Additionally a Java enabled Transbase server also allows for implementing, loading, and executing Java code.
The STP extension of Transbase aims to meet the the standard for SQL-invoked routines as defined in American National Standard for Information Technology - Database Language SQL - Part 4: Persistent Stored Modules (SQL/PSM), ANSI/ISO/IEC 9075-4-1999.
Many features of Transbase STP extension for Java are compatible to the SQL standard extension as defined in American National Standard for Information Technology - Database Languages - SQLJ - Part 1: SQLJ Routines using the Java™ Programming Language, ANSI NCITS 331.1-1999.
However, in some cases, it was more suitable to leave the standard aside, in order to full exploit special features available in the Transbase database system. And finally there were cases where the standard was implemented but additional features or variations were introduced to make STPs and UDFs more powerful and easier to use. Deviations from the standard are annotated.
To value the safety of running interpreted code inside a virtual machine over using the performance oriented approach of loading user-defines libraries into the address space of Transbase, we will first discuss the Java SQL extension.
Chapter Getting Started of this manual is a simple example on how to create and use stored procedures and user-defined functions. The first section addresses code written in Java and the second section attends to code residing in dynamic libraries.
Chapter SQL Extensions Reference is a reference of the Transbase Extension accompanied by numerous examples to illustrate the SQL extension reference.
Chapter Implementation Guide - Java covers Java specific SQL extension topics while Chapter Implementation Guide - Native Libraries addresses the topic of native code in libraries.
Chapter Database Client APIs illustrates how the STP extension is used via a database client API.
Finally, appendix Data Dictionary Extensions is an overview over the Transbase data dictionary extensions for the STP extension.
1. Getting Started
1.1. A 'Hello World from Java' Example
The following tutorial will guide you through the required steps to write, compile, load, publish, and execute a stored procedure and user-defined function on a Java enabled Transbase Server in two simple 'Hello World' examples.
1.1.1. Write a Stored Procedure
Create a new file, name it ClassHelloWorld.java and paste the following code into it.
import java.sql.*;
public class ClassHelloWorld
{
// this is a simple stored procedure
public static void ProcHelloWorld()
throws SQLException, ClassNotFoundException
{
Class.forName("transbase.jdbc.Driver");
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE HelloWorld (mytext CHAR(*))");
stmt.execute("INSERT INTO HelloWorld VALUES" +
"('Hello World from Java!')");
stmt.close();
conn.close();
return;
}
// this is a simple user-defined function
public static String FuncHelloWorld()
{
return "Hello World from Java!";
}
}
1.1.2. Load your code
You can compile and debug your code with your Java Developer Kit or any available Java IDE and load compiled classes into the database. You can also pack your classes into JAR files. There are two ways for adding JARs to the system. JARs are either stored in the system just like classes, refer to CREATE EXTERNAL Statement for details. Otherwise they are stored on the file system and are referenced via the CLASSPATH option as discussed in the ALTER EXTERNAL OPTION section.
Run the Java Compiler javac on your command line:
javac ClassHelloWorld.java
This generates a new file ClassHelloWorld.class. Have tbjdbc.jar available in your CLASSPATH variable for imports. To get your binary into the database, first connect to it with your tbi and type in the following statement:
CREATE EXTERNAL FROM C:\temp\ClassHelloWorld.class;
1.1.3. Publishing your Methods in SQL
In the next step, you tell Transbase, which of your Methods you want to have available in SQL and how you want to name them. So type the following into tbi:
CREATE PROCEDURE CreateHelloWorld() MODIFIES SQL DATA EXTERNAL NAME "ClassHelloWorld.ProcHelloWorld"; CREATE FUNCTION HelloWorld() RETURNS CHAR(*) EXTERNAL NAME "ClassHelloWorld.FuncHelloWorld";
1.1.4. Execute your Methods
To execute a stored procedure use the CALL statement:
CALL CreateHelloWorld(); SELECT * FROM HelloWorld;
A user-defined function could be used in the select clause:
SELECT HelloWorld();
To get an overview on already published functions try the following:
SELECT sqlname, methodname FROM sysexternalmethod;
For a complete overview of built-in Java functions, please refer to this table .
1.2. A 'Hello World from C' Example
The following tutorial will guide you through the required steps to write, load, publish, and execute a Java stored procedure and user-defined function on a Transbase Server in a simple 'Hello World' example.
1.2.1. Write a User-defined Function
Create a new file, name it HelloWorld.c and paste the following code into it.
/* this is a simple user-defined function */
char *FuncHelloWorld(void)
{
return "Hello World from C!";
}
1.2.2. Compile your code
Depending on the platform you are using there is a variety on how to compile your code into a dynamic library. Here is a list of important requirements to consider:
parameters are always passed by reference, never by value
make sure your functions are properly exported
compile as resource library, i.e. without entry point
Here some exemplary command lines
Windows (MS Developer Studio 6.0) :
cl /Gd /LD HelloWorld.c /OUT:libHelloWorld.dll
Linux (gcc/g++):
gcc -shared -fPIC HelloWorld.c -o libHelloWorld.so
Solaris (SunOS 5.7):
cc -G HelloWorld.c -o libHelloWorld.so
AIX (version 3 patch level 4):
cc -bM:SRE -bnoentry -bexpall HelloWorld.c -o libHelloWorld.so
HP-UX:
cc -b HelloWorld.c -o libHelloWorld.so
1.2.3. Load your code
To get the code into the database, first connect to it with your tbi and type in the following statement:
CREATE EXTERNAL FROM C:\temp\libHelloWorld.dll;
or
CREATE EXTERNAL FROM /tmp/libHelloWorld.so;
1.2.4. Publishing your Methods in SQL
In the next step, you tell Transbase, which of your Methods you want to have available in SQL and how you want to name them. So type the following into tbi:
CREATE FUNCTION HelloWorld() RETURNS CHAR(*) EXTERNAL NAME "libHelloWorld.FuncHelloWorld";
1.2.5. Execute your Methods
A user-defined function could be used in the select clause:
SELECT HelloWorld();
To get an overview on already published functions try the following:
SELECT sqlname, methodname FROM sysexternalmethod;
2. SQL Extensions Reference
In order create and manipulate external resources, STPs, and UDFs various extensions have been made to TB/SQL. This chapter is the Reference Manual to those extensions.
2.1. CREATE EXTERNAL Statement
Function:
Loads an external resource and supplies a unique name for it.
Syntax:
|
Explanation:
An external resource of the type specified in the ResourceSpec is loaded to the database and will be available under its unique ResourceName for further use. At this time external resources are always java resources, i.e. java class files.
For loading an external class without ResourceSpec, the class has to be a top-level class, i.e. the class must belong to the default package. Then FileName is then used to compute the ResourceName of the class. If classes residing in packages are used, then the ResourceName must explicitely state the fully qualifying method name (including package information) in Java notation.
Alternatively packages can be moved to a JAR file and be referenced through the CLASSPATH option, as desribed in 'AlterExternalOption' statement. Support of Java Archives (JARs) integrated in the database with this statement may be available in future versions.
For accessing any resources, at least one of their methods has to be imported by the corresponding Create Procedure Statement .
A FileName is the name of a file on a local or network disk. It may be a simple name or may include a path or other elements specific to the operating system.
Without the LOCAL keyword, the specified file is read by the client application and transferred to the Transbase service. If the file is accessible by the Transbase service via <filename>, the LOCAL clause can be used to speed up the transfer process. In this case the service process directly accesses the file under the specified name which must be an absolute path name.
The Debug clause is optional and reserved for future versions, where remote debugging of stored resources on resource basis will be enabled. Remote debugging is currently available through the procedure described in 'RemoteMethodDebugging' .
In the Comment clause you can describe the functionality, version, author, and other additional information on this resource.
Privileges:
The user must have userclass DBA or RESOURCE. For the definition of userclasses see the chapter 'GrantUserclassStatement' in your SQL Reference Manual.
2.2. ALTER EXTERNAL Statement
Function:
Modify an already loaded external resource.
Syntax:
|
Explanation:
Enable or Disable resources without changing access rights. You can replace loaded resources with newer versions, change the debug flag or the associated comment. All changed leave the usage privileges of this resource and its routines unchanged.
If you replace a java class that has already been loaded into your virtual machine, the system will automatically unload this class, and load the new class to the system.
Privileges:
The user must be owner of the external resource.
2.3. ALTER EXTERNAL OPTION Statement
Function:
Modifies the options with which your Java Virtual Machine will be started.
Syntax:
|
Explanation:
The JAVA option is a set of command line options you want to use with your virtual machine. Consult your java documentation for more information or type java -h and java -X on the command line to get short information on standard and non-standard java options, if you have a SDK or JRE installation available on your machine. The default setting of these options for the Transbase Java Runtime Environment is '-Xmx64m' in order to get some control over the Java VMs heap allocation by restricting it to 64 megabytes.
The option JDK points to the base directory of your custom JDK installation. It is strongly recommended to use an SDK (Standard Development Kit) installation instead of the smaller JRE (Java Runtime Environment) because the class compiler is only available in the SDK.
However, if you do not require class compilation, then a simple JRE installation will be sufficient. SDKs and JREs prior to version 1.6 will not function with the Transbase system. For security reasons we recommend to use the latest JRE or SDK versions available.
The CLASSPATH option holds directories and jar files you want the JVM to check for required classes. If the systems fails to find a resource in the set of loaded resources it consults the CLASSPATH to search the local file system. This is very similar to the CLASSPATH environment variable as known for the JVM. This is a way to make resources available that are not stored in the Transbase system itself, in particular jar files that cannot be loaded to the system with this Transbase version. For information on security issues involved in usage of external resources see 'ClassLoadingAndCompilation' .
The JAVADEBUG option is used to supply the JVM with a set of additional options for starting the JVM in debug mode, leaving out the '-Xrunjdwp:' prefix. Check your java documentation for further information. The default setting of JAVADEBUG in the TBJRE is:
transport=dt_socket,address=5000,suspend=y,server=y,quiet=y
This defines the transport protocol and the address where the Transbase server listens for a connecting Java Debugger (JDB). A simple sanity check is performed if the JVM is to be started in debug mode, where the existence of the tokens 'transport=', 'address=', 'suspend=y', 'server=y', 'quiet=y' and the absence of 'Xrunjdwp' is demanded.
The JAVAPERMISSIONS option makes the installed SecurityManager configurable. One can either drop all restrictions by globally permitting any access:
ALTER EXTERNAL OPTION JAVAPERMISSIONS "all"
or by permitting access on a permission class basis by specifying a comma separated list the full classnames of permissions to be granted:
ALTER EXTERNAL OPTION JAVAPERMISSIONS "java.io.FilePermission, java.net.NetPermission"
Note that a change of any option requires a restart of the JVM to take effect.
Unrecognized options will be ignored by the Virtual Machine, in order to guarantee a reliable startup behavior. The actual setting of the JVM properties can be inspected by checking the System.Properties of a Virtual Machine with user-defined functions.
Privileges:
The current user must have userclass DBA.
2.4. DROP EXTERNAL Statement
Function:
Remove an external resource from the system.
Syntax:
|
Explanation:
With this statement you may completely remove a resource from your system. All adjoined procedures, functions and privileges connected to resource or routines will also be dropped.
Privileges:
The user must be owner of the external resource or of userclass DBA.
2.5. CREATE PROCEDURE/FUNCTION Statement
Function:
Specify an SQL name for an external function or procedure.
Syntax:
|
Definitions:
CreateProcedureStatement and CreateFunctionStatement serve to publish external functions and procedures (i.e. Java methods) to the database system by defining procedure and function name, parameter types and, in case of a function, the result data type of an external function or procedure. Additionally a set of options can be defined for optimization, supervision and documentation of an external resource. Finally a reference to the actual external resource and its defining programming language is delivered.
SQLName This is the name of the newly created procedure or function. With this name you can address a function from SQL statements for execution. However, an external routine is not only identified by its name, but also by its signature and the context it is called from. This has the consequence that a set of routines may have the same name. This concept is well known in object oriented programming languages as name overloading and this behavior was implemented to reproduce this concept in TB/SQL. Finally the context of a function call decides on its result type. In a CALL SQLName-statement a stored procedure returning void will be called while SQLNames used in other TB/SQL statements always refers to a function returning a value of some type.
SQLSignature Specifies the SQL parameter data types for a procedure. A complete list of available SQL data types in Transbase along with their legal mappings to Java data types can be found in the 'JavaTypeMappings' subsection of the chapter Java Specifics in this manual and in the SQL Reference Manual. Length and precision specifications of SQL data types are irrelevant for type mapping and thus may be omitted in the SQLSignature clause. Note that two methods of the same SQLName have to differ in at least one SQLDatatype of their signature, for type mapping has to be well-defined for SQL and Java types. See 'JavaTypeMappings' subsection for more details on name overloading.
returns Specifies the result data type of the function. Note that this is an SQL Data type and that for return values always the default type mapping is used. Of course you may cast results in SQL if another type is required or explicitly map them with the optional JavaSignature. Resulttype TABLE may only be used with functions. If resulttype is TABLE then the function may only have IN parameters in its SQLSignature and only Parametermode OUT is allowed in the OutFieldList. If OutFieldList is missing, then the function is a Generic Table Function .
ParameterMode Specifies whether the SQL parameter is input only (IN), output only (OUT) or both input and output (INOUT). The ParameterMode may only be specified for stored procedures, since user-defined functions only use parameters in IN mode.
To meet the object paradigm of the Java programming language, two additional modes are introduced: INSTANCE and INSTANCEOUT. Consult 'ParameterModes' for more detailed information.
These two modes are not defined in the ANSI standard. If you do not want to use non-standard ParameterMode, you still can write a wrapper class for the exported methods in question, and have this wrapper do the casts and type conversions (see 'JavaWrappers' ).
deterministic Optimizer hint. The deterministic option tells the optimizer that for a given set of argument values, the procedure or function returns the same values for OUT and INOUT parameters and function result.
not deterministic Optimizer hint. Specifies that the procedure or function does not have the deterministic property. This is the default, if neither deterministic nor not deterministic is specified.
DataAccessIndication Specifies the SQL facilities that the Java method is allowed to perform. The restrictions apply directly to the specified method itself and to any methods that it invokes, directly or indirectly (e.g. through triggers). However the access indication does not apply to finalizer methods. Finalizer are always executed with a NO SQL access indication. If DataAccessIndication is not specified, then contains sql is the default. Any violation of DataAccessIndication will result in an SQLException and is a hard error. In that case the current transaction will be rolled back.
no sql The method cannot invoke SQL operations.
contains sql The method can invoke SQL operations, but cannot read or modify SQL data. I.e. the method cannot perform SQL open, close, fetch, select, insert, update, or delete operations. The contains sql option is the default DataAccessIndication.
reads sql data The method can invoke SQL operations, and can read SQL data, but cannot modify SQL data. I.e. the method cannot perform SQL insert, update, or delete operations.
modifies sql data The method is allowed to invoke SQL operations and to read and modify SQL data.
with[out] owner rights This option allows the programmer to specify, whether any user executing his procedure or function inherits the creator's specific owner rights for the duration of the function call. This allows users to use and manipulate data in a controlled way through 'privileged' stored procedures or user-defined functions. With owner rights is the default if none of these options is specified. This option is not contained in the ANSI standard.
check exception Optimizer hint. If check exception is specified, then explicit exception checking is performed after any returning procedure and after any function returning a NULL value, that is in any case when an exception may have occurred. This option is not contained in the ANSI standard.
ignore exception Optimizer hint. The Transbase system will not check for exceptions. The programmer has to take care that his routine does not throw any exceptions and to take appropriate actions to detect and handle exceptions in a reasonable way for his application, e.g. try/catch blocks in Java, indicator variable as INOUT or OUT variable of a stored procedure and null-checking. This option is not contained in the ANSI standard.
comment a user-defined comment on the function.
returns null on null input and called on null input Specifies the action to be taken when an argument of a function call is null. If you specify returns null on null input, then at runtime if the value of any argument is null, then the result of the function is set to null, and the function body is not invoked. If you specify called on null input, then a runtime exception is thrown if an SQL null value if found for an argument whose Java data type is boolean, byte, short, int, long, float, or double. If you do not specify either returns null on null input or called on null input, then called on null input is the default.
external Specifies that the create statement defines an SQL name for a routine written in a programming language other than SQL. Optional since Java is currently the only supported external language.
parameter style java Specifies that the runtime conventions for arguments passed to the external routine are those of the Java programming language. Optional since Java is currently the only supported external language.
Name Specifies the name of a Java method. A reference to the SQL name is effectively a synonym for the specified Java method. The external name is specified in a character string literal representing a fully qualifying method name in Java notation, e.g. "[[jarname:]packagename.]classname.methodname". The surrounding double-quotes are the delimiters of that literal. If a class is to be retrieved from the JDK Archives or via the CLASSPATH option, then the jarname has to be added. Transbase cannot check jarname validity, but will try to retrieve the class via the alternative 'ClassLoading' . Note that classes residing in jars may also belong to packages and the packagename is required for the fully qualifying method name. Note that MethodFullName is case sensitive.
JavaSignature this optional second signature offers the possibility to substitute default type mappings with alternative type mappings as described in table 'JavaTypeMappings' . You can also supplement your custom type mapping, if your custom class supports this as described in subsection 'User-defined Type Mapping' . Usage of the JavaSignature requires a complete signature, i.e. one JavaDatatype for every parameter specified in the SQLParameterList, but without ParameterMode or SQLIdentifier. Since OUT and INOUT parameters are always mapped to arrays of the specified type or class, you may omit brackets '[]' in the JavaSignature denoting an array here and rely on the system to take care of them for you. However, you have to keep in mind the ParameterMode you specified in the in your SQLSignature and use arrays for OUT and INOUT parameters when writing your code in Java. Of course the JavaSignature is case sensitive. Table functions use a slightly different syntax:
"ClassFullName[JavaInSignature].methodname[JavaOutSignature]".
This syntax is based on the related Java construct
new ClassFullName(constructorParams).methodname(methodParams).
Note that the SQLSignature is significant for correct name overloading, not the JavaSignature. This means that you cannot create two routines using the same name but varying only in their JavaSignature, although this is possible when you code in Java. Here you can simply use another SQLName to evade that problem. Consult the 'JavaTypeMappings' subsection for a complete documentation of Java specifics and name overloading.
Privileges:
The user must be owner of the external resource or userclass DBA. If MethodFullName references a resource that is stored outside the database, e.g. a jar file on the local file system that is referenced through the CLASSPATH option, userclass DBA is required.
2.6. ALTER PROCEDURE/FUNCTION Statement
Function:
Modifies SQLName, SQLSignature, ReturnType, Options and external reference of an external routine. All user privileges associated with this routine remain unchanged.
Syntax:
|
Explanation:
Enable/Disable These options offer a quick possibility to globally enable or disable an external routine to all users without having to set and reset any user privileges.
Name The Name clause specifies a new name or signature for the function or procedure.
SQLSignature, SQLProperties, and ExternalBlock change assignments of the corresponding routine properties. For details refer to Create Procedure Statement .
returns Specifies the new result data type of the function. See returns section of CreateFunctionStatement for more details.
Privileges:
The user must be owner of the external resource or userclass DBA. If MethodFullName references a resource that is stored outside the database, e.g. a jar file on the local file system that is referenced through the CLASSPATH option, userclass DBA is required.
2.7. CALL Statement
Function:
This statement calls a stored procedure.
Syntax:
|
Explanation:
The CallStatement executes a stored procedure.
By definition a stored procedure has either no result, a record consisting of OUT parameters or it returns one or more result sets.
In any case an exception is thrown if the execution of the code fails for some reason and a detailed description, including the trace stack of the JVM is printed, but no return code will be available. This might turn out to be a problem, if you call the procedure from an application or from another stored procedure or function. Through careful programming, however you can manage that your stored procedure catches any possible exceptions and passes an error code back by using an additional OUT or INOUT parameter type.
In the current Transbase release stored procedures that return ResultSets are not available. You may use a Table Function returning one Table instead.
Privileges:
The current user must have USAGE privilege on the external resource or on the external routine. Note that one routine may call any other routine declared public and available in database or through the CLASSPATH option. USAGE privilege is only checked for the entry-point, whereas SQL privileges such as SELECT, INSERT, UPDATE are checked for every issued SQL statement. SQL privileges will be checked against the resources' owner privileges, if the routine is called WITH OWNER RIGHTS, otherwise the will be checked against the current users' privileges.
2.8. DROP PROCEDURE / FUNCTION Statement
Function:
Removes the SQLName and USAGE privileges of an external function or procedure from the catalog.
Syntax:
|
Explanation:
Drops the reference SQLName SQLSignature to an external routine and all privileges connected to that reference.
Privileges:
The user must be owner of the external resource or of userclass DBA.
2.9. GRANT USAGE Statement
Function:
Extends the SQL grant statement for the USAGE privilege on external resources.
Syntax:
|
Explanation:
Usage to resources referenced through the CLASSPATH option cannot be granted, but a user of userclass DBA may grant access to single methods of those resources and thereby form a public interface to your resource.
Usage privileges for resources loaded with a CREATE EXTERNAL statement can be granted on method or resource basis. Granting an EXTERNAL resource is equivalent to granting all published methods.
Usage privileges on methods belonging to Java classes accessible via the CLASSPATH setting, i.e. built-in java methods, JDBCReader, and methods from user JARs, are globally permitted by granting usage on the CLASSPATH resource.
Privileges for calling native built-in function are globally installed by granting the BUILTIN resource. Finally, privileges for using the OraReader are associated with the ORAREADER resource. Additionally a user of userclass DBA may grant access to single functions of those resources for forming a public interface with GRANT USAGE ON PROCEDURE / FUNCTION statements.
Note: Initially a user has not the privilege to call any routine.
Privileges:
The current user must be owner of the external resource, of userclass DBA, or must have all specified privileges with the right to grant them.
2.10. REVOKE USAGE Statement
Function:
Extends the SQL revoke statement for the USAGE privilege on external resources.
Syntax:
|
Explanation:
If the current user is owner of the resource, then the specified privileges are removed from the user such that none of the privileges are left for the user.
If the current user is not owner of the resource, then the privilege instances granted by the current user are removed from the specified users. If some identical privileges had been additionally granted by other users, they remain in effect.
It is not an error to REVOKE privileges from a user which had not been granted to the user. This case is simply treated as an operation with no effect. This enables an error-free REVOKING for the user without keeping track of the granting history.
Privileges:
The current user must be owner of the external resource, have userclass DBA, or must have all specified privileges with the right to grant them.
3. Implementation Guide - Java
3.1. System Architecture
The introduction of Java STPs and UDFs into the Transbase system brings a fully enabled Java Virtual Machine into the address space of a Java enabled Transbase service. In the context of the Transbase architecture this means that each user that connects to a database managed by this service uses this virtual machine for the Java STPs and UDFs.
The Transbase-specific class loader carefully separates class definitions used under different connections and thus database contexts so that name and resource conflicts are avoided.
While the Transbase service controls the Virtual Machine, a routine running inside the Virtual Machine has no control over the Transbase service or access to its data. A user has full access to the Virtual Machine's resources and limited access to the database over a JDBC connection.
3.2. Transactions
SQL statements issued in STPs or UDFs are executed within the transaction context from which the STP or UDF was originally called.
In particular a STP or UDF may not begin, roll back or commit a transaction. An attempt to do this will always throw an SQLException. This means the transaction context before and after the execution of a STP is exactly the same, if no error occurred. This also means that AutoCommitMode is OFF by default inside a UDF or STP as it is executed as part of the calling statement.
Additionally the consistency level for the transaction may no be changed. Switching AutoCommitMode ON or changing the consistency level for an internal connection will throw an SQLException.
3.3. Java Type Mappings
The most basic topic on implementing Java methods in a database environment is to map SQL data types to Java types and classes. The following table offers an overview of all possible type mapping between SQL types and java types. "Non-nullable" means that Java passes parameters of this type by value, not by reference. Thus there is no representation for an SQL NULL. However you may use the alternative mapping if a representation of SQL NULLs is required in Java. Refer to the 'Null Values' -section in this chapter for more detailed information.
Usually default mapping between SQLTypes and JavaTypes, pretending the existence of a simple one-to-one relation, is performed if your functions do not specify that alternative type mapping is intended. To use the advanced feature of alternative mapping, you have to specify the full corresponding SQLSignature and JavaSignature, refer to Create Procedure Statement for details. To simplify matters you may also omit the leading 'java.lang.', 'java.math', and 'java.sql' portion of the alternative Java type string. Finally you can use custom type mapping to your own user-defined classes is Java with custom type mapping, as described in subsection'User-defined Type Mapping' .
Table 3.1. Java Type Mapping
| TCI Type | SQL Type | Java default type | alternative type |
|---|---|---|---|
| TB__UNDEFTYPE | void | n.a. | |
| TB__ TINYINT | TINYINT | byte (non-nullable) | java.lang.Byte |
| TB__SMALLINT | SMALLINT | short (non-nullable) | java.lang.Short |
| TB__INTEGER/ TB__IKVALUE | INTEGER | integer (non-nullable) | java.lang.Integer |
| TB__BIGINT | BIGINT | long (non-nullable) | java.lang.Long |
| TB__NUMERIC | NUMERIC | java/math/ BigDecimal | n.a. |
| TB__FLOAT | FLOAT | float (non-nullable) | java.lang.Float |
| TB__DOUBLE/ TB__REAL | DOUBLE | double (non-nullable) | java.lang.Double |
| TB__CHAR/ TB__STRING | CHAR(*) | java.lang.String | n.a. |
| TB__VARCHAR | VARCHAR(*) | java.lang.String | n.a. |
| TB__DATETIME | DATETIME | java.sql. Timestamp | java.sql.Date/ java.sql.Time |
| TB__TIMESPAN | TIMESPAN | n.a. | n.a. |
| TB__BOOL | BOOL | boolean (non-nullable) | java.lang. Boolean |
| TB__BINCHAR | BINCHAR | byte[] | n.a. |
| TB__BLOB | BLOB | n.a. | n.a. |
| TB__BITSS | BITSS | boolean[] | n.a. |
| TB__BITSS2/ TB__UBVALUE | BITSS2 | boolean[] | n.a. |
| TB__NULLTYP | n.a. | n.a. | |
| TB__BLOBNAME | n.a. | n.a. | |
| TB__FILEREF | n.a. | n.a. |
![[Note]](assets/images/note.svg) | Note |
|---|---|
For performance critical functionality it is strongly recommended to rely on default type mappings to scalar, non-nullable java types, because for any mapping to a Java Object type at least one instantiation (= one call to the objects constructor method) is necessary for every Object parameter and for every call to this method. Additionally, after a couple of calls to one of those java functions, asynchronous garbage collection start at some time inside the Virtual Machine, consuming even more CPU resources. |
3.4. Parameter Modes
Additionally to the types STPs allow to use parameters in different modes, namely in IN, OUT, and INOUT mode. IN tells the database system that this is only an input parameter. Thus the value of this parameter is not relevant if the method returns, whereas an OUT parameter is used as result of a returning method. INOUT, of course, is the combination of these two modes.
In some cases, where you do not have influence on the method signature, e.g. a method from the Java runtime library rt.jar or from a jar from a third party vendor, then some additional functionality is required to cover all java specific routine calls. These extensions are Transbase specific and are not defined in the ANSI standard. These exceptions apply only to the first parameter of user-defined functions or stored procedures. For both, functions and procedures, this parameter may use ParameterMode INSTANCE, if you want to call a non-static instance method of the Java class mapped to the SQL parameter, and the parameter is in IN mode, e.g.
int java.lang.String.length()
maps to
FUNCTION strlen(INSTANCE CHAR(*)) RETURNS INTEGER.
Use INSTANCEOUT for procedures if you have an instance method and the first parameter is in INOUT mode.
Note that SQL data types are always mapped to the same java type, regardless of variable length or precision. Thus any length or precision specifications are irrelevant for type mapping. They will be adapted dynamically for Java IN parameters during run-time. However, if you use a user-defined function with OUT or INOUT parameters to insert or update data of a variable length SQL type, the function call should be accompanied by an explicit cast specifying the length of the destination field.
3.5. User-defined Type Mapping
Sometimes you may find it useful to map SQL data types not only to the Java types supplemented by the default or alternative type mapping but to map them directly to your own custom classes. To use this feature, your classes have to bring their own type conversion methods with them, i.e. a constructor method for the source SQL type, respectively its default mapped Java type and a conversion method to map your class back to the original SQL type when your method/function returns. Constructor methods are standard component of classes anyway and converter methods are commonly used throughout Java as means of non-trivial casts.
In order to enable the database system to find these methods, some name conventions are required for converter methods. Conversion to a primitive Java type defaulttype is done by a public instance method defaultypeValue() taking no arguments. E.g. an object of class myclass converts to and from Java type int, which is the default mapping of SQL Integer, has to have a constructor method: public myclass(int i) and a converter method: public int intValue().
Conversion to a class or array type Defaultclass has to provide a
public Defaultclass toDefaultclass()
method. E.g. an object of class myclass that converts to and from class String has to have a constructor method:
public myclass(String aString)
and a converter method:
public String toString().
| Note |
|---|---|
Only default and alternative type mappings are within the scope of the ANSI SQL99 standard. |
3.6. Method Resolution
As name overloading is a fundamental concept in java, name overloading has also been made available for Transbase STPs and UDFs. Thus a java method is identified through its name and full signature. However, this concept collides with the concept of value adaptation known is SQL. Transbase tries to bring these two concepts closer together with a small set of rules for function name resolution:
The first resolution attempt uses function name, type (function or procedure) and parameter arity only. If resolution succeeds without type information then value adaptation is performed on the parameter values.
An explicit CAST on a parameter is never overridden.
If the first resolution attempt (rules 1 and 2) was ambiguous, a second attempt using the exact parameter types will commence.
If resolution is still ambiguous, then the method is not determinable.
This concept will allow you to write ad-hoc queries without worrying about the actual signature of a function too much, e.g. it is legal to call function double sqrt(double) with all sorts of scalar expressions as parameter. On the other hand, an application developer should always keep Rule 4 in mind. If at some point in time a second function numeric sqrt(numeric) should be added, e.g. for higher accuracy, then a call sqrt(integer) will fail because of Rule 4 and thus an application might stop working as new methods introducing name collisions are added to the database. A cautious application developer will avoid this problem by using Rules 2 and 3, i.e. by explicitly casting any parameter to the correct type or by converting to the exact signature on the client side. This should be no problem, since UDFs and STPs are typically developed especially for one particular schema where parameter types are defined by column types, and type safety is guaranteed.
3.7. Null Values
Usually function parameters in Java, such as objects or arrays, are passed to the function by reference. These types are:
java.lang.String, java.math.BigDecimal, byte[], java.sql.Timestamp, java.sql.Date, java.sql.Time, java.lang.Boolean, java.lang.Byte, java.lang.Short, java.lang.Integer, java.lang.Long, java.lang.Float, java.lang.Double
So these types may represent SQL NULL values by using a Java null reference. But aside from objects and arrays, Java supports a set of primitive data types, namely
boolean, byte, short, int, long, float, and double.
In Java these parameters are always passed by value. Thus, for these types there exists no representation of SQL NULL values. An attempt to call a UDF with a NULL value for such an scalar parameter will raise an error condition. When you code Java methods specifically for use in SQL, you will probably tend to specify Java parameter data types that are the nullable Java data types. You may, however, also want to use Java methods in SQL that were not coded for use in SQL, and that are more likely to specify Java parameter data types that are the scalar (non-nullable) Java data types.
You can call such functions in contexts where null values will occur by invoking them conditionally, e.g. in case expressions, e.g.:
SELECT CASE WHEN attr IS NOT NULL
THEN myFunc(attr)
ELSE NULL
END FROM table;
You can also make such case expressions implicit, by specifying
the returns null on null input option in the create function
statement, see Create Procedure Statement .
Both approaches are equivalent.
3.8. Security Manager
A Security Manager is installed by Transbase in the context where user-defined methods are executed.
This Security Manager acts very much like Security Managers installed in Web Browsers executing untrusted code in Applets loaded over the network. The Security Manager denies all user-defined classes any access to sockets, threads, file system, system commands, interpreter commands, package access, system properties, networking and graphical output in windows. Additionally the installed ClassLoader denies loading of all java classes providing access to graphical output, sound, etc, i.e. the java.awt classes. This is done to enforce system security and to prevent that system resources are wasted for classes that are apparently of no use in the Transbase server environment. However, it is possible to lower the security policies of the by configuring the installed Security Manager. Refer to the ALTER EXTERNAL OPTION Statement for more details.
3.9. Class Loading
This subsection will give you insight in the mechanism how Transbase finds and retrieves classes. Transbase employs a special ClassLoader to load classes for UDFs and STPs. Unlike a normal ClassLoader that loads classes mostly from the local file system by parsing the CLASSPATH environment variable, the Transbase ClassLoader has three main sources for its classes, checked consecutively by a simple fall-back mechanism.
The first source is the database system itself, where classes can be added with the CREATE EXTERNAL Statement.
The second source, if not all required classes had been found, is the CLASSPATH option (see ALTER EXTERNAL OPTION ). This option is very similar to the CLASSPATH variable mentioned before, but the CLASSPATH variable itself will never be used. This guarantees that only the database administrator can set this option to trusted directories and files. These files can then be protected by carefully setting the privileges on the file system, e.g. to read only. This procedure should prevent replacement of classes used by the database system without knowledge of the database administrator.
The third source for still unresolved class names are the jar files in a directory defined using the JDK option (see ALTER EXTERNAL OPTION ). These jars, too, should be subject to a careful setting of file system privileges.
If a class cannot be found in any of these locations, then a ClassNotFound exception will be thrown by the ClassLoader. Classname resolution during the compilation process works exactly to same way.
Usually you will not have to worry about this mechanism very much if you do not use duplicate class names. But if you have a class myClass.class available in your database and another myClass.class in a jar file, in your CLASSPATH option, then the class from the jar will never be loaded.
3.10. Pattern for a Stored Procedure
In Transbase, stored procedures usually are implemented according to the a simple pattern. A stored procedure is always a public, static method.
The return type of a stored procedure is always void.
For OUT and INOUT parameters the corresponding parameter has be of an array type of the mapped java type, e.g. an INOUT parameter of SQL type INTEGER is either an int[] using default mapping or java.lang.Integer[] using alternative mapping. This array will only contain one element. In case of an INOUT parameter, it holds the input parameter, in case of an out parameter it will initialized with null. The stored procedure is then responsible to supply a new valid value for output or a null value.
If the stored procedure uses SQL, then a JDBC connection to the current database context can be acquired by opening a JDBC default connection, i.e.
Class.forName("transbase.jdbc.Driver");
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
So the body of a stored procedure executing an update query and returning a success or error indicator in a supplied boolean OUT-parameter could look like this
#import java.sql.*
public class myClass1
{
public static void myProc1(String updatequery, boolean[] success)
throws SQLException, ClassNotFoundException
{
Class.forName("transbase.jdbc.Driver");
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
Statement stmt;
ResultSet rs;
try
{
rs=stmt.executeUpdate(updatequery);
}
catch (SQLException e)
{
success[0]=false;
return;
}
success[0]=true;
return;
}
}
3.11. Parameter Values
OUT parameters are never instantiated before a method is called. Any OUT parameter will be passed as null reference to a stored procedure, thus you always have to create an instance before you assign a value, otherwise your procedure will throw a NullPointerException. This is also true for array types such as byte[] or boolean[] as shown in the following
public static void bitss_procedure(boolean[][] a)
{
a[0]=new byte[1]; //instantiation required
a[0][0]=0xFF;
}
whereas
public static void string_procedure(String[] a)
{
a[0]="Hello World from Java!";
}
is legal code, because here an implicit instantiation of String a[0] takes place.
INOUT parameters of fixed length, precision, or range, such as CHAR, BINCHAR, BITSS, NUMERIC, DATETIME, and TIMESPAN must not return a value of greater length, precision, or range as OUT value, than the corresponding IN value yields. This restriction shall provide for type safety and to ensure that the host variable storing the IN-value is also capable of storing the OUT-value. If you know that there is more space required for the return value, then you can explicitly cast your INOUT parameter to provide a higher or the highest possible space for a return value.
CREATE string_procedure(INOUT aString CHAR(*)) EXTERNAL NAME "myClass.stringProc";
CALL string_procedure('Hello World!');
may return any legal CHAR(12) value in aString.
CALL string_procedure('Hello World!' CAST CHAR(200));
may return any CHAR(200) value.
CALL string_procedure('Hello World!' CAST CHAR(*));
may return any CHAR(*) value up to the max. possible length (MAXSTRINGSIZE).
OUT parameters always provide enough space for the max. possible OUT-value of that type.
3.12. Pattern for a User-Defined Value Function
Similar to a stored procedure, there is also an implementation pattern for Value UDFs. The return type of an user-defined value function is generally one of the default java types as defined in the default mapping (TypeMapping). Usually a UDF is a java routine declared static. Since UDFs do not allow for INOUT and OUT parameters, all types allowed by Type Mapping may be used, but arrays are not allowed. A default JDBC connection can be acquired by
Class.forName("transbase.jdbc.Driver");
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
#import java.sql.*
public class myClass2
{
public static boolean myProc2(String updatequery)
{
try
{
Class.forName("transbase.jdbc.Driver");
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
}
catch (ClassNotFoundException e)
{
return false;
}
Statement stmt;
try
{
stmt.executeUpdate(updatequery);
}
catch (SQLException e)
{
return false;
}
return true;
}
}
Additionally to static methods the Transbase extension for Java offers the possibility to use instance methods. However, instance methods do not perform as well as static methods do, because of the additional overhead for creating and destroying the instance. Thus it is recommended to use instance methods with care and only if it is inevitable, if you do not have influence on the signature, e.g. for methods from rt.jar or any other third party jar. With instance methods the first SQL parameter of a value function has to be in parameter mode INSTANCE or INSTANCEOUT. This parameter will then be used by the Transbase system to construct the java object by calling the parameter classes' standard constructor method. This parameter will then of course not appear in the java methods signature, but it is available through its this object self reference, respectively its value by calling this.value(). The return value of the function is set that way too, in case of an INSTANCEOUT parameter. An example is provided in Create Function Statement .
Of course arbitrary instance methods can be called from a static context by writing a wrapper method, see 'JavaWrappers' .
3.13. Pattern for a User-Defined Table Function
Transbase offers the possibility to write user-defined functions returning tables. These so-called table functions may be used anywhere where conventional tables may be used, i.e. in the SQL statements FROM clause. Table functions use a set of IN parameters to construct an object instance that then will return the data rows by successive calls to one of this object's instance methods. To generate this behavior with Java object various conventions are necessary:
The Java class used as table function must have
one public constructor method taking all IN parameters of this method. This method is only invoked once for a pass through a table.
one public non-static method returning boolean and having the OUT-signature defined by the output-table and no IN-parameters. The returned value is true if the OUT-parameters hold valid results or false if no more rows are delivered.
it has be guaranteed that the method returns false after a finite number of evaluations.
The programmer may also supply a public void finalizer() method for cleaning up before the instance is destroyed. Keep in mind that finalizers always run with NO SQL access indication, and therefore no use of SQL is allowed here.
| Note |
|---|---|
If the table function takes part in a nested loop join, then constructor and finalizer will be called several times, once for each loop. Due to the asynchronous nature of Java's garbage collection, that calls the finalizer method immediately before collecting the object, it is recommended to relocate the cleaning up routines from the finalizer to the instance method, just before it returns false for the first time. So it is always guaranteed that used Connections, Statements, ResultSets, or other resources are closed BEFORE the next loop is invocated. If you join two table function, keep in mind that you have to carefully balance opened resources, since both functions concurrently use limited resources such as cursors for open queries (i.e. ResultSets). |
| Note |
|---|---|
Name resolution for table functions uses only the function name and the IN parameter list. |
The following example illustrates this by implementing a hierarchical depth first search in a table defined by
CREATE TABLE hierarchy (id INTEGER NOT NULL, parent INTEGER)
and populated by
INSERT INTO hierarchy TABLE (( 1,NULL), ( 2, 1),( 3, 2), ( 4, 2), ( 5, 4), ( 6, 4), ( 7, 1),( 8, 7), ( 9, 1), (10, 9), (11,10),(12, 9));
and returning records of root and all sons in depth first sort order. The function itself is published to the SQL system by this DDL statement (cp. Create Function Statement )
CREATE FUNCTION dfs(id INTEGER)
RETURNS TABLE (id INTEGER, parent INTEGER)
READS SQL DATA
EXTERNAL NAME "DFS(Integer).dfs(int[],Integer[])";
import java.sql.*;
import transbase.tbx.TBConst;
public class DFS { ResultSet[] rs;
PreparedStatement[] ps;
Connection con;
int usedRS;
public DFS()
throws SQLException, ClassNotFoundException
{
rs=new ResultSet[TBConst.MAX_QUERY_CNT];
ps=new PreparedStatement[TBConst.MAX_QUERY_CNT];
usedRS=0;
Class.forName("transbase.jdbc.Driver");
con = DriverManager.getConnection("jdbc:default:connection");
ps[usedRS] = con.prepareStatement("SELECT id,parent FROM" +
" hierarchy WHERE parent IS NULL");
rs[usedRS]=ps[usedRS].executeQuery();
}
public DFS(Integer root)
throws SQLException, ClassNotFoundException
{
rs=new ResultSet[TBConst.MAX_QUERY_CNT];
ps=new PreparedStatement[TBConst.MAX_QUERY_CNT];
usedRS=0;
Class.forName("transbase.jdbc.Driver");
con = DriverManager.getConnection("jdbc:default:connection");
ps[usedRS] = con.prepareStatement("SELECT id,parent FROM" +
" hierarchy WHERE id=?");
ps[usedRS].setInt(1,root.intValue());
rs[usedRS]=ps[usedRS].executeQuery();
}
public boolean dfs(int[] id, Integer[] parent)
throws SQLException
{
if(rs[usedRS].next())
{
id[0]=rs[usedRS].getInt(1);
if(rs[usedRS].getInt(2)==0 && rs[usedRS].wasNull())
parent[0]=null;
else
parent[0]=new Integer(rs[usedRS].getInt(2));
ps[++usedRS] = con.prepareStatement("SELECT id,parent FROM" +
" hierarchy WHERE parent=?");
ps[usedRS].setInt(1,rs[usedRS-1].getInt(1));
rs[usedRS]=ps[usedRS].executeQuery();
return true;
}
else
{
if(usedRS==0)
{
if(!con.isClosed())
con.close();
return false;
}
else
{
ps[usedRS==].close();
return dfs(id,parent);
}
}
}
Finally the function can be called by typing
SELECT id, parent FROM FUNCTION dfs(2);
3.14. Pattern for Generic Table Functions
In addition to table functions where the structure of a returned table is defined in the CREATE FUNCTION statement, generic table functions can return arbitrary tables. However, this flexibility comes at the expense of some performance.
In Generic Table Functions a Java object instance is created using a set of IN parameters. The structure of the output table is determined dynamically. A Java class used as Generic Table Function must extend the abstract class transbase.generic.Reader. This means it must implement the following interface, which are also called in this order:
First the public constructor method is invoked, taking all IN parameters of this method. This method is invoked twice for one pass through a table. The first call comes from the DBMS SQL compiler in order to determine the table structure.
Then the method public int getColumnCount() is called by the DBMS SQL compiler to retrieve the number of columns. Now, for every column the method public int getColumnType(int column) is invoked to get column type codes as defined in java.sql.Types. Finally the name of each column is queried by calling public String getColumnName(int column). These three methods are to work exactly as their counterparts defined in the java.sql.ResultSetMetaData interface.
Now public void close() is called to conclude the SQL compiler run. Here the class should release all resources, as there is no guarantee that the query is executed later (e.g. in case of an abandoned PreparedStatement).
If the query is executed, the public constructor is called a second time with identical input parameters as the first time to create a new instance.
Then public boolean next(Object[][] arr) is called once for every returned record. It's purpose is to fill it's OUT parameter arr with the next output record. The function returns true if the OUT parameters hold valid results or false if no more rows are delivered. Note that arr is an array with as many entries as appointed by the getColumnCount call. Each entry holds another array of one single element. Each of these arrays is of the Java Object type that corresponds best to the type code returned by getColumnType, e.g. Integer[] for java.sql.Types.INTEGER. Also note that scalar Java types are always 'boxed' in their corresponding class objects, i.e. Integer[] not int[], so NULL values can be represented.
A final call to the public void close() method ends the flow of control.
The following example illustrates the use of Generic Table Functions on an ArrayReader class that returns data from several private arrays. The ArrayReader has one boolean IN parameter showall which arranges that only the first or both columns are returned.
import java.sql.SQLException;
public class ArrayReader
extends transbase.generic.Reader
{
private static String[] text_arr = {"a","b","c"};
private static int[] numb_arr = {1,2,3};
private boolean showall;
private int rowpos=0;
public ArrayReader(boolean showall)
{
this.showall=showall;
}
public int getColumnCount()
throws SQLException
{
if(showall)
return 2;
else
return 1;
}
public int getColumnType(int col)
throws SQLException
{
if(col==1)
return java.sql.Types.CHAR;
else if(showall && col==2)
return java.sql.Types.INTEGER;
else throw new SQLException("Column index " + col + " out of bounds");
}
public String getColumnName(int col)
throws SQLException
{
if(col==1)
return "text";
else if(showall && col==2)
return "numb";
else throw new SQLException("Column index " + col + " out of bounds");
}
public void close()
throws SQLException
{ }
public boolean next(Object[][] o)
throws SQLException
{
if(rowpos==text_arr.length)
return false;
o[0][0]=text_arr[rowpos];
if(showall)
o[1][0]=new Integer(numb_arr[rowpos]);
rowpos++;
return true;
}
}
The function is published by using the following DDL statement (cp. 'CreateFunctionStatement')
CREATE FUNCTION ReadArray(showall bool)
RETURNS TABLE
EXTERNAL NAME "ArrayReader.next"
and invoked with
SELECT * FROM FUNCTION ReadArray(FALSE)
The next example is a SimpleJDBCReader that is capable of querying any JDBC data source using an arbitrary SQL query and makes the data available to the Transbase DBMS. Note that the corresponding JDBC Driver has to be registered before execution. Refer to the java.sql.DriverManager documentation for an overview on how to register various drivers. Additionally the used driver has to be available in the system's CLASSPATH option (see 'AlterExternalOption' ).
![[Tip]](assets/images/tip.svg) | Tip |
|---|---|
A more sophisticated version of this handy Generic Table Function is available as built-in JDBCReader in every Transbase database where Generic Table Functions are supported. |
import java.sql.*;
public class SimpleJDBCReader
extends transbase.generic.Reader
{
static
{
try
{
Class.forName("transbase.jdbc.Driver");
}
catch (Exception e)
{ }
}
private Connection connection;
private PreparedStatement pstatement;
private ResultSet result = null;
public SimpleJDBCReader(String url, String user, String pwd, String sql)
throws SQLException
{
connection = DriverManager.getConnection(url, user, pwd);
pstatement = connection.prepareStatement(sql);
}
public int getColumnType(int column)
throws SQLException
{
return pstatement.getMetaData().getColumnType(column);
}
public int getColumnCount()
throws SQLException
{
return pstatement.getMetaData().getColumnCount();
}
public String getColumnName(int column)
throws SQLException
{
return pstatement.getMetaData().getColumnName(column);
}
public final void close()
throws SQLException
{
connection.close();
}
public final boolean next(Object[][] o)
throws SQLException
{
if (result -- null)
result = pstatement.executeQuery();
if (result.next())
{
for (int i = o.length - 1; i >= 0; i==)
o[i][0] = result.getObject(i + 1);
return true;
}
else
return false;
}
}
The built-in JDBCReader is called with:
SELECT * FROM FUNCTION JDBCReader( 'jdbc:transbase://hostname:2024/dbname', 'user','passwd','select * from mytable')
The following steps show the necessary configuration for using third-party JDBC drivers to be used by the JDBCReader.
1. Add the third-party driver to the JRE's CLASSPATH. Make sure that the file is accessible for the Transbase service. Note that the CLASSPATH points to the JAR file, not only to the directory:
ALTER EXTERNAL OPTION CLASSPATH "/usr/lib/java/acmesql.jar"
2. Make sure the driver registers with the system's JDBC driver manager by providing the driver's fully qualified class name
ALTER EXTERNAL OPTION JAVA "-Djdbc.drivers=com.acme.jdbc.Driver"
3. Allow the driver to access network resources. Possibly other permissions are also required:
ALTER EXTERNAL OPTION JAVAPERMISSIONS "java.net.NetPermission"
Now the third-party database may be accessed by calling the JDBCReader using the appropriate connection URL.
3.15. Static Variables
Although java static methods can be contained in Java classes that have static variables, and in Java static methods can do both, reference and set static variables, it is strongly recommended not to use static variables at all, since that may lead to unpredictable results in a certain combination of circumstances due to the Transbase system architecture. If you intend that users share global variables concurrently across connections, then you should store this variables in a table rather than declaring them static. This behavior is consistent with the ANSI standard.
3.16. Java Wrappers
There are cases when you will find it impossible to use a certain Java method as a STP or UDF because their signature is not mappable to an SQL type signature. A common example for such methods are main(String[] args) methods. In such cases you can still write wrapper methods that have a mappable signature themselves and simple call the method you wanted to call in first place. An example for a wrapper for a main method would be:
public static void callmain(String a, String b, String c)
{
String args[] = {a,b,c};
return someClass.main(args);
}
3.17. Remote Method Debugging
It is often desirable to test and debug new STPs and UDFs directly in an database environment, namely remote method debugging. In order to have the JVM in Transbase running in debug mode, the JVM has to be started that way. Unfortunately the Java Native Interface used by Transbase to communicate with the JVM is still incomplete, and shutting down and restarting a JVM or starting a second instance of the JVM is not possible even with the newest JDK release. Thus the way to remote method debugging is a little bumpy. You have to tell Transbase that you want to debug methods before the JVM is started. This is done with a special call to a stored procedure:
CALL Debugger();
This call will always return an error code, either 16718 (JVM_DEBUG_ON) if you successfully switched to debug mode or 16719 (JVM_DEBUG_OFF) if the JVM is already running and switching to debugging is impossible or if there is something wrong with your debugging configuration (see 'AlterExternalOption' ). If you successfully switched to debugging mode, then the server process associated to your connection will go into suspend mode without any further notice, as soon as you call the first Java based external method. Now you can attach to the server process using an implementation of jdb, e.g. any JDE providing remote debugging capabilities. As soon as this debugging connection is complete, the Transbase server will continue executing Java code. Every time it comes across a breakpoint, it will again suspend itself and pass control over to the Java debugger and you can step though your code.
| Note |
|---|---|
The workaround using 'Call Debugger();' is to be considered temporary and will be removed as soon as a new JVM release provides the required functionality. |
3.18. Evaluating Debug Output
Usually, when Transbase runs in server mode, no output is written, i.e. to stdout or stderr. Thus those ports are blocked for a JVM running in the same address space, too. As a consequence calls to System.out or System.err in Java have no effect, and any other output written by the JVM, e.g. output generated by the '-verbose' or '-Xrunhprof' options of the JVM, is suppressed. However it is often desirable to output some information for debugging. This can be achieved by setting the corresponding database "Redirect JVM output" (-rjo) parameter with the tbadmin tool.
If redirection is on, then all output generated by the JVM during one database connection is written to one temporary file in the databases "Scratch" directory. The file names consist of the four characters of the username that generated that output and are consecutively numbered. That files are updated each time the user calls a java method. Note that all output is buffered before it is written into the file, so a complete protocol is usually not available before the method returns. However, the buffer is flushed whenever a New-Line-character ('\n') is found in the stream, so you can force flushing by using System.out.println() or System.err.println().
If redirection is off, all output will be discarded.
4. Implementation Guide - Native Libraries
4.1. Limitations
In contrast to the framework for STPs from Java, there are a number limitations to the framework for functions from native libraries at the moment.
SQL queries from the libraries to the local Transbase instance are not supported.
Stored Procedures are not supported.
Table Functions are not fully supported. Only generic table functions are available.
4.2. Compiling and Linking
As already stated in the Getting Started Section there are a number of requirements to consider:
parameters are always passed by reference, never by value
make sure your functions are properly exported
compile as resource library, i.e. without entry point
A list of examples on how to compile and link on various platforms can also be found there .
4.3. Pattern for Generic Table Functions
Just like Generic Table Functions written in Java have to implement a predefined interface, their native library counterparts have to export a related interface. Routines belonging to one set of interface functions all have the same user-defined prefix.
int <prefix>Open(void** cctx, void** sctx, int taid, int cons, ...);
int <prefix>Next(void* sctx, void*** result, int* eod);
int <prefix>GetColumnCount(void* sctx);
char* <prefix>GetColumnName(void* sctx, int column);
int <prefix>GetColumnType(void* sctx, int column);
int <prefix>GetLobPart(void* cctx,Blob *b,Uint4 len,Uint4 offset,
char* buf,Uint4* read);
void <prefix>Close(void* sctx);
void <prefix>Disconnect(void* cctx);
void <prefix>GetLastError(void* ctx, char* errmsg, size_t size,
int* errno);
The following represents an optional interface extension if additional initialization and/or cleanup routines are required.
void TbExtLoad(void); void TbExtUnload(void);
All calls to a table instance implemented as Generic Table Function refer to the same Context structs/objects (sctx for statement context and cctx for connection context). These are allocated once in the Open() routine using a freely definable set of input parameters (e.g. connection string, user name, password). Additional parameters for transaction id taid and consistency cons are compulsory. As results, query and connection contexts are passed to the DBMS which retains them and passes them as first parameter to every subsequent call to routines referring to this table. Finally the statement context is freed in a call to Close() and the connection context is closed by calling Disconnect. The structure of the output table is determined by the DBMS dynamically via routines interface, which are called in this order:
First the Open() routine is invoked, taking all IN parameters of this method. This method is invoked twice for one pass through a table. The first call comes from the DBMS SQL compiler in order to determine the table structure.
Then the method GetColumnCount() is called by the DBMS SQL compiler to retrieve the number of columns. Now, for every column the method GetColumnType() is invoked to get column type codes as defined in tci.h. Finally the name of each column is queried by calling GetColumnName().
Now Close() is called to conclude the SQL compiler run. Here the Table Function should release all resources, including the statement context structure, as there is no guarantee that the query is executed later (e.g. in case of an abandoned stored query).
If the query is executed, Open() is called a second time with identical input parameters as the first time to create a new instance of the statement context. The returned connection context should be identical to the one returned in first call. This resembles connection pooling and guarantees transaction isolation. In particular, the table function should always reuse connections were possible, i.e. when input parameters (including taid and cons) permit that.
Then the iterator function Next() is called once for every returned record. It's purpose is to fill it's OUT parameter result with the next output record. This should then point to an array of GetColumnCount() pointers to Transbase data types as defined in tci.h. An SQL NULL is represented by setting this pointer to NULL. If one of the columns returns Blob data, then the corresponding pointer points to a Blob structure (as defined in tci.h). Make sure that the size component is set correctly and set the btype (blob type) component to a unique non-zero value for this blob type. Do not overwrite the connid and btype components. The remainder of the structure may be used freely to identify this particular Blob. It is encouraged to store a pointer to whatever structure is required to identify a Blob at Blob->blobadr. Next() sets the output parameter eod (end-of-data) to false if the OUT parameters hold valid results or true if no more rows are delivered. It has be guaranteed that the method returns false after a finite number of evaluations.
If one of the columns returned Blob data, any number of GetLobPart() calls might follow to retrieve the contents of the Blobs identified via the input parameter b of type Blob*. This call has to copy len bytes of the contents of the Blob beginning at position offset to the provided buffer buf of len bytes. read returns the number of bytes transferred.
The routines Open(), Next() , and GetLobPart() return error codes. All other functions return results. A return code different from zero means an error occurred during the last call. Then the DBMS used GetLastError() to retrieve a human readable error description from connection or statement context, dependent upon where the error occurred.
A call to the Close() method ends the flow of control for this statement and releases all resources associated to the statement context and the statement context itself. Note that after this point calls to GetLobPart() must still be possible.
As the local transaction ends a final call to Disconnect releases all remaining resources, closes the connection and release the connection context itself. In particular resources connected to Blob data are to be released at this point.
5. Built-In Functions
Transbase provides the full set of Open /Open CLI math functions as native built-in functions. These functions are located in a native dynamic library that is shipped with every Transbase distribution. All functions are readily available in any Transbase database. No Java runtime environment is necessary. Refer to this table for further information.
Table 5.1. Built-In Native Functions
| External Name | SQL Name | SQL Parameter List | SQL ReturnType |
|---|---|---|---|
| builtin.acos | acos | DOUBLE | DOUBLE |
| builtin.asin | asin | DOUBLE | DOUBLE |
| builtin.atan | atan | DOUBLE | DOUBLE |
| builtin.atan2 | atan2 | DOUBLE, DOUBLE | DOUBLE |
| builtin.ceil | ceil | DOUBLE | DOUBLE |
| builtin.cos | cos | DOUBLE | DOUBLE |
| builtin.cot | cot | DOUBLE | DOUBLE |
| builtin.degrees | degrees | DOUBLE | DOUBLE |
| builtin.exp | exp | DOUBLE | DOUBLE |
| builtin.floor | floor | DOUBLE | DOUBLE |
| builtin.log | log | DOUBLE | DOUBLE |
| builtin.log10 | log10 | DOUBLE | DOUBLE |
| builtin.pi | pi | DOUBLE | |
| builtin.pow | pow | DOUBLE, DOUBLE | DOUBLE |
| builtin.rad | rad | DOUBLE | DOUBLE |
| builtin.random | random | INTEGER | DOUBLE |
| builtin.sin | sin | DOUBLE | DOUBLE |
| builtin.sqrt | sqrt | DOUBLE | DOUBLE |
| builtin.tan | tan | DOUBLE | DOUBLE |
| builtin.ntruncate | ntruncate | DOUBLE, INTEGER | DOUBLE |
| builtin.add_month | add_month | DATETIME, INTEGER | DATETIME |
| builtin.sub_month | sub_month | DATETIME, INTEGER | DATETIME |
Table 5.2. Built-In Java Functions
| ExternalName | SQLName | Instance | SQL Parameter List | SQL Return Type |
|---|---|---|---|---|
| java.lang. Double.isInfinite | isInfinite | NO | DOUBLE | BOOL |
| java.lang. Double.isNaN | isNaN | NO | DOUBLE | BOOL |
| java.lang. Integer.decode | decodeInt | YES | CHAR(*) | INTEGER |
| java.lang. Integer.parseInt | parseInt | NO | CHAR(*), INTEGER | INTEGER |
| java.lang. Integer.toBinaryString | toBinaryString | NO | CHAR(*), INTEGER | INTEGER |
| java.lang. Integer.toHexString | toHexString | NO | CHAR(*), INTEGER | INTEGER |
| java.lang. Integer.toOctalString | toOctalString | NO | CHAR(*), INTEGER | INTEGER |
| java.lang. Integer.toString | toString | NO | INTEGER, INTEGER | CHAR(*) |
| java.math. BigDecimal.max | getMax | YES | NUMERIC, NUMERIC | NUMERIC |
| java.math. BigDecimal.min | getMin | YES | NUMERIC, NUMERIC | NUMERIC |
| java.math. BigDecimal.movePointToLeft | movePointToLeft | YES | NUMERIC, INTEGER | NUMERIC |
| java.math. BigDecimal.movePointToRight | movePointToRight | YES | NUMERIC, INTEGER | NUMERIC |
| java.math. BigDecimal.scale | getScale | YES | NUMERIC | INTEGER |
| java.math. BigDecimal.signum | signum | YES | NUMERIC | INTEGER |
| java.lang. Math.rint | rint | NO | DOUBLE | DOUBLE |
| java.lang. String.compareTo | compareTo | YES | CHAR(*), CHAR(*) | INTEGER |
| java.lang. String.concat | concat | YES | CHAR(*), CHAR(*) | CHAR(*) |
| java.lang. String.endsWith | endsWith | YES | CHAR(*), CHAR(*) | BOOL |
| java.lang. String.equalsIgnoreCase | equalsIgnoreCase | YES | CHAR(*), CHAR(*) | BOOL |
| java.lang. String.indexOf | indexOf | YES | CHAR(*), CHAR(*) | INTEGER |
| java.lang. String.indexOf | indexOf | YES | CHAR(*), CHAR(*), INTEGER | INTEGER |
| java.lang. String.lastIndexOf | lastIndexOf | YES | CHAR(*), CHAR(*) | INTEGER |
| java.lang. String.lastIndexOf | lastindexOf | YES | CHAR(*), CHAR(*), INTEGER | INTEGER |
| java.lang. String.length | getLength | YES | CHAR(*) | BOOL |
| java.lang. String.regionMatches | regionMatches | YES | CHAR(*), INTEGER, CHAR(*), INTEGER, INTEGER | BOOL |
| java.lang. String.regionMatches | regionMatches | YES | CHAR(*), BOOL, INTEGER, CHAR(*), INTEGER, INTEGER | BOOL |
| java.lang. String.startsWith | startsWith | YES | CHAR(*), CHAR(*) | BOOL |
| java.lang. String.startsWith | startsWith | YES | CHAR(*), CHAR(*), INTEGER | BOOL |
The availability of the full Java runtime environment, residing in the Java runtime library rt.jar and shipped with each SDK and JRE installation, suggests to make functionality of this standard library available to the Transbase system. Refer to this table for a quick reference of available built-in Java functions. Since these classes and their methods are subject to changes through the various SDK and JRE versions, the database administrator has full control over these methods, allowing to remove and alter existing methods or add new methods derived from classes on the rt.jar. See Create Procedure Statement for further information.
6. Database Client APIs
Since user-defined functions are always part of a select, insert, update, or delete statement, there is no separate call interface required for that SQL extension. Stored procedures however represent a new class of SQL statements and extensions to the APIs become necessary.
6.1. JDBC
JDBC provides the CallableStatement class for calling stored procedures. For database compatibility call statements may use the JDBC escape syntax. Two different notations are possible:
{ call procedure_name[(?, ...)] }
This statement will by passed unmodified to the database, apart from the removal of the curly braces. If the optional parameter list is missing then "()" will be appended.
{ ? = call procedure_name[(?, ...)] }
Since Transbase Stored Procedures never return a value, this statement will be modifies in such way that the result parameter (ordinal number: 1) will be moved to the parameter list as first parameter. This change will create a statement corresponding to the first syntax while preserving the original parameter numbering.
The following example illustrated how to call a stored procedure from a JDBC client. This procedure accepts three parameters in parameter modes IN, INOUT, OUT. The parameter types are specified by literals. Note that the value of the third (OUT) parameter has no influence on the result, only the type specification will be used for name resolution.
Class.forName("transbase.jdbc.Driver");
String dburl = "jdbc:transbase://MYHOST:4444/DB";
String uname = "user";
String pw = "passwd";
Connection conn = DriverManager.getConnection(url, uname, pw);
// call a Procedure with parameter modes: IN, INOUT, OUT
java.sql.CallableStatement cstmt;
cstmt = conn.prepareCall("{call proc('some string',2,3.3)}");
cstmt.executeQuery();
int i = cstmt.getInt(1);
BigDecimal bd = cstmt.getBigDecimal(2);
The same statement using parameters:
// call a Procedure with parameter modes: IN, INOUT, OUT
java.sql.CallableStatement cstmt;
cstmt = conn.prepareCall("{call proc(?,?,?)}");
cstmt.setString(1, "some string");
cstmt.setInt(2, 2);
cstmt.registerOutParameter(3, java.sql.Types.DECIMAL);
cstmt.executeQuery();
int i = stmt.getInt(1);
BigDecimal bd = stmt.getBigDecimal(2);
| Note |
|---|---|
All OUT parameters have to registered before the query is executed. Otherwise an SQLException will be thrown. |
6.2. TCI
On the TCI interface a CALL statement to a stored procedure that does not use dynamic parameters, especially no OUT or INOUT parameters, is executed using a TCIExcecuteDirect statement. The following to examples show a query executions where all parameters are in IN mode, i.e. no result is returned, and a query where OUT values are returned as a result.
Error e; char *statement = "call proc(1,2,3)"; /* all modes are IN */ if(e=TCIExecuteDirect(resiud, statement, 1, 0)) goto err0;
Otherwise, when parameters are required, stored procedures behave very much like stored queries, where parameter type and value have to be specified for all input parameters, namely IN and INOUT parameters, whereas only type information is required for mere OUT parameters. The following example illustrates this behavior.
Error e;
Integer params[3];
Integer actsizes[3];
Short nullindicators[3];
TCIStatement *stmtid;
TCIResultset *resuid;
char *statement = "call proc(?,?,?)"; /* modes are in, inout, out */
/* connect, login and begin transaction omitted */
if(e=TCIPrepareStatement(stmtid,statement))
goto err0;
params[0]=1; actsizes[0]=4; nullindicators[0]=0;
params[1]=2; actsizes[1]=4; nullindicators[1]=0;
params[2]=0; actsizes[2]=4; nullindicators[2]=-1;
if( e=TCISetData(resuid, 1, ¶ms[0], actsizes[0], TCI_C_INT4,
nullindicators[0]) )
goto err0;
if( e=TCISetData(resiud, 2, ¶ms[1], actsizes[1], TCI_C_INT4,
nullindicators[1]) )
goto err0;
if( e=TCIExecute(resiud, 1, 0) )
goto err0;
if( e=TCIGetData(resiud, 1, ¶ms[1], 4, &actsizes[1], TCI_C_INT4,
&nullindicator[0]) )
goto err0;
if( e=TCIGetData(resiud, 2, ¶ms[2], 4, &actsizes[2], TCI_C_INT4,
&nullindicator[1]) )
goto err0;
![[Important]](assets/images/important.svg) | Important |
|---|---|
IN parameters are not included in the result, thus if a procedure call has parameters in IN and OUT, one has to be aware of the fact that the index of a parameter in the result set may differ from its index as a parameter, e.g. the INOUT parameter in the above example has parameter index 2 but result record index 1. |
Appendix A. Data Dictionary Extensions
A.1. The sysexternal Table
The sysexternal table contains a single entry for each external resource stored in the database.
Table A.1. sysexternal
| Column | Type |
|---|---|
| rkey | INTEGER |
| rtype | TINYINT |
| owner | INTEGER |
| enabled | BOOL |
| debug | BOOL |
| lastmodified | DATETIME[YY:SS] |
| rname | CHAR(*) |
| rblob | BLOB |
| sblob | BLOB |
| comment | CHAR(*) |
rkey: Identifies a resource, if rkey>=0. rkey<0 is reserved for storing external options, i.e. options for the virtual machine.
rtype: Denotes whether this resource is e.g. of type java class, java source or java archive (jar).
owner: Denotes the owner of the resource by the userid. To retrieve the owner's username, join the tables sysuser and sysexternal.
enabled: Boolean flag, true if this resource is enabled for execution.
debug: Boolean flag, true if this resource is to be executed in debug mode.
lastmodified: Timestamp of the last modification of this resource, i.e. creation, modification or recompilation.
rname: Unique name identifying this resource, i.e. classname.
rblob: The actual external resource in binary format.
sblob: The external resource in source code representation. If corresponding rblob is missing, it will be generated by compilation of sblob on execution.
comment: Textual description of this resource's functionality, version, author, and other additional information.
A.2. The sysexternalmethod Table
The sysexternalmethod table contains a single entry for each method of each external resource in this database.
Table A.2. sysexternalmethod
| Column | Type |
|---|---|
| rkey | INTEGER |
| mkey | INTEGER |
| enabled | BOOL |
| callonnull | BOOL |
| determ | BOOL |
| checkexcept | BOOL |
| ownerrights | BOOL |
| sqlaccess | TINYINT |
| dynasets | TINYINT |
| resulttype | CHAR(1) |
| sqlname | CHAR(*) |
| sqlsig | CHAR(*) |
| extsig | CHAR(*) |
| parammodes | CHAR(*) |
| paramnames | CHAR(*) |
| sqltabsig | CHAR(*) |
| exttabsig | CHAR(*) |
| methodname | CHAR(*) |
| comment | CHAR(*) |
| schema | INTEGER |
rkey: Identifies the external resource to which the entry belongs. The name of the resource can be retrieved via a join between sysexternal and sysexternalmethod on the fields rkey.
mkey: Unique numeric key that identifies a method
enabled: Boolean flag, true if this method is enabled for execution
callonnull: Boolean flag, true if this method is to be called if any of the in parameters is NULL.
determ: Boolean flag, true if this method is deterministic, i.e. for a given set of argument values, the procedure or function returns the same values for out and inout parameters and function result.
checkexcept: Boolean flag, true if explicit exception checking is to be performed of the method returns a NULL result
ownerrights: Boolean flag, true if this method is to be executed with the privileges of it's owner.
sqlaccess: Access indication of the routine. Can be NO SQL (=0), CONTAINS SQL (=1), READS SQL DATA (=2), or MODIFIES SQL DATA (=3). Boolean flag, true if this method contains SQL statements. statements.
dynasets: Number of result sets this method returns at most (reserved for future versions)
resulttype: Character denoting of which SQL type the result of this function is. Stored procedures always return 'V' for void.
sqlname: Name by which this method is referenced from SQL statements
sqlsig: Single char coded SQL type signature.
extsig: JNI style signature string for java methods.
parammodes: Defines mode for each parameter, i.e. 'I' for IN, 'O' for OUT, 'B' for both (Inout), 'M' for INSTANCE, 'R' for INSTANCEOUT.
paramnames: Comma separated list of names for out parameters
sqltabsig: Single char coded SQL type signature for TABLE FUNCTION.
exttabsig: JNI style signature string for TABLE FUNCTION.
methodname: Name of the actual method in the external resource
comment: Textual description of this method's functionality
schema: The schema id of this method
A.3. The sysexternalmethodparam Table
The sysexternalmethodparam table contains a single entry for each parameter of each method of each external resource in this database.
Table A.3. sysexternalmethodparam
| Column | Type |
|---|---|
| rkey | INTEGER |
| mkey | INTEGER |
| param_pos | INTEGER |
| sql_sig | VARCHAR(*) |
| external_sig | VARCHAR(*) |
| param_mode | VARCHAR(*) |
rkey: Identifies the external resource to which the entry belongs. The name of the resource can be retrieved via a join between sysexternal and sysexternalmethod on the fields rkey.
mkey: Unique numeric key that identifies a method
param_pos: The parameter position starting with 1.
sql_sig: The SQL signature, i.e. the SQL data type.
external_sig: The external signature, i.e. the data type in the host language the procedure is written in.
param_mode: The parameter mode, i.e. IN, OUT or INOUT.
A.4. The sysexternalpriv Table
Describes the privileges applying to external resources or methods of the database.
Table A.4. sysexternalpriv
| Column | Type |
|---|---|
| grantee | INTEGER |
| rkey | INTEGER |
| mkey | INTEGER |
| grantor | INTEGER |
| use_priv | CHAR(1) |
grantee: Describes whom this privilege is granted
rkey: Identifies the external resource that is granted.
mkey: Identifies the method of resource rkey to be granted. If mkey is NULL then all methods of resource rkey are granted.
grantor: Id of the user granting the privileges
use_priv: Describes the granted privileged. 'Y' means that grantee has USAGE privilege and 'G' means that grantee may also grant USAGE to other users.
Grantor and grantee refer to field userid of table sysuser. rkey refers to field rkey of table sysexternal and mkey refers to mkey of sysexternalmethod.
Appendix B. Abbreviations
In this manual we use a set of common abbreviations.
Table B.1. Abbreviations
| JRE: | Java Runtime Environment |
|---|---|
| JDB: | Java Debugger |
| JDE: | Java Developer Environment |
| JNI: | Java Native Interface |
| JVM: | Java Virtual Machine |
| PSM: | Persistent Stored Module |
| SDK: | Standard Developer Kit |
| STP : | Stored Procedure |
| TBJRE: | Transbase Java Runtime Environment |
| UDF: | User-Defined Function |