> ## Documentation Index
> Fetch the complete documentation index at: https://doc.lucidworks.com/llms.txt
> Use this file to discover all available pages before exploring further.

# JDBC Lookup Stage

export const schema = {
  "type": "object",
  "title": "JDBC Lookup",
  "description": "Connect to a database and lookup one or more values and inject them into the context",
  "required": ["driver", "connection", "username", "password", "preparedStatement"],
  "properties": {
    "skip": {
      "type": "boolean",
      "title": "Skip This Stage",
      "description": "Set to true to skip this stage.",
      "default": false,
      "hints": ["advanced"]
    },
    "label": {
      "type": "string",
      "title": "Label",
      "description": "A unique label for this stage.",
      "hints": ["advanced"],
      "maxLength": 255
    },
    "condition": {
      "type": "string",
      "title": "Condition",
      "description": "Define a conditional script that must result in true or false. This can be used to determine if the stage should process or not.",
      "hints": ["code", "code/javascript", "advanced"]
    },
    "legacy": {
      "type": "boolean",
      "title": "Legacy",
      "description": "True if this stage only supports legacy mode",
      "hints": ["readonly", "hidden"]
    },
    "asyncConfig": {
      "type": "object",
      "title": "Asynchronous Execution Config",
      "required": ["enabled", "asyncId"],
      "properties": {
        "enabled": {
          "type": "boolean",
          "title": "Enable Async Execution",
          "description": "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": {
          "type": "string",
          "title": "Async ID",
          "description": "A unique value to use as reference in downstream \"Merge Async Results\" stages."
        }
      }
    },
    "driver": {
      "type": "string",
      "title": "JDBC Driver",
      "description": "The fully qualified class name of the JDBC Driver to use."
    },
    "connection": {
      "type": "string",
      "title": "Connection URI",
      "description": "The connection string for the database."
    },
    "username": {
      "type": "string",
      "title": "Username",
      "description": "The username to connect to the database."
    },
    "password": {
      "type": "string",
      "title": "Password",
      "description": "The password to connect to the database.",
      "hints": ["secret"]
    },
    "preparedStatement": {
      "type": "string",
      "title": "SQL Prepared Statement",
      "description": "The SQL Prepared Statement to execute when bound with values.",
      "hints": ["lengthy"]
    },
    "rows": {
      "type": "integer",
      "title": "Rows",
      "description": "The number of rows to return.",
      "default": 10,
      "hints": ["advanced"]
    },
    "join": {
      "type": "boolean",
      "title": "Join With Request",
      "description": "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,
      "hints": ["advanced"]
    },
    "preparedStatementKeys": {
      "type": "array",
      "title": "Prepared Statement Keys",
      "description": "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.",
      "hints": ["advanced"],
      "items": {
        "type": "string"
      }
    },
    "prefix": {
      "type": "string",
      "title": "Result Prefix Key",
      "description": "The string to use as a prefix for all values.",
      "hints": ["advanced"]
    },
    "fetchSize": {
      "type": "integer",
      "title": "Fetch Size",
      "description": "The JDBC Fetch Size to use.  If -1, use the driver default.",
      "default": -1,
      "hints": ["advanced"]
    },
    "validationQueryTimeout": {
      "type": "integer",
      "title": "Validation Query Timeout",
      "description": "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,
      "hints": ["advanced"]
    }
  },
  "category": "Fetch Data",
  "categoryPriority": 6,
  "unsafe": false
};

export const SchemaParamFields = ({schema}) => {
  const sanitize = str => {
    if (typeof str !== "string") return str;
    return str.replace(/^"(.*)"$/s, "$1").replace(/\\/g, "").replace(/"/g, "'");
  };
  const formatDescription = str => {
    const s = sanitize(str);
    return (/[.!?]\)*$/).test(s) ? s : `${s}.`;
  };
  const {description, properties = {}, required: requiredProps = []} = schema;
  const visibleProps = useMemo(() => Object.entries(properties).filter(([, prop]) => !prop.hints?.includes("hidden")), [properties]);
  return <div>
      {description && <p>{formatDescription(description)}</p>}

      {visibleProps.map(([name, prop]) => {
    const isRequired = requiredProps.includes(name);
    const hasDefault = prop.default !== undefined;
    const rawDefault = prop.default;
    const isComplexDefault = hasDefault && (typeof rawDefault === "object" || typeof rawDefault === "string" && (rawDefault.length > 20 || rawDefault.includes('"')));
    const fieldProps = {
      key: name,
      body: prop.title || name,
      type: prop.type,
      ...prop.title && ({
        post: [<><span className="text-stone-400 dark:text-stone-500">API property: </span>{name}</>]
      }),
      ...isRequired && ({
        required: true
      }),
      ...!isComplexDefault && hasDefault ? {
        default: sanitize(String(rawDefault))
      } : {}
    };
    const isObject = prop.type === "object" && prop.properties;
    const isArrayOfObjects = prop.type === "array" && prop.items?.type === "object" && prop.items.properties;
    return <ParamField {...fieldProps}>
            {prop.description && <p>{formatDescription(prop.description)}</p>}

            {isComplexDefault && <div className="flex">
                <p>
                  <strong>Default:</strong>
                </p>
                <pre className="!my-0">
                  <code>
                    {JSON.stringify(rawDefault, null, 2)}
                  </code>
                </pre>
              </div>}

            {isArrayOfObjects && <div className="flex">
              <p>
                <strong>Object attributes:</strong>
              </p>
              <pre className="!my-0">
                <code>
                  {'{\n'}
                  {Object.entries(prop.items.properties).map(([iname, iprop]) => <>
                      {`  ${iname}`}
                      {prop.items?.required?.includes(iname) && <span style={{
      color: 'red'
    }}> required</span>}
                      {`: {\n    display name: ${sanitize(iprop.title || '')}\n    type: ${iprop.type}\n  }\n`}
                    </>)}
                  {'}'}
                </code>
              </pre>
              </div>}

            {isObject && <Expandable title="properties">
                <SchemaParamFields schema={{
      properties: prop.properties,
      required: prop.required
    }} />
              </Expandable>}
          </ParamField>;
  })}
    </div>;
};

export const LwTemplate = ({title = "Key questions to get you started", icon = "sparkles", cta = "Powered by Agent Studio", linkHref = "https://lucidworks.com/demo/?utm_source=docs&utm_medium=referral&utm_campaign=docs_cta_ai"}) => {
  const [isLoaded, setIsLoaded] = useState(false);
  useEffect(() => {
    const timer = setTimeout(() => {
      setIsLoaded(true);
    }, 500);
    return () => clearTimeout(timer);
  }, []);
  return <div className="lw-template-container">
      <Card title={title} icon={icon}>
        {isLoaded && <span dangerouslySetInnerHTML={{
    __html: `<lw-template id="a029c1a9-28be-427e-b0e1-5d918920246a"></lw-template
            >`
  }} />}
        <Link href={linkHref} className="agent-studio-link text-left text-gray-600 gap-2 dark:text-gray-400 text-sm font-medium flex flex-row items-center hover:text-primary dark:hover:text-primary-light group-hover:text-primary group-hover:dark:text-primary-light">Powered by Lucidworks Agent Studio</Link>
      </Card>
    </div>;
};

[localhost link]: http://localhost:3000/docs/5/fusion/reference/config-ref/pipeline-stages/query-stages/jdbc-query-stage

[mintlify link]: https://doc.lucidworks.com/docs/5/fusion/reference/config-ref/pipeline-stages/query-stages/jdbc-query-stage

[old doc.lw link]: https://doc.lucidworks.com/fusion/5.9/260

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.

<Note>
  You must first upload a JDBC Driver to Fusion.
</Note>

<Accordion title="Upload a JDBC Driver to Fusion">
  The JDBC V2 connector is supported, and fetches documents from a relational database via SQL queries. Under the hood, this connector implements the Solr [DataImportHandler (DIH)](https://wiki.apache.org/solr/DataImportHandler) plugin.

  Fusion stores JDBC drivers in the blob store. You can upload a driver using the Fusion UI or the Blob Store API.

  <LwTemplate />

  ## How to upload a JDBC driver using the Fusion UI

  {/* // tag::upload[] */}

  1. In the Fusion UI, navigate to **System** > **Blobs**.

  2. Click **Add**.

  3. Select **JDBC Driver**.\
     The "New 'JDBC Driver' Upload" panel appears.
       <img src="https://mintcdn.com/lucidworks/5yWZ-KtZuBe4Y_Fg/assets/images/4.0/blobstore_add_jdbcdriver1.png?fit=max&auto=format&n=5yWZ-KtZuBe4Y_Fg&q=85&s=b7d0a7b57734a2219cb107db40b0f210" alt="Uploading a connector" style={{ width: "450px" }} width="1041" height="686" data-path="assets/images/4.0/blobstore_add_jdbcdriver1.png" />

  4. Click **Choose File** and select the .jar file from your file system.
       <img src="https://mintcdn.com/lucidworks/5yWZ-KtZuBe4Y_Fg/assets/images/4.0/blobstore_add_jdbcdriver2.png?fit=max&auto=format&n=5yWZ-KtZuBe4Y_Fg&q=85&s=e722ae92551c19068ce783d6e71798b4" alt="Uploading connector" width="2455" height="1012" data-path="assets/images/4.0/blobstore_add_jdbcdriver2.png" />

  5. Click **Upload**.\
     The new driver’s blob manifest appears.
       <img src="https://mintcdn.com/lucidworks/5yWZ-KtZuBe4Y_Fg/assets/images/4.0/blobstore_add_jdbcdriver3.png?fit=max&auto=format&n=5yWZ-KtZuBe4Y_Fg&q=85&s=f3c48edcb769b7df7cd300dc449e249f" alt="Uploaded connector" width="2432" height="1009" data-path="assets/images/4.0/blobstore_add_jdbcdriver3.png" />

  From this screen you can also delete or replace the driver.

  {/* // end::upload[] */}

  ## How to install a JDBC driver using the API

  1. Upload the JAR file to Fusion’s blob store using the [`/blobs/{id}` endpoint](/api-reference/blobs/upload-a-blob).

     Specify an arbitrary blob ID, and a `resourceType` value of `plugin:connector`, as in this example:

     ```bash theme={"dark"}
     curl -u USERNAME:PASSWORD -H "content-type:application/java-archive" -H "content-length:707261" -X PUT --data-binary @postgresql-42.0.0.jar http://localhost:8764/api/blobs/mydriver?resourceType=driver:jdbc
     ```

     Success response:

     ```json theme={"dark"}
     {
       "name" : "mydriver",
       "contentType" : "application/java-archive",
       "size" : 707261,
       "modifiedTime" : "2017-06-09T19:00:48.919Z",
       "version" : 0,
       "md5" : "c67163ca764bfe632f28229c142131b5",
       "metadata" : {
         "subtype" : "driver:jdbc",
         "drivers" : "org.postgresql.Driver",
         "resourceType" : "driver:jdbc"
       }
     }
     ```

     Fusion automatically publishes the event to the cluster, and the listeners perform the driver installation process on each node.

     <Tip>   If the blob ID is identical to an existing one, the old driver will be uninstalled and the new driver will installed in its place. To get the list of existing blob IDs, run: `curl -u USERNAME:PASSWORD https://FUSION_HOST:FUSION_PORT/api/blobs`</Tip>
  2. To verify the uploaded driver, run:

     ```bash theme={"dark"}
     curl -u USERNAME:PASSWORD https://FUSION_HOST:FUSION_PORT/api/blobs/BLOB_ID/manifest
     ```

     Where the `BLOB_ID` is the name specified during upload, such as "mydriver" above. A success response looks like this:

     ```json theme={"dark"}
     {
       "name" : "mydriver",
       "contentType" : "application/java-archive",
       "size" : 707261,
       "modifiedTime" : "2017-06-09T19:05:17.897Z",
       "version" : 1569755095787110400,
       "md5" : "c67163ca764bfe632f28229c142131b5",
       "metadata" : {
         "subtype" : "driver:jdbc",
         "drivers" : "org.postgresql.Driver",
         "resourceType" : "driver:jdbc"
       }
     }
     ```
</Accordion>

This stage supports [asynchronous processing](/docs/5/fusion/getting-data-out/query-basics/query-pipelines/overview).

## Stage configuration 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`*

```bash wrap  theme={"dark"}
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*

```json wrap  theme={"dark"}
{
  "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"
}
```

## Query pipeline stage condition examples

Stages can be triggered conditionally when a script in the **Condition** field evaluates to true.
Some examples are shown below.

Run this stage only for mobile clients:

```js wrap  theme={"dark"}
params.deviceType === "mobile"
```

Run this stage when debugging is enabled:

```js wrap  theme={"dark"}
params.debug === "true"
```

Run this stage when the query includes a specific term:

```js wrap  theme={"dark"}
params.q && params.q.includes("sale")
```

Run this stage when multiple conditions are met:

```js wrap  theme={"dark"}
request.hasParam("fusion-user-name") && request.getFirstParam("fusion-user-name").equals("SuperUser");
!request.hasParam("isFusionPluginQuery")
```

The first condition checks that the request parameter "fusion-user-name" is present and has the value "SuperUser".
The second condition checks that the request parameter "isFusionPluginQuery" is not present.

## Configuration

<Tip>
  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.
</Tip>

<SchemaParamFields schema={schema} />
