You are here: Using BML > Functions & Scripts > Direct DB Access

Direct DB Access

OVERVIEW

This group of functions allows direct access to data stored in CPQ Cloud databases.

ADMINISTRATION

Closedbmql()

This function returns a RecordSet containing the results of the SQL query. CPQ Cloud can query system tables and user-create Data Tables from within BML, using a SQL-like syntax.

For more information about this function, see BigMachines Query Language (BMQL) Overview.


Closedgetboolean()

Returns the boolean value in the Record for the provided field name.

Syntax: Boolean getboolean(Record record, String fieldName)

Parameters:

# Parameter Data Type Description
1 record    
2 fieldName    

Return Type:

Example of getboolean():

rows = bmql("select intcol... ");

for row in rows {

val = getboolean(row, "intcol");

if(val); // can do boolean operations.Useful in if conditions

}


Closedgetdate()

This BMQL function returns the date value in the Record for the provided field name.

Syntax: Date getdate(Record record, String fieldName)

Parameters:

# Parameter Data Type Description
1 record    
2 fieldName    

Return Type:

Example of getdate():

rows = bmql("select datecol... ");

for row in rows {

val = getdate(row, "datecol");

isweekend(val); // can do date operations.Date functions can be applied

}


Closedgetfloat()

Returns the float value in the Record for the provided field name.

Syntax: Float getfloat(Record record, String fieldName)

Parameters:

# Parameter Data Type Description
1 record    
2 fieldName    

Return Type:

Example of getfloat():

rows = bmql("select intcol... ");

for row in rows {

val = getfloat(row, "intcol");

val2 = val * 0.1; // can do float operations

}


Closedgetint()

Returns the integer value in the Record for the provided field name.

Syntax: Integer getint(Record record, String fieldName)

Parameters:

# Parameter Data Type Description
1 record    
2 fieldName    

Return Type:

Example of getint():

rows = bmql("select intcol... ");

for row in rows {

val = getint(row, "intcol");

val2 = val + 10; // can do integer operations

}


Closedgetmessage()

Returns the error message in the given RecordSet if it has errors with query execution; empty otherwise.

Syntax: String getmessage(RecordSet recordSet)

Parameters:

# Parameter Data Type Description
1 record    
2 fieldName    

Return Type:

Example of getmessage():

rows = bmql("select col... ");

if(haserror(rows)) {

msg = getmessage(rows); // msg has the error message why the query failed

}


Closedgetpartsdata()

This function is deprecated, and no longer supported. It returned a 2-D array of String containing parts data for valid PartNumbers passed in.

This function is vulnerable to SQL injection. Use BMQL() instead.


Closedgettabledata()

This function is deprecated, and no longer supported. It returned a 2-D array of String containing Data Table data matching the condition specified.

This function is vulnerable to SQL injection. Use BMQL() instead.


Closedgettransaction()

The function gettransaction(Long bsId) simplifies access to stored transaction information. It retrieves, as a string, the transaction XML for a given Transaction ID.

Syntax: gettransaction(Long bsId)

Parameters:

# Parameter Data Type Description
1 record    
2 fieldName    

Return Type: String

Example of gettransaction():

transactionXML = gettransaction(12345); // 12345 is a transaction id (bs id)

The transaction XML is contained in the <transaction> node. The result string looks like this:

<?xml version="1.0" encoding="UTF-8"?>

<transaction><category>bm_cm_bs_data</category>...<num_transitions>5</num_transitions></transaction>


Closedhaserror()

Returns true if fetching the given RecordSet failed and has errors with query execution; false otherwise.

Syntax: Boolean haserror(RecordSet recordSet)

Parameters:

# Parameter Data Type Description
1 record    
2 fieldName    

Return Type:

Example of haserror():

rows = bmql("select col... ");

if(haserror(rows)) {

... ; // comes in here if the query execution failed

}


Closedrecordset()

Returns a new RecordSet to be used for later assignments. It is a collection of dictionaries. It can also be used as a function in conjunction with BMQL.

Syntax: RecordSet recordset()

Parameters:

# Parameter Data Type Description
1 record    
2 fieldName    

Return Type: record set

Example of recordset():

rs = recordset();

if(...) {

rs = bmql(query1);

} else {

rs = bmql(query2);

}


Closedrecordset() and SQL Queries

Returns a RecordSet containing the results of the SQL query.

Syntax: RecordSet bmql(String sqlQuery [, String Dictionary contextOverride, String Dictionary fieldMap])

Example:

results = bmql("select part_number from _parts where part_number = 'part%'");

for result in results {

partno = get(result, "part_number");

...

}

For more information on using variables in a query, see Dynamic BMQL Variables.


NOTES

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