A database connection can also provide designers and testers of Rulesheets and Ruletests with lists of enumerations, also known as possible values. While these lists can be created and maintained by hand on the Custom Data Types tab of a Vocabulary, you can retrieve lists from the connected database.

Note: The options for SCHEMA and ENUMERATION are not exposed by default. These advanced EDC features can be enabled by setting the following property in your brms.properties file: 
com.corticon.studio.edc.advancedFeatures=true

Consider the general behavior of enumerations, especially when retrieving labels and values from a database:

  • There can be only one instance of any label and any value in the list, whether created manually or imported. An exception will make the Vocabulary invalid. The database retrieval will work as expected but you will have to groom the results to make the lists valid. You can get optimal results when your database source prevents duplicates in the table columns you are using for your values or label-value pairs.
  • If you chose a label in a Rulesheet and that label is no longer available after an update, an error will occur. Any Rulesheet expressions that refer to the defunct label will be flagged as invalid. You must update the Rulesheet expressions to correct the problem.
  • If you chose a label in a Rulesheet and that label takes on a different value after an update, the current value is what is evaluated.
  • The value assigned - whether directly or as the label's value - at the time of deployment does not change thereafter on the server.

It is good practice to ensure that the data types of the retrieved values in the database are consistent with the Custom Data Type, and then extend the corresponding base data value in the attribute.

Procedures

The steps to implement custom data types retrieved from a database are, in summary, as follows:

  • A - Create or locate the database table and columns you want to retrieve.
  • B - Verify the database connectivity, and then import its metadata.
  • C - Define the Custom Data Type lookup information.
  • D - Import the enumeration elements.
  • E - Check the lists for duplicates.
  • F - Set the Data Type of appropriate attributes to the Custom Data Type.
  • G - Verify that the list functions correctly.

A - Create or locate the database table and columns you want to retrieve.

Note: This step uses the procedures detailed in the EDC Tutorials' steps for populating the database.
Note: See the tutorials Modeling Progress Corticon Rules to Access a Database using EDC and Connecting a Progress Corticon Decision Service to a Database using EDC. If you use the database you created in the tutorials, you need to refer to the database as Transportation – not Cargo -- to stay in synch with this example. You could instead simply create a Cargo database in SQL Server, and then import the sample data in the Studio's Tutorial/Tutorial-Done folder, Cargo_data.sql.

You need to add two tables to the SQL Server database to demonstrate both value-only and label+value enumerations:

  1. Start the SQL Server Management Studio, and then expand the tree for Databases : Cargo : Tables. Right-click on Tables and choose New Table. Enter Model as the only column name, as shown:

  2. Choose the menu command File > Save Table_1, enter the name Planes, and then click OK.

  3. Create another table, now with two columns named planeCarrier and planeID, saving it as Carrier.
  4. Click New Query, copy/paste the following text, and then click Execute. 
    
INSERT INTO Cargo.dbo.Planes (Model) VALUES ('DC-10');
INSERT INTO Cargo.dbo.Planes (Model) VALUES ('MD-11');
INSERT INTO Cargo.dbo.Planes (Model) VALUES ('747');
INSERT INTO Cargo.dbo.Planes (Model) VALUES ('777');
INSERT INTO Cargo.dbo.Carrier (planeCarrier,planeID) VALUES ('UPS','N1001');
INSERT INTO Cargo.dbo.Carrier (planeCarrier,planeID) VALUES ('FedEx','N1002');
INSERT INTO Cargo.dbo.Carrier (planeCarrier,planeID) VALUES ('DHL','N1003');
INSERT INTO Cargo.dbo.Carrier (planeCarrier,planeID) VALUES ('GreatWall','N1004');
INSERT INTO Cargo.dbo.Carrier (planeCarrier,planeID) VALUES ('Heavylift','N1005');
  5. In the tree, right-click on dbo.Planes, and then choose Edit Top 200 Rows.

    The Planes data is as we intended. It is ready for our use in the Corticon Studio.

  6. Similarly, right-click on dbo.Carrier, and then choose Edit Top 200 Rows.

    The Carrier data is as we intended. It is ready for our use in the Corticon Studio.

B - Verify the database connectivity, and then import its metadata.

We want to bring the information about the table definitions into the Studio:

  1. In Corticon Studio, confirm that you have the same good connection you achieved in Getting Started with EDC
  2. With Cargo.ecore open its editor, select the Vocabulary root, and the click METADATA Import, as illustrated:

C - Define the Custom Data Type lookup information.

We now can specify how we want to use the data and then bind it to the appropriate database table and columns:

  1. Click on Cargo to get to its top level, and then select the Custom Data Types tab.
  2. Click on the next empty row, enter model as the Data Type Name, select String as the Data Type, and Yes as the Enumeration.
  3. Click on the Lookup column in the row to expose its dropdown, and then choose Cargo.dbo.Planes that we imported in the database metadata.


  4. We are using a values-only lookup, click on the row's Values Column to select its one database column, Model:
  5. For the other table, click on the next empty row, enter carrier as the Data Type Name, select String as the Data Type, and Yes as the Enumeration.
  6. Click on the Lookup Table Name in the row to expose its dropdown, and then choose Cargo.dbo.Carrier that we imported in the database metadata.
  7. We are using a label-values lookup, so click on the row's Labels Column to select planeCarrier, and then in the Values Column to select planeID:

Everything we have entered is red! That's because Studio has no data for either of these enumeration sets.

D - Import the enumeration elements.

Once you have defined the database table and columns you want, you can retrieve the data:

  1. Choose the menu command Database Access > Import Enumeration Elements, as shown:

  2. The retrieved values are displayed in the associated Labels and Values window to the right, as shown for the model:


E - Check the lists for duplicates.

Unless you enforced uniqueness in the source database. To demonstrate what happens, we'll add an existing value to the model enumerations.

  1. In the Values retrieved column, enter a new value that is already there, such as 777, as shown:


    The duplicates are both highlighted in red, and the Cargo.ecore file is marked as being in an error state.
  2. Remove the line (or change it to something unique) and the Vocabulary is again valid.

F - Set the Data Type of appropriate attributes to the Custom Data Type.

With our enumeration lists imported from the database and verified as free of duplicate labels or values, we can link them to the attributes that will use them:

  1. Aircraft.aircraftType:

  2. Aircraft.tailNumber:

G - Verify that the list functions correctly.

To verify that the lists perform as expected, use them in a Rulesheet or Ruletest :

  1. In a Rulesheet Actions area, enter two new lines, one with the attribute syntax Aircraft.aircraftType and the other with Aircraft.tailNumber, as shown:
  2. Click on the aircraftType where it intersects with column 1, as shown:

    The pulldown displays our imported values, as well as blank and null.
  3. Click on the tailNumber where it intersects with column 1, as shown:

    The pulldown displays our imported label, as well as blank. The label is a place holder for its value.

For more information about enumerations and retrieving values from databases, see: Enumerations retrieved from a database