Ask AI
Skip to main content

Fetch from ext. database

Function: Fetch from ext. database​

This function allows you to retrieve one or more records from an external database. It acts like a powerful search tool, letting you specify which database to connect to, which table to look into, and what criteria to use for finding the exact data you need. You can also choose to fetch only specific pieces of information (columns) rather than entire records.

Input​

  • Database connection:
    • Description: The necessary details to establish a connection with your external database.
    • Type: Database Connection
      • Type: Choose the type of database you are connecting to (e.g., MSSQL, MYSQL).
      • Server: The address or name of the database server.
      • Database: The specific database name you want to access on the server.
      • User: The username for logging into the database.
      • Password: The password for the database user.
      • Port: The port number used for the database connection (e.g., 1433 for MSSQL, 3306 for MySQL).
  • Table:
    • Description: The name of the table within the database from which you want to retrieve data. This is a required field.
    • Type: Text
  • Data format:
    • Description: Defines the expected structure of the data you are fetching from the table. This helps the platform understand how to interpret the incoming information.
    • Type: Data Schema
  • Filters:
    • Description: A set of conditions to narrow down the records you want to fetch. You can add multiple filters.
    • Type: List of Objects
      • Attribute: Select the specific column in the table that you want to filter by.
      • Operator: Choose how the attribute should be compared (e.g., "Equal to", "Greater than", "Contains").
      • Value: The value to compare against the selected attribute.
  • Attributes:
    • Description: Choose specific columns you want to retrieve from the table. If you leave this empty, all columns from the table will be fetched.
    • Type: List of Data Schema Attributes

Output​

  • Result:
    • Description: The data records fetched from the external database that match your criteria.
    • Type: List of Objects
    • Default Variable Name: QUERY_RESULT

Execution Flow​

Real-Life Examples​

Example 1: Fetching all customer details from a specific table​

Imagine you need to get a complete list of all customers from your external CRM database.

  • Inputs:
    • Database connection:
      • Type: MYSQL
      • Server: crm.mycompany.com
      • Database: customer_data
      • User: crm_user
      • Password: secure_pass123
      • Port: 3306
    • Table: Customers
    • Data format: (Assumes a Customer data format exists in your platform matching the table structure)
    • Filters: (Left empty)
    • Attributes: (Left empty, to fetch all columns)
  • Result: The QUERY_RESULT variable will contain a list of all customer records from the Customers table, including all their details like CustomerID, Name, Email, Address, etc.

Example 2: Finding active orders placed in the last month​

You want to see all orders that are currently "Active" and were placed within the last 30 days from your external order management system.

  • Inputs:
    • Database connection:
      • Type: MSSQL
      • Server: orders.mycompany.net
      • Database: OrderManagement
      • User: order_admin
      • Password: order_pass#456
      • Port: 1433
    • Table: Orders
    • Data format: (Assumes an Order data format exists)
    • Filters:
      • Filter 1:
        • Attribute: OrderStatus
        • Operator: Equal
        • Value: Active
      • Filter 2:
        • Attribute: OrderDate
        • Operator: Greater than or equal
        • Value: [Current Date - 30 days] (This would typically be a dynamic value from another action or variable)
    • Attributes:
      • OrderID
      • CustomerName
      • OrderDate
      • TotalAmount
  • Result: The QUERY_RESULT variable will contain a list of active orders placed in the last month, showing only their OrderID, CustomerName, OrderDate, and TotalAmount.

Example 3: Retrieving product names and prices for items in a specific category​

You need a list of product names and their prices for all products belonging to the "Electronics" category.

  • Inputs:
    • Database connection:
      • Type: MYSQL
      • Server: products.mycompany.org
      • Database: ProductCatalog
      • User: catalog_reader
      • Password: read_only_prod
      • Port: 3306
    • Table: Products
    • Data format: (Assumes a Product data format exists)
    • Filters:
      • Filter 1:
        • Attribute: Category
        • Operator: Equal
        • Value: Electronics
    • Attributes:
      • ProductName
      • Price
  • Result: The QUERY_RESULT variable will hold a list of products from the Products table, specifically those in the "Electronics" category, with each item showing only its ProductName and Price.