You can get the list of parameters used in the query via the TacQueryBuilder.Parameters collection since the very beginning. But you had to rely on the means of the TQuery component to apply parameter values to the query. This made hard to carry out such tasks as:

• cut off conditions with undefined parameter values,
• replace a single parameter with a list of values,
• replace parameters with SQL expressions,
• convert parameters to the right notation.

Cutting off extra conditions

In addition, it cuts off conditions and SQL expressions with undefined parameter values. This lets you include all the worth-while conditions to the query and get the query only with conditions useful at the moment.

With TacQueryParametersTransformer you need not write a line of code to make this happen! Just assign the needed values and get the result SQL query or the ready-to-use TDataset object!

Macros and lists of values (Professional version)

Replacing parameters with SQL expressions was possible only through unsafe tricky string manipulations.
Now you can use the TacQueryParametersTransformer to replace parameters with complex SQL conditions and sub-queries in the query text. You can even replace whole query parts with this technique, but we opt for using the AQB API to change SQL queries by code.

The most frequent scenario for using macros is to replace a single parameter with a list of values in expressions like “Field IN (:list_param)”. The component signals you when it’s possible to apply a list, tells the data type of expected items, and checks the assigned expression for validity.

When it is not enough just to set scalar values for parameters, use macros.

Changing the parameters notation

Database servers and their respective data providers (OLE DB, ODBC) allow using various parameter notations, such as question marks and @-prefixed named parameters, so they are familiar to query writers. But most of the Delphi data access components accept only the old-school colon-prefixed notation. TacParametersValuesTransformer converts parameters to the right notation to follow these rules. (find the list of allowed notations here).

VCL Edition

How the TacQueryParametersTransformer works?

• It collects all parameters from the TacQueryBuilder.Parameters collection and group named parameters by their name.
• Calculates to which fields they are applied and other properties, such as if it's possible to replace a parameter with a list of values.
• Groups parameters used in one condition, such as "BETWEEN :date_begin AND :date_end" so that they can be cut off only together.
• Generates the SQL query text in which all groups with unset parameter values are cut off and macros are applied.
• Prepares the right TDataSet descendant with parameters' values assigned to the Params collection, so it's ready to execute.

How to work with the TacQueryParametersTransformer?

Follow the steps below to get the TDataset descendant ready for execution.

Consider that you already followed the steps of the Quick Start Guide, so you have the TacQueryBuilder object, syntax and metadata providers available in your unit.

1. Create new TacQueryParametersTransformer object.
2. Assign the instance of TacQueryBuilder object to the TacQueryParametersTransformer.QueryProvider property.
3. Look through the items of TacQueryParametersTransformer.ParameterValues collection and assign scalar values to the Value property or lists and SQL expressions to the ValueExpression property.
4. Get the result SQL query from the SQL property or call the CreateSQLDataset method to get TDataset object attached to the same DB connection as your metadata provider, with the Params collection initialized with parameter values.

That's all. You can use the result dataset to display the result to the user or for further data processing in your app. 

Code sample

paramsTransformer.QueryProvider := queryBuilder;

if paramsTransformer.Groups[0].FindByName('param') <> nil then
  paramsTransformer.Groups[0].Enabled := False;
  
for i := 0 to paramsTransformer.ParameterValues.Count - 1 do
  if paramsTransformer.ParameterValues[i].Name <> 'param' then
    paramsTransformer.ParameterValues[i].Value := 42;
    
dataSet := paramsTransformer.CreateSQLDataset(false, false);
sql := paramsTransformer.Sql;

Integration into the workflow

The newly created TacQueryParametersTransformer object can get the initial query from any object implementing the IacQueryProvider interface. It is currently implemented by the TacQueryBuilder, TacQueryTransformer objects and now by the TacQueryParametersTransformer by itself which makes Tac*Transformer objects stackable to design custom query processing logic.

It makes sense to create a chain of TacQueryBuilder - TacQueryTransformer - TacQueryParametersTransformer, so the user can:

• design a query in the TacQueryBuilder component,
• manipulate with data (change sorting, apply additional filters, etc.) by means of the TacQueryTransformer API and the accompanying TacCriteriaBuilder control,
• apply parameter values via the TacQueryParametersTransformer.

The usage sample of the TacQueryParametersTransformer API can be found in the main demo project.

ActiveX Edition

In the ActiveX Edition, you can leverage this feature via the ActiveQueryBuilderX.ParameterValues* set of properties.

How to work with ParameterValues collection?

Follow the steps below to get the text of your query with applied parameter values and cut-off conditions in the case a value is not defined for the parameter.

Consider that you already followed the steps of the Quick Start Guide, so you have the ActiveQueryBuilderX object available in your module.

1. Create a query with parameters (find the list of allowed notations here).
2. Look through the items of ActiveQueryBuilderX.ParameterValues[] collection and assign values to the Value property of each item. Strings will be treated as string literals and quoted with apostrophes in the result query text. You can apply scalar and list values to the Values property. Learn if it's possible to apply a list by checking the item's CanUseListExpressions property.
   OR
   Assign SQL expression to the Expression property of each item. 
3. Get the result SQL query from the ActiveQueryBuilderX.ParameterValuesSQL property.

Code sample in a PowerShell script

$aqb = New-Object -Com ActiveQueryBuilderXControls.ActiveQueryBuilderX
$aqb.SQL = "select 1 from Orders o where o.OrderId=:oid and o.CustomerId in (:two)"
 
Write-Output "query: $($aqb.SQL)"
Write-Output "ParametersCount: $($aqb.ParametersCount)"

Write-Output "paramValues.Count: $($aqb.ParameterValuesCount)"
For ($i=0; $i -lt $aqb.ParameterValuesCount; $i++) {
  $p = $aqb.ParameterValues($i)
  $p.Name
}

Write-Output "query without values: $($aqb.ParameterValuesSQL)"

# indexing by number
$one = $aqb.ParameterValues(0)

Write-Output "one.ParamsCount: $($one.ParamsCount)"
For ($i=0; $i -lt $one.ParamsCount; $i++) {
  $p = $one.Params($i)
  Write-Output "$($i): $($p.ComparedObject).$($p.ComparedField) $($p.CompareOperator) $($p.FullName)"
}

# indexing by parameter name
$two = $aqb.ParameterValues("two")

$one.Value = "124+1"
Write-Output "query with value: $($aqb.ParameterValuesSQL)"

$one.Expression = "124+1"
Write-Output "query with expression: $($aqb.ParameterValuesSQL)"

$two.Value = 1, "2+2", "'3'"
Write-Output "query with values list: $($aqb.ParameterValuesSQL)"

$two.Expression = 1, "2 + 2", "'3'"
Write-Output "query with expressions list: $($aqb.ParameterValuesSQL)"

Result:

query: Select 1 From Orders o Where o.OrderId = :oid And o.CustomerId In (:two)
ParametersCount: 2
paramValues.Count: 2
oid
two
query without values: Select 1 From Orders o
one.ParamsCount: 1
0: o.OrderId = :oid
query with value: Select 1 From Orders o Where o.OrderId = '124+1'
query with expression: Select 1 From Orders o Where o.OrderId = 124 + 1
query with values list: Select 1 From Orders o Where o.OrderId = 124 + 1 And o.CustomerId In (1, '2+2', '''3''')
query with expressions list: Select 1 From Orders o Where o.OrderId = 124 + 1 And o.CustomerId In (1, 2 + 2, '3')