You are here: Using BML > Functions & Scripts > Dynamic BMQL Variables

Dynamic BMQL Variables

OVERVIEW

CPQ Cloud’s query language, BMQL, allows the use of dynamic variables for

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.

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.

ADMINISTRATION

ClosedSyntax

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:

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);


ClosedDynamic Variables in the WHERE Clause

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 = int[] { 3, 4, 5 };

results = bmql("select part_number from _parts where part_number = $pno and lead_time in $lead");

Example 3:

Using the function Closedrecordset() 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")

...

}

ClosedExample 1: Changing a Query Based on Regions
ClosedExample 2: Changing a Query Based on User Inputs

ClosedGrammar

A dollar sign ($) indicates a dynamic variable.

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.

"results = bmql("SELECT $columns FROM $table WHERE $where")";

"results = bmql("SELECT value FROM " + tableName + " WHERE date = $current_date")";

"results = bmql(bmqlStringVariable)";

Closed$ Notation


ClosedException Handling

There are two exception handling situations.

This BML will validate, but will throw an error when run.

This BML will not throw any errors and will work. Optional parameters are ignored if they are not needed.


ClosedThe WHERE Clause as a Variable

The entire WHERE clause can be a string variable.

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.


ClosedMaking Everything Dynamic

In this example, everything that can be dynamic is dynamic.

bmqlReturn = "nothing";

select = "string1";

from = "uploadXMLtable";

lang = dict("string");

fields = dict("string");

x_var = "6.08";

put(fields, "$field1", x_var);

where = "float1 = $field1";

results = bmql("select $select from $from WHERE $where", lang, fields);

for result in results {

bmqlReturn = get(result, "string1");

}

return bmqlReturn;

Consult the Function Wizard to see Data Table names and associated column details.


NOTES

There are several issues to consider when using dynamic variables in BML queries.

ClosedValidation

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.

ClosedSQL Injection

Continue to follow existing best practices regarding SQL injection. Do not allow any user-generated data to be used directly in a BMQL statement.

In a fully dynamic WHERE clause, you must put variables into the fields array. These variables in the fields array will have SQL characters escaped.

For example:

fields = dict("string");

dict.put(fields, "$ca1", commerceAttribute1);

where = "field1 = $ca1 AND field2 = 'someValue'";

results = bmql("SELECT col1 FROM table WHERE $where", lang, fields);

Each customer is individually responsible for writing and testing their own dynamic BMQL calls to ensure that they are safeguarded from potential SQL injections.

BMQL does not support a parts query that retrieves more than 500 parts from a non-default price book.

RELATED TOPICS

Related Topics Link IconSee Also