Skip to content
DREDREDRE
You're reading the docs for DRE 0.10 (v0.10.0). This page for 0.11

JDBC

JDBC databases using user-supplied drivers: the jdbc source, from the jdbc package. Declare the package once in dependencies.yml; DRE installs it on demand:

dependencies.yml
plugins:
  - jdbc

Profile fields

Section titled “Profile fields”

Set in profiles.yml, under connections:, in a target with type: jdbc.

Field Default Notes
url (required) Complete JDBC URL or minimal vendor URL; may contain credentials. Secret: use env_var().
driver_jars (required) Local driver JAR path, or a list of paths; vendor drivers are user-supplied.
driver_class Optional fully qualified JDBC driver class instead of service discovery.
properties Arbitrary case-sensitive vendor settings with string, boolean or numeric values. Secret: use env_var().

The optional jdbc source runs SQL through a vendor JDBC driver and returns typed Arrow batches to DRE. Preparation and result queries share one database connection. The generic source is exercised end to end with Oracle and SQL Server; it does not translate SQL dialects or contain vendor-specific query paths.

The jdbc package installs with dre deps. Supply a compatible Java 17 or newer runtime on the host, selected through JAVA_HOME or PATH. There is no upper-version cap; a vendor driver may require a newer Java version. The plugin does not install, download or update Java. Supported packages are macOS and Linux on x86_64 or aarch64, and Windows x64. There is no Windows ARM64 package for this release.

Supply the vendor driver yourself. The plugin does not redistribute or download drivers, and its reach depends on a compatible driver accepting the configured URL and properties.

Install

Section titled “Install”

Declare jdbc and any output packages in the project’s dependencies.yml, then install them:

plugins: [jdbc, xlsx]
Terminal window
dre deps

The package installs into the normal versioned plugin directory. Its launcher uses JAVA_HOME/bin/java (java.exe on Windows) when JAVA_HOME is nonempty, otherwise java from PATH. A missing or older runtime fails with setup guidance before a connection opens. If JAVA_HOME is set incorrectly, correct it or unset it to use PATH.

Install a maintained Java runtime through your organization’s usual software management or the Temurin installation guide. Confirm the selected runtime with java -version; keep security updates under your normal Java patch policy. DRE updates only the plugin and its Java libraries through dre deps or the ordinary plugin update command.

Build and run

Section titled “Build and run”

From the repository root, for plugin development without an installable package:

Terminal window
mvn -B -f java/jdbc/pom.xml package
mkdir -p /tmp/jdbc-plugins/target
cp java/jdbc/dre-plugin-jdbc /tmp/jdbc-plugins/
cp java/jdbc/target/dre-plugin-jdbc.jar /tmp/jdbc-plugins/target/
export DRE_PLUGINS_DIR=/tmp/jdbc-plugins

Declare jdbc and the required output packages in the project’s dependencies.yml:

plugins: [jdbc, xlsx]

Keep DRE_PLUGINS_DIR set for dre deps, dre validate and dre run; the ordinary dependency workflow installs other declared plugins there. The manually supplied JDBC development launcher is discovered by the existing plugin host. Its JAR belongs in the adjacent target/ directory.

An Oracle profile using a minimal Thin URL and driver properties:

connections:
  reporting:
    targets:
      dev:
        type: jdbc
        url: "{{ env_var('JDBC_URL') }}"
        driver_jars: "{{ env_var('JDBC_DRIVER_JAR') }}"
        properties:
          user: "{{ env_var('JDBC_USER') }}"
          password: "{{ env_var('JDBC_PASSWORD') }}"
          oracle.jdbc.fanEnabled: false

For example, JDBC_URL can be jdbc:oracle:thin:@//localhost:1521/FREEPDB1. A complete vendor URL containing credentials also works, without a properties block. Treat the full URL as a secret. Specify each setting once: JDBC leaves duplicate URL/property precedence to the driver; DRE does not parse or rebuild vendor URLs or impose a universal override rule.

A SQL Server profile can use a minimal URL and properties:

connections:
  reporting:
    targets:
      dev:
        type: jdbc
        url: jdbc:sqlserver://localhost:1433
        driver_jars: /path/to/mssql-jdbc.jar
        properties:
          user: "{{ env_var('JDBC_USER') }}"
          password: "{{ env_var('JDBC_PASSWORD') }}"
          databaseName: reporting
          encrypt: false
          trustServerCertificate: true

A complete SQL Server URL containing credentials, database and TLS settings also works with no properties block. The driver settings shown here are examples, not a DRE-owned allowlist.

Field Meaning
url Required JDBC URL beginning with jdbc:<vendor>:. Kept unchanged.
driver_jars Required readable local JAR path or list of paths. Relative paths use the project directory; ~/ uses the home directory.
driver_class Optional fully qualified java.sql.Driver implementation; otherwise JDBC service discovery finds a supplied driver accepting the URL.
properties Optional case-sensitive map of arbitrary vendor property names. Strings pass unchanged; booleans become true/false; numbers become decimal strings. Nested blocks, lists and nulls are rejected.

Driver settings are unrestricted, but DRE-owned fields and their types are checked. Unknown top-level connection keys warn, matching the existing protocol. A successful static validation does not prove the driver recognizes every property; drivers may ignore unknown properties.

Run a report containing preparation followed by a query, for example:

alter session set nls_date_format = 'YYYY/MM/DD';
select cast('000019' as varchar2(20)) as "account_id",
       cast(12.34 as number(18,2)) as "balance",
       to_char(date '2026-01-31') as "session_date"
from dual;

The SQL Server equivalent uses its own setup and query files:

-- setup.sql
set nocount on;
create table #accounts (account_id varchar(20) not null, balance decimal(18,2) not null);
insert into #accounts values ('000019', 12.34);


-- accounts.sql
select account_id, balance from #accounts order by account_id;

Mark the setup query tab: false in the report YAML. DRE runs it before the final query on the same JDBC session.

Use a named query entry and output: {format: xlsx} as in any DRE report. With the same configuration, dre run <report> --preview executes SQL and keeps the workbook locally. Preparation still executes in preview mode; use database permissions to limit what it can do.

Types and session limits

Section titled “Types and session limits”

The source uses Apache Arrow’s JDBC adapter, emitting batches of at most 8,192 rows. The tested Oracle and SQL Server fixtures cover strings with leading zeros and long identifiers, nulls, Decimal128(18,2), Decimal128(38,18), millisecond timestamps and binary values. Text identifiers remain text through Arrow and xlsx output. A bound row_limit limits emitted rows; zero still returns a schema-bearing empty result. It is not a promise that the server avoids computing the rest of the query.

Decimals must declare precision 1–38 and scale 0–precision. Unconstrained Oracle NUMBER, negative scales and wider precision fail with a request to cast explicitly. Values needing rounding during conversion also fail. Prefer cast(amount as number(18,2)) when that precision matches the business rules; cast to character data when preserving a wider decimal exactly. SQL Server decimal(38,18) is covered at Arrow precision, including large values where Excel’s 15-significant-digit numeric limit would change the number. xlsx writes values above that limit as exact text and warns. That display conversion is not a database or Arrow precision loss; exact text or a supported output format remains authoritative for wider values.

JDBC TIME and TIMESTAMP columns with declared fractional precision above 3 are refused in this version rather than silently truncated. Cast to millisecond precision explicitly if appropriate, or return exact text. Timestamps without a timezone retain their wall-clock fields in a timezone-free Arrow timestamp; the isolated JVM uses UTC to make transport independent of the host’s timezone. Timezone-bearing vendor types and broader type fidelity need separate evidence. SQL Server datetime2(3) is tested. datetime, datetimeoffset, deprecated timestamp/rowversion, money and other types are not implied to be lossless; return an explicit supported cast or exact text where needed. Unsupported JDBC conversions fail; cast the column to a supported representation in SQL.

Oracle DATE includes time-of-day. The driver’s default timestamp mapping preserves it. If a driver setting such as oracle.jdbc.mapDateToTimestamp: false exposes it as JDBC DATE, the source refuses that date-only mapping and asks for an explicit TIMESTAMP(3) or text cast. This avoids silently dropping the clock fields. Oracle RAW and SQL Server varbinary values have Arrow/workbook coverage; xlsx renders binary values as hexadecimal text. SQL Server date is a separate date-only type; generic Oracle DATE handling must not be applied to it.

sessions, validate and catalog are advertised. There is no check, temporary lookup load, or restricted-query capability. dre catalog with a JDBC profile uses DatabaseMetaData to return accessible schemas, tables/views, columns, types, comments and identifier rules through the existing dre/catalog/v1 output. Requested bare table names use the current schema; quote mixed-case names to preserve their exact spelling. tables or schemas in the catalog request scope the result. A missing requested object and a permission failure fail explicitly. If the driver supplies no comments, the result carries a warning. An explicit read_only: true protocol request is refused. JDBC read-only hints are not an authorization boundary; configure a database account with appropriate permissions. Cancellation uses the running JDBC statement’s cancel() and checks between batches. Actual server cancellation still depends on the driver.

Diagnostics and secrets

Section titled “Diagnostics and secrets”

The URL and all property values are potentially sensitive. The plugin never logs them or sends raw driver exception text. Driver stdout/stderr is discarded because it can contain credentials or fragments extracted from a URL. Structured errors retain the failure category, SQLSTATE and numeric vendor code, plus the relevant configuration/query checks. Look up those codes in the vendor documentation; do not enable raw driver logging to troubleshoot production credentials. Authentication failures and a deliberately noisy test driver are checked with secret sentinels.

Tested fixtures

Section titled “Tested fixtures”

The reproducible Oracle environment is Oracle Free 23.26.3.0-lite with Oracle JDBC ojdbc11 23.26.3.0.0. The reproducible SQL Server environment is SQL Server 2022 CU27 (16.0.4295.3) on x86-64 Linux with Microsoft JDBC Driver 13.6.1.jre11. Both run on Java 17, Apache Arrow Java 19.0.0 and the development plugin build. CI pins the container digests, verifies the SQL Server driver SHA-256 and runs the database tests on GitHub’s x86-64 ubuntu-latest runner. Apple Silicon SQL Server emulation is not support evidence.

The Oracle fixture and SQL Server fixture use the same logical rows and separate dialect setup/query files. Tests check Arrow types and values through the protocol, then run the ordinary CLI path and inspect workbook values, formats and null cells. They also cover complete URLs, minimal URLs with properties, preparation in the final session, cancellation, cleanup, precision refusal, secret redaction and scoped catalog discovery of mixed-case table and view names. These results establish the two tested environments, not certification of every database or driver version.

SQL Server date, timezone-bearing datetimeoffset, deprecated timestamp/rowversion, and further JDBC databases still need separate evidence. Generic JDBC reach depends on compatible user-supplied drivers.

Sources: Apache Arrow JDBC adapter, Java JDBC URL/property contract, Oracle Thin URL forms.