Ask AI
Skip to main content

Fetch one from ext. database

Function: Fetch one from ext. database​

This action allows you to retrieve a single record from an external database. You can specify which table to query, apply filters to narrow down the results, and select specific fields you want to retrieve. This is useful for fetching specific data, like a customer's details or a product's information, from an external system into your application.

Input,

  • Database connection: Details required to connect to your external database.
    • Type: Choose the type of database you are connecting to (e.g., MSSQL, MYSQL).
    • Server: The address or hostname of your database server.
    • Database: The name of the specific database you want to connect to.
    • User: The username for accessing 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: The name of the table in the external database from which you want to fetch a record.
  • Data format: Select a data format (schema) that matches the structure of the table you are querying. This helps in defining filters and selecting attributes.
  • Filters: (Optional) A list of conditions to narrow down which record to retrieve. You can add multiple filters.
    • Attribute: Choose a field (column) from your selected data format to filter by.
    • Operator: Select how the attribute should be compared (e.g., Equal, Greater than, Contains).
    • Value: Provide the value to compare against the chosen attribute.
  • Attributes: (Optional) Select specific fields (columns) you want to retrieve from the record. If no attributes are selected, all fields will be returned.

Output,

  • Result: An object representing the first record found that matches your criteria. If no record is found, this output will be empty. The structure of this object will match the selected "Data format".

Execution Flow,

Real-Life Examples,

Example 1: Fetching a Customer's Full Details​

Imagine you have a customer ID and want to retrieve all information about that customer from your external CRM database.

  • Inputs:
    • Database connection: (e.g., Type: MYSQL, Server: mycrmdb.com, Database: crm_data, User: admin, Password: securepass, Port: 3306)
    • Table: Customers
    • Data format: Customer_Schema (a predefined schema matching your Customers table)
    • Filters:
      • Attribute: CustomerID
      • Operator: Equal
      • Value: 12345
    • Attributes: (Leave empty to fetch all attributes)
  • Result: The Result output will contain an object with all the details (e.g., Name, Email, Address, Phone) for the customer with CustomerID 12345.

Example 2: Finding a Specific Product's Price and Stock​

You need to quickly check the price and current stock level for a product using its product code.

  • Inputs:
    • Database connection: (e.g., Type: MSSQL, Server: productdb.internal, Database: inventory, User: inv_user, Password: inv_pass, Port: 1433)
    • Table: Products
    • Data format: Product_Schema
    • Filters:
      • Attribute: ProductCode
      • Operator: Equal
      • Value: PROD-XYZ-789
    • Attributes: Select Price, StockLevel
  • Result: The Result output will be an object containing only the Price and StockLevel for the product with ProductCode PROD-XYZ-789.

Example 3: Retrieving the Latest Order from a Specific Region​

You want to find the most recent order placed by a customer in the 'North' region. Since this action fetches only one record, you'd typically combine it with a sorting mechanism if your database connection supports it, or ensure your filters are specific enough to yield a single, desired result. For this example, we'll assume the database naturally returns the latest if multiple match, or we're just looking for any order from that region.

  • Inputs:
    • Database connection: (e.g., Type: MYSQL, Server: orders.cloud, Database: sales, User: sales_api, Password: api_key, Port: 3306)
    • Table: Orders
    • Data format: Order_Schema
    • Filters:
      • Attribute: Region
      • Operator: Equal
      • Value: North
      • (Optional additional filter for date if you want to ensure it's recent, e.g., OrderDate Greater than 2023-01-01)
    • Attributes: Select OrderID, CustomerName, OrderDate, TotalAmount
  • Result: The Result output will be an object containing the OrderID, CustomerName, OrderDate, and TotalAmount for one order from the 'North' region that matches the criteria.