What is the eConnector for JDBC?

Databases are at the core of all Documentum work and whether you work at the DFC level with objects,
at the API level with raw commands to the Documentum server, or through a GUI interface,
in reality, the work is being done in a database.

JDBC is a standard java API for accessing relational databases. It is supported by most major database vendors,
and implements an abstraction of database functionality. The eConnector for JDBC uses the JDBC standard to give you a connection to a docbase. It is this abstraction that is the key to
the power of JDBC, allowing for services like transparent connection pooling. Also, since JDBC is the recommended way of accessing databases
on the J2EE platform, it can easily become part of a EJB production-level bean.

It is worth noting that, although DQL commands mimic the SQL syntax, you can’t take advantage of the services, or the standard interface to fetch your results.

So, why would one want to use the JDBC eConnector, when there are so many other ways? It simply comes down to
standardizing the access method to DCTM. JDBC is a well known standard in the java community, and so it is an
easy and well documented way to access a database. Also, if you are using WebCache, you have to use JDBC to access the external database that contains the meta-data. The eConnector for JDBC also has the ability to query and manipulate this WebCache data as well.

JDBC core concepts

Let’s get some basic JDBC terminology out, so we can get an understanding of how we will be interacting
with the JDBC interface.

At the heart, is the
DriverManager
which keeps track of all various
JDBC implementations that we might be dealing with. When you ask for a connection to a docbase, WebCache, Oracle,
or any other JDBC driver, it is the DriverManager that takes care of the details of making this happen.

The
Connection
is our single connection to the datasource. It stores the information about the database,
and gives us the handles to the
Statement
class that we will need to make our queries.

Statement
is the main class we interact with to get our queries executed. Whether we call a SELECT, UPDATE,
or INSERT, this class and its subclasses are used to make the call and get out the results.

Probably the most used class will be the
ResultSet
. After a Statement is executed, the results are
returned through the ResultSet, and allow you to navigate through the results of your query.

Metadata on a query, such as how many columns were returned and what their names are can be accessed through
the
ResultSetMetaData
interface.

JDBC Documentum connection

Now that we have some terminology and a general view of the architecture and classes
that will be participating in our program, let’s go through a little pseudo-code for
making the JDBC connection where our specific driver will be for Documentum.

These are the steps we will be taking:

• Import the packages
• Register the Driver
• Get a Connection
• Create a Statement
• Execute a Query
• Get the MetaData for the results
• Go through the results
• Cleanup

Import the packages

Since we will be using JDBC we simply include the standard java package for that:

import java.sql.*;

Register the Driver

Then we need to register the driver, so we dynamically pull in the DCTM eConnector:

Class.forName (“com.documentum.oca.jdbc.jdbc20.DjdbcDriver”);

For WebCache that would be:

Class.forName (“com.dcoumentum.oca.jdbc.webcache.DjdbcDriver”);

Get a Connection

Then we populate some properties, and pass them into the DriverManager in order to give us
the actual connection we will use to get to DCTM:

Properties info = new Properties();
info.put("user",username);
info.put("password",password);
info.put("database",docbase);
// Connect to the database
Connection con = DriverManager.getConnection(
"jdbc:documentum:oca:docbase",
info);
// for WebCache use this connection string
// jdbc:documentum:oca:webcache

Create a Statement

Then we use the Connection to create a Statement:
Statement stmt = con.createStatement();

Execute a Query

Then use the statement to execute a query:
ResultSet rs = stmt.executeQuery("select object_name from dm_cabinet");

Get the MetaData for the results

Then we can fetch the metadata from the ResultSet:
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();

Go through the results

Now, we just iterate through our results:
System.out.println("showing results...");
while(rs.next()) {
System.out.println(rs.getString("object_name"));
} // each row

Cleanup

Finally, we need to close our ResultSet, Statement, and Connection:

rs.close();
stmt.close();
con.close();

Sample JDBC GUI

The sample program created for this article is a very simple swing interface to connect to a JDBC datasource
and display the results in a tabular manner. [Download the sample program]

We are offering the source code to this little program, which means you’ll need to compile and run it
using a java compiler. This will need to be JDK 1.1 with the optional JDBC package or JDK 1.2. The jars that
need to be in your classpath include dfc.jar, and dmjdbc.jar which comes with the Documentum
download of the JDBC eConnector. The classpath also needs to know the location of the dmjdbc.properties file.
Furthermore, your path needs to be modified to include the Documentum libraries
as well as the directory that contains the JDBC libraries. For UNIX, use the LD_LIBRARY_PATH instead of PATH.

To quote the Documentum documentation on this:

• Set the java apps running environment
  set PATH=%PATH%;c:documentumjdbc
• Add the client libraries
  set PATH=%PATH%;c:documentumlib
• Add the driver jar file to your classpath
  set CLASSPATH=%CLASSPATH%;c:documentumjdbcdmjdbc.jar

After you have finished setting these environment variables you can compile the program by typing:

javac JDBCGui.java

To run the program you will then need to type:

java JDBCGui

Note: Personally, I don’t setup my classpath this way. I actually include the dfc.jar,
dmjdbc.jar, dmjdbc.properties, dmjdbcpool.properties, and JDBCGui.jar in the same directory. Then
I compile the program like this:

javac -classpath .;dfc.jar;dmjdbc.jar JDBCGui.jar

Also, if you have a problem running the program, try including rt.jar, which should be in your jdk/jre/lib directory.

The variables in the application are initially populated with a default set of values, which you will need to change
to run in your environment. Simply fill in a valid username, password, and docbase name, and then press “Run Query”
in order to run the simplest query which is a list of all the cabinets in your docbase.

The most important pieces in the code are the canConnect() method, and executeQuery() method. Take a look
at these two points in the code to see how we made the Connection using the DriverManager, and how we
executed a query, and then brought back the metadata results and then the actual rows of the results.

Summary

In this article we have explored the core interfaces of the JDBC, which is an introduction to a core J2EE concept.
using that knowledge, we have used the eConnector for JDBC to connect to a docbase and run a simply query, viewing
the results.

Further explorations should include the Javadocs on JDBC, downloads of JDBC documentation from Documentum,
and tutorials on JDBC which can be found on the web.

We have only skimmed the surface of JDBC, we have not gone into queries like UPDATE, DELETE, JOINS, or the
specifics on making a WebCache connection. But, with this base knowledge, and your first compiled program, you
have hurdled the highest barrier of entry.