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 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.
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.
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.
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.