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.
Virtual DataPort provides a driver that implements the main characteristics of the JDBC 4.1 API (Java Database Connectivity). 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
- Support for canceling the current statement execution by using
cancel()method of the
java.sql.Statementclass. When a query is cancelled, the Virtual DataPort Server will cancel all current accesses to data sources and cache. After invoking the
cancelmethod, 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
ResultSetthat is being used.
- Invocation of stored procedures using the
- Support for submitting batches of commands.
ResultSetobjects 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
ResultSetobjects are closed when the current transaction is committed (i.e.
The JDBC driver is at
This directory also contains the file
denodo-vdp-jdbcdriver-basic.jar is. The section When to Use the “Basic” Version of the JDBC Driver
explains when you should use this
jar file instead of the
The class that implements the driver is
The syntax of the database URL is
jdbc:vdb://<hostName>:<port>/<databaseName>[?<paramName>=<paramValue> [&<paramName>=<paramValue>]* ]
The name of the database is mandatory.
The connection URL only references one port (by default, 9999). However, the driver also opens a connection to the Auxiliary port of the Denodo server (by default, 9997). Therefore, if there is a firewall between the client and the Denodo server, you need to allow connections to both ports. You can get the value of this port using the administration tool, in the menu Administration > Server Configuration, in the tab Server connectivity.
If the name of the database contains non-ASCII characters, they have to be URL-encoded. For example, if the name of the database is “テスト”, the connection URL to the database will be this:
DENODO_HOME/samples/vdp/vdp-clients contains examples
of client programs accessing Virtual DataPort through JDBC (the
of this path explains how to generate and publish the views accessed by
the clients in the example).