Informix Guide to SQL: Syntax SQL Statements

Use the EXECUTE PROCEDURE statement to execute a user-defined procedure.

The EXECUTE PROCEDURE statement invokes the named user-defined procedure and specifies its arguments.

In Dynamic Server, for backward compatibility, you can use the EXECUTE PROCEDURE statement to execute an SPL function that was created with the CREATE PROCEDURE statement.

In Enterprise Decision Server, use the EXECUTE PROCEDURE statement to execute any SPL routine. Enterprise Decision Server does not support the EXECUTE FUNCTION statement.

In ESQL/C, if the EXECUTE PROCEDURE statement returns more than one row, it must be enclosed within an SPL FOREACH loop or accessed through a cursor.

Use the INTO clause to specify where to store the values that the SPL function returns.

If an SPL function returns more than one value, the values are returned into the list of variables in the order in which you specify them. If an SPL function returns more than one row or a collection data type, you must access the rows or collection elements with a cursor.

You cannot prepare an EXECUTE PROCEDURE statement that has an INTO clause. For more information, see Alternatives to Preparing an EXECUTE FUNCTION...INTO Statement.

Dynamic routine-name specification simplifies the writing of an SPL routine that calls another SPL routine whose name is not known until runtime. To specify the name of an SPL routine in the EXECUTE PROCEDURE statement, instead of listing the explicit name of an SPL routine, you can use an SPL variable to hold the routine name.

If the SPL variable names an SPL routine that returns a value (an SPL function), include the INTO clause of EXECUTE PROCEDURE to specify a receiving variable (or variables) to hold the value (or values) that the SPL function returns.

For more information on how to execute SPL procedures dynamically, see the Informix Guide to SQL: Tutorial.

The EXECUTE PROCEDURE statement returns an error under the following conditions:

• It has more arguments than the called procedure expects.
• One or more arguments are missing and do not have default values. In this case the arguments are initialized to the value of UNDEFINED.
• The fully qualified procedure name or the signature is not unique.
• No procedure with the specified name or signature is found.

Use the SQLJ Driver built-in procedures for one of the following tasks:

• to install, replace, or remove a set of Java classes
• to specify a path for Java class resolution for Java classes that are included in a Jar file
• to map or remove the mapping between a user-defined type and the Java type to which it corresponds
s

The SQLJ built-in procedures are stored in the sysprocedures catalog table. They are grouped under the sqlj schema.

Tip: For any Java static method, the first built-in procedure that you execute must be the install_jar() procedure. You must install the jar file before you can create a UDR or map a user-defined data type to a Java type. Similarly, you cannot use any of the other SQLJ built-in procedures until you have used install_jar().

sqlj.install_jar

Use the install_jar() procedure to install a Java jar file in the current database and assign it a jar identifier.

Element	Purpose	Restrictions	Syntax
deploy	Integer that causes the procedure to search for deployment descriptor files in the jar file	None.	Literal Number, p. 4-237
jar_file	URL of the jar file that contains the UDR written in Java	The maximum length of the URL is 255 bytes.	Quoted String, p. 4-260

For example, consider a Java class Chemistry which contains the following static method explosiveReaction():

public static int explosiveReaction(int ingredient);

Suppose that the Chemistry class resides in the following jar file on the server computer:

/students/data/Courses.jar 

You can install all classes in the Courses.jar jar file in the current database with the following call to the install_jar() procedure:

EXECUTE PROCEDURE 
	sqlj.install_jar("file://students/data/Courses.jar",
	"course_jar")

After you define jar ID in the database, you can use that jar ID when you create and execute a UDR written in Java.

When you specify a nonzero number for the third argument, the database server searches through any included deployment descriptor files. For example, you might want to included descriptor files that include SQL statements to register and grant privileges on UDRs in the jar file.

Use the replace_jar() procedure to replace a previously installed jar file with a new version. When you use this syntax, you provide only the new jar file and assign it to the jar ID for which you want to replace the file.

If you attempt to replace a jar file that is referenced by one or more UDRs, the database server generates an error. You must drop the referencing UDRs before replacing the jar file.

For example, the following call replaces the Courses.jar file, which had previously been installed for the course_jar identifier, with the Subjects.jar file:

EXECUTE PROCEDURE 

	sqlj.replace_jar("file://students/data/Subjects.jar",
	"course_jar")

Before you replace the Course.jar file, you must drop the user-defined function sql_explosive_reaction() with the DROP FUNCTION (or DROP ROUTINE) statement.

Use the remove_jar() procedure to remove a previously installed jar file from the current database.

If you attempt to remove a jar file that is referenced by one or more UDRs, the database server generates an error. You must drop the referencing UDRs before replacing the jar file.

For example, the following SQL statements remove the jar file associated with the course_jar jar id:

DROP FUNCTION sql_explosive_reaction;
EXECUTE PROCEDURE sqlj.remove_jar("course_jar")

When you specify a nonzero number for the second argument, the database server searches through any included deployment descriptor files. For example, you might want to include descriptor files that include SQL statements that revoke privileges on the UDRs in the associated jar file and to drop them from the database.

Use the alter_java_path() procedure to specify the jar-file path to use when the routine manager resolves related Java classes for the jar file of a UDR written in Java.

Element	Purpose	Restrictions	Syntax
class_id	Name of the Java class that contains the method to implement the UDR	The Java class must exist in the jar file that jar_id identifies. The fully qualified identifier of package_id.class_id must not exceed 255 bytes.	Name must conform to language-specific rules for Java identifiers.
package_id	Name of the package that contains the Java class	The fully qualified identifier of package_id.class_id must not exceed 255 bytes.	Name must conform to language-specific rules for Java identifiers.

The jar IDs that you specify, (the jar ID for which you are altering the jar-file path and the resolution jar ID) must have been installed with the sqlj.install_jar procedure.

When you invoke a UDR written in Java, the routine manager attempts to load the Java class in which the UDR resides. At this time, it must resolve the references that this Java class makes to other Java classes. The three types of such class references are:

1. References to Java classes that the JVPCLASSPATH configuration parameter specifies (such as Java system classes like java.util.Vector)
2. References to classes which are in the same jar file as the UDR
3. References to classes which are outside the jar file that contains the UDR

The routine manager implicitly resolves classes of type 1 and 2 in the preceding list. However, to resolve type 3 references, the routine manager examines all the jar files in the jar-file path that the latest call to alter_java_path() has specified. The routine manager throws an exception if it cannot resolve a class reference.

The routine manager checks the jar-file path for class references after it performs the implicit type 1 and type 2 resolutions. If you want a Java class to be loaded from the jar file that the jar-file path specifies, make sure the Java class is not present in the JVPCLASSPATH configuration parameter. Otherwise, the system loader picks up that Java class first, which might result in a different class being loaded than what you expect.

Suppose that the install_jar() procedure and the CREATE FUNCTION statement have been executed as described in preceding sections. The following SQL statement invokes sql_explosive_reaction() function in the course_jar jar file:

EXECUTE PROCEDURE alter_java_path("course_jar", 
	"(professor/*, prof_jar)");
EXECUTE FUNCTION sql_explosive_reaction(10000)

The routine manager attempts to load the class Chemistry. It uses the path that the call to alter_java_path() specifies to resolve any class references. Therefore, it checks the classes that are in the professor package of the jar file that prof_jar identifies.

Use the setUDTExtName() procedure to define the mapping between a user-defined data type and a Java class.

To look up the Java class for a user-defined data type, the database server searches in the jar-file path, which the alter_java_path() procedure has specified. For more information on the jar-file path, seesqlj.alter_java_path.

On the client side, the driver looks into the CLASSPATH path on the client environment before it asks the database server for the name of the Java class.

The setUDTExtName procedure is an extension to the SQLJ:SQL Routines using the Java Programming Language specification.

Use the remove_jar() procedure to remove the mapping from a user-defined data type to a Java class.

Element	Purpose	Restrictions	Syntax
data_type	Name of user-defined type for which you want to remove the mapping	The data type must exist.	Identifier, p. 4-205

This procedure removes the SQL-to-Java mapping, and consequently removes any cached copy of the Java class from database server shared memory.

The unsetUDTExtName procedure is an extension to the SQLJ:SQL Routines using the Java Programming Language specification.

Related statements: CREATE FUNCTION, CREATE PROCEDURE, EXECUTE FUNCTION, GRANT, CALL, FOREACH, and LET