CREATE PROCEDURE Statement (Java UDF)

Creates an interface to an external Java table UDF.

CREATE PROCEDURE Statement reference information for external procedures is located in a separate topic. CREATE PROCEDURE Statement reference information for table UDFs is located in a separate topic.

If your query references Sybase IQ tables, note that different syntax and parameters apply compared to a query that references only catalog store tables.

Java table UDFs are only supported in the FROM clause.

Syntax

For a query referencing at least one Sybase IQ table:

CREATE[ OR REPLACE ] PROCEDURE 
[ owner.]procedure-name ( [ parameter, …] )
[ RESULT (result-column, ...)]
[ SQL SECURITY { INVOKER | DEFINER } ] 
EXTERNAL NAME ‘java-call’  [ LANGUAGE Java ] }

For a query referencing catalog store tables only:

CREATE[ OR REPLACE ] PROCEDURE 
[ owner.]procedure-name ( [ parameter, …] )
[  RESULT (result-column, ...)]
| NO RESULT SET
[ DYNAMIC RESULT SETS integer-expression ]
[ SQL SECURITY { INVOKER | DEFINER } ]  
EXTERNAL NAME ‘java-call’ [ LANGUAGE Java ] }

Parameters

parameter – For a query referencing at least one Sybase IQ table:
[ IN parameter_mode parameter-name data-type [ DEFAULT expression ]

For a query referencing catalog store tables only:

[ IN | OUT | INOUT ] parameter_mode parameter-name data-type [ DEFAULT expression ]
result-column – column-name data-type
JAVA – [ ALLOW | DISALLOW SERVER SIDE REQUESTS ]
java-call – '[ package-name.]class-name.method-name method-signature'

Usage

For Java table functions, exactly one result set is allowed. If the Java table functions are joined with a Sybase IQ table or if a column from a Sybase IQ table is an argument to the Java table function then only one result set is supported.

If the Java table function is the only item in the FROM clause then N number of result sets are allowed.

JAVA: [ ALLOW | DISALLOW SERVER SIDE REQUESTS ]:

DISALLOW is the default.

ALLOW indicates that server-side connections are allowed.

Note: Do not specify ALLOW unless necessary. A setting of ALLOW slows down certain types of Sybase IQ table joins. If you change a procedure definition from ALLOW to DISALLOW, or vice-versa, the change will not be recognized until you make a new connection.

Do not use UDFs with both ALLOW SERVER SIDE REQUESTS and DISALLOW SERVER SIDE REQUESTS in the same query.

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—The Transact-SQL CREATE PROCEDURE statement is different.
SQLJ—The syntax extensions for Java result sets are as specified in the proposed SQLJ1 standard.

Permissions

Must have RESOURCE authority, unless creating a temporary procedure. Users with DBA authority can create UDFs for other users by specifying an owner. Users must have DBA authority to create an external UDFs or to create an external UDF for another user