LabKey JDBC Driver

2024-03-28

This topic is under construction for the 24.3 (March 2024) release of LabKey Server. For current documentation of this feature, click here.

Premium Feature — Available in the Professional and Enterprise Editions of LabKey Server. Learn more or contact LabKey.

The JDBC driver for LabKey Server allows client applications to query against the schemas, tables, and queries that LabKey Server exposes using LabKey SQL. It implements a subset of the full JDBC functionality, supporting read only (SELECT) access to the data. Update, insert, and delete operations are not supported.

Containers (projects and folders) are exposed as JDBC catalogs. Schemas within a given container are exposed as JDBC schemas.

Supported Applications

The following client applications have been successfully tested with the driver:

Other tools may work with the driver as it is currently implemented, but some tools may require driver functionality that has not yet been implemented.

Acquire the JDBC Driver

The JDBC driver is bundled with the connectors module and distributed as an add-on or in the Professional and Enterprise distributions of LabKey Server. Please contact your Account Manager if you need assistance in locating the driver.

To download the driver:

  • Log in to your Premium Edition LabKey Server with administrator credentials.
  • Select > Site > Admin Console.
  • Click External Analytics Connections under Premium Features.
  • Check the box to Allow Connections and Save.
  • Select External Tool Access under the user menu.
  • You'll find buttons for downloading both the LabKey and Postgres JDBC Drivers.
  • Click Download LabKey JDBC Driver.

Note that this driver jar also contains the LabKey Java client api and all of its dependencies.

Driver Usage

  • Driver class: org.labkey.jdbc.LabKeyDriver
  • Database URL: The base URL of the web server, including any context path, prefixed with "jdbc:labkey:". This value is shown in the panel above the button you clicked to download. Examples include "jdbc:labkey:http://localhost:8080/" and "jdbc:labkey:https://www.labkey.org/". You may include a folder path after a # to set the default target, without the need to explicitly set a catalog through JDBC. For example, "jdbc:labkey:http://localhost:8080/#/MyProject/MyFolder"
  • Username: Associated with an account on the web server
  • Password: Associated with an account on the web server

Properties

The driver supports the following properties, which can be set either in Java code by setting in the Properties handed to to DriverManager.getConnection(), or by setting on the Connection that is returned by calling setClientInfo().

  • rootIsCatalog - Setting rootIsCatalog true will force the root container on the server to only be exposed as a catalog in calls to getTables(). Otherwise the schemas/tables will also be exposed at the top level of the tree. Note that folders and projects in LabKey Server are exposed as individual catalogs (databases) through the jdbc driver. Ordinarily we might expose the schemas for the LabKey Server root container at both the top level of a tree, and in a catalog with name "/". This can be problematic if the user connecting doesn’t have permissions to the root container (i.e., is not an admin); attempting to enumerate the top level schemas results in a 403 (Forbidden) response. Setting the "rootIsCatalog" flag true will cause the driver to skip enumerating the top level schemas, and only expose root as a catalog.
  • timeout - In DbVisualizer, set the Timeout in the Properties tab on the connection configuration. The default timeout is 60 seconds for any JDBC command. You may set it to 0 to disable the timeout, or the specific timeout you'd like, in milliseconds.
  • containerFilter - Specify a container (folder) filter for queries to control what folders and subfolders of data will be queried. Possible values are:
    • Current (Default)
    • CurrentAndSubfolders
    • CurrentPlusProject
    • CurrentAndParents
    • CurrentPlusProjectAndShared
    • AllFolders
  • ignoreNetrc - when set to "true", prevents the driver from using credentials from the user's .netrc file when present. Added in 1.1.0 of the driver.
For example,
Class.forName("org.labkey.jdbc.LabKeyDriver");
Properties props = new Properties();
props.put("user", "$<USERNAME>");
props.put("password", "$<MYPASSWORD>");
props.put("containerFilter", "CurrentAndSubfolders");
Connection connection = DriverManager.getConnection("$<DATABASE URL>", props);
connection.setClientInfo("Timeout", "0");
connection.setCatalog("/home");
ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM core.Containers");

Learn how to use the container filter with Spotfire in this topic: Integration with Spotfire.

Logging

The driver has the following logging behavior:

  • Unimplemented JDBC methods get logged as SEVERE (java.util.logging) / ERROR (log4j/slf4j)
  • Queries that are returned and many other operations get logged as FINE / DEBUG
  • The package space for logging is org.labkey.jdbc.level

Example Java Code

Class.forName("org.labkey.jdbc.LabKeyDriver");
Connection connection = DriverManager.getConnection("jdbc:labkey:https://www.labkey.org/", "user@labkey.org", "mypassword");
connection.setClientInfo("Timeout", "0");
connection.setCatalog("/home");
ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM core.Containers");

Related Topics