Entities, operations, and actions

All the Integration Connectors provide a layer of abstraction for the objects ofthe connected application. You can access an application's objects only through this abstraction. The abstraction is exposed to you as entities, operations, and actions.

• Entity:An entity can be thought of as an object, or a collection of properties, in theconnected application or service. The definition of an entity differs from a connector to a connector. For example, in a database connector, tables are the entities, in a file server connector, folders are the entities, and in a messaging system connector, queues are the entities.

  However, it is possible that a connector doesn't support or have any entities, in which case theEntities list will be empty.

• Operation:An operation is the activity that you can perform on an entity. You can performany of the following operations on an entity:
  • List
  • Get
  • Create
  • Update
  • Delete

  Selecting an entity from the available list, generates a list ofoperations available for the entity. For a detailed description of the operations, see the Connectors task'sentity operations. However, if a connector doesn't support any of the entity operations, such unsupported operations aren't listed in theOperations list.

• Action:An action is a first class function that is made available to the integrationthrough the connector interface. An action lets you make changes to an entity or entities, and vary from connector to connector. Normally, an action will have some input parameters, and an output parameter. However, it is possible that a connector doesn't support any action, in which case theActions list will be empty.

Supported data types

The following are the supported data types for this connector:

• BIGINT
• BINARY
• BIT
• BOOLEAN
• CHAR
• DATE
• DECIMAL
• DOUBLE
• FLOAT
• INTEGER
• LONGN VARCHAR
• LONG VARCHAR
• NCHAR
• NUMERIC
• NVARCHAR
• REAL
• SMALL INT
• TIME
• TIMESTAMP
• TINY INT
• VARBINARY
• VARCHAR

Action examples

This section describes how to perform some of the actions in this connector.

Example - Execute a group by query

1. In theConfigure connector task dialog, clickActions.
2. Select theExecuteCustomQuery action, and then clickDone.
3. In theTask Input section of theConnectors task, clickconnectorInputPayload and then enter a value similar to the following in theDefault Value field:

   {"query":"select E.EMPLOYEE_ID,E.EMPLOYEE_NAME,E.CITY from EMPLOYEES E LEFT JOIN EMPLOYEE_DEPARTMENT ED ON E.EMPLOYEE_ID=ED.ID where E.EMPLOYEE_NAME = 'John' Group by E.CITY,E.EMPLOYEE_ID,E.EMPLOYEE_NAME"}

This example selects the employee records from theEMPLOYEES and theEMPLOYEE_DEPARTMENT tables. If the action is successful, your connector task'sconnectorOutputPayload response parameter will have the query result set.

Example - Execute a parameterized query

1. In theConfigure connector task dialog, clickActions.
2. Select theExecuteCustomQuery action, and then clickDone.
3. In theTask Input section of theConnectors task, clickconnectorInputPayload and then enter a value similar to the following in theDefault Value field:

   {"query":"select C.ID,C.NAME,C.CITY,C.O_DATE,E.EMPLOYEE_ID from customqueries C,Employees E where C.ID=E.Employee_id and C.NAME=?","queryParameters":[{"value":"John","dataType":"VARCHAR"}],"timeout":10,"maxRows":3}

This example selects employee records where the name of the employee isJohn. Notice that the name of the employee is parameterized by using thequeryParameters parameter. If the action is successful, your connector task'sconnectorOutputPayload response parameter will have a value similar to the following:

[{"NAME":"John","O_DATE":"2023-06-01 00:00:00.0","EMPLOYEE_ID":1.0},{"NAME":"John","O_DATE":"2021-07-01 00:00:00.0","EMPLOYEE_ID":3.0},{"NAME":"John","O_DATE":"2022-09-01 00:00:00.0","EMPLOYEE_ID":4.0}]

Example - Insert a record by using a sequence value

1. In theConfigure connector task dialog, clickActions.
2. Select theExecuteCustomQuery action, and then clickDone.
3. In theTask Input section of theConnectors task, clickconnectorInputPayload and then enter a value similar to the following in theDefault Value field:

   {"query":"INSERT INTO AUTHOR(id,title) VALUES(author_table_id_seq.NEXTVAL,'Sample_book_title')"}

This example inserts a record in theAUTHOR table, by using an existingauthor_table_id_seq sequence object. If the action is successful, your connector task'sconnectorOutputPayload response parameter will have a value similar to the following:

[{}]

Example - Execute a query with an aggregate function

1. In theConfigure connector task dialog, clickActions.
2. Select theExecuteCustomQuery action, and then clickDone.
3. In theTask Input section of theConnectors task, clickconnectorInputPayload and then enter a value similar to the following in theDefault Value field:

   {"query":"SELECT SUM(SALARY) as Total FROM EMPLOYEES"}

This example calculates the aggregate value of salaries in theEMPLOYEES table. If the action is successful, your connector task'sconnectorOutputPayload response parameter will have a value similar to the following:

[{"TOTAL":13000.0}]

Example - Create a new table

1. In theConfigure connector task dialog, clickActions.
2. Select theExecuteCustomQuery action, and then clickDone.
3. In theTask Input section of theConnectors task, clickconnectorInputPayload and then enter a value similar to the following in theDefault Value field:

   {"query":"CREATE TABLE TEST1 (ID INT, NAME VARCHAR(40),DEPT VARCHAR(20),CITY VARCHAR(10))"}

This example creates theTEST1 table. If the action is successful, your connector task'sconnectorOutputPayload response parameter will have a value similar to the following:

[{}]

Entity operation examples

Example - List all the employees

This example lists all the employees in theEmployee entity.

1. In theConfigure connector task dialog, clickEntities.
2. SelectEmployee from theEntity list.
3. Select theList operation, and then clickDone.
4. Optionally, inTask Input section of theConnectors task, you can filter your result set by specifying afilter clause.

Example - Get employee details

This example gets the details of the employee with the specified ID, from theEmployee entity.

1. In theConfigure connector task dialog, clickEntities.
2. SelectEmployee from theEntity list.
3. Select theGet operation, and then clickDone.
4. In theTask Input section of theConnectors task, clickEntityId and then enter45 in theDefault Value field.

   Here,45 is the primary key value of theEmployee entity.

Example - Create a employee record

This example adds a new employee record in theEmployee entity.

1. In theConfigure connector task dialog, clickEntities.
2. SelectEmployee from theEntity list.
3. Select theCreate operation, and then clickDone.
4. In theTask Input section of theConnectors task, clickconnectorInputPayload and then enter a value similar to the following in theDefault Value field:

   {"EMPLOYEE_ID":69.0,"EMPLOYEE_NAME":"John","CITY":"Bangalore"}

   If the integration is successful, your connector task'sconnectorOutputPayload field will have a value similar to the following:

   {"ROWID":"AAAoU0AABAAAc3hAAF"}

Example - Update an employee record

This example updates the employee record whose ID is 69 in theEmployee entity.

1. In theConfigure connector task dialog, clickEntities.
2. SelectEmployee from theEntity list.
3. Select theUpdate operation, and then clickDone.
4. In theTask Input section of theConnectors task, clickconnectorInputPayload and then enter a value similar to the following in theDefault Value field:

   {"EMPLOYEE_NAME":"John","CITY":"Mumbai"}

5. ClickentityId, and then enter69 in theDefault Value field.

   Alternately, instead of specifying theentityId, you can also set thefilterClause to69.

   If the integration is successful, your connector task'sconnectorOutputPayload field will have a value similar to the following:

   {}

Example - Delete an employee record

This example deletes the employee record with the specified ID in theEmployee entity.

1. In theConfigure connector task dialog, clickEntities.
2. SelectEmployee from theEntity list.
3. Select theDelete operation, and then clickDone.
4. In theTask Input section of theConnectors task, clickentityId and then enter35 in theDefault Value field.