create procedure (SQLJ)

Description

Creates a SQLJ stored procedure by adding a SQL wrapper to a Java static method. Can accept user-supplied parameters and return result sets and output parameters.

For syntax and usage information about the Transact-SQL command for creating procedures, see create procedure.

Syntax

create procedure [owner.]sql_procedure_name
	 ([[in | out | inout] sql_parameter_name 
		sql_datatype [(length) | 
		 (precision[, scale])]
		[=default]
	...])
	[, [in | out | inout] sql_parameter_name
		sql_datatype [(length) | 
		 (precision[, scale])]]
		[=default]
	...])
	[modifies sql data] 
	[dynamic result sets integer] 
	[deterministic | not deterministic] 
	language java 
	parameter style java
	external name 'java_method_name 
		[([java_datatype[, java_datatype
		...]])]'

Parameters

sql_procedure_name: is the Transact-SQL name of the procedure, which must conform to the rules for identifiers and cannot be a variable. Specify the owner’s name to create another procedure of the same name owned by a different user in the current database. The default value for owner is the current user.

in | out | inout: specifies the mode of the listed parameter. in indicates an input parameter; out indicates an output parameter; and inout indicates a parameter that is both an input and an output parameter. The default mode is in.

sql_parameter_name: is the name of an argument to the procedure. The value of each input parameter is supplied when the procedure is executed. Parameters are optional; a SQLJ stored procedure need not take arguments.

Parameter names must conform to the rules for identifiers. If the value of a parameter contains nonalphanumeric characters, it must be enclosed in quotes. This includes object names qualified by a database name or owner name, since they include a period. If the value of the parameter begins with a numeric character, it also must be enclosed in quotes.

sql_datatype [(length) | (precision [, scale])]: is the Transact-SQL datatype of the parameter.

sql_datatype is the SQL procedure signature.

default: defines a default value for the procedure’s parameter. If a default is defined, you can execute the procedure without a parameter value. The default must be a constant. It can include wildcard characters (%, _, [ ], and ^) if the procedure uses the parameter name with the keyword like.

The default can be NULL. The procedure definition can specify some action to be taken if the parameter value is NULL.

modifies sql data: indicates that the Java method invokes SQL operations, reads, and modifies SQL data in the database. This is the default and only implementation. It is included for syntactic compatibility with the ANSI standard.

dynamic result sets integer: specifies that the Java method can return SQL result sets. integer specifies the maximum number of result sets the method can return. This value is implementation-defined.

deterministic | not deterministic: this syntax is supported for compatibility with other SQLJ-compliant vendors.

language java: specifies that the external routine is written in Java. This is a required clause for SQLJ stored procedures.

parameter style java: specifies that the parameters passed to the external routine at runtime are Java parameters. This is a required clause for SQLJ stored procedures.

external: indicates that create procedure defines a SQL name for an external routine written in a programming language other than SQL.

name: specifies the name of the external routine (Java method). The specified name is a character-string literal and must be enclosed in single quotes:

'java_method_name [ java_datatype [{, java_datatype} ...]]'

java_method_name: specifies the name of the external Java method.

java_datatype: specifies a Java datatype that is mappable or result-set mappable. This is the Java method signature.

Examples

Example 1

Creates the SQLJ procedure java_multiply, which multiplies two integers and returns an integer.

create procedure java_multiply (param1 integer,
      param2 integer, out result integer)
    language java parameter style java
    external name 'MathProc.multiply'

Example 2

Returns values that are always larger than 10:

create procedure my_max (a int = 10, b int = 10)
language java parameter style java
external name 'java.lang.Math.max'

exec my_max
 (return status = 10)

exec my_max 8
 (return status = 10)

See also the examples for Transact-SQL create procedure.

Usage

The SQLJ create procedure syntax differs from the Transact-SQL create procedure syntax for compatibility with the SQLJ ANSI standard. Adaptive Server executes each type of stored procedure in the same way.
To avoid seeing unexpected results due to changes in settings, run set rowcount 0 as your initial statement before executing create procedure. The scope of set is limited to just your create procedure command, and resets to your previous setting once the procedure exits.
You can include a maximum of 31 in, inout, and out parameters in a create procedure statement.
To comply with the ANSI standard, do not precede parameter names with the @ sign. When executing a SQLJ stored procedure from isql or other non-Java client, however, you must precede parameter names with the @ sign, which preserves the naming order.

Permissions

When you create a procedure, no permission checks are made on objects, such as tables and views, that are referenced by the procedure. Therefore, you can create a procedure successfully even though you do not have access to its objects. All permission checks occur when a user executes the procedure.

When the procedure is executed, permission checks on objects depend upon whether the procedure and all referenced objects are owned by the same user.

If the procedure’s objects are owned by different users, the invoker must have been granted direct access to the objects. For example, if the procedure performs a select from a table that the user cannot access, the procedure execution fails.
If a procedure and its objects are owned by the same user, special rules apply. The invoker automatically has “implicit permission” to access the procedure’s objects even though the invoker could not access them directly. Without having to grant users direct access to your tables and views, you can give them restricted access with a stored procedure. In this way, a stored procedure can be a security mechanism. For example, invokers of the procedure might be able to access only certain rows and columns of your table.

The following describes permission checks for create procedure (also when creating an extended procedure) that differ based on your granular permissions settings.

Granular permissions enabled	When granular permissions is enabled, you must have the create procedure privilege. You must have the create any procedure privilege to run create procedure for other users.
Granular permissions disabled	With granular permissions disabled, you must be the database owner or have the create procedure privilege.