Home » Tech Reference » MetaMatrix Procedure Language

MetaMatrix Features

MetaMatrix Procedure Language

MetaMatrix supports a procedural language for defining virtual procedures. These are similar to stored procedures in relational database management systems. In addition, if you are using MetaMatrix Enterprise, you can use this language to define the transformation logic for decomposing INSERT, UPDATE, and DELETE commands against virtual tables; these are known as update procedures.

The MetaMatrix procedure language is used in conjunction with SQL when defining transformations in MetaMatrix Designer.



Procedure Statements

Within your procedures, you can use a variety of statements. This section details each statement. You must use a semicolon (;) at the end of each statement, except statements that use a nested block, such as IF and WHILE.

Command Statement

A command statement executes a SQL command, such as SELECT, INSERT, UPDATE, DELETE, or EXECUTE, against one or more other models (and their underlying data sources).

Example Command Statements
SELECT * FROM MyModel.MyTable WHERE ColA > 100; INSERT INTO MyModel.MyTable (ColA,ColB) VALUES (50, 'hi');

Dynamic SQL Command

Dynamic SQL allows for the execution of an arbitrary SQL command in a virtual procedure. Dynamic SQL is useful in situations where the exact command form is not known prior to execution.

Dynamic SQL Syntax
EXECUTE STRING <expression> [AS <variable> <type> [, <variable> <type>]* [INTO <variable>]] [USING <variable>=<expression> [,<variable>=<expression>]*] [UPDATE <literal>]
  • The "AS" clause is used to define the projected symbols names and types returned by the executed SQL string. The "AS" clause symbols will be matched positionally with the symbols returned by the executed SQL string. Non-convertible types or too few columns returned by the executed SQL string will result in an error.
  • The "INTO" clause will project the dynamic SQL into the specified temp table. At runtime with the "INTO" clause specified, the dynamic command will actually execute a statement that behaves like a SELECT INTO. If the dynamic SQL command creates a temporary table with the "INTO" clause, then the "AS" clause is required to define the table’s metadata.
  • The "USING" clause allows the dynamic SQL string to contain special element symbols that are bound at runtime to specified values. This allows for some independence of the SQL string from the surrounding procedure variable names and input names. In the dynamic command "USING" clause, each variable is specified by short name only. However in the dynamic SQL the "USING" variable must be fully qualified to "USING.". The "USING" clause is only for values that will be used in the dynamic SQL as legal expressions. It is not possible to use the "USING" clause to replace table names, keywords, etc. This makes using symbols equivalent in power to normal bind (?) expressions in prepared statements. The "USING" clause helps reduce the amount of string manipulation needed. If a reference is made to a USING symbol in the SQL string that is not bound to a value in the "USING" clause, an exception will occur.
  • The "UPDATE" clause is used to specify how many models are updated as a result of executing this dynamic SQL. Accepted values are (0,1,*) denoting 0, 1, or many models. 0 is the default value if the clause is not specified.
Example Dynamic SQL
... /* Typically complex criteria would be formed based upon inputs to the procedure. In this simple example the criteria is references the using clause to isolate the SQL string from referencing a value from the procedure directly */ DECLARE string criteria = 'Customer.Accounts.Last = USING.LastName'; /* Now we create the desired SQL string */ DECLARE string sql_string = 'SELECT ID, First || ‘‘ ‘‘ || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || criteria; /* The execution of the SQL string will create the #temp table with the columns (ID, Name, Birthdate). Note that we also have the USING clause to bind a value to LastName, which is referenced in the criteria. */ EXECUTE STRING sql_string; AS ID integer, Name string, Birthdate date INTO #temp USING LastName='some name'; /* The temp table can now be used with the values from the Dynamic SQL */ loop on (SELCT ID from #temp) as myCursor ...
Here is an example showing a more complex approach to building criteria for the dynamic SQL string. In short, the virtual procedure AccountAccess.GetAccounts has inputs ID, LastName, and bday. If a value is specified for ID it will be the only value used in the dynamic SQL criteria. Otherwise if a value is specified for LastName the procedure will detect if the value is a search string. If bday is specified in addition to LastName, it will be used to form compound criteria with LastName.
Example Dynamic SQL with USING clause and dynamically built criteria string
CREATE VIRTUAL PROCEDURE BEGIN DECLARE string crit = null; IF (AccountAccess.GetAccounts.ID IS NOT NULL) crit = ‘(Customer.Accounts.ID = using.ID)’; ELSE IF (AccountAccess.GetAccounts.LastName IS NOT NULL) BEGIN IF (AccountAccess.GetAccounts.LastName == ‘%’) ERROR "Last name cannot be %"; ELSE IF (LOCATE(‘%’, AccountAccess.GetAccounts.LastName) < 0) crit = ‘(Customer.Accounts.Last = using.LastName)’; ELSE crit = ‘(Customer.Accounts.Last LIKE using.LastName)’; IF (AccountAccess.GetAccounts.bday IS NOT NULL) crit = ‘(‘ || crit || ‘ and (Customer.Accounts.Birthdate = using.BirthDay))’; END ELSE ERROR "ID or LastName must be specified."; EXECUTE STRING ‘SELECT ID, First || ‘‘ ‘‘ || Last AS Name, Birthdate FROM Customer.Accounts WHERE ’ || crit USING ID=AccountAccess.GetAccounts.ID, LastName=AccountAccess.GetAccounts.LastName, BirthDay=AccountAccess.GetAccounts.Bday; END
    Known Limitations and Work-Arounds
  • The use of dynamic SQL command results in an assignment statement requires the use of a temp table.
    Example Assignment
    EXECUTE STRING <expression> AS x string INTO #temp; DECLARE string VARIABLES.RESULT = SEELCT x FROM #temp;
  • The construction of appropriate criteria will be cumbersome if parts of the criteria are not present. For example if "criteria" were already NULL, then the following example results in "criteria" remaining NULL.
    Example Dangerous NULL handling
    criteria = ‘(‘ || criteria || ‘ and (Customer.Accounts.Birthdate = using.BirthDay))’;
    The preferred approach is for the user to ensure the criteria is not NULL prior its usage. If this is not possible, a good approach is to specify a default as shown in the following example.
    Example NULL handling
    criteria = ‘(‘ || nvl(criteria, ‘(1 = 1)’) || ‘ and (Customer.Accounts.Birthdate = using.BirthDay))’;
  • If the dynamic SQL is an UPDATE, DELETE, or INSERT command, and the user needs to specify the "AS" clause (which would be the case if the number of rows effected needs to be retrieved).
    The user will still need to provide a name and type for the return column if the into clause is specified, e.g.
    EXECUTE STRING <expression> as x integer INTO #temp;
    This name does not need to match the expected update command symbol "count".
  • Unless used in other parts of the procedure, tables in the dynamic command will not be seen as sources in the Designer.
  • When using the "AS" clause only the type information will be available to the Designer. ResultSet columns generated from the "AS" clause then will have a default set of properties for length, precision, etc.

Declaration Statement

A declaration statement, which uses the DECLARE command word, declares a variable and its type. After you declare a variable, you can use it in that block within the procedure and any sub-blocks. A variable is initialized to null by default, but can also be assigned the value of an expression as part of the declaration statement.

Example Declaration Statements
DECLARE integer x; DECLARE string str = 'some value';

Assignment Statement

An assignment statement assigns a value to a variable by either evaluating an expression or executing a SELECT command that returns a column value from a single row.

Example Assignment Statements
myString = "Thank you"; myFloat = 100.0; x = SELECT Column1 FROM MyModel.MyTable;

If Statement

An IF statement evaluates a condition and executes either one of two blocks depending on the result. You can nest IF statements to create complex branching logic to accommodate the reverse transformations for your sources.

A dependent ELSE statement will execute its block of code if the IF statement evaluates to false and does not execute.

An example of an IF block with an associated ELSE is shown below.

Example If Statement
IF ( var1 = 'North America') BEGIN ...statement... END ELSE BEGIN ...statement... END
Null Values
Null variable values can be checked using a condition of the form var1 IS NULL where the case of null is not important.
NOTE: NULL is a keyword, and not enclosed in quotes.

Loop Statement

A LOOP statement is an iterative control construct that is used to cursor through a result set.

Example Loop Statement
LOOP ON <select statement> AS <cursorname> BEGIN ... END

The cursor name used for the loop statement cannot be used outside the context of that loop statement.

Nested loop statements cannot use the same cursor name.

While Statement

A WHILE statement is an iterative control construct that is used to execute a set of statements repeatedly whenever a specified condition is met.

Example While Statement
WHILE <condition> BEGIN ... END

Continue Statement

A CONTINUE statement is used inside a LOOP or WHILE construct to continue with the next loop by skipping over the rest of the statements in the loop. It must be used inside a LOOP or WHILE statement.

Break Statement

A BREAK statement is used inside a LOOP or WHILE construct to break from the loop. It must be used inside a LOOP or WHILE statement.

Error Statement

An ERROR statement declares that the procedure has entered an error state and should abort. This statement will also roll back the current transaction, if one exists. Any valid expression can be specified after the ERROR keyword.

Example Error Statements
ERROR "Invalid INSERT statement."; ERROR 'Invalid input value: ' || Acct.GetBalance.AcctID;