SAP HANA

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.

Actions

This section lists the actions supported by the connector. To understand how to configure the actions, see Action examples.

EMPLOYEE_PROCEDURE_HANA action

This action returns data of an Id.

Input parameters of the EMPLOYEE_PROCEDURE_HANA action

For example on how to configure the EMPLOYEE_PROCEDURE_HANA action, see Examples.

ExecuteCustomQuery action

This action allows you to perform specific tasks. For example, HANADB enables you to execute a custom SQL query.

Input parameters of the ExecuteCustomQuery action

For example on how to configure the ExecuteCustomQuery action, see Examples.

Action examples

Example - EMPLOYEE_PROCEDURE_HANA

1. In the Configure connector task dialog, click Actions.
2. Select the EMPLOYEE_PROCEDURE_HANA action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "ID":1
   }
   

If the action is successful, the EMPLOYEE_PROCEDURE_HANA task's connectorOutputPayload response parameter will have a value similar to the following:

 [{
"EMP_ID": 1.0,
"NAME": "Komal",
"CITY": "UP",
"SALARY": 7500.0,
"DEPARTMENT": "IT" 
}]

Example - ExecuteCustomQuery Join_GroupBy

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT CUSTOMER.COMPANY, SUM(ORDERS.AMOUNT) AS Total
   FROM ORDERS
   INNER JOIN CUSTOMER
   ON ORDERS.CUSTOMER_ID = CUSTOMER.ID
   GROUP BY CUSTOMER.COMPANY"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
"COMPANY": "google India",
"TOTAL": 1560.0
},{
"COMPANY": "IBM India",
"TOTAL": 4500.0
}]

Example - ExecuteCustomQuery Join

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID"
     }]
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
  "ID": 3.0,
  "NAME": "Kaushik",
  "AGE": 23.0,
  "AMOUNT": 3000.0
}, {
  "ID": 3.0,
  "NAME": "Kaushik",
  "AGE": 23.0,
  "AMOUNT": 1500.0
}, {
  "ID": 2.0,
  "NAME": "Khilan",
  "AGE": 25.0,
  "AMOUNT": 1560.0
}, {
  "ID": 4.0,
  "NAME": "Chaitali",
  "AGE": 25.0,
  "AMOUNT": 2060.0
}]

Example - ExecuteCustomQuery Groupby_Having_Count

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT COUNT(ID), CITY FROM GCP_HANA_CUSTOM GROUP BY CITY HAVING COUNT(ID) > 1"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

 [{
  "COUNT(ID)": 2.0,
  "CITY": "Delhi"
}] 

Example - ExecuteCustomQuery GroupBy

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT COUNT(ID), CITY FROM GCP_HANA_CUSTOM GROUP BY CITY"
   }
   

This example will update the ExecuteCustomQuery records. If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
  "COUNT(ID)": 1.0,
  "CITY": "Kolkata"
}, {
  "COUNT(ID)": 1.0,
  "CITY": "Katihar"
}, {
  "COUNT(ID)": 1.0,
  "CITY": "Bangalore"
}, {
  "COUNT(ID)": 2.0,
  "CITY": "Delhi"
}]

Example - ExecuteCustomQuery GroupBy_OrderBy

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT COUNT(ID), CITY FROM GCP_HANA_CUSTOM GROUP BY CITY ORDER BY COUNT(ID) DESC"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
  "COUNT(ID)": 2.0,
  "CITY": "Delhi"
}, {
  "COUNT(ID)": 1.0,
  "CITY": "Kolkata"
}, {
  "COUNT(ID)": 1.0,
  "CITY": "Bangalore"
}, {
  "COUNT(ID)": 1.0,
  "CITY": "Katihar"
}]

Example - ExecuteCustomQuery Insert_Table

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "INSERT INTO CUSTOMER VALUES (3, 'IBM India')"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
}]

Example - ExecuteCustomQuery SUM_AggFunc

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT SUM(SALARY) FROM CUSTOMERS"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

 [{
"SUM(SALARY)": 12000.0
}] 

Example - ExecuteCustomQuery AVG_AggFunc

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT AVG(SALARY) FROM CUSTOMERS"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
"AVG(SALARY)": 3000.0
}] 

Example - ExecuteCustomQuery MAX_AggFunc

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT MAX(SALARY) FROM CUSTOMERS"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

 [{
"MAX(SALARY)": 6500.0
}] 

Example - ExecuteCustomQuery MIN_AggFunc

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT MIN(SALARY) FROM CUSTOMERS"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
"MIN_SALARY": 1500.0
}]

Example - ExecuteCustomQuery COUNT_AggFunc

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT COUNT(*) FROM CUSTOMERS"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
"COUNT(*)": 4.0
}]

Example - ExecuteCustomQuery CreateTable

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "CREATE TABLE CUSTOMER (ID INT NOT NULL,COMPANY VARCHAR (20))"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
}]

Example - ExecuteCustomQuery AlterTable

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "ALTER TABLE GCP_HANA_CUSTOM ADD CONSTRAINT test PRIMARY KEY (ID)"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
}]

Example - ExecuteCustomQuery TruncateTable

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "TRUNCATE TABLE GCP_HANA_RENAME"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
}]

Example - ExecuteCustomQuery DropTable

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "DROP TABLE GCP_HANA_DROP"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
}]

Example - ExecuteCustomQuery RenameTable

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "RENAME TABLE GCP_HANA_CUSTOM1 TO GCP_HANA_RENAME"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
}]

Example - ExecuteCustomQuery UpdateTable

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 3"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
}]

Example - ExecuteCustomQuery SELECT_DATA_Table

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "SELECT * FROM CUSTOMERS"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
  "ID": 1.0,
  "NAME": "Ramesh",
  "AGE": 32.0,
  "ADDRESS": "Ahmedabad",
  "SALARY": 2000.0
}, {
  "ID": 2.0,
  "NAME": "Khilan",
  "AGE": 25.0,
  "ADDRESS": "Delhi",
  "SALARY": 1500.0
}, {
  "ID": 4.0,
  "NAME": "Chaitali",
  "AGE": 25.0,
  "ADDRESS": "Mumbai",
  "SALARY": 6500.0
}]

Example - ExecuteCustomQuery DeleteTable

1. In the Configure connector task dialog, click Actions.
2. Select the ExecuteCustomQuery action, and then click Done.
3. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "query": "DELETE FROM CUSTOMERS WHERE ID = 3"
   }
   

If the action is successful, the ExecuteCustomQuery task's connectorOutputPayload response parameter will have a value similar to the following:

[{
}]

Example - Insert a JSON array into a DB

To bulk insert data from a JSON array into a SAP HANA database table you can either use a For Each Loop task in Application Integration or a stored procedure in your SAP HANA database.

Using a For Each Loop task

This method uses Application Integration to loop through the JSON array. For each JSON object in the array, it calls a sub-integration that performs a single INSERT operation. The iteration happens within the integration itself.

1. Go to the Integration and create a For Each Loop task.
2. Locate the List to iterate field and assign your input JSON array variable to this field. The loop will now iterate over each object within this array.
3. Under Sub-integration details, provide the Sub-integration name and the API trigger ID of the sub-integration you want to call for each iteration.
4. In the Execution Task section, provide a Sub-integration variable name.
5. In the Sub-integration, use an ExecuteCustom query or a pre-configured entity configuration to perform the INSERT operation using the data from the incoming JSON object.

Using a stored procedure

This method uses your database to handle looping and insertion. You'll create a stored procedure in SAP HANA that can parse a JSON array and insert the data, and then you can use a connectors task in Application Integration to call the procedure and pass the entire JSON array to it.

1. Create a stored procedure in your SAP HANA database that can parse a JSON array and insert the data.

   {
   CREATE PROCEDURE "YOUR_SCHEMA"."SP_INSERT_FROM_JSON" (
       IN JSON_STRING CLOB
   )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   DEFAULT SCHEMA "YOUR_SCHEMA"
   AS
   BEGIN
   
       -- Parse JSON string using JSON_TABLE and insert directly into target table
       INSERT INTO "YOUR_SCHEMA"."YOUR_TABLE" (ID, NAME, GRADE)
       SELECT ID, NAME, GRADE
       FROM JSON_TABLE(:JSON_STRING, '$[*]'
           COLUMNS (
               ID   INT PATH '$.ID',
               NAME NVARCHAR(100) PATH '$.NAME',
               GRADE FLOAT PATH '$.GRADE'
           )
       );
   
   END;
   }
   

2. Use the Connectors task in Application Integration to call the procedure and pass the entire JSON array to it. In the Configure connector task dialog, click Actions.
3. Select the created procedure, and then click Done.
4. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a JSON data value similar to the following in the Default Value field:

   [
     { "ID": 1, "NAME": "Alice", "GRADE": 11.11 },
     { "ID": 2, "NAME": "Bob", "GRADE": 22.22 }
   ]
   

   If the action is successful, the data will be inserted into the database with the response code of 200.

Entity operation examples

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

Example - List all the employees

This example lists all the employees in the Employee entity.

1. In the Configure connector task dialog, click Entities.
2. Select Employee from the Entity list.
3. Select the List operation, and then click Done.
4. Optionally, in Task Input section of the Connectors task, you can filter your result set by specifying a filter clause. Specify the filter clause value always within the single quotes ('). For example, City='Bangalore'. You can also specify multiple filter conditions by using the logic operators. For example, City='Bangalore' and region='asia-south2'.

Example - Get a record

This example gets a record with the specified ID from the Employee entity.

1. In the Configure connector task dialog, click Entities.
2. Select Employee from the Entity list.
3. Select the Get operation, and then click Done.
4. In the Task Input section of the Connectors task, click EntityId and then enter 12294 in the Default Value field.

   Here, 12294 is a primary key value in the Employee entity.

Example - Create a record in Hana_unicode

This example creates a record in the Hana_unicode entity.

1. In the Configure connector task dialog, click Entities.
2. Select Hana_unicode from the Entity list.
3. Select the Create operation, and then click Done.
4. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "ID": 2.0,
   "UNICODEDATA": "Unicode データ型はグローバル化されたデータの保存に使用されます",
   "TEST": "Test7"
   }

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

   {
   "ID": 2.0,
   "UNICODEDATA": "Unicode データ型はグローバル化されたデータの保存に使用されます",
   "TEST": "Test7"
   }

Example - Create a record in Languages

This example creates a record in the Languages entity.

1. In the Configure connector task dialog, click Entities.
2. Select Languages from the Entity list.
3. Select the Create operation, and then click Done.
4. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "ID": 5,
   "ARABICTEXT": "مرحبا",
   "GERMANTEXT": "Günther",
   "HINDITEXT": "नमस्ते",
   "ENGLISHTEST": "Test1"
   }

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

   {
   "ID": 5.0,
   "ARABICTEXT": "مرحبا",
   "GERMANTEXT": "Günther",
   "HINDITEXT": "नमस्ते",
   "ENGLISHTEST": "Test1"
   }

Example - Update a record

This example updates a record in the Employee entity.

1. In the Configure connector task dialog, click Entities.
2. Select Employee from the Entity list.
3. Select the Update operation, and then click Done.
4. In the Task Input section of the Connectors task, click connectorInputPayload and then enter a value similar to the following in the Default Value field:

   {
   "SALARY": 9000.0
   }

5. Click entityId, and then enter 12294 in the Default Value field.

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

   {
   "SALARY": 9000.0
   }

Example - Delete a record

This example deletes the record with the specified ID in the Employee entity.

1. In the Configure connector task dialog, click Entities.
2. Select Employee from the Entity list.
3. Select the Delete operation, and then click Done.
4. In the Task Input section of the Connectors task, click entityId and then enter 10001 in the Default Value field.