Connecting Using the JDBC Driver

Using a JDBC driver requires knowledge of the following information:

  • The JDBC Driver implementation of the java.sql.Driver interface

  • The URL for connecting to the desired database

Driver Class

The BBj JDBC Driver implementation of the Java.SQL.Driver interface is as follows:

com.basis.jdbc.BasisDriver

In order to have access to the com.basis.jdbc.BasisDriver class, the CLASSPATH environment variable must include the location of the BBjJDBC.jar file. This file is located in the <bbj install>/.lib directory.

See Session-Specific Classpath (SSCP) for more information on classpaths.

Connection URL

To make a connection to the JDBC driver, use a connect string in the following format:

jdbc:basis:<server>:<port>?database=<database_name>&user=<user_name>&password=<password>

The important properties that can be specified are as follows:

Property Description BBj Version

database=<database_name>

Database on the BBj SQL server to open. The database name must be provided.

 All

user=<user_name>

A BBj SQL user to open the database.

 All

password=<password>

A BBj SQL password for the provided user to authenticate with.

 All
connectioncache=<true or false>

When enabled (default), the first 4 JDBC connections with identical connection properties and user credentials will share the same network socket connection to the server.

Some third-party applications use multiple simultaneous connections to perform tasks such as populating a spreadsheet. This allows users to utilize these applications while only consuming a single license, as only one connection to the server exists. However, this may impact performance since multiple JDBC connections share the same network socket. In such cases, it may be advantageous to disable the connection cache.

 All
prefetch=<num_results> Most SQL query processing occurs server-side to minimize data transferred to the client (3rd party application). To optimize the retrieval of results, the JDBC driver pre-fetches multiple results as a single call rather than making separate calls to the server for each result. The default value (50) was chosen by BASIS developers based on extensive testing over the years. When the value is too high or too low, it may negatively impact performance. Use caution when changing this value. All

ssl=<true or false>

 

Indicate whether the BBj SQL server is using SSL. If this property is set to "true", the server must be using SSL in order to connect. Alternately, if this property is set to "false", the client will be unable to connect to a server that is using SSL. This ensures that the user knows whether or not the connection to the BBj SQL server is secure.

 4.0

keystore=<keystore>

This is optional. If a user wants to specify their own SSL keys and authentication connecting to the BBj SQL server this property indicates where the keystore is found. If this parameter is not specified, the default keystore is used.

 4.0

keypass=<keystore_password>

This is optional. If a user has specified their own keystore, they must provide the keystore password. If this parameter is not specified, the keystore password for the default keystore is used.

 4.0

readonly=<true or false>

Set the connection to be read only or read-write. This setting does NOT bypass underlying security settings for the database.

 6.0

data=<path to DATA directory>

Bypasses the location of the data files as specified in the DATA setting for the database. This setting will be used to replace all references in file paths to the (DATA) global setting.

 6.0

dictionary=<path to DICTIONARY files>

Bypasses the location of the dictionary files specified in the database properties so that the SQL engine will look in this new location for its data dictionary files.

 6.0

txn_isolation=<level>

Specifies the transaction isolation level if the database supports transactions. The database supports transactions only on ESQL tables. Supported values are: NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, and SERIALIZABLE.

 6.0

socket_timeout=<milliseconds>

Amount of time in milliseconds that the connection should wait for a response from the server during an operation before giving up. If the timeout is ever reached, an error appears, the connection immediately closes, and the connection is no longer usable. To attempt the operation again, open a new connection.

 6.0

autocommit=<true or false>

If true, all statements will be automatically committed at the time of their execution. If set to false, UPDATE and INSERT statements will not be permanently saved to disk until a commit is performed (only on files that support transactions such as ESQL files).

 6.0

padvarlength=<true or false>

If set to true, VARCHAR values will get padded with spaces to the complete length defined for the column before being written to the table.

 6.0

connect_timeout=<milliseconds>

Amount of time in milliseconds that the client should wait for the initial connection to the server to succeed. If the timeout is reached then the connection attempt will fail with an error. If a value is not specified, then the default timeout is 5000 milliseconds (5 seconds). Any timeout specified will be rounded up to at least 1000 milliseconds (1 second). A timeout of 0 means that the connection attempt will wait forever.

 18.0

For example, to connect to the sample Chile Company database that is installed with the BASIS DBMS located on a machine called myserver, use the following connect string:

jdbc:basis:myserver?database=ChileCompany&user=admin&pwd=admin123

Note that if the port is omitted, the default port of 2001 is used.

User Defined Database Properties

Applications can also include user defined database properties.  This makes it possible for each application instance to specify alternate values to change the behavior of the database connection.  The most common use for this is to allow an application to use an alternate location for one or more data files.  To specify a value for a user defined property, use NAME=VALUE with each property separated by an ampersand (&):

jdbc:basis:theserver?database=MyDatabase&USERPROP1=value&ANOTHERPROP=value

  Changing File Location Using User Defined Properties

To allow applications to alter the file path to certain data files, change the file path in the table's definition using the Enterprise Manager.  First, open the table editor for the table (see Enterprise Manager: Databases - Tables).  Then set the Data File Path for the table to include a placeholder for each property to be replaced. For example, to allow an application to alter the directory name for a table's data file, change the path to something like the following:

(DATA)(USER_DIR)/mytablefile.dat

Now, JDBC connections would specify a value for USER_DIR and the database will replace the (USER_DIR) placeholder with the value specified in the JDBC connection URL.

Example

...
// Load the Driver implementation into memory
Class.forName("com.basis.jdbc.BasisDriver");

// Establish a connection to the ChileCompany database
Connection con = DriverManager.getConnection(
    "jdbc:basis:myserver?database=ChileCompany",
    "admin",
    "admin123");
...

Just add PREFETCH=size to your connect string.  For example:

jdbc:basis:myserver?database=MyDB&prefetch=100


For information about using a JDBC driver in a third party JDBC application, refer to the third party documentation.

For additional information on the JDBC API and how it works, visit the Sun Java website at http://java.sun.com.