Important: CIC Studio version 5.1.11 or later is required to create and use Reference Tables.
You can use Reference Tables during transformation processing to convert incoming values into values your system can use. In addition, by defining your table in a particular way, you can make it available for viewing and modifying in the CIC Cockpit.
To create a Reference Table, perform the following procedures:
Create a Data Source
In this step, you create a project and a Data Source to be used by the CIC Integration Engine to access the Reference Table.
- Log in to CIC Studio.
- Create a new CIC Project (File > New > CIC Project). For more information, see Projects, Packages, and Objects.
- Create a Data Source in the new Project. For more information, see Data Sources.
- Close the editor displaying the Data Source without entering any data to define the Data Source. This creates a "blank" Data Source to be populated in the steps below.
- Open the new Data Source in the generic text editor.
- Copy the following
def connect()
anddef databaseMetaData()
statements and paste them in the text editor replacing the existingdef connect()
statement.def connect() { jdbcDriver { driverName = "<com.extol.ebi.globalvariable.lib.GlobalVariables.JDBC_DRIVER_NAME>" classpathEntries { "<com.extol.ebi.globalvariable.lib.GlobalVariables.JDBC_DRIVER>" } } url = "<com.extol.ebi.globalvariable.lib.GlobalVariables.URL>"
defaultSchema = "REFTABLES" username = "<com.extol.ebi.globalvariable.lib.GlobalVariables.USERNAME>" password = "<com.extol.ebi.globalvariable.lib.GlobalVariables.PASSWORD>" } def databaseMetaData() { } - Save the Data Source and close the generic text editor.
When you open the Data Source with the Data Source editor, you see the following:
Create an SQL Access Object
This SQL Access Object is used to actually create the table in the CIC Internal Database. In this step, you also ensure that the "cicSerialId" column is included. This column is required to make the Reference Table available in the CIC Cockpit.
- Create a SQL Access Object (File > New > SQL Access Object). For details, see SQL Access.
- Select the Internal Data Source (created in Create a Data Source above) in the Data Source Section.
- Add the SQL QUERY for Table creation. Here’s a sample:
CREATE TABLE "REFTABLES"."PURCHASE_ORDERS"(
"cicSerialId" SERIAL NOT NULL,
"OrderId" INTEGER NOT NULL PRIMARY KEY,
"Description" CHARACTER VARYING(255),
"SupplierPartAuxID" CHARACTER VARYING(255) NOT NULL, "ItemId" INTEGER NOT NULL,
"UnitPrice" NUMERIC(10,3) NOT NULL,
"Quantity" INTEGER NOT NULL
);
Note: The “cicSerialId” column must be added to the Reference table in order for the table to appear as a Reference Table in the CIC Cockpit UI. Without the “cicSerialId” column, insert, update, and delete operations cannot be performed. The column name is case-sensitive and should always be in camel case. Therefore, when you build the SQL Access query statement, make sure the column name is in camel case and wrapped properly in quotes, for example, “cicSerialId”
. The column should be of type SERIAL and NOT NULL as shown in the sample.
Creating a Description of the Reference Table
In the CIC Cockpit, when you view a Reference Table, there is a Description field that is blank by default. You can populate this field by adding a COMMENT to the table definition SQL QUERY you used when you created the SQL Access Object above like this:
COMMENT ON TABLE "REFTABLES"."<TABLE_NAME>" IS '<DESCRIPTION>';
For example, the following SQL:
COMMENT ON TABLE "REFTABLES"."PURCHASE_ORDERS" IS 'Table to view info related to Purchase Orders';
would result in a description for the PURCHASE_ORDERS table that reads "Table to view info related to Purchase Orders" in the CIC Cockpit Reference Table viewer.
Creating and Running the BPS
To use the SQL Access object, you must create a Business Process (BPS) that references the SQL Access object you set up to create the Reference table. See Creating Business Processes. Then, you must deploy the Project that contains the BPS. Once the Project is deployed, run the BPS to create the Reference table.
After it has been created, the table is visible in the CIC Cockpit UI (Integration Tab > Reference Tables).
Comments
0 comments
Please sign in to leave a comment.