JBoss.orgCommunity Documentation
Teiid supports a procedural language for defining virtual procedures . These are similar to stored procedures in relational database management systems. 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 .
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 5.1. Example Command Statements
SELECT * FROM MyModel.MyTable WHERE ColA > 100; INSERT INTO MyModel.MyTable (ColA,ColB) VALUES (50, 'hi');
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.
Usage:
EXECUTE STRING <expression> [AS <variable> <type> [, <variable> <type>]* [INTO <variable>]]
[USING <variable>=<expression> [,<variable>=<expression>]*] [UPDATE <literal>]
Syntax Rules:
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. With the "INTO" clause specified, the dynamic command will actually execute a statement that behaves like an INSERT with a QUERY EXPRESSION. 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 the updating model count. Accepted values are (0,1,*). 0 is the default value if the clause is not specified.
Example 5.2. 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 5.3. Example Dynamic SQL with USING clause and dynamically built criteria string
... 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; ...
Known Limitations and Work-Arounds
The use of dynamic SQL command results in an assignment statement requires the use of a temp table.
Example 5.4. 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 5.5. 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 5.6. 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.
Example 5.7. Example with AS and INTO clauses
/* This name does not need to match the expected update command symbol "count". */ EXECUTE STRING <expression> AS x integer INTO #temp;
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.
A declaration statement 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.
Usage:
DECLARE <type> [VARIABLES.]<name> [= <expression>];
Example Syntax
declare integer x;
declare string VARIABLES.myvar = 'value';
Syntax Rules:
You cannot redeclare a variable with a duplicate name in a sub-block
The VARIABLES group is always implied even if it is not specified.
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.
Usage:
<variable reference> = <expression>;
Example Syntax
myString = 'Thank you';
VARIABLES.x = SELECT Column1 FROM MyModel.MyTable;
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. A dependent ELSE statement will execute its block of code only if the IF statement evaluates to false.
Example 5.8. Example If Statement
IF ( var1 = 'North America') BEGIN ...statement... END ELSE BEGIN ...statement... END
NULL values should be considered in the criteria of an IF statement. IS NULL criteria can be used to detect the presense of a NULL value.
A LOOP statement is an iterative control construct that is used to cursor through a result set.
Usage:
LOOP ON <select statement> AS <cursorname> BEGIN ... END
A WHILE statement is an iterative control construct that is used to execute a set of statements repeatedly whenever a specified condition is met.
Usage:
WHILE <criteria> BEGIN ... END
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.
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.
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 5.9. Example Error Statement
ERROR 'Invalid input value: ' || nvl(Acct.GetBalance.AcctID, 'null');
Virtual procedures are defined using the Teiid procedural language. A virtual procedure has zero or more input parameters, and a result set return type. Virtual procedures support the ability to execute queries and other SQL commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.
Usage:
CREATE VIRTUAL PROCEDURE BEGIN ... END
The CREATE VIRTUAL PROCEDURE line demarcates the beginning of the procedure. The BEGIN and END keywords are used to denote block boundaries. Within the body of the procedure, any valid statement may be used.
The last command statement executed in the procedure will be return as the result. The output of that statement must match the expected result set and parameters of the procedure.
Virtual procedures can take zero or more input parameters. Each input has the following information that is used during runtime processing:
Name - The name of the input parameter
Datatype - The design-time type of the input parameter
Default value - The default value if the input parameter is not specified
Nullable - NO_NULLS, NULLABLE, NULLABLE_UNKNOWN; parameter is optional if nullable, and is not required to be listed when using named parameter syntax
You reference an input to a virtual procedure by using the fully-qualified name of the param (or less if unambiguous). For example, MyModel.MyProc.Param1.
Example 5.10. Example of Referencing an Input Parameter for 'GetBalance' Procedure
CREATE VIRTUAL PROCEDURE BEGIN SELECT Balance FROM MyModel.Accts WHERE MyModel.Accts.AccountID = MyModel.GetBalance.AcctID; END
This example is a LOOP that walks through a cursored table and uses CONTINUE and BREAK.
Example 5.11. Virtual Procedure Using LOOP, CONTINUE, BREAK
CREATE VIRTUAL PROCEDURE BEGIN DECLARE double total; DECLARE integer transactions; LOOP ON (SELECT amt, type FROM CashTxnTable) AS txncursor BEGIN IF(txncursor.type <> 'Sale') BEGIN CONTINUE; END ELSE BEGIN total = (total + txncursor.amt); transactions = (transactions + 1); IF(transactions = 100) BEGIN BREAK; END END END SELECT total, (total / transactions) AS avg_transaction; END
This example is uses conditional logic to determine which of two SELECT statements to execute.
Example 5.12. Virtual Procedure with Conditional SELECT
CREATE VIRTUAL PROCEDURE BEGIN DECLARE string VARIABLES.SORTDIRECTION; VARIABLES.SORTDIRECTION = PartsVirtual.OrderedQtyProc.SORTMODE; IF ( ucase(VARIABLES.SORTDIRECTION) = 'ASC' ) BEGIN SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID; END ELSE BEGIN SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID DESC; END END
You execute procedures using the SQL EXECUTE command. If the procedure has defined inputs, you specify those in a sequential list, or using "name=value" syntax. You must use the name of the input parameter, scoped by the full procedure name if the parameter name is ambiguous in the context of other elements or variables in the procedure.
A virtual procedure call will return a result set just like any SELECT, so you can use this in many places you can use a SELECT. However, within a virtual procedure itself you cannot always use an EXEC directly. Instead, you use the following syntax:
SELECT * FROM (EXEC ...) AS x
The following are some examples of how you can use the results of a virtual procedure call within a virtual procedure definition:
LOOP instruction - you can walk through the results and do work on a row-by-row basis
Assignment instruction - you can run a command and set the first column / first row value returned to a variable
SELECT * INTO #temp FROM (EXEC ...) AS x
- you can
select the results from a virtual procedure into a temp table,
which you can then query against as if it were a physical table.
Virtual tables are abstractions above physical sources. They typically union or join information from multiple tables, often from multiple data sources or other views. Teiid can perform update operations against virtual tables. Update commands - INSERT, UPDATE, or DELETE - against a virtual table require logic to define how the tables and views integrated by the virtual table are affected by each type of command. This transformation logic is invoked when an update command is issued against a virtual table. Update procedures define the logic for how a user's update command against a virtual table should be decomposed into the individual commands to be executed against the underlying physical sources. Similar to virtual procedures , update procedures have the ability to execute queries or other commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.
Usage:
CREATE PROCEDURE BEGIN ... END
The CREATE VIRTUAL PROCEDURE line demarcates the beginning of the procedure. The BEGIN and END keywords are used to denote block boundaries. Within the body of the procedure, any valid statement may be used.
You can use a number of special variables when defining your update procedure.
Every attribute in the virtual table whose UPDATE and INSERT transformations you are defining has an equivalent variable named INPUT.<column_name>
When an INSERT or an UPDATE command is executed against the virtual table, these variables are initialized to the values in the INSERT VALUES clause or the UPDATE SET clause respectively.
In an UPDATE procedure, the default value of these variables, if they are not set by the command, is null. In an INSERT procedure, the default value of these variables is the default value of the virtual table attributes, based on their defined types. See CHANGING Variables for distinguishing defaults from passed values.
Similar to INPUT Variables, every attribute in the virtual table whose UPDATE and INSERT transformations you are defining has an equivalent variable named CHANGING.<column_name>
When an INSERT or an UPDATE command is executed against the virtual table, these variables are initialized to true
or false
depending on whether the INPUT variable was set by the command.
For example, for a virtual table with columns A, B, C:
If User Executes... | Then... |
---|---|
INSERT INTO VT (A, B) VALUES (0, 1) | CHANGING.A = true, CHANGING.B = true, CHANGING.C = false |
UPDATE VT SET C = 2 | CHANGING.A = false, CHANGING.B = false, CHANGING.C = true |
Teiid returns the value of the VARIABLES.ROWS_UPDATED variable as a response to an update command executed against the virtual table. Your procedure must set the value that returns when an application executes an update command against the virtual table, which triggers invocation of the update procedure. For example, if an UPDATE command is issued that affects 5 records, the ROWS_UPDATED should be set appropriately so that the user will receive '5' for the count of records affected.
You can use a number of special SQL clauses when defining UPDATE or DELETE procedures. These make it easier to do variable substitutions in WHERE clauses or to check on the change state of variables without using a lot of conditional logic.
You can use the HAS CRITERIA clause to check whether the user’s command has a particular kind of criteria on a particular set of attributes. This clause evaluates to either true or false. You can use it anywhere you can use a criteria within a procedure.
Usage:
HAS [criteria operator] CRITERIA [ON (element list)]
Syntax Rules
The criteria operator, can be one of =, <, >, <=, >=, <>, LIKE, IS NULL, or IN.
If the ON clause is present, HAS CRITERIA will return true only if criteria was present on all of the specified elements.
The elements in a HAS CRITERIA ON clause always refer to virtual elements.
Some samples of the HAS CRITERIA clause:
SQL | Result |
---|---|
HAS CRITERIA | Checks simply whether there was any criteria at all. |
HAS CRITERIA ON (element1, element2) | Checks whether the criteria uses element1 and element2. |
HAS = CRITERIA ON (element1) | Checks whether the criteria has a comparison criteria with = operator. |
HAS LIKE CRITERIA | Checks whether the criteria has a match criteria using LIKE. |
The HAS CRITERIA predicate is most commonly used in an IF clause, to determine if the user issued a particular form of command and to respond appropriately.
You can use the TRANSLATE CRITERIA clause to convert the criteria from the user application’s SQL command into the form required to interact with the target source or view tables. The TRANSLATE CRITERIA statement uses the SELECT transformation to infer the element mapping. This clause evaluates to a translated criteria that is evaluated in the context of a command.
Usage:
TRANSLATE [criteria operator] CRITERIA [ON (element list)] [WITH (mapping list)]
Syntax Rules
The criteria operator, can be one of =, <, >, <=, >=, <>, LIKE, IS NULL, or IN.
If the ON clause is present, TRANSLATE CRITERIA will only form criteria using the specified elements.
The elements in a TRANSLATE CRITERIA ON clause always refer to virtual elements.
You can use these mappings either to replace the default mappings generated from the SELECT transformation or to specify a reverse expression when a virtual element is defined by an expression.
Some samples of the HAS TRANSLATE clause:
SQL | Result |
---|---|
TRANSLATE CRITERIA | Translates any user criteria using the default mappings. |
TRANSLATE CRITERIA WITH (element1 = 'A', element2 = INPUT.element2 + 2) | Translates any criteria with some additional mappings: element1 is always mapped to 'A' and element2 is mapped to the incoming element2 value + 2. |
TRANSLATE = CRITERIA ON (element1) | Translates only criteria that have = comparison operator and involve element1. |
The TRANSLATE CRITERIA, ON clause always refers to virtual elements. The WITH clause always has items with form <elem> = <expression>, where the <elem> is a virtual element and the <expression> specifies what that virtual element should be replaced with when TRANSLATE CRITERIA translates the virtual criteria (from UPDATE or DELETE) into a physical criteria in the command. By default, a mapping is created based on the SELECT clause of the SELECT transformation (virtual column gets mapped to expression in SELECT clause at same position).
The user application submits the SQL command through one of SOAP, JDBC, or ODBC.
The virtual table that this SQL command is executed against is detected.
The correct procedure is chosen depending upon whether the command is an INSERT, UPDATE, or DELETE.
The procedure is executed. The procedure itself can contain SQL commands of its own which can be of different types than the command submitted by the user application that invoked the procedure.
Commands, as described in the procedure, as issued to the individual physical data sources or other views.
A value representing the number of rows changed is returned to the calling application.