Product Selector

Fusion 5.11
    Fusion 5.11

    JDBC Lookup Stage

    Table of Contents

    The JDBC Lookup query pipeline stage is used to call out to a database as part of a pipeline stage, to inject results into either the context/request or the pipeline document. For example, if you needed to look up a user from a DB and add their profile information onto a request for downstream use in a pipeline, the JDBC Lookup Stage would facilitate this.

    This stage supports asynchronous processing.

    Example

    An example of setup for a JDBC Lookup query pipeline stage.

    Upload stage config via POST to Fusion REST API endpoint `api/query-stages/instances`

    curl -u USERNAME:PASSWORD -X POST -H "Content-Type: application/json" -d '{"id": "jdbc-quer","type":"jdbc-query-lookup","driver":"postgresql-9.3-1101-jdbc4.jar","connection":"jdbc:postgresql:database","username":"user","password":"password1","preparedStatement":"select ID as id from DATABASE;"}' http://FUSION_HOST:FUSION_PORT/api/query-stages/instances

    Response

    {
      "type" : "jdbc-query-lookup",
      "id" : "jdbc-quer",
      "driver" : "postgresql-9.3-1101-jdbc4.jar",
      "connection" : "jdbc:postgresql:database",
      "username" : "user",
      "password" : "password1",
      "preparedStatement" : "select ID as id from DATABASE;",
      "fetchSize" : -1,
      "join" : true,
      "rows" : 10,
      "skip" : false,
      "label" : "jdbc-query-lookup",
      "type" : "jdbc-query-lookup"
    }

    Configuration

    When entering configuration values in the UI, use unescaped characters, such as \t for the tab character. When entering configuration values in the API, use escaped characters, such as \\t for the tab character.

    Connect to a database and lookup one or more values and inject them into the context

    skip - boolean

    Set to true to skip this stage.

    Default: false

    label - string

    A unique label for this stage.

    <= 255 characters

    condition - string

    Define a conditional script that must result in true or false. This can be used to determine if the stage should process or not.

    asyncConfig - Asynchronous Execution Config

    enabled - boolean

    Run the expensive data loading or processing part of this stage in a separate thread allowing the pipeline to continue executing. The results of this asynchronous execution can be merged into the pipeline request using a downstream "Merge Async Results" stage.

    Default: false

    asyncId - string

    A unique value to use as reference in downstream "Merge Async Results" stages.

    driver - stringrequired

    The fully qualified class name of the JDBC Driver to use.

    connection - stringrequired

    The connection string for the database.

    username - stringrequired

    The username to connect to the database.

    password - stringrequired

    The password to connect to the database.

    preparedStatement - stringrequired

    The SQL Prepared Statement to execute when bound with values.

    rows - integer

    The number of rows to return.

    Default: 10

    join - boolean

    If true, the results will be added on to the request using the prefix key and the row id, else the results will be put in the pipeline context using. This option <em>should not</em> be usedin conjunction with asynchronous execution.

    Default: true

    preparedStatementKeys - array[string]

    The keys in the Request/Header/Context to use to map request attributes into the prepared statement. These must map to the '?'s in your prepared statement. They must also be able to be resolved as the first parameter of that name in a request.

    prefix - string

    The string to use as a prefix for all values.

    fetchSize - integer

    The JDBC Fetch Size to use. If -1, use the driver default.

    Default: -1

    validationQueryTimeout - integer

    The validation query timeout, the amount of time, in seconds, that connection validation will wait for a response from the database when executing a validation query. Use a value less than or equal to 0 for no timeout.

    Default: -1