Accessing external data by querying a database is a complex task that usually requires some coding by developers. For some complex and on-the-fly decision making it is necessary that rules are able to access external data (e.g., database) by querying without changing the rule application coding.

There are some situations where all the data we require to make a decision is loaded and available in memory. For example, if we are about to insert a product into inventory, we receive the product information from an actor (e.g., user, system, etc.) and then, based on the information gathered, we try to validate the input to make sure everything is entered correctly before proceeding with the process.

But there are situations where the rule application needs to access external data (e.g., database). In this example, the external data source can be any type of database. Your rule application can query a database during rule execution and fetch data to load it into its local variable/context and then make a decision.

To make rules access external data by querying a database, a Database command can simply be used as follows:

<Procedure name="Select Person FirstName" enabled="true">
    <Declaration>
        <Define name="recordId" direction="in"/>
        <Define name="person" direction="local"/>
        <Define name="firstName" direction="out"/>
    </Declaration>
    <Scope>
        <Var name="cnn" value="YOUR_CONNECTION_STRING"/>
        <Database connection-ref="cnn" type="DATABASE_TYPE">
            <SelectRow command="select * from person where id = @id" return="person">
                <Param name="id" ref="recordId"/>
            </SelectRow>
        </Database>
    </Scope>
    <Var name="firstName" value="person.FName"/>
</Procedure>

Database types can include Access, MsSql, Oracle or Odbc. And based on that type, your connection string must be set properly.

As you can see in the last line

<Var name="firstName" value="person.FName"/>

you can directly access the value of the column named FName that is stored in the person object. The next step is simply to integrate this rule within a decision flow and connect it to the next step, which runs a validation engine to validate the logic based on the values you have retrieved from the database.

It is worth mentioning that the rule parameter definition is untyped.

<Define name="person" direction="local"/>

What that means is that you do not even need to have a pre-defined type/class to access it in the rule application. All data will be jammed as the properties of person and will be accessible in runtime.

Last updated December 15th, 2020 at 08:04 am, Published June 11th, 2014 at 08:04 am