Function of Java External Procedures
When you write the code for an external procedure definition, you indicate that its language is Java by specifying LANGUAGE JAVA in the required LANGUAGE clause (see "LANGUAGE Clause" in General Usage Guidelines: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)).
Java external procedures run SQL code using the standard JDBC driver interface. Because the Java external procedure is running on the database and is invoked from within a logged on session, its connection to the database is by means of a default connection named jdbc:default:connection.
A Java program can create a separate connection to another database, or to the same database (by means of another session to be logged onto). If an external Java procedure does this, its actions can create an undetectable deadlock.
- You must compile the Java source code outside the database.
The external Java language routine must be written using the JDBC standard for coding embedded SQL statements in a Java program.
The resulting class or classes (the byte code) must be placed in a JAR file.
- You must then register the JAR file with the database.
- To do this, you call an external procedure named SQLJ.Install_Jar.
- The system creates the Java external procedure using its EXTERNAL NAME clause, which specifies the registered JAR file and its associated Java class.
You can access the Java routine in the same manner as any external procedure.
Rules for Creating a Java External Procedure
- The user who creates a Java external procedure must be the same user who registered its associated JAR file by calling SQLJ.Install_Jar (see the following section, "JAR Files" and SQLJ Database: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)). Therefore, the jar_name you specify when you create the procedure must refer to a JAR file that you registered. If any other user registered the JAR file you specify, the CREATE/REPLACE PROCEDURE request aborts and returns an error to the requestor.
- The containing database for any Java external procedure you create must match the default database at the time you register its JAR file using SQLJ.Install_Jar.
JAR Files
A JAR (Java ARchive) file is a collection of Java classes in a zip file format. The classes (byte compiled Java) are referenced when an external Java routine is created using the EXTERNAL NAME clause of CREATE PROCEDURE (External Form).
You create JAR files outside the database. Before you can reference a class, you must register it and copy it into the SQL environment. After installing a JAR in the database, you cannot change its content, you can only delete or replace the JAR file.
A JAR file is available only to the user who installs them with a call to the SQLJ.Install_Jar external procedure (see SQLJ Database: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)). The system creates a directory on the platform for each database that contains a JAR. Other users or databases cannot access a JAR file for a Java external routine. However, the user or database who installs the JAR can grant another user or database access to the Java external routine by using the same privileges used for C and C++ external routines. See GRANT (SQL Form).
To make sure all users who must run Java external procedures can access them, install all JAR files and create all Java external procedures in the same database and grant access to them to all users who must run them
SQLSTATE Values for Java External Procedures
A Java external procedure can raise a java.lang.SQLException code with an exception number and message that are returned to the database and used to set the SQLSTATE and error message. Java exceptions thrown during the execution of a Java external procedure using SQL are often handled within Java. When this occurs, the exceptions do not affect SQL processing. Only those Java exceptions that are not handled within Java are seen in the SQL environment as SQL exception conditions.
When this occurs, the procedure returns an exception number and message to the database, which the system uses to set the SQLSTATE value and error message that it returns to the requestor.
The range for valid SQLSTATE SQLException codes is 38U00 - 38U99, inclusive. The Teradata implementation also supports predefined error cases that have specific codes defined by the ANSI SQL standard (such as non-valid JAR name). See Mapping Database Error Messages to SQLSTATE Values.