Integrating Database Components

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Module Objectives • At the end of this module, you will be able to: • Access database tables, views, and stored procedures directly from your PBL code and integrate them into your process • Expose database tables for direct access • Integrate embedded SQL statements with your PBL code • Create SQL components and SQL Query components • Display database data in you business process application

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Module Roadmap

Database Integration – The Mechanics Working with Embedded SQL Displaying Database Data

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Integrating with a Database - Why • Makes database data available to the Studio Catalog • Drag/drop access to data from Studio’s Method Editor • Easy display and processing of data

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Target Databases

Informix

Oracle 8i or 9i

MS SQL Server 2000 or 2005

Sybase ASE

Cloudscape

PointBase

IBM DB2

MySQL

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Integrating With a Database Overview 1. Create the External Resource SQL Database resource

2. Create a Database Component SQL component or

SQL Query component

3. Introspect the database

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

~

Database Components • Database Components are responsible for exposing database information to the activity • There are two types • SQL – exposes a single database table • SQL Query – allows access to result set from a multi-table select or from stored procedure or view

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

External Resources • What are they? • Resources required by a Database Component that are not provided by Studio • Usually some type of server • Examples: database server, application server, COM bridge, .NET server, Java Jar file, Naming & Directory Service

• Most consist of connection or configuration data

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

The SQL Database External Resource • Required for the Database Component • Contains database server connection information • Specifically, the connection string for connecting to the database server

• Function: Establish JDBC connection with the database

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Creating a SQL Database External Resource Create the SQL Database External Resource 1. Create a New External Resource 2. Select the type of database and the JDBC driver 3. Enter specific database connection information See following slides for details…

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Creating a SQL Database External Resource 1. 2. 3. 4.

In Studio’s Catalog, right click on External Resources
New External Resource Name the resource Set type to SQL Database Select the type of database driver you’ll be using

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Creating a SQL Database External Resource 5. Fill in the connection information for your database server a. Varies with db type

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Example: Oracle SQL Database External Resource

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Exposing a Single Database Table • Finally, we create a SQL Component • Introspects the table

Database

• Determines columns, data types • Analyzes the dependencies • Identifies primary key(s)

• Exposes the table and selected columns as an object in the Catalog with attributes and methods

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Creating a SQL Database Component Create the SQL Database Component 1. Create a Database Component 2. Specify the configuration 3. Select table(s) to introspect See following slides for details…

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Creating a SQL Database Component 1. 2.

Create a new Catalog module for Database Components Right click on the new module, create a SQL Database Component

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Creating a SQL Database Component 3.

Specify which configuration •

SQL Database resource you previously defined

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Creating a SQL Database Component 3.

Select table(s) to introspect •

Each table becomes a separate component within the module

Introspected table now available in Catalog

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Other Options: Exposing a SQL Result Set • The SQL Query type Database Component • Encapsulate in an object a result set based on • Multi-table select • View • Stored procedure

• Types • Self-Contained: no parameters to the query or sp OR • Parametric: one or more parameters required by the query or sp

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Creating a SQL Query Database Component: Self-Contained Create the SQL Query Database Component 1. 2. 3. 4.

Add SQL Query Component to the module Enter your Select statement View results of your SQL statement View resulting data structure that Introspection creates

See following slides for details…

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Creating a SQL Query Database Component: Self-Contained 1. Add Component
SQL Query to your module 2. Choose your SQL Database type external resource as the configuration 3. Enter a name for the component

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Creating a SQL Query Database Component: Self-Contained 4. Enter the select statement a. Or stored procedure call

5. Click Preview to test the sql and see data

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Creating a SQL Query Database Component: Self-Contained 6. Click Next to see the resulting data structure that Introspection creates

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Other Options: Creating a SQL Query Database Component - Parametric Create the SQL Query Database Component 1. 2. 3. 4. 5.

Add SQL Query Component to the module Select the Parametric checkbox Add Input parameter View results of your SQL statement View resulting data structure that Introspection creates

See following slides for details… BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Creating a SQL Query Database Component: Parametric 1. Follow the same steps as you did for the self-contained SQL Query Component you previously created, with the following exception: select the Parametric checkbox

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Creating a SQL Query Database Component: Parametric 2. Add input parameter to query $:DATATYPE

3. Click Next and test with data value

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Creating a SQL Query Database Component: Parametric 4.

Preview to check syntax A popup window will appear

5.

Finally, close the popup preview and click Next to finish Introspection

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

More on the Database Component • Provides • Read/Write access for column data (SQL type component) • Read access for fields of result set (SQL Query type component) • Component built-in methods (load, store, delete – more in ALBPM Programming 2, Integration)

• Can also wrap the Database Component in a BPM Object and add user defined methods to access the component • Effectively encapsulating business rules for database access

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Module Roadmap

Database Integration – The Mechanics Working with Embedded SQL Displaying Database Data

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Working with Database Components • Access the component’s result set as if it were a database table • Use embedded SQL • No need to code the JDBC connection, create Statement, etc.

• Component is auto-commit – any SQL operation is automatically committed to the database

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Using Embedded SQL with Database Components • Use regular (ANSI-92 Entry Level) embedded SQL in your PBL code to access the component • Select, update, delete, insert against the result set in the database component • Exception: SQL Query components are read only

• Integrate embedded SQL with for/each or while loops to process result set

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Example: Select Using Database Component Return Multiple Rows SELECT name, customer_id, address FROM CUSTOMER WHERE customer_id > 200

Database Component

Join 2 components in a Select SELECT name, order_id FROM CUSTOMER, SALES_ORDER WHERE CUSTOMER.customer_id = SALES_ORDER.customer_id AND CUSTOMER.name = "STADIUM SPORTS"

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Example: Returning a Single Row Using the Load Method • An alternative to embedded SQL -- you can use the component’s load() method to return a single row, if the primary key for the table has been set

// Set customer_id (primary key) CUSTOMER.customer_id = 224

// Load the row with this id CUSTOMER.load()

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Example: Process a Result Set with a For/Each Loop // Store result in array and then display data for each row in SELECT * FROM CUSTOMER ORDER BY name ASC do customers[] = row.name

Note the dot notation to reference a column of the result set: row.name

end display customers

• row can be any variable (no need to declare it) • It represents an element of the array being processed by the for/each • Select statement is seen as an array in this context

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Module Roadmap

Database Integration – The Mechanics Working with Embedded SQL Displaying Database Data

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Displaying the Data: Some Possibilities • display with an array input will output a simple one column read only table • Examples on next slide

• Can “wrap” an exposed database table in a BPM Object, use a Presentation to display data • More about this in Part 2

• Use Fuego Block Component UI.Table • Selectable • Multi-column capability • Details later in this chapter

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Using the display Statement With SQL Query Database Components • Self – contained for each element in DatabaseComponents.ProductPrices do display “Min Price = “ + element.minprice end

• Parametric for each element in DatabaseComponents.ProductPrices (100860) do display “Min Price = “ + element.minprice end

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Block Components • Block components are Java components that are provided with Studio • They supply a rich suite of functionality and UI components that you can use in your ALBPM applications

• There are three ways to access block components • Verbose • Object method • Functional

• Examples on the next slide

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Using Fuego Block Components in Your Code: Three Styles • Verbose selMenu as String show Menu using entries = [“File”,”Edit”,”Help”], title = “Main Menu” returning selection = selMenu

• Object Method Menu.show (out selection: selMenu, title: “Main Menu”, entries: [“File”,”Edit”,”Help”] )

• Functional show (Menu, out selection: selMenu, title: “Main Menu”, entries: [“File”,”Edit”,”Help”] )

Return argument

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Using the Table Component to Display Data

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.

Labs •

Lab 14a: Integrating SQL Components • • • • •

•

Create external resource to support database components Expose database tables for the Order Management process Process a result set from a SQL Component Display a list of rows from a table Test using the Workspace

Lab 14b: Integrating SQL Query Components • • • •

Create a SQL Query Component Modify existing code to use the component Map arguments into the Freight Calculator subprocess Test

BPM Week (6 – 10 Oct 2008): OBPM – Essentials



End of Module

BPM Week (6 – 10 Oct 2008): OBPM – Essentials

Copyright © 2008 BEA Systems, Inc. All Rights Reserved. Unauthorized Duplication Prohibited

.