(Formerly, the Arithmetic Plug-in.)
MetraPipeline.MTSQLInterpreter.1
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:
Support for built in types: integer, double precision, decimal, Boolean and variable length string
Statically typed interpreter (type inconsistencies will be caught before runtime)
Explicit CAST operation for type conversion
Binary arithmetic operators (+, -, *, /, %) for numeric types
Unary arithmetic operators (+, -) for numeric types
Special string operators such as string concatenation (+)
Local variables
Function calls
Embedding of SQL SELECT INTO statements
PRINT statements for output of string parameters to MetraTech log files
The MTSQL language is missing some features that one might expect in such a language, such as:
Language does not support automatic conversions between data types
Language does not support wild cards except as part of SELECT INTO statements
Language does not support nulls.
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 ">").
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.
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:
The CAST form and ENUM value to a VARCHAR value has the same effects as calling the MTEnumConfig::GetEnumeratorByID() COM API function.
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.
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.
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.
None.
None.
Tag Name |
Value Type |
Description |
program |
String |
Denotes the beginning and end of an MTSQL procedure. |
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.
Here is a linked, alphabetical index of MTSQL keywords, operators, and data types. | |
Aggregate functions perform a calculation on a set of values and return a single value. | |
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. | |
Indicates user-provided annotation of code. | |
In MTSQL, these two keywords are used to establish the parameters that are going to be used in the XML file. | |
These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value. | |
These scalar functions perform a calculation, usually based on input values provided as arguments, and return a numeric value. | |
MTSQL allows for SELECT INTO database queries and DECLARE, PRINT, and SET statements | |
These scalar functions perform an operation on a string input value and return a string or numeric value. | |
These scalar functions perform operations on and return information about values, objects, and settings. | |
Arithmetic operators perform mathematical operations on two expressions of any of the data types of the numeric data type category. | |
Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category. | |
Comparison operators test relationships between two expressions. Comparison operators can be used on all expressions except expressions of the text. | |
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. |
Note: Unlike T-SQL, MT-SQL does not support automatic conversion between 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. |
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 |
||
A |
N |
||
B |
BEGIN, BINARY_CHECKSUM*†, BOOLEAN, BREAK |
O |
|
C |
CASE, CAST, C0NTINUE, CHECKSUM*, CHECKSUM_AGG*†, COUNT*, CREATE PROCEDURE |
P |
|
D |
R |
||
E |
S |
||
F |
T |
||
G |
GETDATE, GETUTCDATE, GROUPING*†, GROUP BY* |
U |
|
I |
V |
||
L |
W |
*These keywords only have valid usage within SELECT INTO statements.
†These keywords are only valid when the system is using a SQL Server database.
Keyword |
Explanation |
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 | |
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 | |
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 | |
|
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 |
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 | |
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 |
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 |
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 |
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 |
Returns the average of the values in a group. Null values are ignored. | |
Returns the maximum value in the expression. | |
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. | |
Returns the minimum value in the expression. | |
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. | |
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. | |
Returns the checksum of the values in a group. Null values are ignored. | |
Returns the statistical standard deviation of all values in the given expression. | |
Returns the number of items in a group. | |
Returns the statistical standard deviation for the population for all values in the given expression. | |
Returns the statistical variance of all values in the given expression. | |
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. | |
Returns the statistical variance for the population for all values in the given expression. | |
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.
Keyword |
Explanation |
Returns the current system date and time in the SQL standard internal format for datetime values. Code Example | |
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 |
Keyword |
Explanation |
Returns a numeric expression, rounded to the specified length or precision. Round only takes DECIMAL arguments. Code Example |
Keyword/Symbol |
Explanation |
Returns a character expression after converting uppercase character data to lowercase. Code Example | |
Returns a character expression with lowercase character data converted to uppercase. Code Example | |
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. | |
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). |
Keyword |
Explanation |
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:
For a searched CASE function:
| |
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 |
Symbol |
Explanation |
Adds two numbers. Code Example Note: This operator is also used for string concatenation. See entry above. | |
Subtracts two numbers. Code Example | |
Multiplies two expressions (an arithmetic multiplication operator). Code Example | |
Divides one number by another (an arithmetic division operator). Code Example | |
Provides the remainder of one number divided by another. Code Example |
Symbol |
Explanation |
Performs a bitwise logical AND operation between two integer values. Code Example | |
Performs a bitwise logical OR operation between two given integer values as translated to binary expressions within MTSQL statements. Code Example | |
Performs a bitwise exclusive OR operation between two given integer values as translated to binary expressions within MTSQL statements. Code Example | |
Performs a bitwise logical NOT operation for one given integer value as translated to binary expressions within MTSQL statements. Code Example |
Symbol |
Explanation |
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 | |
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 | |
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. | |
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 | |
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. | |
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. |
Keyword |
Explanation |
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 | |
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 | |
Negates a Boolean input. Code Example |
Keyword |
Explanation |
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 | |
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 | |
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 | |
"Character varying". Variable-length non-Unicode data. In MTSQL, there is no maximum length of strings. Code Example | |
Double precision. Code Example | |
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. | |
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 | |
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.)
Note: Unlike in T-SQL, in MTSQL TRUE and FALSE are reserved keywords.
Keyword |
Explanation |
A reserved keyword in MTSQL associated with the BOOLEAN DATATYPE | |
A reserved keyword in MTSQL associated with the BOOLEAN DATATYPE. Code Example |
Keyword |
Explanation |
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 | |
Returns a user-defined message as a string to a VAR or VARCHAR variable. Code Example | |
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 | |
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.) |
(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>
(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>
(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>
(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>
(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>
CREATE PROCEDURE testNestedWhileWithContinueAndBreak @a INTEGER, @b INTEGER, @c INTEGER, @d INTEGER
AS
DECLARE @counter1 INTEGER
SET @counter1 = 0
SET @d = 0
BEGIN
IF @counter1 = 1
BEGIN
SET @counter1 = 1 + @counter1
END
DECLARE @counter2 INTEGER
SET @counter2 = 0
WHILE @counter2 < @c
BEGIN
IF @counter2 = 2
BEGIN
SET @counter2 = 1 + @counter2
END
SET @d = @b + @d
SET @counter2 = 1 + @counter2
END
SET @d = @b + @d
SET @counter1 = 1 + @counter1
END
CREATE PROCEDURE testDate @a DATETIME @b DATETIME @c DOUBLE
AS
SET @c = CAST(@b AS DOUBLE) - CAST(@a AS DOUBLE)
CREATE PROCEDURE testLower @a VARCHAR, @d VARCHAR
AS
CREATE PROCEDURE testUpper @a VARCHAR, @d VARCHAR
AS
CREATE PROCEDURE testVarcharMod @a VARCHAR @b VARCHAR @c VARCHAR AS SET @a = @b % @c
CREATE PROCEDURE testIntegerBitwiseAnd @a INTEGER @b INTEGER @c INTEGER AS SET @a = @b & @c;
CREATE PROCEDURE testIntegerBitwiseOr @a INTEGER @b INTEGER @c INTEGER AS SET @a = @b | @c;
CREATE PROCEDURE testIntegerBitwiseXor @a INTEGER @b INTEGER @c INTEGER AS SET @a = @b ^ @c;
CREATE PROCEDURE testIntegerBitwiseNot @a INTEGER @b INTEGER @c INTEGER AS SET @a = ~@b:
CREATE PROCEDURE testNotEqual @a INTEGER @b INTEGER
AS
SET @c = @a <> @b
SET @d = CASE @c
WHEN TRUE THEN @ a
ELSE @ b
END
CREATE PROCEDURE testBooleanLiteral1 @a BOOLEAN
AS
SET @a = TRUE
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;
CREATE PROCEDURE testSimpleIfNot @a INTEGER @b BOOLEAN
AS
SET @a = 1
ELSE
SET @a = 2