> ## 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

> Query pipeline stage configuration specifications

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/lucidworks-search/09-developer-documentation/config-specs/query-pipeline-stages/jdbc-lookup

[mintlify link]: https://doc.lucidworks.com/docs/lucidworks-search/09-developer-documentation/config-specs/query-pipeline-stages/jdbc-lookup

[old doc.lw link]: https://doc.lucidworks.com/managed-fusion/5.9/a8d5qj

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 Lucidworks Search**.
</Note>

<Accordion title="Upload a JDBC Driver to Lucidworks Search">
  The JDBC connector 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.

  Lucidworks Search stores JDBC drivers in the blob store. You can upload a driver using the Lucidworks Search UI or the Blob Store API. Learn more about the [JDBC V2 connector](/docs/fusion-connectors/connectors/jdbc-v2).

  <LwTemplate />

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

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

  1. In the Lucidworks Search 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 Lucidworks Search’s blob store using the [`/blobs/{id}` endpoint](/api-reference/blobs/get-blob-store-service-status).

     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 https://EXAMPLE_COMPANY.b.lucidworks.cloud/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"
       }
     }
     ```

     Lucidworks Search 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://EXAMPLE_COMPANY.b.lucidworks.cloud/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/lucidworks-search/05-move-data-out/query-pipeline/overview).

## Example

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

*Upload stage config via POST to Lucidworks Search 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://EXAMPLE_COMPANY.lucidworks.cloud/api/query-stages/instances
```

<Note>
  Replace `EXAMPLE_COMPANY` with the name provided by your Lucidworks representative.
</Note>

*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"
}
```

## 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} />