MTSQL Interpreter

(Formerly, the Arithmetic Plug-in.)

Program ID

MetraPipeline.MTSQLInterpreter.1

Description

The purpose of the arithmetic plug-in is to allow a Systems Integrator to quickly generate business logic that can be added to a MetraTech pipeline. With the arithmetic plug-in, Systems Integrators are not required to directly use the pipeline interface, or even to have the development environment necessary to create COM objects. The business logic is input using a simple language based on Microsoft's T-SQL stored procedure language used in SQL Server (see MTSQL-Supported Language Features for details).

While creating a pipeline, you can add a new arithmetic plug-in using the Arithmetic Plug-in Editor in the Metratech Platform Manager. After creating the plug-in, configure it by specifying the MTSQL procedure definition containing the business logic you want to apply to sessions.

The architecture of the MTSQL interpreter will support the use of the interpreter in a varied collection of computation contexts. However, the primary context in which MTSQL programs will be executed is the MTSQL plug-in. The MTSQL plug-in is an ordinary MetraTech plug-in COM object and is configured and executed in a MetraTech pipeline stage. When an MTSQL plug-in executes, PROCEDURE variables in the MTSQL program will be transparently mapped to like-named session variables within the pipeline that executes the MTSQL plug-in (in fact, the session variables are required have the same name as the PROCEDURE variables except that the leading ampersand is elided). The PROCEDURE will be executed once per session that is passed through the session. All database actions will be executed using the database context configured in the pipeline's dbaccess.xml file.

The MTSQL plug-in is configured by embedding an MTSQL program into the plug-in configuration file (the details of the language are specified in MTSQL-Supported Language Features). Here is a summary:

The MTSQL language is missing some features that one might expect in such a language, such as:

The program will be specified in the <Program> element of the plug-in configuration's <configdata> element. It is recommended that the program contents be included in a CDATA section due to the fact that the MTSQL syntax using reserved XML tokens.

Note: As a best practice, it is suggested that you open your program with <![CDATA[ and close with ]]> (CDATA sections are used in XML to escape blocks of text containing characters which might otherwise be recognized as markup data, such as "<" and ">").

"CREATE PROCEDURE"

MTSQL uses the CREATE PROCEDURE keywords to establish the parameters that are going to be used in an MTSQL "program" (All MTSQL programs must begin with CREATE PROCEDURE). As in T-SQL, each parameter following these keywords needs to begin with an "@" symbol. MTSQL limits the use of the command for this one purpose. Another difference with Microsoft T-SQL is that there is no statement that executes the procedure per se. Rather, the MTSQL plugin implicitly executes the procedure once per session.

Casting

MTSQL allows you to cast between any two data types. For the most part, the conversions behave as you would expect in any programming language. However, there is some unique behavior in MTSQL that is worth mentioning:

CAST from ENUM to VARCHAR

The CAST form and ENUM value to a VARCHAR value has the same effects as calling the MTEnumConfig::GetEnumeratorByID() COM API function.

CAST(expression AS ENUM)

The CAST from a VARCHAR value to an ENUM value has the same effect as calling the MTNameID::GetNameID() COM API function. As such, the value in the VARCHAR variable must be a valid, fully qualified name.

CASTs from all other types proceed by converting the value to an INTEGER and then treating that INTEGER as the enumerated type id of the enumerator in question.

MTSQL Data Types

Important: Unlike T-SQL, MT-SQL does not support automatic conversion between data types.

Supported data types in MTSQL are similar to T-SQL. Standard supported data types from T-SQL are INTEGER, DATETIME, DECIMAL, VARCHAR, and DOUBLE. For these data types, MTSQL differs from T-SQL in only two respects: in MTSQL there is no maximum length of strings for VARCHAR, and for DECIMAL the precision is always 6 and the scale is always 32.

MTSQL includes three data types that are extensions to T-SQL: ENUM, BOOLEAN, and TIME.

ENUM:

MTSQL supports the primitive type ENUM. An ENUM value corresponds to a MetraTech Enumerated Type Object. ENUM types can be used in SET statements, SELECT statements and can be used in equality tests. There are no ENUM literals in MTSQL.

Additionally, ENUM types can be CAST to other MTSQL primitive types and other MetraTech primitive types can be CAST to ENUM. See Casting for more information.

Example:

CREATE PROCEDURE testEnum @in VARCHAR @out ENUM

AS

SET @out = CAST(‘Global/BooleanYN/No’ AS ENUM)

 

BOOLEAN:

MTSQL supports the primitive type BOOLEAN. A BOOLEAN type can take only two values: True and False. BOOLEAN can be used in SET statements, SELECT statements and can be used in equality tests. There are BOOLEAN literals ‘TRUE’ and ‘FALSE’ in the MTSQL language.

Example:

CREATE PROCEDURE testBoolean @in INTEGER @out DECIMAL
  AS
  DECLARE @test BOOLEAN
  SET @test = @in > 199 AND @in < 999
  IF @test = TRUE
     SET @out = 99.88
  ELSE
     SET @out = 887.873

TIME:

MTSQL supports the primitive type TIME. A TIME type stores the number of seconds since January 1, 1970 UTC. This is the same representation as a Unix time_t data structure and is compatible with MetraTech TIME session properties. TIME values can be used in SET statements, SELECT statements and comparison operators. There are no time literals in the MTSQL programming language.

Required Input Properties

None.

Required Output Properties

None.

Configuration Tags

Tag Name

Value Type

Description

program

String

Denotes the beginning and end of an MTSQL procedure.

 

MTSQL-Supported Language Features

The following table shows the language features/descriptions supported in MTSQL (Each category links to a list of supported keywords/operators).

Note on Sources: Many of these definitions are taken from Microsoft's T-SQL reference, Version 8.

Index

Here is a linked, alphabetical index of MTSQL keywords, operators, and data types.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value.

Control-of-Flow Language

Imposes conditions on the execution of SQL statements, encloses a series of SQL statements so that groups of statements can be executed, provides for exiting a series of SQL statements.

Comments

Indicates user-provided annotation of code.

"CREATE PROCEDURE"

In MTSQL, these two keywords are used to establish the parameters that are going to be used in the XML file.

Date and Time Functions

These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value.

Mathematical Functions

These scalar functions perform a calculation, usually based on input values provided as arguments, and return a numeric value.

Statements

MTSQL allows for SELECT INTO database queries and DECLARE, PRINT, and SET statements

String Functions

These scalar functions perform an operation on a string input value and return a string or numeric value.

System Functions

These scalar functions perform operations on and return information about values, objects, and settings.

Arithmetic Operators

Arithmetic operators perform mathematical operations on two expressions of any of the data types of the numeric data type category.

Bitwise Operators

Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category.

Comparison Operators

Comparison operators test relationships between two expressions. Comparison operators can be used on all expressions except expressions of the text.

Logical Operators

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE or FALSE.

Data Types

Note: Unlike T-SQL, MT-SQL does not support automatic conversion between data types.

Data Types

Each column, local variable, expression, and parameter has a related data type, which is an attribute that specifies the type of data (integer, character, money, and so on) that the object can hold.

 

Alphabetical Index of MTSQL Supported Keywords/Data Types

Note: Although most functions and data types operate identically to Microsoft T-SQL, note that there are some minor differences. Check the entries for keywords for details. Some keywords only have valid usage within SELECT statements. These are marked with an asterisk (*).

Symbols

+, -, *, /, %, /*...*/, &, |, ^, ~, =, >, <, >=, <=, <>

M

MAX*, MIN*

A

AND, AS, AVG*

N

NOT

B

BEGIN, BINARY_CHECKSUM*†, BOOLEAN, BREAK

O

OR

C

CASE, CAST, C0NTINUE, CHECKSUM*, CHECKSUM_AGG*†, COUNT*, CREATE PROCEDURE

P

PRINT

D

DATETIME, DECIMAL, DECLARE, DOUBLE

R

RETURN, ROUND

E

ELSE, END, ENUM

S

SELECT INTO, SET, SUM*, STDEV*, STDEVP*†, SUBSTRING

F

FALSE

T

THEN, TIME, TRUE

G

GETDATE, GETUTCDATE, GROUPING*†, GROUP BY*

U

UPPER

I

IF, INTEGER

V

VAR*, VARCHAR, VARP*†

L

LOWER

W

WHEN, WHILE

*These keywords only have valid usage within SELECT INTO statements.

†These keywords are only valid when the system is using a SQL Server database.

Control-of-Flow Language

Keyword

Explanation

BEGIN...END

Encloses a series of MTSQL statements so that a group of MTSQL statements can be executed. BEGIN and END are control-of-flow language keywords. Code Example

BREAK

Exits the innermost WHILE loop. Any statements following the BREAK keyword are ignored. BREAK is often, but not always, activated by an IF test. Code Example

CONTINUE

Restarts a WHILE loop. Any statements after the CONTINUE keyword are ignored. CONTINUE is often, but not always, activated by an IF test. Code Example

IF...ELSE

 

Imposes conditions on the execution of an MTSQL statement. The MTSQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate MTSQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE). The ELSE keyword is also used in statements using the CASE keyword (see below). Code Example

RETURN

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed. Code Example

WHILE

Sets a condition for the repeated execution of an MTSQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords. Code Example

 

Comments

Keyword

Explanation

 /*...*/

Indicates user-provided text. Comments can be inserted on a separate line, nested (-- only) at the end of a SQL command line, or within a SQL statement. The comment is not evaluated by the server. The text between the /* and */ commenting characters is not evaluated by the server.

 --

Indicates user-provided text. Two hyphens standard indicator for comments. Code Example

 

"CREATE PROCEDURE"

Keyword

Explanation

CREATE PROCEDURE... AS

In MTSQL, these two keywords are used to establish the name and parameters that are going to be used in the procedure (All MTSQL programs must begin with CREATE PROCEDURE). As in T-SQL, each parameter following these keywords needs to begin with an "@" symbol. MTSQL limits the use of this command for this one purpose. The call to the XML file essentially "invokes" the procedure. The keyword AS specifies the actions the procedure is to take. Code Example

 

Aggregate Functions

Note: The following are only supported as part of SELECT INTO statements. The usage of these keywords within SQL statements is the same as in T-SQL. Some of these aggregate functions can only be used on SQL Server database.

Note: In SELECT INTO statements, wild cards are permitted.

Keyword

Explanation

AVG

Returns the average of the values in a group. Null values are ignored.

MAX

Returns the maximum value in the expression.

BINARY_CHECKSUM*

Returns the binary checksum value computed over a row of a table or over a list of expressions. BINARY_CHECKSUM can be used to detect changes to a row of a table.

MIN

Returns the minimum value in the expression.

CHECKSUM*

Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indices.

SUM

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

CHECKSUM_AGG*

Returns the checksum of the values in a group. Null values are ignored.

STDEV

Returns the statistical standard deviation of all values in the given expression.

COUNT

Returns the number of items in a group.

STDEVP*

Returns the statistical standard deviation for the population for all values in the given expression.

VAR

Returns the statistical variance of all values in the given expression.

GROUPING*

Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.

Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.

VARP*

Returns the statistical variance for the population for all values in the given expression.

GROUP BY

Divides a table into groups. Groups can consist of column names or results or computed columns.

*These keywords can only be used with SQL Server databases.

Date and Time Functions

Keyword

Explanation

GETDATE

Returns the current system date and time in the SQL standard internal format for datetime values. Code Example

GETUTCDATE

Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running. Code Example

Mathematical Functions

Keyword

Explanation

ROUND

Returns a numeric expression, rounded to the specified length or precision. Round only takes DECIMAL arguments. Code Example

String Functions

Keyword/Symbol

Explanation

LOWER

Returns a character expression after converting uppercase character data to lowercase. Code Example

UPPER

Returns a character expression with lowercase character data converted to uppercase. Code Example

SUBSTRING

Returns part of a character, binary, text, or image expression. For more information about the valid Microsoft® SQL Server™ data types that can be used with this function.

+ (Concatenate)

An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

System Functions

Keyword

Explanation

CASE

Evaluates a list of conditions and returns one of multiple possible result expressions. Case operates identically to how it would in T-SQL, using the additional keywords WHEN, THEN, and ELSE. CASE has two formats:

For a simple CASE function:

  • WHEN: Is the expression returned when the input expression equals when expression evaluates to TRUE, or the boolean expression evaluates to TRUE. The result expression is any valid SQL Server expression.

  • THEN: Is the expression returned when the input expression equals when expression evaluates to TRUE, or the boolean expression evaluates to TRUE. result expression is any valid SQL Server expression.

  • ELSE: Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. The ELSE result expression is any valid SQL Server expression. The data types of the ELSE result expression and any result expression must be the same or must be an implicit conversion.

For a searched CASE function:

  • Evaluates, in the order specified, the boolean expressions for each WHEN clause.

  • Returns a result expression of the first boolean expression that evaluates to TRUE.

  • If no Boolean expression evaluates to TRUE, MTSQL returns the ELSE result expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

  Code Example

CAST

The CAST function converts an expression to a specified datatype. The syntax for the CAST function is CAST (expression AS datatype). Note that unlike MS T-SQL, there are no implicit casts in MTSQL. (see above for more information on casting in MTSQL). Code Example

Arithmetic Operators

Symbol

Explanation

+ (Add)

Adds two numbers. Code Example

Note: This operator is also used for string concatenation. See entry above.

 - (Subtract)

Subtracts two numbers. Code Example

* (Multiply)

Multiplies two expressions (an arithmetic multiplication operator). Code Example

/ (Divide)

Divides one number by another (an arithmetic division operator). Code Example

% (Modulo)

Provides the remainder of one number divided by another. Code Example

Bitwise Operators

Symbol

Explanation

& (Bitwise AND)

Performs a bitwise logical AND operation between two integer values. Code Example

| (Bitwise OR)

Performs a bitwise logical OR operation between two given integer values as translated to binary expressions within MTSQL statements. Code Example

^ (Bitwise Exclusive OR)

Performs a bitwise exclusive OR operation between two given integer values as translated to binary expressions within MTSQL statements. Code Example

~ (Bitwise NOT)

Performs a bitwise logical NOT operation for one given integer value as translated to binary expressions within MTSQL statements. Code Example

Comparison Operators

Symbol

Explanation

= (Equals)

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if both operands are equal; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical. Code Example

> (Greater Than)

Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand has a higher value than the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical. Code Example

< (Less Than)

Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand has a lower value than the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical.

>= (Greater Than or Equal To)

Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand has a higher or equal value than the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical. Code Example

<= (Less Than or Equal To)

Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand has a lower or equal value than the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical.

<> (Not Equal To)

Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical.

Logical Operators

Keyword

Explanation

OR

Combines two conditions. When more than one logical operator is used in a statement, OR operators are evaluated after AND operators. However, you can change the order of evaluation by using parentheses. Code Example

AND

Combines two Boolean expressions and returns TRUE when both expressions are TRUE. When more than one logical operator is used in a statement, AND operators are evaluated first. You can change the order of evaluation by using parentheses. Code Example

NOT

Negates a Boolean input. Code Example

Data Types

Keyword

Explanation

INTEGER

As in T-SQL, this is integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. Code Example

DATETIME

An MTSQL datetime is stored as an 8-byte real value (double), representing a date between January 1, 100 and December 31, 9999, inclusive. The value 2.0 represents January 1, 1900; 3.0 represents January 2, 1900, and so on. Adding 1 to the value increments the date by a day. The fractional part of the value represents the time of day. Therefore, 2.5 represents noon on January 1, 1900; 3.25 represents 6:00 A.M. on January 2, 1900, and so on. Negative numbers represent dates prior to December 30, 1899. Code Example

DECIMAL

Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1. In MTSQL, the precision is always 6 and the scale is always set to 32. Code Example

VARCHAR

"Character varying". Variable-length non-Unicode data. In MTSQL, there is no maximum length of strings. Code Example

DOUBLE

Double precision. Code Example

TIME*

MTSQL support the primitive type TIME. A TIME type stores the number of seconds since January 1, 1970 UTC. This is the same representation as a Unix time_t data structure and is compatible with MetraTech TIME session properties. TIME values can be used in SET statements, SELECT statements and comparison operators. There are no time literals in the MTSQL programming language.

ENUM*

MTSQL supports the primitive type ENUM. An ENUM value corresponds to a MetraTech Enumerated Type Object. ENUM types can be used in SET statements, SELECT statements and can be used in equality tests. There are no ENUM literals in MTSQL. For more on this data type, see the discussion of MTSQL data types above. Code Example

BOOLEAN*

MTSQL supports the primitive type BOOLEAN. A BOOLEAN type can take only two values: True and False. ENUM types can be used in SET statements, SELECT statements and can be used in equality tests. There are BOOLEAN literals ‘TRUE’ and ‘FALSE’ in the MTSQL language. For more on this data type, see the discussion of MTSQL data types above. Code Example

*Indicates an extension specific to MTSQL. (See above.)

Boolean Type Keywords

Note: Unlike in T-SQL, in MTSQL TRUE and FALSE are reserved keywords.

Keyword

Explanation

TRUE

A reserved keyword in MTSQL associated with the BOOLEAN DATATYPE

FALSE

A reserved keyword in MTSQL associated with the BOOLEAN DATATYPE. Code Example

Statements

Keyword

Explanation

DECLARE

Variables are declared in the body of a procedure with the DECLARE statement and are assigned values with SET statement. After declaration, all variables are initialized. Code Example

PRINT

Returns a user-defined message as a string to a VAR or VARCHAR variable. Code Example

SET

Sets the specified local variable, previously created with the DECLARE @local_variable statement, to the given value. This is the only use of SET permitted in MTSQL. Code Example

SELECT INTO

MTSQL also allows for SELECT INTO database queries and allows aggregate functions keywords within these queries. SELECT INTO database queries must take the form of SELECT a, b, c, (etc.) INTO @x, @y, @z, (etc.) FROM A B C (etc.)

Configuration Examples

CalcCancellationCharges.xml

(This XML document calculates the charges levied on a customer for cancelling a teleconferencing reservation.)

<xmlconfig>
  <mtsysconfigdata>
  <effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>
  <timeout ptype="INTEGER">30</timeout>
  <configfiletype>CONFIG_DATA</configfiletype>
  </mtsysconfigdata>
  <mtconfigdata>
 <version ptype="INTEGER">1</version>
 <!-- First processor configuration -->
 <processor>
  <name>CalcCancellationCharges</name>
  <progid>MetraPipeline.MTSQLInterpreter.1</progid>
  <description>Calculates Cancellation Charges</description>
<!--
  <autotest>
  <file>cancelcharges.xml</file>
  </autotest>
  -->
  <inputs>
  </inputs>
  <outputs>
  </outputs>

  <!-- Processor specific configuration data -->
  <configdata>
  <Program>
<![CDATA[
CREATE PROCEDURE calccancelcharges @ScheduledConnections INTEGER
  @CancelRate DECIMAL
  @CancelMaxCharge DECIMAL
  @CancelChargeType ENUM
  @CancelApplicBool BOOLEAN
  @CancelCharges DECIMAL
  AS
  DECLARE @Fixed VARCHAR;
  DECLARE @strChargeType VARCHAR;
  SET @strChargeType = CAST(@CancelChargeType AS VARCHAR)
  SET @CancelCharges = 0.0;
  IF (@CancelApplicBool = FALSE)
  RETURN;
  SET @CancelCharges =
  CASE
  WHEN (@strChargeType = 'Fixed')
  THEN @CancelRate
  WHEN (@strChargeType = 'PerPort' AND @CancelRate * CAST(@ScheduledConnections AS DECIMAL) >= @CancelMaxCharge)
  THEN @CancelMaxCharge
  WHEN @strChargeType = 'PerPort'
  THEN @CancelRate * CAST(@ScheduledConnections AS DECIMAL)
  ELSE
  0.0
  END;
  ]]>
  </Program>
  </configdata>
 </processor>
  </mtconfigdata>
</xmlconfig>

CalcOverusedPorts.xml

(This XML document calculates the actual number of ports, both unused or overused.)

<xmlconfig>
  <mtsysconfigdata>

  <effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>

  <timeout ptype="INTEGER">30</timeout>

  <configfiletype>CONFIG_DATA</configfiletype>

  </mtsysconfigdata>

  <mtconfigdata>

 <version ptype="INTEGER">1</version>

 <!-- First processor configuration -->

 <processor>

  <name>CalcOverusedPorts</name>

  <progid>MetraPipeline.MTSQLInterpreter.1</progid>

  <description>Calculates Actual Number of Ports - Either unused or overused</description>

  <inputs>

  </inputs>

  <outputs>

  </outputs>

  <!-- Processor specific configuration data -->

  <configdata>

  <Program>

<![CDATA[

CREATE PROCEDURE calcports @ScheduledConnections INTEGER

@ActualNumConnections INTEGER

@UserNumConnections INTEGER

@UnusedExcludeRoles BOOLEAN

@UnusedPortChargeDef ENUM

@UnusedThreshFree INTEGER

@UnusedAdvancedPercentage INTEGER

@UnusedPortDelta INTEGER

@NumUnusedPorts INTEGER

AS

DECLARE @PortsNotUsed INTEGER;

DECLARE @AdvancedCalc INTEGER;

DECLARE @NumUsedPorts INTEGER;

DECLARE @PortsUseType VARCHAR;

DECLARE @strChargeDef VARCHAR;

SET @strChargeDef = CAST(@UnusedPortChargeDef AS VARCHAR);

SET @NumUnusedPorts = 0;

SET @PortsUseType = CASE WHEN (@ActualNumConnections > @ScheduledConnections) THEN 'Overused' ELSE 'Underused' END;

IF @PortsUseType = 'Overused'

RETURN;

SET @PortsNotUsed = @ScheduledConnections - @ActualNumConnections;

PRINT CAST(@PortsNotUsed AS VARCHAR)

SET @AdvancedCalc = (@ScheduledConnections * @UnusedAdvancedPercentage / 100) - @ActualNumConnections;

SET @NumUnusedPorts =

CASE

WHEN (@strChargeDef = 'Simple')

THEN @PortsNotUsed

WHEN @strChargeDef = 'Threshold'

THEN @PortsNotUsed - @UnusedThreshFree

WHEN @strChargeDef = 'Advanced' AND @PortsNotUsed > @UnusedPortDelta

THEN @AdvancedCalc

ELSE 0

END;

SET @NumUnusedPorts = CASE WHEN @NumUnusedPorts > 0 THEN @NumUnusedPorts ELSE 0 END;

]>

  </Program>

  </configdata>

 </processor>

  </mtconfigdata>

</xmlconfig>

CalcTotalConfCharge.xml

(This XML document calculates the total charge for a customer after a teleconferencing session.)

<xmlconfig>
  <mtsysconfigdata>

  <effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>

  <timeout ptype="INTEGER">30</timeout>

  <configfiletype>CONFIG_DATA</configfiletype>

  </mtsysconfigdata>

  <mtconfigdata>

 <version ptype="INTEGER">1</version>

 <!-- First processor configuration -->

 <processor>

  <name>CalcTotalConfCharge</name>

  <progid>MetraPipeline.MTSQLInterpreter.1</progid>

  <description>Calculates Total Conferencing Charges</description>

<!--

  <autotest>

  <file>cancelcharges.xml</file>

  </autotest>

  -->

  <inputs>

  </inputs>

  <outputs>

  </outputs>

  <!-- Processor specific configuration data -->
  <configdata>

  <Program>

<![CDATA[

CREATE PROCEDURE calctotalcharges @ConnectionTotalAmount DECIMAL

  @CancelCharges DECIMAL

  @ReservationCharges DECIMAL

  @UnusedPortCharges DECIMAL

  @OverusedPortCharges DECIMAL

  @ConfChargeMinimum DECIMAL

  @TotalCallCharges DECIMAL

  @AdjustmentAmount DECIMAL

  @Amount DECIMAL

AS

SET @TotalCallCharges = 0.0;

SET @Amount = @ConnectionTotalAmount + @CancelCharges + @UnusedPortCharges + @OverusedPortCharges + @ReservationCharges;

SET @AdjustmentAmount =0.0;

IF (@Amount < @ConfChargeMinimum)

BEGIN

SET @AdjustmentAmount = @ConfChargeMinimum - @Amount;

SET @Amount = @ConfChargeMinimum;

END;

SET @TotalCallCharges = @Amount - @ConnectionTotalAmount;

 ]]>

  </Program>

  </configdata>

 </processor>

  </mtconfigdata>

</xmlconfig>

CalcActualDuration.xml

(This XML document calculates the total duration of a teleconferencing session.)

<xmlconfig>
  <mtsysconfigdata>

  <effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>

  <timeout ptype="INTEGER">30</timeout>

  <configfiletype>CONFIG_DATA</configfiletype>

  </mtsysconfigdata>

  <mtconfigdata>

 <version ptype="INTEGER">1</version>

 <!-- First processor configuration -->

 <processor>

  <name>CalcActualDuration</name>

  <progid>MetraPipeline.MTSQLInterpreter.1</progid>

  <description>Calculates Conference Actual Duration</description>

<!--

  <autotest>

  <file>actualduration.xml</file>

  </autotest>

  -->

  <inputs>

  </inputs>

  <outputs>

  </outputs>

  <!-- Processor specific configuration data -->
  <configdata>

  <Program>

<![CDATA[

CREATE PROCEDURE calcactualduration @RawActualDuration INTEGER @ActualDuration DECIMAL

AS

DECLARE @nonFractionalDuration INTEGER;

DECLARE @fraction DECIMAL;

SET @nonFractionalDuration = @RawActualDuration / 60;

SET @ActualDuration = CAST(@RawActualDuration AS DECIMAL) / 60.0;

SET @fraction = @ActualDuration - CAST(@nonFractionalDuration AS DECIMAL);

SET @ActualDuration =

CASE WHEN (@fraction > 0.0)

THEN CAST(@nonFractionalDuration AS DECIMAL) + (@fraction * 0.6)

END;

/*Round it to 2 digits*/

SET @ActualDuration = ROUND(@ActualDuration, 2);

]]>

  </Program>

  </configdata>

 </processor>

  </mtconfigdata>

</xmlconfig>

SetStartTime.xml

(This XML document creates a times stamp indicating when a teleconferencing session began.)

<xmlconfig>
  <mtsysconfigdata>

  <effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>

  <timeout ptype="INTEGER">30</timeout>

  <configfiletype>CONFIG_DATA</configfiletype>

  </mtsysconfigdata>

  <mtconfigdata>

 <version ptype="INTEGER">1</version>

 <!-- First processor configuration -->

 <processor>

  <name>SetStartTime</name>

  <progid>MetraPipeline.MTSQLInterpreter.1</progid>

  <description>Calculates Total Conferencing Charges</description>

<!--

  <autotest>

  <file>setstarttime.xml</file>

  </autotest>

  -->

  <inputs>

  </inputs>

  <outputs>

  </outputs>

  <!-- Processor specific configuration data -->
  <configdata>

  <Program>

<![CDATA[

CREATE PROCEDURE settimestamp@ ActualStartTime DATETIME @_Timestamp DATETIME

 AS

  SET @_Timestamp = @ActualStartTime;

  ]]>

  </Program>

  </configdata>

 </processor>

  </mtconfigdata>

</xmlconfig>

Brief Code Examples

WHILE, CONTINUE, BREAK:

CREATE PROCEDURE testNestedWhileWithContinueAndBreak @a INTEGER, @b INTEGER, @c INTEGER, @d INTEGER

  AS

  DECLARE @counter1 INTEGER

  SET @counter1 = 0

  SET @d = 0

  WHILE @counter1 < @a

  BEGIN

  IF @counter1 = 1

  BEGIN

  SET @counter1 = 1 + @counter1

  CONTINUE

  END

  DECLARE @counter2 INTEGER

  SET @counter2 = 0

  WHILE @counter2 < @c

  BEGIN

  IF @counter2 = 2

  BEGIN

  SET @counter2 = 1 + @counter2

  BREAK

  END

  SET @d = @b + @d

  SET @counter2 = 1 + @counter2

  END

  SET @d = @b + @d

  SET @counter1 = 1 + @counter1

  END

GETDATE, GETUTDATE, DOUBLE:

CREATE PROCEDURE testDate @a DATETIME @b DATETIME @c DOUBLE

  AS

  SET @a = getdate()

  SET @b = getutcdate()

  SET @c = CAST(@b AS DOUBLE) - CAST(@a AS DOUBLE)

LOWER:

CREATE PROCEDURE testLower @a VARCHAR, @d VARCHAR

  AS

  SET @d = lower(@a)

UPPER:

CREATE PROCEDURE testUpper @a VARCHAR, @d VARCHAR

  AS

  SET @d = upper(@a)

% (Modulo):

CREATE PROCEDURE testVarcharMod @a VARCHAR @b VARCHAR @c VARCHAR AS SET @a = @b % @c

(Bitwise AND):

CREATE PROCEDURE testIntegerBitwiseAnd @a INTEGER @b INTEGER @c INTEGER AS SET @a = @b & @c;

(Bitwise OR):

CREATE PROCEDURE testIntegerBitwiseOr @a INTEGER @b INTEGER @c INTEGER AS SET @a = @b | @c;

(Bitwise Exclusive OR):

CREATE PROCEDURE testIntegerBitwiseXor @a INTEGER @b INTEGER @c INTEGER AS SET @a = @b ^ @c;

(Bitwise NOT):

CREATE PROCEDURE testIntegerBitwiseNot @a INTEGER @b INTEGER @c INTEGER AS SET @a = ~@b:

<> (Does Not Equal):

CREATE PROCEDURE testNotEqual @a INTEGER @b INTEGER

AS

SET @c = @a <> @b

SET @d = CASE @c

WHEN TRUE THEN @ a

ELSE @ b

END

Boolean Keyword TRUE:

CREATE PROCEDURE testBooleanLiteral1 @a BOOLEAN

  AS

  SET @a = TRUE

OR:

CREATE PROCEDURE testBooleanLocal @a BOOLEAN @b BOOLEAN @c BOOLEAN

  AS

  /* Just testing out how well multiline

  comments are lexed out */

  DECLARE @local BOOLEAN;

  SET @local = @b OR (CASE 1/0 WHEN 100 THEN @c ELSE @c END);

  SET @a = @local;

NOT:

CREATE PROCEDURE testSimpleIfNot @a INTEGER @b BOOLEAN

  AS

  IF NOT @b

  SET @a = 1

  ELSE

  SET @a = 2