Premium Feature — This feature is available in the Professional Plus and Enterprise Editions. 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, supporing read only (SELECT) access to the data. Update, insert, and delete operations are not supported.

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.

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

Acquire the JDBC Driver

The JDBC driver is included in the Professional Plus and Enterprise distributions of LabKey Server.

To download the driver:

  • Go to your customer support portal on labkey.org. (For example: https://www.labkey.org/CUSTOMER_NAME/project-begin.view?)
  • Click the Server Builds button.
  • Open the node Related Products, Source Code and Previous Releases
  • To download the driver click labkey-api-jdbc-XX.X.jar (XX.X will be the current release of number of the server.)

Note this 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:". Examples include "jdbc:labkey:http://localhost:8080/labkey" 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/labkey#/MyProject/MyFolder"
  • Username: Associated with an account on the web server
  • Password: Associated with an account on the web server

Properties

The driver also 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 (ie, 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.

Logging

The driver has the following logging behahior:

  • 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.remoteapi.query.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

Discussion

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand all collapse all