Direct SQL Binding (Magic xpa 4.x)
Binding variables for SQL statements is valid for Oracle and PostgreSQL databases only.
When using Direct SQL tasks, Magic xpa sends the SQL statements to the database after creating them dynamically. The database receives the SQL statements as Alpha or Numeric string values, and parses and executes the SQL statements.
The parsing stage includes translating the statement and checking its validity, checking the columns and table definitions, checking privileges and finding the best way for executing the statement.
Parts of the SQL statement can be placeholders for predefined values. Replacing placeholders with values is called binding, which eliminates the process of reparsing SQL statements by the database.
The analyzed statement is kept in the Shared SQL area. The parsing can be re-used for the next statements only if the statement looks exactly the same.
Example:
select * from orders_line where order_line=1
if the second statement looks as follows there is no reuse:
select * from orders_line where order_line=2
The parsing process may be long when the SQL statements are complicated. The binding allows the sending of values as parameters and allows more general statements in the parsing stage. After the parsing, the values are bound with the statement in the parameters and eventually the statement is executed.
Magic xpa implicit programs automatically use binding to allow for the best performance.
In Direct SQL tasks, parameters can be not only values but also part of the statement itself. By default, Magic xpa does not use binding in Direct SQL tasks. The statement is sent to the database with the values and no reusing is available in parsing.
To specify a variable as a binding variable, the parameters should be written with the tilde character (~) instead of the colon (:) in the input arguments. Magic xpa replaces any ~1 with the appropriate value according to its data type.
For example:
Select * from orders_lines where order_line = ~1
Update tab set F3 ='value' where F1=~1 and F2=~2
Delete from tab set where F1=~1
Binding is supported for Alpha, Logical, and Numeric Magic xpa types. Single straight quotation marks (' ') are not required when binding Alpha values. Magic xpa stores Date, Time, Logical, and Numeric Magic xpa types as Numeric values.
You can use the binding mechanism when the parallel data type in Oracle is Numeric.
Note: If a Numeric expression is used, Magic xpa surrounds it with quotes. If you do not want to have quotes in your task, use a bound variable instead of an expression.
Binding variables can be used with the following database and gateway restrictions:
-
You can use binding only with select statements or stored procedures that return output.
-
You can use binding only for values, not for field names.
|
|
Binding for commands that do not return result set is supported only for Oracle database.
Since version: 4.7
|
SQL statements with bound (~) and unbound parameters eliminate the benefits of binding variables.
Using an expression such as ~5 =0 (a bound expression that is supposed to represent a column name) is not supported.
