The following sections provide insights into techniques and behaviors you might find useful:

Entity identity on reads

Reads from a database table requires the Entity Identity Columns returned for each row so that Corticon's in-memory tables uniquely identify each row returned from the Database. When the column value is not returned, ADC throws an error informing the user that Entity Identity has not been set in the Vocabulary Entity.primary key on every read statement.
Views, as a composite of tables with primary keys, require for each read of a row that you provide uniqueness with a composite of the primary keys of the underlying tables that form the view, or a composite key that is every column identity in the view.

Use of an IN ( ) instead of comparison operators in WHERE clause

Use an IN ( ) clause instead of an = sign in your WHERE clause. They mean the same thing; however, the IN ( ) clause can handle multiple values, while the = sign can only handle one value.

Consider here are three A Entities in memory. That means there are three values for { A.id }. In the following SQL note that the one with the IN ( ) is valid while the = sign is not: 
Select * from Patients where patientId IN ( 1, 2, 3 ) Valid
Select * from Patients where patientId = 1, 2, 3      Invalid
You cannot use an IN clause with <, <=, >, and =>. To prevent invalid SQL through variable substitution with <, <=, >, and =>, there can only be one instance of the Entity in working memory.

Inserting or updating multiple rows into specific database table(s)

When a Ruleflow establishes an ADC Service Call-out using the CorticonADC.write, ADC uses the metadata inside CORTICON_ADC_WRITE, and CORTICON_ADC_WRITE_DEFS tables to determine which Entities in the Vocabulary will be used to insert into which database table.

The core Table that contains the data about which Entity or Entities will be inserted or updated into the Database is in the CORTICON_ADC_WRITE_DEFS table. This section describes how the SEQUENCE, SQL, PRIMARY_NAME are used in one or multiple CORTICON_ADC_WRITE_DEFS to insert multiple records into the intended table.

Much like the CORTICON_ADC_READ_DEFS’ SEQUENCE field, the CORTICON_ADC_WRITE_DEFS’ SEQUENCE field determines in which order the CORTICON_ADC_WRITE_DEFS will fire. For each CORTICON_ADC_WRITE_DEFS’ SQL, there is a PRIMARY_ENTITY, which is used to create individual Insert Statements to be used by the database.

Using database Identity Strategies to populate Primary Key values is highly recommended. If Primary Key values are set within Rules, there are potential problems inserting or updating database records because of constraint violations.

Variable substitution is used to substitute the PRIMARY_ENTITY values into the SQL Statement.

Example:

SQL = UPDATE Treatment SET approved={Treatment.approved} 
             WHERE treatmentId={Treatment.treatmentId}				
PRIMARY_ENTITY = Treatment

For every instance of Treatment in memory a new SQL Statement will get created using those values inside the Treatment instance.

The user controls the SQL statement, and can customize an INSERT SQL to match the Identity Strategy appropriate for a particular Database:

  • In Oracle, Database Sequences are used to set the Primary Keys. You need to create your own Database Sequence and add that Sequence Name to the SQL statement.
  • In SQL Server, you can just set your Table to use Identity strategy to populate the Primary Key.
Note: Because you have control over the SQL, you can inject Database Functions directly in the SQL that are unrelated to Corticon, such as a sysdate function.

Multiple ADC instances can be added to one or many Ruleflows

There is no restriction on how many ADC instances you can have in a Ruleflow. Its position on the Ruleflow canvas is based on your use case. When retrieving extra data that is only needed in certain cases, you can put an ADC instance inside a Branch that will only fire under certain conditions. Similarly, you can control whether a Ruleflow execution writes and where it writes..

Each instance of the ADC works independently to do what it is assigned to do.

ADC limits which PRIMARY_ENTITY instances are used when the SQL Statement is an UPDATE instead of an INSERT

ADC will inspect each PRIMARY_ENTITY instance to determine if its Entity Identity attributes already have a value. Depending on whether these attributes are set, the PRIMARY_ENTITY will be classified as a UPDATE or INSERT candidate.

If all the Entity Identity attributes are set inside the instance, it is assumed that this instance already has a matching database record. In this case, you only want to use this instance in an UPDATE Statement rather than an INSERT Statement. If this instance were used in an INSERT Statement, a duplicate row would be created or the new row would fail because of a Primary Key Constraint Violation, since the record already exists in the Table.

If not all the Entity Identity attributes are set inside the instance, it is assumed that this instance does not have a matching database record. This instance should only be used in an INSERT Statement and not in an UPDATE Statement.

For example:

Patient
   patientId = 1
   patientName = "John"
   gender = "M"

Patient
   patientId = <null>
   patientName = "Jennifer"
   gender = "F"

CORTICON_ADC_WRITE_DEF
   SQL = INSERT INTO Patient (patientName, gender)
         VALUES ({Patient.patientName}, {Patient.gender})

Only the Jennifer Patient will be used with this SQL.

CORTICON_ADC_WRITE_DEF
   SQL = UPDATE Patient 
         SET (patientName = {Patient.patientName}, gender = {Patient.gender})
         WHERE id = {Patient.patientId}

Only the John Patient will be used with this SQL.

Each ADC task can use a different Datasource

Each instance of an ADC can call any CORTICON_ADC_READ or CORTICON_ADC_WRITE operation, and, for each CORTICON_ADC_READ and CORTICON_ADC_WRITE, there is a Datasource configuration.

In the following illustration, the root level of the Vocabulary shows tabs for the connections to four datasources:



The Query Datasource is shared by all ADC Datasources.

Information when execution fails

Various errors can occur during the execution of the ADC. Some common issues are:

  • CORTICON_ADC_READName or CORTICON_ADC_WRITEName does not exist.
  • Bad SQL statement, possibly due to variable substitution issues.
  • Bad Join Statement definition for an association.
  • Failed to connect to the Datasource.

Whatever the type of error, execution will not only stop on the service callout, but for the entire execution. If there is an issue in the service callout, then current working memory could be incomplete or corrupted. Either way, the safest play is to stop all execution.

An entry is made in the Corticon Log with the Exception, and a CcRuleMessage -> Violation message added to the Response.

Reloading revised query definitions

Corticon ADC and Batch processing rely on query definitions stored in a database. These definitions are loaded when a decision service is deployed to Corticon Server. If these query definitions change, you must either redeploy the decision service or notify Corticon to reload the query definitions.

  • When deployed as a WebService, the Corticon REST management API provides end points to force reload of query definitions. See /decisionService/reloadQueryService in the Corticon 6.3 REST API documentation.
  • When deployed in-process, the Corticon API provides methods to force reload of query definitions. See reloadDecisionServiceQueryService and reloadAllDecisionServicesQueryService in the Progress Corticon 6.3 Server API JavaDocs.
  • When running in Corticon Studio Tester, the decision service must be redeployed.