webMethods

webMethods

Join this online group to communicate across IBM product users and experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.

 View Only
Back to Library

How to use the Insert and Select operations using the Database Application? 

Fri April 17, 2020 08:25 AM

What is a Database Application in Integration Cloud?

The Database Application allows you to perform database operations with cloud databases. All the database operations are performed using JDBC Driver provided by the database vendor.

You can create an integration with any cloud databases using the Database Application which supports operations such as Insert, Select, Delete, Update, and so on. All the operations in the Database Application share similar user interfaces and design approaches.

1364×435 101 KB

Actors

  • Administrator
  • A user who creates integration to the database

Preconditions

  • Knowledge to create and execute an operation
  • Basic knowledge of Database and SQL queries
  • An existing database with appropriate permissions
  • A valid tenant that contains subscription with access to Database Application

Basic Flow

  1. Log in with the tenant credentials.
  2. You can create a new project or use an existing project to create an account, operation, and so on using the below screen:

    image.png1221×335 20.9 KB

    image.png1341×363 14.2 KB

  3. Click Applications and find the Database application as shown below:

    image.png1331×317 31.2 KB

    image.png1332×319 47.2 KB

  4. You can now create an account, operation, and integration using the below screen:

    image.png1900×554 41.5 KB

  5. Let us assume that you want to insert a row into an EMPLOYEE table using the INSERT operation and then select the inserted row using the SELECT operation. The sample structure for the Employee table can be as shown below:

    EMP_NO           NUMBER(4)
    EMP_NAME      VARCHAR2(10)
    JOB                   VARCHAR2(9)
    MANAGER        NUMBER(4)
    HIRE_DATE       DATE
    SALARY             NUMBER(7,2)
    COMM               NUMBER(7,2)
    DEPT_NO          NUMBER(2)
  6. Do the following to insert a row into the Employee table:
    1. Add a JDBC driver.

      Navigate to the particular project where you want to create the operation Then, go to Applications > Database > DRIVER MANAGEMENT > Add Driver. Provide values as shown below and click Add.

      image.png755×340 11.3 KB

      Note: The Database Application also provides pre-bundled JDBC drivers. If you want to use a pre-bundled JDBC driver, you need not add a new JDBC driver.
    2. Create a new account.

      Navigate to the particular project where you have already added the JDBC driver. Then go to Applications > Database > ACCOUNTS > Add New Account.

      Provide the values for each field as shown below and save the account details:

      image.png1871×911 45 KB

      Note: The Truststore Alias field is used only when you create a secure connection.
    3. Configure the Insert operation.

      Navigate to the particular project where you have created the account.

      Then go to Applications > Database > OPERATIONS > Add New Operation.

      Follow the below steps to configure the Insert operation:

      1. On the Account screen, provide the name of the operation, description and choose the account to create the operation as shown below:

        image.png1893×985 35.5 KB

      2. On the Operation screen, select the Insert operation from the list of operation templates:

        image.png1862×991 57.1 KB

      3. For Tables, click Add Table, and select the Employee table as shown below:

        image.png1868×993 33.7 KB

      4. For Insert Values, click Add Fields, and select the table columns:

        image.png1874×991 44.9 KB

        Note: The default value for the Expression field is ?, which specifies to provide the value for that particular column when you execute the Insert operation.
      5. The Summary displays the operation details such as the name of the Operation, Account, and SQL query formed. Click Finish or Save.
    4. Execute the Insert operation and provide values for the selected columns.
  7. To select a row from the Employee table, do the following:
    1. Add a JDBC Driver.

      The JDBC Driver is already added while creating the Insert operation.

    2. Create a new account.

      The account is already created while creating the Insert operation.

    3. Configure the Select operation.

      To configure, navigate to the respective project where you have created the account.

      Later navigate to Applications > Database > OPERATIONS > Add New Operation.

      Do the following to configure the Select operation:

      1. On the Account screen, provide the name of the operation, description, and choose the account to create the operation as shown below:

        image.png1881×466 34.6 KB

      2. On the Operation screen, choose the Select operation from the list of operation templates as shown below:

        image.png1878×551 52.7 KB

      3. For Tables, click Add Table and select the Employee table as shown below:

        image.png1882×716 32.9 KB

      4. If you select multiple tables, select Joins to configure the joins for those tables. You can skip this option if you have selected a single table.

      5. For Data Fields, click Add Fields, and select the table columns as shown below:

        image.png1887×764 47.6 KB

      6. In the Conditions screen, configure the WHERE clause of the SQL query as shown below:

        image.png1245×365 5.96 KB

        image.png1876×733 34.9 KB


        Note: The default value for the Expression field is ?, which specifies to provide the value for that column when you execute the Select operation.

      7. The Summary displays the operation details such as the name of the operation, account, and SQL query formed. Click Finish or Save.
    4. Execute the Select operation.

#wiki
#webMethods
#Adapters-and-E-Standards

Statistics
0 Favorited
0 Views
0 Files
0 Shares
0 Downloads

Comments

webMethods Community Member
Fri August 05, 2022 03:41 AM

The database is very important nowadays. Many people are unaware of its benefits and need of it. I have written this article which will show you why it is important to have membership database software.


#Adapters-and-E-Standards
#webMethods
#wiki