# 7. Data Access Interfaces

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Data Access Interfaces
Virtuoso .NET Data Provider
Interactive SQL Utility
Virtuoso Driver for ODBC
Virtuoso Driver for JDBC
Virtuoso Drivers for JDBC Packaging Virtuoso Driver For JDBC URL Format Virtuoso Driver JDBC 3.0 features Installation & Configuration Steps
OLE DB Provider for Virtuoso
Virtuoso In-Process Client
Unix Domain Socket Connections

## 7.4. Virtuoso Driver for JDBC

The Virtuoso Drivers for JDBC are available in "jar" file formats for JDBC 1.x, JDBC 2.x and JDBC 3.x specifications. These are Type 4 Drivers implying that utilization is simply a case of adding the relevant "jar" file to your CLASSPATH and then providing an appropriate JDBC URL format in order to establish a JDBC session with a local or remote Virtuoso server. It is important to note that when you make a JDBC connection to a Virtuoso Server, you do also have access to Native and External Virtuoso tables. Thus, you actually have a type 4 JDBC Driver for any number of different database types that have been linked into Virtuoso.

The JDBC 2 and JDBC 3 drivers also incorporate SSL encryption to enable very secure connections to the Virtuoso database.

### 7.4.1. Virtuoso Drivers for JDBC Packaging

These drivers are installed alongside the Virtuoso Server or as part of a Virtuoso Client components only installation. They are packaged as follows:

Table: 7.4.1.1. Features Comparison
Driver Name | Java Package | "jar" File Archive | Default Location | Java Version
--- | --- | --- | --- | ---
virtuoso.jdbc.Driver | virtuoso.jdbc | virtjdbc.jar | <virtuoso installation directory>\jdk11 | Java 1.1.x
virtuoso.jdbc2.Driver | virtuoso.jdbc2 | virtjdbc2.jar | <virtuoso installation directory>\jdk12 | Java 1.2/1.3
virtuoso.jdbc3.Driver | virtuoso.jdbc3 | virtjdbc3.jar | <virtuoso installation directory>\jdk13 | Java 1.4

### 7.4.2. Virtuoso Driver For JDBC URL Format

JDBC compliant applications and applets connect to JDBC Drivers using JDBC Uniform Resource Locators (URLs). Although there are two Virtuoso Drivers for JDBC, both share the same JDBC URL format.

The Virtuoso Driver for JDBC URL format takes the following form:

    jdbc:virtuoso://<Hostname>:<Port#>/DATABASE=<dbname>/UID=<user name>/PWD=<password>/
      CERT=<certificate_alias>/KPATH=<keystore_path>/PASS=<keystore_password>/
      PROVIDER=<ssl_provider_classname>/SSL/CHARSET=<character set>/
      TIMEOUT=<timeout_secs>/PWDTYPE=<authentication_type>

Each part of the URL is explained below:

Protocol Identifiers

this is a constant value of "jdbc" since JDBC is the protocol in question

Sub Protocol Identifier

this is a constant value that identifies "virtuoso" as a sub protocol of JDBC

Hostname

this identifies the machine hosting a server process that speaks the "virtuoso" sub dialect of the "jdbc" protocol

Port Number

this identifies the port number on the machine from which the server which speaks the "virtuoso" sub dialect of "jdbc" listening for incoming client connections The default port number for a Virtuoso server is "1111".

/DATABASE

this identifies the database (Qualifier or Catalog) that you are connecting to via a Virtuoso server

/UID

a valid user name for the Virtuoso database that you are connecting to via JDBC

/PWD

a valid password for the user name

/CERT=<certificate_alias>

name of the certificate to use for the SSL connection stored in the keystore. This is a required option for an SSL authenticated connection

/KPATH=<keystore_path>

This optional parameter lets you specify the keystore file name (default: $HOME/.keystore). The path separator is \, and which is then replaced during the connection by the right platform path separator.

/PASS=<keystore_password>

password required for accessing the keystore file. This is required for the SSL authenticated connection.

/PROVIDER=<ssl_provider_classname>

The class name of the SSL Provider (e.g. com.sun.ssl.net.internal.ssl.Provider) to use for the SSL cryptography. This parameter is required for SSL connections.

/SSL

The SSL option is used only for SSL connection without user authentication

/CHARSET=<character set>

This allows the client to specify a character set for data encoding. When this option is set then all Java strings, natively Unicode, are converted to the character set specified here.

/TIMEOUT=<timeout_secs>

Specifies the maximum amount of time (in seconds) that the driver will wait for a response to a query. When this time is exceeded a time-out error will be reported and the network connection closed, assumed to be broken.

/PWDCLEAR=<authentication_type>

Specifies the authentication mode; how the user credentials may be transmitted to the server. This option can be one of the following 3 types: cleartext, encrypt, digest. The default is digest.

"cleartext" will transfer the password to the server in cleartext

"encrypt" will transfer the password to the server using Virtuoso's symmetric encryption technique.

"digest" will calculate an MD5 digest of the password (and some additional session variables) that will be sent to the server to be compared with the value calculated server-side.

Note:

Since JSSE has only incorporated SSL support for JDK 1.2 and above, SSL has only been implemented for the JDBC 2.x and JDBC 3.x drivers for Virtuoso.

### 7.4.3. Virtuoso Driver JDBC 3.0 features

#### 7.4.3.1. Virtuoso Driver For JDBC 3.0 javax.sql.DataSource

JDBC 3.0 compliant applications and applets may connect to a JDBC data source using JDBC javax.sql.DataSource instances. The Virtuoso JDBC 3.0 driver provides an implementation of the javax.sql.DataSource interface in the virtuoso.jdbc3.VirtuosoDataSource class, supporting the following properties:

Table: 7.4.3.1.1. JDBC 3.0 VirtuosoDataSource properties
Name | Type | URL Option Equivalent | Description
--- | --- | --- | ---
dataSourceName | java.lang.String |  | used in connection pooling
description | java.lang.String |  | string to describe the data source (free form)
serverName | java.lang.String |  | The host name of the remote host to connect to
portNumber | int |  | The port on the remote host to connect to
user | java.lang.String | /UID | username to use for the session
password | java.lang.String | /PWD | password to use for the session
databaseName | java.lang.String | /DATABASE | Initial catalog qualifier for the session
charset | java.lang.String | /CHARSET | Charset used in wide<->narrow translations
pwdClear | java.lang.String | /PWDTYPE | authentication method

#### 7.4.3.2. Virtuoso Driver For JDBC 3.0 & Connection Pooling

The Virtuoso JDBC 3.0 driver supports connection pooling.

The virtuoso.jdbc3.VirtuosoDataSource implements the javax.sql.ConnectionPoolDataSource interface. In order to use the connection pooling the administrator must deploy one instance of the virtuoso.jdbc3.VirtuosoDriver in the JNDI repository and set all of it's properties except dataSourceName. This is the "main" connection pooling data source. Then the administrator should deploy a second instance of the virtuoso.jdbc3.VirtuosoDataSource class and set only it's dataSourceName property.

Applications will use the second virtuoso.jdbc3.VirtuosoDataSource instance to get a connection. It will in turn call the first one to obtain all connect info and return the java.sql.Connection instance.

#### 7.4.3.3. Virtuoso Driver For JDBC 3.0 & Distributed Transactions

Virtuoso supports the industry standard XA specification for distributed transaction processing. The XA specification defines an interface between the transaction manager (TM) and resource manager (RM) in a distributed transaction system. This is a generic interface and it does not directly address the use of distributed transactions from Java. The Java mapping of the XA interface is defined in Sun Microsystems Java Transaction API (JTA) and JDBC 3.0 specifications. The Virtuoso JDBC 3.0 driver supports the JTA architecture by providing the implementation of JTA resource manager interfaces.

The Virtuoso JDBC 3.0 driver provides the virtuoso.java3.VirtuosoXid, virtuoso.java3.VirtuosoXADataSource, virtuoso.java3.VirtuosoXAConnection, and virtuoso.java3.VirtuosoXAResource classes which implement the interfaces javax.transaction.xa.Xid, javax.transaction.xa.XADataSource, javax.sql.XAConnection, and javax.sql.XAResource respectively. The use if these interfaces is usually transparent for applications and the application developer shouldn't bother with them. They are used only by the JTS transaction manager which normally runs as a part of the J2EE server.

The task of the J2EE server administrator is to setup the necessary Virtuoso XA datasources. The exact procedure of this depends on the J2EE server in use (such as BEA WebLogic, IBM WebSphere, etc). Generally, this includes two steps:

1. Include the JDBC driver's jar file into J2EE server's class path.

2. Deploy an instance of javax.transaction.xa.XADataSource with appropriately set properties into the J2EE server's JNDI repository.

The virtuoso.java3.VirtuosoXADataSource class is derived from virtuoso.java3.VirtuosoDataSource and inherits all of its properties. These properties has to be set as described in the section Virtuoso Driver For JDBC 3.0 javax.sql.DataSource.

For example, the following has to be done in case of Sun's J2EE Reference Implementation.

1. Add the path of virtjdbc3.jar to the J2EE_CLASSPATH variable in the file $(J2EE_HOME)/bin/userconfig.bat on Windows or $(J2EE_HOME)/bin/userconfig.sh on Unix/Linux:

           set J2EE_CLASSPATH=C:/Virtuoso/lib/virtjdbc3.jar

   or

           J2EE_CLASSPATH=/home/login/virtuoso/lib/virtjdbc3.jar
           export J2EE_CLASSPATH

2. Using the following command add the XA datasource with JNDI name "jdbc/Virtuoso" which refers to the Virtuoso server running on the same computer on port 1111:

           j2eeadmin -addJdbcXADatasource jdbc/Virtuoso virtuoso.jdbc3.VirtuosoXADataSource -props serverName=localhost portNumber=1111

#### 7.4.3.4. JDBC 3.0 javax.sql.RowSet Driver Implementation

The Virtuoso JDBC 3.0 driver has two implementations of the javax.sql.RowSet interface - virtuoso.javax.OPLCachedRowSet and virtuoso.javax.OPLJdbcRowSet.

The virtuoso.javax.OPLCachedRowSet class implements a totally disconnected, memory cached rowset and the virtuoso.javax.OPLJdbcRowset class spans the rest of the JDBC API to implement it's methods.

### 7.4.4. Installation & Configuration Steps

Perform the following steps in order to make use of your Virtuoso Drivers for JDBC:

Note:

You only have to perform these steps if a first attempt to use the Virtuoso Drivers for JDBC fails, the Virtuoso installer will attempt to configure these settings for you at installation time.

1. Ensure your PATH environment variable is pointing to a version of the Java Virtual Machine (JVM) that is compatible with the version of the JDBC Driver Manager installed on your machine. Consult the section above to double check. You can also type the following command to verify Java versions:

       java -version

2. Add the appropriate Virtuoso for JDBC "jar" file to your CLASSPATH environment variable.

3. Attempt to make a connection using one of the sample JDBC Applications or Applets provided with your Virtuoso installation.

Note:

If you have problems using the virtuoso JDBC driver despite your CLASSPATH being defined correctly, you may force the Java Virtual Machine to load a specific JDBC driver using: -D on the java command line: e.g:

-Djdbc.drivers=virtuoso.jdbc.Driver.

You can check the Virtuoso JDBC driver version from the command line using: java virtuoso.jdbc.Driver

Previous Virtuoso Driver for ODBC | Chapter Contents | Next OLE DB Provider for Virtuoso