skip to main content
Corticon Server: Data Integration Guide : Advanced ADC Topics : Tips and techniques in SQL data integration

Try Corticon Now

Tips and techniques in SQL data integration

The following sections provide insights into techniques and behaviors you might find useful:
* Use of an IN ( ) instead of comparison operators in WHERE clause
*Inserting or updating multiple rows into specific database table(s)
*Multiple ADC instances can be added to one or many Ruleflows
*Each ADC task can use a different Datasource
*Information when execution fails

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 { }. 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_DEFSSEQUENCE field, the CORTICON_ADC_WRITE_DEFSSEQUENCE field determines in which order the CORTICON_ADC_WRITE_DEFS will fire. For each CORTICON_ADC_WRITE_DEFSSQL, 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.
SQL = UPDATE Treatment SET approved={Treatment.approved}
WHERE treatmentId={Treatment.treatmentId}
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:
*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.