CREATE PARAMETERS Statement

Syntax

CREATE PARAMETERS '(' parameter_name DEFAULT "initial_value" ,...] ')';

Substitutable Fields

parameter_name

A unique identifier (name) for the parameter.

initial_value

The default, or initial, value for the parameter. The value must be enclosed in quotation marks ("initial_value") regardless of its type. String types must also be enclosed in escaped quotation marks ("\"initial_value\""). Multiple parameters can be declared in one CREATE PARAMETERS statement. In this case, separate each parameter declaration with a comma. The parentheses are a required part of the syntax.

Discussion

In StreamSQL, a parameter is a named character string that can be referenced at other places in the same StreamSQL file through its assigned name. Enclose the initial, default value assigned to the parameter in quotation marks, as in the following example:

CREATE PARAMETERS (parameter_name DEFAULT "value");

If the parameter is a string type, then value must be enclosed in escaped quotation marks:

CREATE PARAMETERS (parameter_name DEFAULT "\"value\"");

Note

A CREATE PARAMETERS statement, if used, must be the first statement in a StreamSQL file.

By following these conventions, parameters of all types can be referenced using the same syntax, which is:

${parameter_name}

A parameter in StreamSQL can represent a value, a constant, a literal, or a character string that can be used in a StreamSQL expression or predicate, or as a StreamSQL statement.

Notes

Throughout StreamBase, in both EventFlow and StreamSQL programs, there is a single concept of a parameter, and a single ${param} syntax to resolve parameters to their values. Parameter values can be set at various points in an application or configuration file, each having different scope and order of precedence. For StreamSQL programs:

  • Parameters declared in a StreamSQL file with CREATE PARAMETERS can be referenced anywhere in the same file, and can be used in expressions as well as in other parts of StreamSQL statements (as described below).

  • Parameters declared in the server's configuration file with <operator-parameter> elements have a global scope for the current sbd instance.

If a StreamSQL program calls another StreamSQL program as a module, it can pass parameter values to the called module by passing a parameter-list argument with the APPLY MODULE statement.

The order of precedence for resolving parameters is described in Order of Precedence in Resolving Values.

The following parameter declaration:

CREATE PARAMETERS (max_value DEFAULT "10000");

can be used in an expression:

SELECT (if tuple_field < ${max_value}) 
  then tuple_field else ${max_value} AS alias_name ...

or in a predicate:

SELECT ...
  WHERE tuple_field < ${max_value} ...

and a string parameter type could be used as follows:

CREATE PARAMETERS (preface DEFAULT "\"Hello \"");
CREATE INPUT STREAM in (name string);

SELECT ${preface} + name AS greeting
  FROM in => CREATE OUTPUT STREAM out;

This example emits a tuple with a string field named greeting that contains the content Hello name.

Additionally, a parameter can contain another StreamSQL statement, which can then be used in the StreamSQL document. For example, consider the following module:

CREATE PARAMETERS (in_stream DEFAULT "CREATE INPUT STREAM",
  in_stream_name DEFAULT "in",
  out_stream_name DEFAULT "out");

  ${in_stream} ${in_stream_name} (name string, age int);
  SELECT * FROM ${in_stream_name} => 
    CREATE OUTPUT STREAM ${out_stream_name};

Note that CREATE INPUT STREAM, in, and out are treated as character strings (enclosed in quotation marks), not as string literals (enclosed in escaped quotation marks).

When a module that uses parameters is referenced from another module, the referencing module can override the default (initial) parameter values by including a parenthesized list of parameter_name=value entries in the APPLY clause. As in the CREATE PARAMETER statement, value must be enclosed in quotation marks ("value") and strings must also be enclosed in escaped quotation marks ("\"value\""). In the following example, the APPLY statement is used to reference the module1.ssql StreamSQL module. Note that only one of the parameters (in_stream_name) was overridden from the referencing module.

CREATE INPUT STREAM i (name string, age int);
CREATE OUTPUT STREAM o;

APPLY MODULE "module1.ssql" (in_stream_name = "myInStream")
  FROM i INTO o;