Access Through JDBC¶

JDBC (Java Database Connectivity) is a Java API that allows executing statements on a relational database regardless of the Database Management System used.

Denodo provides a JDBC Type 4 driver that implements the main characteristics of the JDBC 4.1 API (Java Database Connectivity). This means that the driver is platform independent.

Get the Denodo JDBC driver from:

1. The Denodo Community: https://community.denodo.com/drivers/jdbc/. You need to register on this site to download it (anyone can register).

   Download the version of the update installed in the Denodo server. To find out the version of the Denodo server you are connecting to, log in to that server with the Design Studio, click the menu File > About… and look for VDP server (below installed updates).

2. Or from your installation of the Denodo Platform (<DENODO_HOME>/tools/client-drivers/jdbc/vdp-jdbcdriver-core/denodo-vdp-jdbcdriver.jar).

Java Version

The JDBC driver is compatible with Java 8, 9, 10, 11 and 17.

Driver Class Name

The class name of this driver is this:

com.denodo.vdp.jdbc.Driver

Connection URL

The syntax of the database URL is this:

jdbc:denodo://<hostname>[:<port>]/[<database>][?<paramName>=<paramValue>[&<paramName>=<paramValue>]*]

• <port>: optional; if you do not put it, the driver assumes “9999” (the default port for Virtual DataPort).

• <database>: optional.

For example:

jdbc:denodo://denodo-server.acme.com?queryTimeout=100000&userAgent=myApplication

As this is a URL, the name of the database and the values of the parameters must be URL-encoded. That is, if the name of the database contains spaces, non-ASCII characters, etc. For example, if the name of the database is “テスト”, the connection URL to the database must be like this:

jdbc:denodo://localhost:9999/%E3%83%86%E3%82%B9%E3%83%88?queryTimeout=100000&userAgent=myApplication

For backward compatibility, the driver also supports the prefix “jdbc:vdb” in addition to “jdbc:denodo”.

Backward Compatibility

• You can connect to Virtual DataPort version 9 with the JDBC driver and the ODBC driver of the version 8.0.

• You can connect to Virtual DataPort version 8.0 with the JDBC driver and the ODBC driver of the version 9.

See more about this topic in the page Backward Compatibility Between the Virtual DataPort Server and Its Clients.

Features of the Driver

These are some of the features of the JDBC specification supported by the Virtual DataPort JDBC driver:

• The data types supported are defined in the VQL Guide (includes support for all basic types and for fields of the type array and register).

• Execution of statements to query, insert, update and delete data. In addition, to create new elements such as data sources, views, etc.

• Support for metadata description statements and listing of server catalog elements.

• Support for PreparedStatements.

• Support for canceling the current statement execution by using the cancel() method of the java.sql.Statement class. When a query is cancelled, the Virtual DataPort Server will cancel all current accesses to data sources and cache. After invoking the cancel method, it is still possible for the server to return some results, if these were retrieved before the source access canceling were effective. Therefore, the query cancellation does not imply closing the ResultSet that is being used.

• Invocation of stored procedures using the CALL statement.

• Support for submitting batches of commands.

• The ResultSet objects returned by Virtual DataPort are not updatable (i.e. CONCUR_READ_ONLY) and have a cursor that moves forward only (i.e. TYPE_FORWARD_ONLY). In addition, the ResultSet objects are closed when the current transaction is committed (i.e. CLOSE_CURSORS_AT_COMMIT).

• The driver implements the method ResultSet getGeneratedKeys() of the class java.sql.Statement (see Get Auto-Generated Keys in IDU Statements).

• Support for JDBC escape syntax for timestamp, date, and time literals (see Queries with Datetime Literals).