DynamicSQL

Use the DynamicSQL component to run any SQL statement on any database that is configured as an external resource in your project. The SQL statements are sent directly to the database server via the JDBC driver; the Process Execution Engine does not check or modify the statements in any way.

You can do the following by using the DynamicSQL component:

Run database-specific SQL to improve performance or to leverage special features of your database server. For example, you can run Oracle's parallel query SQL.
Execute complex SQL statements that are not normally supported by PBL's embedded SQL syntax. For example, you can nest SELECT statements to create simple and correlated subqueries, or use the EXIST keyword.
Build database statements during runtime (hence the name DynamicSQL), using variables as parameters (late binding). For example, you can build a conditional expression (for example, a WHERE clause) based on input from the end users.

Important: For security and performance reasons, you should never build SQL statements by concatenating variables or Strings that are not trusted (either entered by end users or having come from external systems). Instead, use placeholders in the statement (defined with question marks) and supply the variable values to be used in their place. In this way, the JDBC driver "escapes" the values (protecting the query from malformed data) and increases the chances of caching and reusing the statement.

To access the BAM (Business Activity Monitoring) database, use the BAMQuery component.

DynamicSQL is built on top of Java's java.sql.PreparedStatement.

Example 1: Running a Simple SQL Query Using DynamicSQL


externalResource = "coffee_db"
sql = "SELECT COF_NAME, PRICE FROM COFFEES"

for each row in executeQuery(DynamicSQL, sentence : sql,
                             implname : externalResource ) do
    coffeeName = row[0]
    coffeePrice = row[1]
    display "Coffee "+coffeeName + "is $"+ coffeePrice                              
end

Example 2: Updating a Database Using DynamicSQL with Placeholders and Parameters

externalResource = "coffee_db"
sql = "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? "
sales = 75
coffeeName = "Colombian"
rowsModified = executeUpdate(DynamicSQL, sentence : sql,
	                     implname : externalResource,
			     inParameters : params)

Fuego.Sql : DynamicSQL

Example 1: Running a Simple SQL Query Using DynamicSQL

Example 2: Updating a Database Using DynamicSQL with Placeholders and Parameters