Using Java-SQL capabilities, you can install Java classes in the database and then invoke those methods from a client or from within the SQL system. You can also invoke Java static (class) methods in another way—as SQLJ stored procedures.
SQLJ stored procedures:
Can return result sets and/or output parameters to the client
Behave exactly as Transact-SQL stored procedures when executed
Can be called from the client using ODBC, isql, or JDBC
Can be called within the server from other stored procedures or native Adaptive Server JDBC
The end user need not know whether the procedure being called is a SQLJ stored procedure or a Transact-SQL stored procedure. They are both invoked in the same way.
The SQLJ syntax for create procedure is:
create procedure [owner.]sql_procedure_name ([[ in | out | inout ] sql_parameter_name sql_datatype [( length) | (precision[, scale])] [, [ in | out | inout ] sql_parameter_name sql_datatype [( length) | (precision[, scale]) ]] ...]) [modifies sql data] [dynamic result sets integer] [deterministic | not deterministic] language java parameter style java external name 'java_method_name [([java_datatype[, java_datatype ...]])]'
To comply with the ANSI standard, the SQLJ create
procedure command syntax is different from syntax used
to create Sybase Transact-SQL stored procedures.
Refer to the Reference Manual for a detailed description of each keyword and option in this command.
When creating SQLJ stored procedures:
The SQL procedure signature is the SQL datatype sql_datatype of each procedure parameter.
When creating a SQLJ stored procedure, do not include an @ sign before parameter names. This practise is compliant with the ANSI standard.
Sybase adds an @ sign internally to support parameter name binding. You will see the @ sign when using sp_help to print out information about the SQLJ stored procedure.
When creating a SQLJ stored procedure, you must include the parentheses that surround the sql_parameter_name and sql_datatype information—even if you do not include that information.
For example:
create procedure sqlj_sproc () language java parameter style java external name ‘SQLJExamples.method1’
You can include the keywords modifies sql data to indicate that the method invokes SQL operations and reads and modifies SQL data. This is the default value.
You must include the dynamic result sets integer option when result sets are to be returned to the calling environment. Use the integer variable to specify the maximum number of result sets expected.
You can include the keywords deterministic or not deterministic for compatibility with the SQLJ standard. However, Adaptive Server does not make use of this option.
You must include the language java parameter and style java keywords, which tell Adaptive Server that the external routine is written in Java and the runtime conventions for arguments passed to the external routine are Java conventions.
The external name clause indicates that the external routine is written in Java and identifies the Java method, class, and package name (if any).
The Java method signature specifies the Java datatype java_datatype of each method parameter. The Java method signature is optional. If one is not specified, Adaptive Server infers one from the SQL procedure signature.
Sybase recommends that you include the method signature as this practice handles all datatype translations. See “Mapping Java and SQL datatypes” for more information.
You can define different SQL names for the same Java method using create procedure and then use them in the same way.
