Specify Input Parameters for a Packaged Query
An input parameter is not required for a database-specific query. However, when creating a packaged query you can define one or more numbered input parameters to insert prior to execution and submission of the database-specific query. For each input value in the database-specific SQL, you must create an input parameter in the packaged query using the Parameters panel editor in Studio.
For example, your database-specific query might be used to fetch a row from a table but require an ID to identify the specific row. Packaged queries can easily define an ID input parameter or other parameter values for insertion at runtime.
When TDV Server executes a packaged query, it inserts actual values into the database-specific query before sending the packaged query to the data source for execution.
An input parameter consists of a name and a data type, both of which you specify when you define the parameter. The parameter name is displayed at query execution time, prompting for an input value. The parameter name has no effect on the database-specific query; it is used by users of your packaged query. However, the data type you specify does affect how a value is formatted when it is inserted into the database-specific query.
Data Types
Packaged queries support the following input data types:
DECIMAL, DOUBLE, FLOAT, NUMERIC, BIGINT, BIT, INTEGER, SMALLINT, TINYINT, CHAR, LONGVARCHAR, VARCHAR, DATE, TIME, TIMESTAMP
Input Substitution
The package query can define N inputs, with substitution patterns numbered from {0} to {N‑1}.
Input Substitution Pattern
Packaged queries use one of two different input substitution patterns for each numbered input. The first line of the SQL must begin with <version 2>
• {N}—the numbered input is replaced with the data value with no changes.
• {N:string-sql-literal}—for data types of VARCHAR and TIMESTAMP the numbered input placeholder is replaced with the substituted string value and enclosed within single quotes.
If any single-quote characters are present in the string value that is to be substituted, they are automatically escaped with a second single-quote.
Null values are substituted.
Package queries that do not require an input, do not need a substitution pattern or substitution placeholders; however, the query must have exactly one output parameter that is a cursor with at least one column.
When STRING and DATE data types are used, you should use a single quote before and after the input substitution pattern. This rule does not apply to numeric data types.
Input Parameters Evaluation
This section illustrates how the packaged query input parameters are evaluated at runtime. The first six query examples are valid. The final two query examples are invalid.
Valid Query 1: Simple Substitution
In the following example {0} is a string, {1} is a number, and the package query is defined with the following:
<version 2>
SELECT customer.balance
FROM customer
WHERE customer.name = '{0}' AND customer.id = {1}
The pattern for a string is enclosed in single quotes. The first input value replaces all the occurrences of {0}, the second input value replaces {1}, and so on.
Valid Query 2: Multiple Substitutions
Each input substitution pattern is allowed to appear more than once in a packaged query:
<version 2>
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = {0} AND customer.zip = {2})
OR (customer.id = {1} AND customer.zip = {2})
Packaged Query before Substitution
<version 2>
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = {0} AND customer.zip = {2})
OR (customer.id = {1} AND customer.zip = {2})
Packaged Query after Substitution
Given actual input values like a first input value of 101, a second input value of 102, and a customer.zip value of 94403, the substitution would look like the following:
<version 2>
SELECT
customer.name, customer.balance
FROM
customer
WHERE
(customer.id = 101 AND customer.zip = 94403)
OR (customer.id = 102 AND customer.zip = 94403)
Valid Query 3: Escaping Characters to Stop Substitution
If a pattern such as '{i}' needs to be preserved, use a backslash (\) to escape the opening and closing curly braces ('\{i\}').
Packaged Query before Substitution
<version 2>
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = {0}
AND (customer.note = 'preserved \{1\}input')
OR (customer.id = {1} AND customer.zip = {2}
Packaged Query after Substitution
Substituting the same input values as the previous example:
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = 101
AND (customer.note = 'preserved {1} input')
OR (customer.id = 102 AND customer.zip = 94403
Note: The backslash (\) is removed from the original query. Escaping the second curly brace is optional.
Valid Query 4: Escaping Escaped Characters
If backslashes are part of a valid string (for example, '\{i\}') that should not be altered, add another backslash character ('\\{i\\}').
Packaged Query before Substitution
<version 2>
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = {0}
AND (customer.zip = '\\{2\\}')
OR (customer.id = {1} AND customer.zip = {2})
Packaged Query after Substitution
Substituting the same input values as the previous examples:
SELECT customer.name, customer.balance
FROM customer
WHERE
(customer.id = 101
AND (customer.zip = '\{2\}')
OR (customer.id = 102 AND customer.zip = 94403)
Valid Query 5: {N:string-sql-literal} Option
If a query input is a string containing a single quote, the input placeholder should be {N:string-sql-literal} so that it is enclosed in single quotes and escaped by the packaged query to become two single quotes after substitution.
Packaged Query before Substitution
<version 2>
SELECT customer.balance
FROM customer
WHERE
customer.name = {0:string-sql-literal} AND customer.id = {1}
Packaged Query after Substitution
The first substitution value is “Michael’s son” and the second is “123”.
SELECT customer.balance
FROM customer
WHERE
customer.name = ’Michael’’s son’ AND customer.id = 123
Valid Query 6: Another Escape Example for Numeric Values
If a query input contains a number enclosed in curly braces such as‘{55}’, escape each curly brace with a backslash. There is no need to escape the curly braces, if the curly braces enclose a string such as ‘{the Second}’.
Packaged Query before Substitution
<version 2>
SELECT customer.balance
FROM customer
WHERE
customer.id = {0}
AND customer.name = ‘George {the Second}’
AND customer.id = ‘\{55\}’
Packaged Query after Substitution
SELECT customer.balance
FROM customer
WHERE
customer.id = 123
customer.name = ‘George {the Second}’
AND customer.id = ‘{55}’
Invalid Query A
The following query is invalid because substitution pattern input {1} is missing from the query.
SELECT customer.name, customer.balance
FROM customer
WHERE customer.id = {0} AND customer.status = {2}
Invalid Query B
The following query is invalid because the database-specific query defines four inputs, but the packaged query has defined only three input substitutions. The fourth SQL input placeholder{3} makes the query invalid. You need to define one more input parameter. Also the customer.zip is an integer that does not need to be enclosed in single quotes.
SELECT customer.name, customer.balance
FROM customer
WHERE
customer.id = {0} AND customer.zip = '{1}'
AND customer.status = {2} AND customer.email = {3}