SQL Lookup

Description

Returns results of executing an SQL query against a JDBC database. 

It is typically used to return further data from a database. E.g., Lookup Customer First Name and Last Name from Account Number.

The SQL Lookup function configuration requires the user to specify a JDBC connection in Hero Platform_, add dynamic values, output fields, and type an SQL query.

Be aware that queries similar to:

select * from table

 The the query is set to query an entire table and the table consists of millions of rows, the result will most likely be an Out of Memory error.

Use

  • Argument
    • Select an argument. (Any)
    • Optional: Select additional arguments. (Any)
  • Output Field
    • Enter an output field name.
      • Select a data type. (Any)
  • The SQL Lookup function result maps each first column to the first output field, the second for the second column for the second output field, and so on.
  • An error occurs if the incorrect field type is chosen for the output field.
  • The output field matches every value to every row that is received from the SQL query. 
    • E.g., The input for the SQL lookup was one value. If the query was ```select * from table```, and the table contains 1000 rows, the results will be 1000 values.


  • Connections
    • Select a JDBC connection in Hero Platform_.
    • Select a 
  • Enter an SQL query string.
  • Enter the query timeout value. (Seconds)
    • Query statements that are not completed by the timeout value are canceled.
    • The value of "0" disables the query timeout feature.
  • Click OK.

Example

The database table has the following columns:

Id (Long), firstName(String), lastName(String)

The SQL query entered is:

select * from table where argument = ${argument1};

The first output field is mapped with 'Id' (the first column). The second output field is mapped with "firstName" (the second column) and so on.

Type

Lookup

Search