CREATE PROCEDURE

Procedures are a type of routine invoked explicitly via the SQL CALL statement. Procedures are not associated with a particular data type (they do not return a value), and they cannot be used in SQL row expressions. An SQL-invoked procedure is similar to a C++ free function with void return type.

Syntax

CREATE PROCEDURE <qualified-procedure-name> ( [ <procedure-param-def>,... ] )
LANGUAGE JAVA
[ PARAMETER STYLE JAVA ]
[ [ SPECIFIC <specific-name>
| CONTAINS SQL | READS SQL DATA | 
| --- | --- | MODIFIES SQL DATA ]
EXTERNAL NAME '<external-name>'
[ EXTERNAL SECURITY { DEFINER | INVOKER | IMPLEMENTATION DEFINED } ]
<procedure-param-def> := [IN] <data-type>

Notes:

  • In specifying procedures, LANGUAGE must be specified as JAVA.
  • The optional PARAMETER STYLE, if specified, must be JAVA.
  • The optional specification regarding SQL usage must, if specified, say NO SQL.
  • SPECIFIC, however, can specify a name, which is used as an additional name by which an overloaded routine is known in the catalog.

Using SPECIFIC to specify a name allows a routine to be referenced by that specific name without having to include its full signature.

Limitations

SQLstream only supports IN parameters - INOUT and OUT parameters are not supported.

Example

CREATE PROCEDURE set_java_property(IN name varchar(128),IN val varchar(128))
LANGUAGE JAVA
NO SQL
EXTERNAL NAME 'class com.yourcompany.TestUDR.setSystemProperty';

Assuming a jar in the s-Server classpath with

public static void setSystemProperty(String name, String value)
{
System.setProperty(name, value);
}

Then:

CALL set_java_property("VAR", "VALUE");

would set the “VAR” system property in the JVM of the s-Server. See also the topic Software Development Kit in this guide.