Using SQL Components

You can catalog SQL tables, views and store procedures. Use SQL components from your processes and BPM objects to query, update and execute SQL statements on external databases.

Oracle BPM provides developers the following mechanisms for accessing JDBC-compliant databases:

Using cataloged SQL components. For each SQL table, view or store procedure, Studio generates a BPM component with attributes and methods to read and update tables, and call procedures. In general, each BPM component represents a table and each component instance represents a row in that table. SQL components inherit from the standard Fuego.Sql.SqlObject component.
Using SQL syntax embedded in PBL code. This allows you use SQL statements which are statically checked at compilation time. A subset of the standard SQL language is accepted. Vendor-specific statements are not allowed in embedded SQL code. See Embedded SQL Overview for more details.
Using the standard Fuego.Sql.DynamicSQL component. This component gives you the most flexibility but provides no static checks at compilation time. With this component, you can build SQL statements dynamically at runtime, and you can send any command to your database. Refer to the Fuego.Sql.DynamicSQL component documentation for more details.

SQL Component methods

Components for cataloged SQL tables provide three methods:

load(): Loads a row from the database into the component attributes.
store(): Inserts/updates the component attribute values to the database.
remove(): Deletes the database row associated with this component instance.

Refer to the reference documentation of the standard Fuego.Sql.SqlObject component for details.

Components for cataloged stored procedures provide a single method:

call(...): Executes the store procedure. This method accepts the arguments defined by the stored procedure.

Auto-loading

If the primary key attributes of SQL table component are set, the component automatically calls its load() method the first time you read or set one of its non-key attributes. Example:

customer = CUSTOMER()  // New instance of CUSTOMER table component 
customer.id = "1234"   // sets value of primary key attribute

logMessage "Customer Name=" +
	 customer.name  // this triggers an implicit call to "load()"

You can disable this automatic loading behavior by setting the accessDatabase attribute of the component to false. This example is equivalent to the previous one but without using auto-loading:

customer = CUSTOMER()  // New instance of CUSTOMER table component 
customer.id = "1234"   // sets value of primary key attribute
customer.accessDatabase = false // disable auto-loading

// Note: "customer.name" evaluates to null (row not auto-loaded) 

customer.load()        // load row explicitly

logMessage "Customer Name=" + customer.name

Handling Database Connections

The Process Execution Engine manages and pools the connections to the database servers automatically.

From PBL code you don't explicitly open or close JDBC connections. When your PBL code instantiates a SQL component, the Engine automatically assigns a connection to that database from the pool. When all SQL components for that database get out of scope, the Engine releases the connection back to the pool.

SQL Components connecting to the same database within the same transaction share a single connection.

Handling Database Transactions

The Process Execution Engine always executes PBL code in the context of a transaction. In general, PBL code is called from a process activity, and the execution of the activity task defines the transaction boundaries.

From PBL code you don't explicitly start, commit or rollback a database transaction. When your PBL code first instantiates a SQL component, the Engine automatically starts a transaction on that database.

When the execution of a PBL script succeeds (no Exceptions thrown), the Engine commits all associated database transactions. When the execution of a PBL script fails (an Exception is thrown or predefined variable action indicates so) the Engine rolls back all associated database transactions.

When the Process Exception Engine runs on an EJB container (such as Oracle WebLogic or IBM WebSphere) and all JDBC data sources involved are configured to be "XA" (for distributed transactions), then database connections will participate on the same global transaction managed by the container.

SQL on client-side methods

SQL components can only execute in the context of the Process Execution Engine.

If you are using SQL components from a BPM object method, you should set property Service Side Method of your method to Yes.

Server-side methods cannot be invoked directly from BPM Object Presentations. If you need to access SQL components from a Presentation, extract the code that uses SQL into a separate method, make this new method Server Side and call it from the original client-side method.