JDBC Driver Installation and Connection

If your business intelligence tools use the JDBC protocol, you need to install a special JDBC driver included with the CData Virtuality Suite or downloadable from our web server. This manual describes how to install it and gives an example.

Driver Installation

The driver is contained in the file called datavirtuality-jdbc.jar. If you have the CData Virtuality Suite installed, it is found in ..\CData Virtuality Suite\Drivers, and if not, you can download the file from the server.

To install the driver, you must add it in your tool settings and indicate its file path. If you have downloaded the driver file separately, you can put it in any convenient folder (such as the tool's folder).

For setting up the connection, you need to know the driver class and the JDBC URL. The driver class for our driver is com.datavirtuality.dv.jdbc.Driver, and the JDBC URL should be in the following form: jdbc:datavirtuality:<vdb-name>@mm[s]://<host>:<port>[;prop-name=prop-value].

Our JDBC URLs are as follows:

Simple connection string: jdbc:datavirtuality:datavirtuality@mm://localhost:31000
SSL connection string (recommended): jdbc:datavirtuality:datavirtuality@mms://localhost:31001

URL components:

Component	Description
<vdb-name>	Name of the VDB you are connecting to. Primary VDB is called `datavirtuality`
`mm`/`mms`	JDBC protocol, `mm` for simple channel and `mms` for secure channel
`host`	Server where the CData Virtuality Server is installed
`port`	Port on which the CData Virtuality Server is listening for incoming JDBC connections. Default: `31000` for simple connection/`31001` for secure connection
`[;prop-name=prop-value]`	Optional component where you can supply any number of name-value pairs separated by semi-colon [;]. For a list of all compatible URL properties, see URL Connection Properties below. Property values should be URL encoded if they contain reserved characters, e.g. (’?’, '=', ';', etc.)

host and port may be a comma-separated list to specify multiple hosts

URL Connection Properties

The following table shows all the connection properties you can use with the CData Virtuality JDBC Driver URL connection string, or on the CData Virtuality JDBC Data Source class.

Property	Type	Description
`ApplicationName`	`string`	Name of the client application; allows the administrator to identify the connections
`FetchSize`	`int`	Size of the resultset. Default: `500`. `⇐0` indicates that the default should be used
`partialResultsMode`	`boolean`	Enable/disable partial results mode. Default: `FALSE`
`autoCommitTxn`	`string`	Only applies when `autoCommit` is set to `TRUE`. This determines how an executed command must be transactionally wrapped inside the CData Virtuality engine to maintain the data integrity. Possible values: `ON`: always wrap the command in a distributed transaction `OFF`: never wrap the command in a distributed transaction `DETECT` (default): if the executed command spans more than one source, it automatically uses distributed transaction
`disableLocalTxn`	`boolean`	If `TRUE`, the `autoCommit` setting, commit, and rollback will be ignored for local transactions. Default: `FALSE`
`user`	`string`	User name
`Password`	`string`	User password
`ansiQuotedIdentifiers`	`boolean`	Sets the parsing behaviour for double-quoted entries in SQL. If set to `TRUE`, parses doubled quoted entries as identifiers. If set to `FALSE`, then double-quoted values that are valid string literals will be parsed as string literals. Default: `TRUE`
`version`	`integer`	Version number of the VDB
`resultSetCacheMode`	`boolean`	ResultSet caching is turned on/off. Default: `FALSE`
`autoFailover`	`boolean`	If `TRUE`, will automatically select a new server instance after a communication exception. Default: `FALSE`. This is typically not needed when connections are managed, as the connection can be purged from the pool. If `TRUE` in embedded mode, connections will reconnect to a newer VDB of the same name/version
`SHOWPLAN`	`string`	(typically not set as a connection property) Possible values: `ON`: returns the query plan along with the results `DEBUG`: additionally prints the query planner debug information in the log and returns it with the results. Both the plan and the log are available through JDBC API extensions `OFF` (default)
`NoExec`	`string`	(typically not set as a connection property) Possible values: `ON`: prevents query execution, but parsing and planning will still occur `OFF` (default)
`QueryTimeout`	`integer`	Default query timeout in seconds. Must be >= 0. `0` indicates no timeout. Can be overridden by `Statement.setQueryTimeout`. Default: `0`
`useJDBC4ColumnNameAndLabelSemantics`	`boolean`	A change was made in JDBC4 to return unaliased column names as the `ResultSetMetadata` column name. Before this, if a column alias was used, it was returned as the column name. Setting this property to `FALSE` will enable backwards compatibility with JDBC3 and earlier. Default: `TRUE`
`jaasName`	`string`	JAAS configuration name. Only applies when configuring a GSS authentication. Default: `DVClient`
`kerberosServicePrincipleName`	`string`	Kerberos principal name. Only applies when configuring a GSS authentication. Default: `DVServer/hostname`
`encryptRequest`	`boolean`	Only applies to non-SSL socket connections. When set to `TRUE`, the request message and any associate payload will be encrypted using the connection encryptor. Default: `FALSE`
`disableResultSetFetchSize`	`boolean`	In some situations, tooling may choose undesirable fetch sizes for processing results. To disable honouring `ResultSet.setFetchSize`, set to `TRUE`. Default: `FALSE`
`loginTimeout`	`integer`	Login timeout in seconds. Must be >= 0. `0` indicates no specific timeout, but other timeouts may apply. An exception will be thrown if a connection cannot be created in approximately the timeout value. A default of `0` does not mean that the login will wait indefinitely. Typically if an active VDB cannot be found, the login will fail at that time. Local connections that specify a VDB version, however, can wait by default for up to the time specified in the `org.teiid.clientVdbLoadTimeoutMillis[org.teiid.clientVdbLoadTimeoutMillis` property
`reportAsViews`	`boolean`	If `TRUE`, the CData Virtuality Server will report views as a `VIEW` table type. If `FALSE`, views will be reported as a `TABLE`. Default: `TRUE`
`removeQuotesFromCatalog`	`boolean`	When set to `TRUE`, double quotes around the catalog name are removed to load the correct metadata
`useDvRoles`	`boolean`	When set to `TRUE`, CData Virtuality roles are used to get permission for SSO users

removeQuotesFromCatalog and useDvRoles properties available since v4.2

Example Using the SQuirrel SQL Client

This example shows how to add the driver if the file has been downloaded separately and put into the tool's folder. If you have the CData Virtuality Suite installed, you can indicate the file path to the file in the Suite's Drivers folder.

Open Squirrel and press the '+' sign on drivers, or use Drivers → New Driver in the menu.
Set a name for the driver (e.g. 'dv').
Enter example URL: jdbc:datavirtuality:datavirtuality@mm://localhost:31000.
Switch to the Extra Class Path tab, click Add, and find the datavirtuality-jdbc.jar file in your filesystem (in this example, it has been put into the tool's folder):
Click List Drivers. The Class Name field should have com.datavirtuality.dv.jdbc.Driver in it.
Click OK to save the driver configuration.
Click on the Alias tab and press the + sign to add an alias.
Choose your alias name set up at Step 2 (in our example, dv) in the URL selection field.
This is what it looks like at this step:
Enter your credentials and connect.