obpm external database

The table should already be created. The sql script is in Avio-OBPM Training\Database\

 

In obpm, create external resources

 

module >> catalogue >> SQL

select the external source created

Here are the objects imported from the database

Here are some sample code to use the external database

customerNames as String[]
for each element in 
    SELECT name FROM customer
do
    customerNames[] = element.name
end
display customerNames

// This is the SQL query to be run by the DynamicSQL component
query as String = "select name from customer"

// this implementation name is the name of the database's external resource
extResource as String = "Order Management Database"

customerNames as String[]
for each row in executeQuery(DynamicSQL, 
                             sentence : query, 
                             implname : extResource) do

    customerNames[] = String(row["name"])
end

display customerNames

query as String = "select count(*),sum(credit_limit),avg(credit_limit),"
query = query + "max(customer_id),min(customer_id) from customer"

extResource as String = "Order Management Database"

// this only will return one row so it will enter to loop and immediately leave
for each row in executeQuery(DynamicSQL, 
                        sentence : query, 
                        implname : extResource) do
    // Note how to retrieve the returned value for each of the aggregate functions
    display "The count of customers is " + row["count(*)"] + " customers" + 
    "\nThe sum of all their credit limits is: " + row["sum(credit_limit)"] + 
    "\nThe average of their credit limits is: " + row["avg(credit_limit)"] + 
    "\nThe maximum of all the customer ids is: " + row["max(customer_id)"] + 
    "\nThe minimum of all the customer ids is: " + row["min(customer_id)"]
end

param as Any[]
customerList as String[]
param[] = "E%"
param[] = 200
query as String = "select name from customer where name like ? and customer_id > ?"
extResource as String = "Order Management Database"

for each row in executeQuery(DynamicSQL, 
                     sentence : query, implname : extResource, 
inParameters : param) do
    // keep adding customer names to the array until all the customers have been added
    customerList[] = "     " + row["name"]
end

display customerList

query as String
query = query + "select * from customer c1 where credit_limit > "
query = query + "(select avg(c2.credit_limit) from customer c2 "
query = query + "where c1.state = c2.state)"

extResource as String = "Order Management Database"
customerList as String[]

for each row in executeQuery(DynamicSQL, sentence : query, implname : extResource) do
    // keep adding customer names to the array until all the customers have been added
    customerList[] = "     " + row["name"]
end

display customerList

2nd part

Create Heir

The result is a BPM object would be created

Then you can create presentation from BPM object. For example, Detail_Customer

Select the textbox of “CustomerId”, click “Properties” tab in the upper right corner.

This will cause a database call to be invoked based on the value the user entered in this field. All the other fields will be automatically populated.

Create a method on BPM object to test it, “testPresentation”

Put in this code to test

input this using selectedPresentation = "Detail_Customer"

Right click in the editor and click “Run This”. Enter 226 in the customerId and the other fields are populated automatically.

It is also possible to build BPM object from Scratch and use sql to populate the data. For detail see the example in  Avio-OBPM Training\Database\