Using direct variable substitution, called dynamic variables, makes it simpler to write BML queries that change based on user input, without having to write a unique clause in the query for every possible permutation of inputs. As a result, scripts using this feature is much more scalable.
CPQ’s query language, BMQL, allows the use of dynamic variables for column names , Data table names and WHERE clauses.
When a BMQL call contains dynamic variables, the data types for the variables are validated and the dynamic variables are replaced with the input values at run-time. For more information on validation, see the section Validation below. Then BMQL returns a full SQL string that is executed.
To support dynamic variables in BMQL, an additional optional parameter, fieldMap, is available. There are two optional parameters: contextOverride and fieldMap.
The BMQL method supports the following three signatures:
bmql(QueryString)
bmql(QueryString, contextOverride)
bmql(QueryString, contextOverride, fieldMap)
When using contextOverride to specify a certain language and fieldMap to use variables inside a variable WHERE clause, the syntax for the entire call is:
bmql(QueryString, contextOverride, fieldMap); bmql("select columnName from tableName WHERE $where", lang, fields);
For more information about these optional parameters, see BMQL Parameters.
If variable substitution is needed within the WHERE clause, use the method signature which passes in the values of each of the variables.
These variables must be defined in a string Dictionary and passed in as the third parameter. In this case, a second parameter must also be defined.
For example, if the $where variable used the declared variables x_var and y_var, the variables would need to be added to a dictionary prior to the BMQL call.
Example 1:
table = “dataTableName”; //dataTableName is the name of an existing data table columns = “columnName”; fields = dict("string"); put(fields, "$field1", x_var); put(fields, "$field2", y_var); where = "x = $field1 AND y = $field2"; results = bmql("SELECT $columns FROM $table WHERE $where", lang, fields);
Example 2:
pno = "part123"; lead = integer[]{3,4,5} results = bmql("select part_number from _parts where part_number = $pno and lead_time in $lead");
The '$' symbol must be prepended to the variable name when used in the query string.
The data types of the variables must be string, integer or float.
For an IN condition, the data types of the variables must be string[], integer[] or float[].
Line item variables cannot be used in the query.
Example 3:
Using the function recordset() as an example, you can see how using variables works.
results = bmql("select part_number from _parts where part_number = 'part%'"); for result in results { partno = get(result, "part_number") ... }
BMQL returns results, which contains the list of data that matched the query.
Use the for loop on results to go through all the rows of data returned.
Use the get function to get the specific column from each iterated row.
Imagine that you want to make a complicated query that will change depending upon user inputs. Without dynamic variables, you are forced to write something like the following code, where you must have a clause for every possible input.
Before
result = recordset(); if (len(_bm_pline_name) <> 0 and len(_bm_pline_variable_name) <> 0) { result = BMQL(“SELECT str1 FROM table_100columns WHERE $where”,lang, fields); } elif (len(_bm_pline_name == 0 and len(_bm_pline_variable_name) <> 0) { result = bmql (“SELECT str1 FROM table_100_columns WHERE str3 = $_bm_pline_variable_name”); } elif (len(_bm_pline_name) <> 0 and len (_bm_pline_variable_name) == 0) { result = bmql(“SELECT str1 FROM table_100columns WHERE str2 = $_bm_pline_name”); } else { result = bmql(“SELECT str1 FROM table_100columns”); } print result;
After
Using dynamic variables, you can write something like this instead:
result = recordset();
fields = dict("string");
fields.put(fields, "$name", _bm_pline_name);
put(fields,"$varname",_bm_pline_variable_name);
where = " WHERE";
conjunction = "";
if (len(_bm_pline) &lt;&gt; 0) {
where = where + "str2 = $name";
conjunction = " OR ";
}
if (len(_bm_pline_variable_name) &lt;&gt; 0) {
where = where + conjunction + "str3 = $varname";
}
result = BMQL("SELECT str1 FROM table_100columns $where", fields);
The code is longer in this instance, but it is much more scalable, since you do not need a separate clause for each possible input.
When writing a query in BMQL that will use a dynamic variable, direct variable substitution should be used in lieu of string concatenation or full substitution.
Correct: Direct Variable Substitution
"results = bmql("SELECT $columns FROM $table WHERE $where")";
Incorrect: Concatenating Strings
"results = bmql("SELECT value FROM " + tableName + " WHERE date = $current_date")";
If there are variables in the WHERE clause variable, you must define these variables in a string Dictionary and pass them as a third parameter. In this case, you must also define a second parameter.
Most of the validation in BMQL that uses dynamic variables will occur when the code is executed. Previously, validation occurred when the code was checked or saved.
This change is required because variables aren't replaced with their values until runtime. However, syntax will still be checked when code is saved to confirm that variables are in the right places and the standard keywords are still there.
Therefore, when using variables for columns, Data Tables, or the entire WHERE clause, more rigorous testing of the BMQL should be done and potential errors should be handled by using the hasError(rs) and getMessage(rs) BML functions.
String literal BMQL calls are not affected by these changes and will continue to perform a full validation when checked and saved.
Dynamic Variables in the WHERE Clause
