Overview
We have created a prototype JDBC driver for LabKey Server that allows clients to query against the schemas, tables, and queries that LabKey Server exposes using
LabKey SQL. It only implements a subset of the full JDBC functionality, but it is enough to allow third-party tools to connect, retrieve metadata, and execute queries. It has been successfully used from
DbVisualizer, for example.
The driver is included in the standard Java client API JAR file.
Containers (projects and folders) are exposed as JDBC catalogs. Schemas within a given container are exposed as JDBC schemas. In DbVisualizer, double-click a given catalog/container to set it to be active.
Driver Usage
- Classpath: You must include both the LabKey Java client API JAR, and its dependencies. They are included in the Java client distribution. Version numbers may vary, but here is a guide (note that newer releases of the Java client API also include a single JAR that includes all dependencies, with a "-all" suffix in the file name):
- labkey-client-api-14.1.jar
- commons-codec-1.2.jar
- commons-httpclient-3.1.jar
- commons-logging.jar
- commons-logging-api.jar
- json_simple-1.1.jar
- log4j-1.2.8.jar
- opencsv-2.0.jar
- Driver class: org.labkey.remoteapi.query.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
Timeouts
The driver currently supports one other property, "Timeout". This can be set either in Java code by setting it in the Properties handed to to DriverManager.getConnection(), or by setting it on the Connection that is returned by calling setClientInfo(). In DbVisualizer, you may 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.
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"); 
