Page Comparison

You can create an Advanced Database Layout Builder feature helps you to upload a sample or an original source (JSON downloaded from Adv. Database layout manage page) file to define the layout. By default, this feature using Layout Builder in Adeptia Connect. Layout Builder helps you create an Advanced Database Layout by importing a JSON (downloaded from Advanced Database Layout manage page) file or by using a database table. By default, Layout Builder remains disabled in the product . You – you need to enable this feature before you start working on this. enable it before you can create a layout.

Steps to create Advanced Database Layout:

1. Click Configure > LAYOUTS > Advanced Database.
2. Click Create Advanced Database Layout.
   
3. On the Create Layout screen, select the Layout Builder option from the drop-down list of  option in the Select Layout UI Type field.
   
   
    
4. Click Next. The Create New Advanced Database screen is displayed.
   
   
   

5. Type the name and description of the new layout activity in the Name and Description

    text boxes

    fields.

   Info
   Name: Only alphanumeric and underscore are allowed and you must start the layout name with an alphabet or underscore. Description: Only alphanumeric and few special characters (_ - . : , @ $ ? \\ and space) are allowed and you must start the description with an Alphabet/Underscore/Number.

   
   

6. Select the definition mode for the Advanced Database Layout. You can define the layout in the following ways: 
   • Import Definition file
   • Use Database Table

   Using Import Definition File
   

   1. Select Import Definition File.
   2. Select the type of file from JSON in the drop down list. The available option is JSON.Definition File File field.

   3. Browse and upload the

      data

      JSON file

      in 

      in the Upload Data File field.

   4. Select the project from the Project drop-down list field.

   5. Click Continue.

   Using Use Database Table

   1. Select Use Database Table.
   2. Select the database info activity from the Database Info drop-down list field. You can also edit the database info by clicking Image Added button.
   3. Select the project from the Project drop-down list field.

   4. Click Continue.

   5. Anchor

      On the

      Database Schema Database Schema

      On the Record Definition
      page

       page,

      select the Database Schema from the drop-down list

      in the Database Schema field, select a database layout to create the first record.

      Info
      When you select the Database Schema, all the database tables associated with the selected schema are populated in the

      drop-down list of the

      Database Table field.

      
      

   6. Select  the

      required Database Table from the drop-down list

      required database table from the Database Table field. Based on the selected Database Table,

      column details will

       Column Details will be displayed.

   7. In

      the column details

      the Column Details, select the column(s) that you want to include in Select

      Query by selecting the checkbox

      query statement.

      Info
      You can also select the Primary Key checkbox

      to include column(s) in Select Query. 

      Info
      You can also enter the Query in the Query Editor manually.

      Based on the selected column(s), Select Query will be displayed in the Query Editor. You

      if you want the field to be the Primary Key. Primary Key is the field on the basis of which the layout updates the target database table. You can use Primary Key only when you are using database layout activity as target database.

      As you select a column, the Select query statement is automatically defined in the Query Editor field. You can also manually define an altogether new or update an existing Select query statement in this field. You can edit the query and add

      the

      the where clause with dynamic parameters as shown in the screenshot below

      screenshot.

      Image Removed

      Placeholders will be shown for

      . For all the added dynamic parameters

      , where

      a new field called Placeholders is added to the screen – here you can enter the values

      of placeholders which is used to generate the XSD for this schema. 

      for the dynamic parameters.
      
      Image Added
      
      

   8. Click Save to add this record

      on main view

      .

   The Record Definition page displays the details of created records and Records Hierarchy.
   

   Screenshot

   
   Image Added
   

   Info
   On Record Definition

   window

   screen, you can

   click

   Click Image Modified

   to edit and click Image Removed to remove record.On Records Hierarchy  window, You can

   to edit a record. Click Image Added to remove a record. The database table you selected in the previous step gets listed under the Records Hierarchy panel. You can also click Image Modified to add a root table. Image Modified

   Records Hierarchy displays selected Database table name.

   You can

   hover over the table name and click Image Removed to add

   select a table under the Record Hierarchy panel and, Click Image Added to add a child table

   and click Image Removed to

   to it. Click Image Added to view the data in the table.

   Image Removed

   Image Added

   
   

7. In the Definition panel, 
   
8. Select the Project from the drop-down list of Project. 
   

9. 1. Expand the Advanced Properties

 to view the advanced properties of the layout

10. 1. .

    2. Define the Character-set encoding for your Database layout in the Character Set Encoding field. For example UTF-8.

Schema Version

11. 1. Select the Auto increment Key Support checkbox if required. This feature is used when the parent table's Auto Increment column is referenced in the child table. Enabling this checkbox automatically populates the value in the child table's foreign key column with the Auto increment column's value from the parent table.

    2. Select schema version 2.0 checkbox if you want to create the layout without the name dependency.

    3. Select the Filter Invalid XML Characters checkbox to filter out invalid XML characters (if any) in the input data

       Info
       In case, the input data contains some characters that are invalid in XML, then this may result in the mapping getting aborted.

       
       

    4. Select the Query Batch Update checkbox to enable the query batch update, and enter the batch size in the Query Batch Size field.
       

       Info
       Enabling the Query Batch Update checkbox leads to database updation in batches as per the positive value set in the Query

 

12. 1. Batch Size field. For example, setting a value '10' in the Query

 

13. 1. Batch Size field will group 10 separate statements and submit them as a single batch. However setting the value to '0' will disable the batch execution and execution of every statement will occur simultaneously. You can achieve performance optimization by using batch update. The advantage of batch update is to reduce the network calls to the database by not executing every single SQL query when they are raised. By using this feature, you can send multiple queries to the database in a single instant and thereby reducing the number of JDBC calls and improving performance.

 


14. 1. This feature is available for the Insert and Update operation only.

       
       

    2. Select the Use Commit Count

 check box

15. 1.  checkbox to enable the Commit Count field, and enter the number of statements that you want to commit to the database in a single instance in a field.

       Info
       When you set the commit count value to a positive integer value then the layout starts and commits a batch of transaction as per the number in the Commit Count text box. This helps in reducing the network traffic and in turn increases network performance. This is called as batch transaction.

To handle Null or Empty elements, check 

Info
Only one of the options between Handle Null/Empty Element(s) and Update Empty Tag should be checked true. Checking both of these true will abort the process flow.

16. On the Database Layout screen, click the Save button

    1. 
       

    2. Select the Handle Null/Empty Element(s) 

true. Check this option false to skip this handling. By default the option is checked true.

17. 1. checkbox to handle Null or Empty elements. 

    2. Select the Use Parent/Child DataSet. This field works in tandem with Commit Count.

       • If this checkbox and Commit Count is enabled then this will call commit when parent record (including its hierarchy) count is equal to commit count. In case of any error rollback will not happen
       • If this checkbox is enabled and Commit Count is disabled then this will call commit count for every record including its hierarchy. In case of any error in the record including its hierarchy, the complete record will be rolled back.
       • If this checkbox is disabled and Commit Count is enabled  then this will call commit when count of its parent and its child record is equal to commit count. In case of any error rollback will not happen.
         
         
18. Click Save Layout to save the Advanced Database Layout.

Anchor
Adding a Child table Adding a Child table

Adding a Child table

You can also add a child table to the parent table. To add a child table, follow the steps given below:

1. In the Record Hierarchy panel, select the parent table and click Image Added.

2. Follow the instructions from step e to step g in the section Using Use Database Table. 

   Info
   You need to define a related key in the Related Key column in the column details to establish the relationship between the child and parent database table.

   
   

3. Click Save.
   The child table is added to the Record Hierarchy panel.