Chapter 5: SQLJ Functions and Stored Procedures

SQLJ and Sybase implementation: a comparison

This section describes differences between SQLJ Part 1 standard specifications and the Sybase proprietary implementation for SQLJ stored procedures and functions.

Table 5-2 describes Adaptive Server enhancements to the SQLJ implementation.

**Table 5-2: Sybase enhancements**
Category	SQLJ standard	Sybase implementation
create procedure command	Supports only Java methods that do not return values. The methods must have void return type.	Supports Java methods that allow an integer value return. The methods referenced in create procedure can have either void or integer return types.
create procedure and create function commands	Supports only SQL datatypes in create procedure or create function parameter list.	Supports SQL datatypes and nonprimitive Java datatypes as abstract data types (ADTs).
SQLJ function and SQLJ procedure invocation	Does not support implicit SQL conversion to SQLJ datatypes.	Supports implicit SQL conversion to SQLJ datatypes.
SQLJ functions	Does not allow SQLJ functions to run on remote servers.	Allows SQLJ functions to run on remote servers using Sybase OmniConnect capabilities.
drop procedure and drop function commands	Requires complete command name: drop procedure or drop function.	Supports complete function name and abridged names: drop proc and drop func.

Table 5-3 describes SQLJ standard features not included in the Sybase implementation.

**Table 5-3: SQLJ features not supported**
SQLJ category	SQLJ standard	Sybase implementation
create function command	Allows users to specify the same SQL name for multiple SQLJ functions.	Requires unique names for all stored procedure and functions.
utilities	Supports sqlj.install_jar, sqlj.replace_jar, sqlj.remove_jar, and similar utilities to install, replace, and remove JAR files.	Supports the installjava utility and the remove java Transact-SQL command to perform similar functions.

Table 5-4 describes the SQLJ standard features supported in part by the Sybase implementation.

**Table 5-4: SQLJ features partially supported**
SQLJ category	SQLJ standard	Sybase implementation
create procedure and create function commands	Allows users to install different classes with the same name in the same database if they are in different JAR files.	Requires unique class names in the same database.
create procedure and create function commands	Supports the key words no sql, contains sql, reads sql data, and modifies sql data to specify the SQL operations the Java method can perform.	Supports modifies sql data only.
create procedure command	Supports java.sql.ResultSet and the SQL/OLB iterator declaration.	Supports java.sql.ResultSet only.
drop procedure and drop function commands	Supports the key word restrict, which requires the user to drop all SQL objects (tables, views, and routines) that invoke the procedure or function before dropping the procedure or function.	Does not support the restrict key word and functionality.

Table 5-5 describes the SQLJ implementation-defined features in the Sybase implementation.

**Table 5-5: SQLJ features defined by the implementation**
SQLJ category	SQLJ standard	Sybase implementation
create procedure and create function commands	Supports the deterministic \| not deterministic keywords, which specify whether or not the procedure or function always returns the same values for the out and inout parameters and the function result.	Supports only the syntax for deterministic \| not deterministic, not the functionality.
create procedure and create function commands	The validation of the mapping between the SQL signature and the Java method signature can be performed either when the create command is executed or when the procedure or function is invoked. The implementation defines when the validation is performed.	If the referenced class has been changed, performs all validations when the create command is executed, which enables faster execution.
create procedure and create function commands	Can specify the create procedure or create function commands within deployment descriptor files or as SQL DDL statements. The implementation defines which way (or ways) the commands are supported.	Supports create procedure and create function as SQL DDL statements outside of deployment descriptors.
Invoking SQLJ routines	When a Java method executes a SQL statement, any exception conditions are raised in the Java method as a Java exception of the Exception.sqlException subclass. The effect of the exception condition is defined by the implementation.	Follows the rules for Adaptive Server JDBC.
Invoking SQLJ routines	The implementation defines whether a Java method called using a SQL name executes with the privileges of the user who created the procedure or function or those of the invoker of the procedure or function.	SQLJ procedures and functions inherit the security features of SQL stored procedures and Java-SQL functions, respectively.
drop procedure and drop function commands	Can specify the drop procedure or drop function commands within deployment descriptor files or as SQL DDL statements. The implementation defines which way (or ways) the commands are supported.	Supports create procedure and create function as SQL DDL statements outside of deployment descriptors.

View this book as PDF