Enterprise Manager: Databases > Accessing Third Party Databases

Description

BBj uses the power of the JDBC (Java Database Connectivity) API to provide access to third party databases from BBj programs. The language offers several methods to retrieve and modify data, or create database entities such as tables and views. The process for accessing a third party database includes the following steps:

Download the appropriate third party JDBC driver.
Install the JDBC driver to make it available to BBj.
Build an appropriate JDBC connection URL.
Connect to the database from BBj.

Download Third Party JDBC Driver

Most database management systems (DBMS) offer one or more JDBC drivers. It is very important to acquire the correct driver if multiple versions are available. Some drivers utilize a combination of Java and native code and so it is important to ensure that any native libraries match the bit type for the Java Virtual Machine (JVM) used to run BBjServices (64- or 32-bit). Note that BBj currently provides off-the-shelf support for BASIS databases and Microsoft Access Database files (.mdb or .accdb). See Accessing Microsoft Access Databases below for more information. Any other databases require configuration of the appropriate drivers.

Install the JDBC Driver

JDBC drivers typically consist of a JAR file that contains the necessary classes to provide connectivity. After downloading the appropriate JAR file(s), make the JAR available to BBj as follows::

Log into the Enterprise Manager.

Double-click on the “Java Settings” node.

Select the “Classpath” tab.

Select the <default> classpath item.

Click the add button above the classpath entries and add the required JAR(s) to the classpath.

Save the changes. Restart BBjServices.

Build a Connection URL

The JDBC connection URL tells the JDBC API in BBj how to connect to the database of interest. Connection URLs contain information that indicates which driver to use, the hostname of the server, name of the database, and other connection properties. A connection URL is specific to a particular database and so URLs for different DBMS products will differ. Below are some examples of connection URLs to popular databases:

MySQL	jdbc:mysql://myserver/mydatabase
Microsoft Access	jdbc:ucanaccess:///Users/jdoe/mydb.mdb
Microsoft SQL Server	jdbc:jtds:sqlserver://MYPC/Blog;instance=SQLEXPRESS;user=sa;password=s3cr3t
Oracle	jdbc:oracle:thin:scott/tiger@localhost:1521:orcl

Connect to a Database from BBj

BBj provides several ways to execute SQL queries from programs. This example shows how to use the SQL verbs to establish a connection. For information on other methods please see SQLRecordSet or consult the JDBC API specification.

Connecting to a third party database from BBj is simple. The program needs two pieces of information, the class that implements java.sql.Driver, and the connection URL. The JDBC documentation for the third party driver will provide the java.sql.Driver class implementation which will look something like com.mysql.jdbc.Driver or com.basis.jdbc.BasisDriver. This example shows how to connect to a MySQL database:

REM Load the JDBC driver class into memory. Note this
REM only needs to happen one time.
Class.forName("com.mysql.jdbc.Driver")

REM Open the database connection.
chan = SQLUNT
url$ = "jdbc:mysql://myserver/mydatabase"
SQLOPEN(chan, mode="user=myname,pwd=secret") url$

REM Execute an SQL query
SQLPREP(chan)"SELECT * FROM my_table"
SQLEXEC(chan)

Accessing Microsoft Access Databases

Version 15.0 of BBj provides off-the-shelf support for Microsoft Access database files using the UCanAccess JDBC driver (installed and ready to use automatically when installing BBj). Accessing data in an MS Access database is as easy as specifying the location of the .mdb or .accdb database file. Note that this powerful feature now makes it possible to access MS Access databases from any operating system, not just Windows. Since it is already built in to BBj, there is no need to load the JDBC driver:

REM Open the database connection.
chan = SQLUNT
url$ = "jdbc:ucanaccess://C:/mydatabase.accdb"
SQLOPEN(chan) url$