Contents

Popular
 Contents

Objective

To use the DB component to run simple Insert and Select SQL statements on a Database.

Prerequisites

  • Start Fiorano Enterprise Server (FES) and Fiorano Peer Server (FPS)
  • Login to eStudio application
  • Understand the basic menus and perspectives (panels) in eStudio application
  • Before configuring the database, add the JDBC driver of the database as a resource to the JDBC System lib. Please Refer to the Adding Resources to a Microservice section to perform this.

Scenario

  1. Configure a DB component to insert data into a table in a database using Insert Statement.
  2. Configure a DB component to fetch data, from the database that is used to insert data, using Select Statement.

Components used

Setting up the Example

The DB component is an all-encompassing powerful component which can be used to configure simple and nested queries like insert, update, delete and select. Following sections illustrate the steps to configure the component with Insert and Select query, which comes under the purview of this example, and thereby the steps to run the example as well.

Insert Query Event Process Configuration

The first step to create the Insert/Select example using DB is to configure the database. Perform the following actions to configure DB component with Insert query:

  1. Add a new Event Process with name 'DB_Insert_Eg'.
  2. Drag the DB component from DB category in the Service Palette to the Fiorano Orchestrator.


    Figure 1: Adding DB component to Fiorano Orchestrator

  3. Click the DB component and do any of the following to rename the component from 'DB1' to 'DB_Insert' to distinguish it from the DB component that gets configured for Select statement in 'Select Query Event Process Configuration' section:
    1. Press F2, change the name and click OK.


      Figure 2: Renaming using Keyboard action 'F2'
       
  4. Go to Name property under General tab in Properties, change the name and press ENTER.


    Figure 3: Renaming using General Properties

Configuring DB component

To configure the DB component, perform the following actions:

  1. Double-click the DB_Insert in the Orchestrator to open Configuration Property Sheet (CPS).
  2. Click the Database Configuration ellipsis button to open DbConfigurations Panel.


    Figure 4: DB Component CPS
     
  3. Provide the following details in DbConfigurations Panel and click OK:
    1. Database: Oracle (Choose the database to be connected to. It is Oracle in this example)
    2. Driver: oracle.jdbc.driver.OracleDriver (Retain the default value)
    3. URL: jdbc:oracle:thin:@<SystemIP>:xe (in place of 'SystemIP', enter the machine IP in which the Oracle database is installed and 'xe' is the database name).
    4. User name: User name to connect to the database.
    5. Password: Password for the above user name.
    6. Named Configuration: OracleNamedConfig (Provide any value here to save the database configuration as Named Configuration to be used in future. 'OracleNamedConfig' is the name used in this example)


      Figure 5: Database configuration in DbConfigurations Panel
       
  4. Click the Test button in Managed Connection Factory panel to check if the database has been configured correctly.


    Figure 6: Testing Database configuration in Managed Connection Factory Panel
     
  5. If the configuration is valid, Test Connection Configuration pop-up displays confirmation message.


    Figure7: Testing Database Configuration
     
  6. After the database has been configured properly, click the Next button in Managed Connection Factory panel to navigate to Interaction Configurations panel.
  7. Click SQL Configuration ellipsis button to configure Insert query in SQL Configuration Wizard panel.


    Figure 8: SQL Configuration in Interaction Configurations Panel
     
  8. Click the Add button and select Insert Statement option from the menu.


    Figure 9: Selecting 'Insert Statement' in 'SQL Configuration Wizard' panel
     
  9. In the Insert Query Builder panel, enter a name (InsertQuery in this example) in Query Name text box and click Add.


    Figure 10: Insert Query Builder
     
  10. Click the Fetch Schemas button to fetch all the schemas present in the database into the Schema dropdown. Select a schema and then click Refresh Tables button to fetch/refresh the table list for the selected schema. Select the table from the list for which InsertQuery has to be configured and click OK button.


    Figure 11: Selecting the table in Table Selection Dialog Panel
     
  11. As the columns of the table get fetched into Insert Query Builder panel, click OK.


    Figure 12: Table columns in Insert Query Builder
     
  12. Click Finish in SQL Configuration Wizard.


    Figure 13: Finishing configuration in SQL Configuration Wizard
     
  13. Click Finish button to finish the DB_Insert component configuration.


    Figure 14: Clicking 'Finish' in Interactions Configurations Panel

Configuring Feeder Component

Feeder component needs to be added to invoke query from DB_Insert. To configure the same, perform the following actions:

  1. Add Feeder and Display components from the Util category in Service Palette into the DB_Insert_Eg' EP.
  2. Rename 'Feeder1' to 'Feeder_DBInsert' and 'Display1' to 'Display_DBInsert'. Change in name is to distinguish between the componentsthat are configured for 'Insert' and 'Select' queries.
  3. Connect Feeder_DBInsert and Display_DBInsert to input and output ports of DB_Insert respectively.


    Figure 15: Adding Feeder and Display components to DB_Insert component
     
  4. Double-click Feeder_DBInsert and click Connected Port Schemas in Message Type Configuration panel.


    Figure 16: Configuring Feeder component
     
  5. Select IN_PORT to import DB_Insert schema into Feeder_DBInsert and click OK.


    Figure 17: Selecting 'IN_PORT' of 'DB_Insert' component
  6. Click the Select Root Element ellipsis button to open Root Element dialog box. Select the element (SQL_CFG_1 here) and click OK.


    Figure 18: Selecting Root Element
     
  7. The Select Root Element text box gets populated and schema editor also gets populated with the DB schema. Click Next.


    Figure 19: Schema editor with the DB_Insert Schema populated
     
  8. In Message Details Configuration panel, click Generate Sample to open XSD Sample Generation Dialog.


    Figure 20: Clicking 'Generate Sample' in 'Message Details Configuration' panel
     
  9. Enter '1' in max text field under No. of Repeatable elements to be generated to reduce the number of samples generated to just one, and click OK.


    Figure 21: Setting No. of Repeatable elements to be generated
     
  10. Click the Finishto complete Feeder_DBInsert configuration.


    Figure 22: Clicking 'Save and Close' to complete Feeder_DBInsert configuration

Select Query Event Process Configuration

Add a new Event Process with name 'DB_Select_Eg' and drag the DB component from DB category in the Service Palette to the Fiorano Orchestrator.


Figure 13: Adding DB component to Fiorano Orchestrator

Also, rename the DB component from 'DB1' to 'DB_Select'.

Configuring DB Component

To configure the DB component, perform the following actions:

  1. Double-click DB_Select to open the CPS. Click the Database Configuration ellipsis button to open DbConfigurations Panel.


    Figure 24: DB Component CPS
     
  2. Select the named configuration 'OracleNamedConfig' (created during Insert Query Event Process Configuration) from the Named Configuration drop-down and click OK.


    Figure 25: Selecting the Named Configuration
     
  3. Click Next in Managed Connection Factory panel to navigate to Interaction Configurations panel.


    Figure 26: Moving to Interaction Configurations Panel
     
  4. Click SQL Configuration ellipsis button to configure Select query in SQL Configuration Wizard panel.


    Figure 27: SQL Configuration in Interaction Configurations Panel
     
  5. Click the Add button and select Select Statement option from the menu.


    Figure 28: Selecting 'Select Statement' in SQL Configuration Panel
     
  6. In the Select Query Builder panel, enter a name in 'Query Name' text box (SelectQuery in this example) and click Add.


    Figure 29: Select Query Builder
     
  7. In Table Selection Dialog, click the Fetch Schemas button to fetch all the schemas in the database into Schema drop-down. Select a schema and then click Refresh Tables button to fetch/refresh the table list for the selected schema. Select the table from the list for which SelectQuery has to be configured and click OK.


    Figure 30: Selecting the table in Table Selection Dialog Panel
     
  8. The columns of the table get fetched into Insert Query Builder panel; click OK.


    Figure 31: Table columns in Insert Query Builder
     
  9. Click Finish in the SQL Configuration Wizard.


    Figure 32: Finishing configuration in SQL Configuration Wizard
     
  10. Click Save and Close to finish configuring DB_Select component.


    Figure 33: Clicking 'Save and Close' in Interactions Configurations Panel

Configuring Feeder Component

Feeder component needs to be added to invoke query from DB_Select. To configure the same perform the following actions:

  1. Add Feeder and Display components from the Util category in Service Palette into the DB_Select_Eg' EP.
  2. Rename 'Feeder1' to 'Feeder_DBSelect' and 'Display1' to 'Display_DBSelect'.
  3. Connect Feeder_DBSelect and Display_DBSelect to input and output ports of DB_Select respectively.


    Figure 34: Adding Feeder and Display components to DB_Select component
     
  4. Double-click Feeder_DBSelect and click Connected Port Schemas in Message Type Configuration panel.


    Figure 35: Configuring Feeder component
     
  5. Select IN_PORT to import DB_Select schema into Feeder_DBSelect and click OK.


    Figure 36: Selecting 'IN_PORT' of 'DB_Select' component
  6. Click the Select Root Element ellipsis button to open Root Element dialog box. Select the element (SQL_CFG_1 here) and click OK.


    Figure 37: Selecting Root Element
     
  7. The Select Root Element text box gets populated and schema editor also gets populated with the DB schema. Click Next.


    Figure 38: Schema editor with the DB_Select Schema populated
     
  8. In Message Details Configuration panel, click Generate Sample to open XSD Sample Generation Dialog.


    Figure 39: Clicking 'Generate Sample' in 'Message Details Configuration' panel
     
  9. Enter '1' in max text field under No. of Repeatable elements to be generated to reduce the number of samples generated to just one, and click OK.


    Figure 40: Setting No. of Repeatable elements to be generated
     
  10. Click the Save and Close button to complete Feeder_DBInsert configuration.


    Figure 41: Clicking 'Save and Close' to complete Feeder_DBSelect configuration

Running the Example

Running Insert Query Event Process

Open DB_Insert_Eg Event Process and perform the following actions:

  1. Click Check Resource and Connectivity  icon (or press ALT+SHIFT+C) to check resource and connectivity.
  2. Click Run Event Process  icon (ALT+SHIFT+R) to run the event process. Feeder_DBInsert and Display_DBInsert windows get opened.
  3. Provide the required column values (in this example, EMPID: 101, EMPNAME: MICHAEL and EMPDEPARTMENT: HR) in Feeder_DBInsert window and click Send.


    Figure 42: Sending input from Feeder
     
  4. If the Insert query is successful, a success message with 'UpdateCount' as '1' will be displayed in the Display_DBInsert window.


    Figure 43: Insert Query Success message shown in Display component

Running Select Query Event Process

Open DB_Select_Eg Event Process Orchestrator and perform the following actions:

  1. Click Check Resource and Connectivity  icon (or press ALT+SHIFT+C) to check resource and connectivity.
  2. Click Run Event Process  icon (ALT+SHIFT+R) to run the event process. Feeder_DBSelect and Display_DBSelect windows get opened.
  3. Click Send in Feeder_DBSelect window to send input.


    Figure 44: Sending input from Feeder
     
  4. The query results (data inserted in "Running Select Statement Event Process" section along with the records that are already present in the table) will be displayed in Display_DBSelect window.


    Figure 45: Select Query results in Display component

Reference

Adaptavist ThemeBuilder EngineAtlassian Confluence