SAP ASE (Sybase)

Entities, operations, and actions

All the Integration Connectors provide a layer of abstraction for the objects of the 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 the connected 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 the Entities list will be empty.

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

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

• Action: An action is a first class function that is made available to the integration through 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 the Actions list will be empty.

Action examples

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

Example - Create a table

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   CREATE TABLE customqueries98(
        ID   INT ,
        NAME VARCHAR (20),
        AGE  INT,
        City  CHAR (25) ,
        SALARY   DECIMAL (18, 2)  );

This example shows the Execute Custom Query to create a new table. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter returns a blank payload.

Example - Alter a table

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   ALTER TABLE customqueries98 ADD CONSTRAINT ID PRIMARY KEY (ID)

This example shows the Execute Custom Query to alter a table. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter returns a blank payload.

Example - Truncate a table

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   TRUNCATE TABLE customqueries99

This example shows the Execute Custom Query to truncate a table If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter returns a blank payload.

Example - Drop a table

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   DROP TABLE customqueries99

This example shows the Execute Custom Query to drop a table. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter returns a blank payload.

Example - Validate SUM

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   SELECT SUM(SALARY) as Total FROM EMPLOYEES

This example shows the Execute Custom Query to validate the SUM of the Aggregate function. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

[{
  "Total": 7500.0
  }] 

Example - Validate AVG

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   SELECT AVG(SALARY) as Average FROM EMPLOYEES

This example shows the Execute Custom Query to validate AVG of Aggregate function. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

[{
  "Average": 7500.0
  }] 

Example - Validate MAX

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   SELECT MAX(SALARY) as Max_Salary FROM EMPLOYEES

This example shows the Execute Custom Query to validate MAX of Aggregate Function. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

[{
  "Max_Salary": 7500.0
  }] 

Example - Validate MIN

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   SELECT MIN(SALARY) as Min_Salary FROM EMPLOYEES

This example shows the Execute Custom Query to validate MIN of Aggregate function. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

[{
  "Min_Salary": 7500.0    
  }]

Example - Validate COUNT

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   select count(*) from EMPLOYEES

This example shows the Execute Custom Query to validate Count of Aggregate function. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

[{
  "": 1.0
  }]

Example - Use Join and GroupBy

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   SELECT SmallMoney.amount, Sum(Testsybase.clone) AS Total
     FROM Testsybase
     INNER JOIN SmallMoney
     ON Testsybase.id = SmallMoney.id
     GROUP BY SmallMoney.amount;

This example shows the Execute Custom Query by using Join and GroupBy. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

[{
    "amount": 1122.56,
    "Total": 14.0
  }, {
    "amount": 123.4567,
    "Total": 11.0
  }, {
    "amount": 1123.45,
    "Total": 13.0
  }]

Example - Use Join and Query parameters

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   SELECT e.payload,e.rest,ed.id FROM Testsybase e, Smallmoney ed where e.id=ed.id and ed.id=?

This example shows the Execute Custom Query by using Join and Query parameters. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

 [{
    "payload": 5678.9,
    "rest": 13.6666,
    "id": 2.0
  }] 

Example - Use GroupBy, Having and Count

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   select payload, count(payload) from Testsybase group by payload having count(payload)>=1

This example shows the Execute Custom Query by using GroupBy, Having and Count. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

[{
    "payload": 1234.56,
    "": 1.0
  }, {
    "payload": 2345.67,
    "": 1.0
  }, {
    "payload": 4352.78,
    "": 1.0
  }, {
    "payload": 6789.12,
    "": 1.0
  }]

Example - Use Column Cast, LIKE, and other operators

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   select e.id,e.clone from Testsybase e where e.payload>10 and e.rest LIKE '%1%' AND e.id<>3 order by e.id;

This example shows the Execute Custom Query by using Column Cast, LIKE, and other operators, such as i.e. <>. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

[{
    "id": 1.0,
    "clone": 10.0
  }, {
    "id": 2.0,
    "clone": 99.0
  }, {
    "id": 4.0,
    "clone": 14.0
  }]

Example - Execute a function by providing inputs in integration

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   SELECT dbo.MultiplyNumbers(5, 10) AS Result

This example shows the Execute Custom Query for executing a function by providing inputs in integration. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

 [{
    "Result": 50.0
  }] 

Example - Create a function by providing inputs in integration

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   CREATE FUNCTION MultiplyNumbersTest1
     (@num1 INT, @num2 INT) 
     RETURNS INT AS RETURN @num1 * @num2

This example shows the Execute Custom Query for creating a function by providing inputs in integration. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter returns a blank payload.

Example - Execute a procedure by providing inputs in integration

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Data Mapping section Open Data Mapping Editor and then enter a value similar to the following in the Input field:

   {
     "@EmpID": 1.0
     } 
     

This example shows the Execute Custom Query for executing a procedure by providing some input in integration. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter will have a value similar to the following:

 [{
    "EmpID": 1.0,
    "LastName": "Test",
    "FirstName": "Bcone",
    "Address": "Noida",
    "City": "UP"
  }] 
  

Example - Create a procedure by providing inputs in integration

1. In the Configure Connector dialog, select Region, Connection and Actions.
2. Select Execute Custom Query from the actions, and then click Done.
3. In the Task Input section of Connector, click on Edit Custom Script and then enter a value similar to the following in the Custom Query field:

   CREATE PROCEDURE SelectAllEmployees2 @EmpID int
     AS
     SELECT * FROM Employees WHERE EmpID = @EmpID

This example shows the Execute Custom Query for creating a procedure by providing inputs in integration. If the action is successful, the Execute Custom Query task's connectorOutputPayload response parameter returns a blank payload.

Entity operation examples

This section shows how to perform some of the entity operations in this connector.

Example - List all the records

1. In the Configure connector task dialog, click Entities.
2. Select numbers_table from the Entity list.
3. Select the List operation, and then click Done.

Example - Get a record from an entity

1. In the Configure connector task dialog, click Entities.
2. Select numbers_table from the Entity list.
3. Select the Get operation, and then click Done.
4. Set the entity ID to Get single numbers_table. To set the entity ID, in the Data Mapper section of the Data Mapping, click Open Data Mapping Editor and then enter 1.0 in the Input Value field and choose the EntityId as Local variable.

Example - Create a record

1. In the Configure connector task dialog, click Entities.
2. Select numbers_table from the Entity list.
3. Select the Create operation, and then click Done.
4. In the Data mapper section of the Task click OpenDataMapping editor and then enter a value similar to the following in the field:

   {
   "bigint_column": 1.2345678901234516E16,
   "usmallint_column": 124.0,
   "numeric_column": 987.664,
   "real_column": 67.88999938964847,
   "uint_column": 125.0,
   "ubigint_column": 1.2345678901234516E16,
   "decimal_column": 124.459,
   "smallint_column": 16.0,
   "int_column": 125.0,
   "floatn_column": 154.45,
   "tinyint_column": 127.0,
   "id": 20.0
   }

   Running this example, returns a response similar to the following in the Connector task's connectorOutputPayload output variable:

   {
   "Success": true 
   }
   

Example - Update a record

1. In the Configure connector task dialog, click Entities.
2. Select numbers_table from the Entity list.
3. Select the Update operation, and then click Done.
4. Set the entity ID to Update a numbers_table. To set the entity ID, in the Data mapper section of the Tasks, click entityId and then enter 20.0 in the given field.
5. In the Data mapper section of the Task click OpenDataMapping editor and then enter a value similar to the following in the field:

    
   {
   "uint_column": 128.0,
   "floatn_column": 154.95
   }

Running this example, returns an empty payload on success.

Example - Delete data from a table

1. In the Configure connector task dialog, click Entities.
2. Select numbers_table from the Entity list.
3. Select the Delete operation, and then click Done.
4. Set the entity ID to Delete numbers_table. To set the entity ID, in the Data Mapper section of the Data Mapping, click Open Data Mapping Editor and then enter 2.0 in the Input Value field and choose the EntityId as Local variable.