Skip to content

SQL functions

Chapman Flack edited this page Jun 20, 2017 · 4 revisions

Functions in the sqlj schema

install_jar

The install_jar command loads a jarfile from a location appointed by an URL into the SQLJ jar repository. It is an error if a jar with the given name already exists in the repository.

Usage

SELECT sqlj.install_jar(<jar_url>, <jar_name>, <deploy>);

Parameter Description
jar_url The URL that denotes the location of the jar that should be loaded
jar_name This is the name by which this jar can be referenced once it has been loaded
deploy True if the jar should be deployed according to a deployment descriptor, false otherwise

replace_jar

The replace_jar command will replace a loaded jar with another jar. Use it to update already loaded files. It's an error if the jar is not found.

Usage

SELECT sqlj.replace_jar(<jar_url>, <jar_name>, <redeploy>);

Parameter Description
jar_url The URL that denotes the location of the jar that should be loaded.
jar_name The name of the jar to be replaced.
redeploy True if the jar should be undeployed according to the deployment descriptor of the old jar and deployed according to the deployment descriptor of the new jar, false otherwise.

remove_jar

The remove_jar command will drop the jar from the jar repository. Any classpath that references this jar will be updated accordingly. It's an error if the jar is not found.

Usage

SELECT sqlj.remove_jar(<jar_name>, <undeploy>);

Parameter Description
jar_name The name of the jar to be removed.
undeploy True if the jar should be undeployed according to a deployment descriptor, false otherwise.

get_classpath

The get_classpath command will return the classpath that has been defined for the given schema. NULL is returned if the schema has no classpath. It's an error if the given schema does not exist.

Usage

SELECT sqlj.get_classpath(<schema>);

Parameter Description
schema The name of the schema

set_classpath

The set_classpath command will define a classpath for the given schema. A classpath consists of a colon separated list of jar names. It's an error if the given schema does not exist or if one or more jar names references nonexistent jars.

Usage

SELECT sqlj.set_classpath(<schema>, <classpath>);

Parameter Description
schema The name of the schema.
classpath The colon separated list of jar names.

add_type_mapping

The add_type_mapping command installs a mapping between a SQL type and a Java class. Once the mapping is in place, parameters and return values will be mapped accordingly. Please read Mapping an SQL type to a Java class for detailed information.

Usage

SELECT sqlj.add_type_mapping(<sql type>, <java class>);

Parameter Description
sql type The name of the SQL type. The name can be qualified with a schema (namespace). If the schema is omitted, it will be resolved according to the current setting of the search_path.
java class The name of the class. The class must be found in the classpath in effect for the current schema

drop_type_mapping

The drop_type_mapping command removes a mapping between a SQL type and a Java class.

Usage

SELECT sqlj.drop_type_mapping(<sql type>);

Parameter Description
sql type The name of the SQL type. The name can be qualified with a schema (namespace). If the schema is omitted, it will be resolved according to the current setting of the search_path.

Note on jar URLs

The install_jar and replace_jar commands accept a URL (that must be reachable from the server) to a jar file. It is even possible, using the rules for jar URLs, to construct one that refers to a jar file within another jar file. For example:

jar:file:outer.jar!/inner.jar

However, Java's caching of the "outer" jar may frustrate attempts to replace or reload a newer version within the same session.