CREATE PROCEDURE

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > CREATE statements >

CREATE PROCEDURE

Previous pageReturn to chapter overviewNext page

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>

  [ NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA ]

  EXTERNAL NAME '<external-name>'

  [ EXTERNAL SECURITY { DEFINER | INVOKER | IMPLEMENTATION DEFINED } ]

  <procedure-param-def> := [IN] <param-name> <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.