Open topic with navigation

Kony Fabric console User Guide: Integration > Configure the Integration Service > Database Adapter

Relational Database Adapter

With Kony Fabric database adapter, you can connect to your own database as an endpoint. After you configure the database adapter in Kony Fabric Console, you can perform create, read, update, and delete (CRUD and Binary CRUD) operations on data in the tables and invoke stored procedures, functions, and views.

For example, banks maintain a store of users and their details. With Kony Fabric database adapter, banks can connect to their own databases and manage customers data.

Advantages of Kony Fabric Database adapter

Following are the advantages of using Kony Fabric Database Adapter:

Create a Database Adapter

Creating a database adapter involves the following steps:

Configure Database End-point Adapter

To configure the Database Adapter in the Integration service definition tab, follow these steps:

  1. In the Name field, provide a unique name for your service.
  2. From the Service Type list, select Database.

    If you select Database, the Database Type, Database Connection URL,and other details are displayed.

    Note: XML is selected, by default.

  3. Provide the following details in the Connection Parameters section to create a Database Adapter.
    Field NameDescription
    Database

    Type the database driver class details manually in the box or select the database type from the database list. If you select the database type from the database list, the driver class details of the selected database will be filled automatically in the field. The database driver class for each database type is as follows:

    • MySQL: com.mysql.jdbc.Driver
    • PostgreSQL: org.postgresql.Driver
    • SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver
    • Oracle: oracle.jdbc.OracleDriver
    Database Connection URL

    Type the connection URL in the format given in the help text. The help text in the box changes based on the selected database driver class. You can also copy and paste the connection URL format by clicking the help icon at Database Connection URL, and make the required changes to format. The format of the connection URL is
    jdbc:<type of Database>://<ip_address>:<port>

    The connection URL of the supported database types are as follows:

    • Oracle: jdbc:oracle:thin:@10.10.1.192:8081:sid
    • MySQL: jdbc:mysql://10.10.1.192:8081
    • SQL Server:jdbc:sqlserver://10.10.1.190:8081
    • PostgreSQL: jdbc:postgresql://10.10.1.192:8081

    User IDType the user ID for the connection URL.
    Password Type the password for the user ID which you have entered.
    Advanced Settings

    Specify the advanced settings like Soft Delete Flag and Autogenerated fields.

     {'softdeleteflag':'<columnName>', 
'softdeleteactivevalue':<active-value>, 
'softdeleteinactivevalue':<inactive-value>, 
'AutogeneratedFields': {'TableName': ['Field1Name','field2Name'...] }
{"issoftdeletedefault ": "false",
    "lastupdatetimestamp": "<columnname>"}

    Note: When the lastupdatetimestamp is set, the RDBMS adapter identifies this column as the ChangeTrackingColumn to track delta/updated records. All the objects in the given Object Service must have the same ChangeTracking column name.

    • To soft delete a record - 'softdeleteflag':'<columnName>.

      Note: To soft delete a column mention the softdelete column name. Ensure all the tables have the same softdelete column name.

    • Specify the active/inactive values for softdelete column by setting -.

       {'softdeleteactivevalue':<active-value>, 
'softdeleteinactivevalue':<inactive-value> }
    • To specify the autogenerated fields - ''AutogeneratedFields': {'TableName': ['Field1Name','field2Name'...] }}

    • To turn off the default behavior of soft delete set - {"issoftdeletedefault ": "false"}.

    Max Pool Size

    Specify maximum number of connections in the connection pool.

    Connection Timeout (ms)Type the connection timeout in milliseconds.
  4. Click Test Connections if you want to check the database connection. If the entered details are correct, the system displays the following message: Valid Database connection details.
  5. If your database is configured with a proxy server, you must select an environmentand then click Test Connection to test the database connectivity. The environment should be => V8.3.
    For example, you have the Kony Fabric Console installed on one machine, and the Runtime and Database servers installed on another machine. When you create an integration service of type Relational Database, the Console must be established with a VPN connection to the RDBMS server. So that, when you test the Database connection, the test case will be successful. You can do this by selecting the correct environment for your Runtime Server which will ensure a VPN connection between the Console and the Runtime Server and test the database.
    If the entered details are correct, the system displays the following message: Valid Database connection details.
  6. ClosedFor additional configuration of your service definition, provide the following details in the Advanced section.
    Field Description
    Custom Code To specify a JAR associated to the service, select one from the Select Existing JAR drop-down menu or click Upload New to add a new JAR file. Make sure that you upload a custom JAR file that is built on the same JDK version used for installing Kony Fabric Integration.
    API Throttling
    • If you want to use API throttling in Kony Fabric Console, to limit the number of request calls within a minute. do the following:

      1. In the Total Rate Limit text box, enter a required value. This will limit the total number of requests processed by this API.

      2. In the Rate Limit Per IP field, enter a required value. With this value, you can limit the number of IP address requests configured in your Kony Fabric console in terms of Per IP Rate Limit.

    • To override throttling from Kony Fabric App Services Console, refer to Override API Throttling Configuration.

    Note: All options in the Advanced section are optional.

  7. Enter the Description for the service.
  8. Click SAVE to save your service definition.

Create CRUD Operations for Database Adapter

The Operations List tab appears only after the service definition is saved.

Note: Click Operations List tab > Configure Operation. The Configured Operations list appears.

  • To create an operation, follow these steps:
    1. Click SAVE & ADD OPERATION in your service definition page to save your service definition and display the NewOperation tab for adding operations.
                          OR
      Click Add Operation to add a new operation or from the tree in the left pane, click Add > Add New Operation.
      ClosedClick to View image

      Note: To use an existing integration service, refer to How to Use an Existing Integration Service.

    1. Provide the following details to configure an operation.
      FieldDescription
      Schema Select the schema loaded based on your database configuration.
      Object Type
      ClosedSelect the object from the list.
      • Table - Select available tables from the Objectlist, and select CRUD operations for the tables.
      • View - Select available views from the Object list and, select CRUD operations for the tables.
      • Stored Procedures - Select available stored procedures from the Object list.

      Note: While adding operations to a database integration service, Kony Fabric allows you to select CRUD operations only for table and view object types.

      ObjectSelect the checkboxes for the selected object type. You can select one or more objects.
      OperationsFrom the operations list, select the required check boxes for CRUD operations for tables. For views, only GET is supported. You can select one or more CRUD operations.
    2. Click Add Operation. The new operations are created under Configured Operations section.

      Operation names are auto-generated in the format. The default name format of a database operation is <schema_name>_<table_name>_<operations>. You can change the operation name if required.
      For example, RdbmsDetails_CustomerDetails_create.

      When an admin creates CRUD operations for a database adapter, the admin is under a particular schema. To customize fields, refer to How to Configure CRUD Operations for Database Service.

    Configure CRUD Operations for a Database Adapter

    After you create an Database service, configure CRUD operations as following:

    Create a Database Record with Create Operation

    Under Configured Operations list, hover your cursor over the create operation and click Settings (the three dots) >> Edit.

    Note:  To edit an operation, you can also click the operation from the service tree pane.

    The system displays the selected operation in the edit mode. You can perform following actions in this window:

    1. You can add an entry by clicking Add Parameter, if entries for the input and the output tabs do not exist.

    2.  To make duplicate entries, select the check box for the entry, click Copy, and then click Paste.

    3. To delete an entry, select the check box for an entry, and click Delete.
    4. Enter the following fields to create a database record.
      FieldsDescription
      NameThe Name field is pre-populated with fields names of the selected database. You can edit this field.
      Operation Security Level

      It specifies how a client must authenticate to invoke this operation.

      ClosedSelect one of the following security operations in the Operation Security Level field.
        • Authenticated App User – It restricts the access to clients who have successfully authenticated using an Identity Service associated with the app.
        • Anonymous App User – It allows the access from trusted clients that have the required App Key and App Secret. Authentication through an Identity Service is not required.
        • Public – It allows any client to invoke this operation without any authentication. This setting does not provide any security to invoke this operation and you should avoid this authentication type if possible.
        • Private - It blocks the access to this operation from any external client. It allows invocation either from an Orchestration/Object Service, or from the custom code in the same run-time environment.

      Note: The field is set to Authenticated App User, by default.

      Action

      The field is pre-populated with operation names of the selected database. You cannot edit this field.

    1. ClosedFor additional configuration of request (or) response operations, provide the following details in the Advanced section.
      Custom Code Invocation You can add pre and post processing logic to services to modify the request inputs. When you test, the services details of various stages in the service execution are presented to you for better debugging. All options in the Advanced section are optional. For more details, refer to Preprocessor and Postprocessor.
      Additional Configuration Properties Additional Configuration Properties allows you to configure service call time out cache response. For information on different types of configuration properties, refer Properties.
      Front-end API Front-end API allows you map your endpoint (or) backend URL of an operation to a front-end URL. For detailed information, refer Custom Front-end URL.
      Server Events Using Server Events you can configure this service to trigger or process server side events. For detailed information, refer Server Events.

      Note: All options in the Advanced section for operations are optional.

    Configure Request Operation for Database Adapter

    Integration services accept only form-url-encoded inputs for all input parameters provided in service input parameters (request input).

    1. To configure the parameters, do the following:
      FieldDescription
      TEST VALUEEnter a value. A test value is used for testing the service.
      DEFAULT VALUEEnter the value, if required. The default value will be used if the test value is empty.
      SCOPE

      Select request or session. This field is set to Request, by default.

      DATA TYPEThe default datatype for the selected column is loaded under DATATYPE field.
      EncodeSelect the checkbox to enable an input parameter to be encoded. For example, the name New York Times would be encoded as New%20York%20Times  when the encoding is set to True. The encoding must also adhere the HTML URL encoding standards.
      DescriptionEnter the description for request input.
    2. To validate the details, click Fetch Response. For more information, refer Test a Service Operation. The result of the operation appears.
    3. Click SAVE OPERATION to save the changes in the create operation.

      Note: You can view the service in the Data Panel feature of Kony Visualizer. By using the Data Panel, you can link back-end data services to your application UI elements seamlessly with low-code to no code. For more information on Data Panel, click here.

    How to Query a Database and Display Information with the Read Operation

    1. Under Configured Operations, hover your cursor over the Read operation, click Settings>> Edit.

      The system displays the selected operation in the edit mode. The Read operation has the Request Input and Response Output tabs.

      Note:  You can add an entry by clicking the Add Parameter button if entries for the input and the output tabs do not exist.

      In the read operation, the Name drop-down list contains a Select option that acts as a label for the list. Select itself is not a command.



      -  To make duplicate entries, select the check box for the entry, click Copy, and then click Paste.


      -  To delete an entry, select the check box for an entry, and then click the Delete button.

    2. In the Request Input, configure the following ODATA commands to filter the data:

      1. The NAME field in the Request Input is prepopulated with ODATA commands.
      2. In the TEST VALUE filed, enter the query parameter for the selected ODATA command.

        For example (sample employee table), shown below:

        Command NameTest value for the command Result
        $filteremp_Id ge 30Filters and displays data in the table based on age of employes who are older than 30.
        $orderbyemp_AgeArranges data in the table based on employees' age.
        $top5Displays top five records in the table.
        $skip5Displays all records in the table except top five records.

        For example (sample configuration for ODATA commands), shown below:

      3. In the DEFAULT VALUE, enter the value if required.
      4. In the DESCRIPTION, provide the description.
    3. To validate the details, click Fetch Response. For more information, refer Test a Service Operation. The result of the operation appears.
    4. Click SAVE OPERATION to save the changes in the read operation.
    5. In the Response Output tab, configure the fields of the table for displaying the data.

    1. To configure the Response Output operation, provide the following details .

      Field Description
      Name

      The Name field in the Response Output tab is pre-populated with database columns
      Scope

      Select request or session. This field is set to Request, by default.

      Note: If you define parameters inside a record as the session, the session scope will not get reflected for the parameters.
      DATA TYPE The default datatype for the selected column is loaded under DATATYPE field.
      Description Enter the description for response output.
    2. To validate the details, click Test. For more information, refer Test a Service Operation. The result of the operation appears.
    3. Click SAVE OPERATION to save the changes in the read operation.

      Note: You can view the service in the Data Panel feature of Kony Visualizer. By using the Data Panel, you can link back-end data services to your application UI elements seamlessly with low-code to no code. For more information on Data Panel, click here.

    How to Update a Database Record with Update Operation

    1. Under Configured Operations, hover your cursor over the Update operation, click the Settings button, and then click Edit.

      The system displays the selected operation in the edit mode. The update operation has the Request Input tab.

    1. The NAME field contains primary key of the table. You cannot modify these details.

      The Name column is prepopulated with fields names in the database.

      Note:  You can add an entry by clicking the Add Parameter button if entries for the input and the output tabs do not exist.

      -  To make duplicate entries, select the check box for the entry, click Copy, and then click Paste.


      -  To delete an entry, select the check box for an entry, and then click the Delete button.

    2. Update the values in the fields, such as TEST VALUE, DEFAULT VALUE, and SCOPE, if required.

      To validate the details, click Fetch Response. The result of the operation appears.

    3. Click SAVE OPERATION to save the changes in the update operation.

    How to Delete a Database Record with Delete Operation

    1. Under Configured Operations, hover your cursor over the Delete operation, click the Settings button, and then click Edit.

      The system displays the selected operation in the edit mode. The delete operation has the Request Input tab.

      1. Under Configured Operations, hover your cursor over the Delete operation, click the Settings button, and then click Edit.

        The system displays the selected operation in the edit mode. The delete operation has the Request Input tab.

      2. The NAME field contains the primary key of the table. You cannot modify these details. The Request Input tab contains only the primary key of the table.

      3. In the TEST VALUE field, enter the valid primary key value.

      4. Click Fetch Response to validate the details. If the test value matches the primary key in the database, the system deletes the record from the database.

      5. Click SAVE OPERATION to save the changes in the delete operation.

    Create SQL Custom Query

    If you want to write your own SQL query in the operation, do the following:

    Perform the following steps to write a custom query:

    Note: Currently only MySql, SQL Server, Oracle and PostgreSQL are supported.

    Important: If any error occurs in the database due to the SQL query, Kony Fabric will return the same error message which it receives from the database. If the error message contains any sensitive information, it is the responsibility of the developer to handle the sensitive information and display a generic error message.

    Limitations

    Database Adapter Limitations

    Following are the limitations to use the Kony Fabric Database Adapter:

    Copyright © 2020 Kony, Inc. All rights reserved.

    Open topic with navigation