PointBase Database Server

Before proceeding with the discussion on the features of JDBC, let's take a minute and talk about a pure-Java database server that we'll use in our examples.

PointBase is a pure-Java implementation of a relational database management system (RDBMS). You must have a JVM installed on your machine to use PointBase server, which you will if you have WebLogic Server installed. PointBase documentation says the server works on all platforms with a Java Virtual Machine. It has been tested on a variety of platforms including Microsoft Windows NT, Macintosh, Solaris, and Linux. It comes equipped with a graphical database management console to enable you to create and work with database schemas. PointBase comes in three different flavors:

• PointBase Micro Edition— Provides single connection to the database from one application running on the same JVM. This edition provides local access only.

• PointBase Embedded Edition— Provides multiple connections to the database from one application running on the same JVM. This edition provides local access only.

• PointBase Server Edition— Provides multiple connections to the database from multiple Java applications. Provides both local and network connection support. This edition is ideal for a server, such as an application server, serving multiple clients locally or over a network.

WebLogic Server 8.1 ships with an evaluation version of PointBase Server Edition. At the time this book is being written, the version shipping with WebLogic 8.1 is PointBase Server Edition 4.4. You can download an evaluation copy of a later version of PointBase (if available) from http://www.pointbase.com, although doing so isn't required for the purpose of this chapter. In this section, we discuss how to work with the PointBase server version 4.4 that ships with WebLogic 8.1.

Looking for PointBase Components in Your WebLogic Installation

The PointBase server components can be found under the subdirectory common/eval/pointbase (${POINTBASE_HOME}) under the WebLogic installation directory (${WL_HOME}) on your machine.

The docs subdirectory contains useful documentation for using the server and the console. The lib sub-directory contains the JAR files needed to use the database Server. The tools subdirectory contains scripts that you can use to start the default PointBase database instance and the console to connect to it.

Three JARs make up the PointBase database server. They are as follows:

• pbserver.jar— Contains the core PointBase database server, which will be used to start the PointBase server.

• pbclient.jar— Contains the class files that will be used by the clients connecting to the PointBase server.

• pbtools.jar— Contains some useful tools, including the classes that make up the PointBase server console.

Starting the PointBase Server

To start the PointBase server, set your CLASSPATH to include the JAR files $JAVA_HOME/lib/tools.jar and the full path to the pbserver.jar. Now execute the class file com.pointbase.net.netServer. Some of the command-line parameters used by this class are

• /port:nnnn— Where nnnn is a valid port number. By default, the PointBase server starts on port 9092. You need to specify this only if you want to change the default behavior.

• /pointbase.ini=<ini_file>— Where <ini_file> is a qualified filename of the PointBase server .ini filename. This file indicates several parameters to the server. One of the key parameters is the directory under which the databases must be created and found. A sample .ini file for PointBase comes with your WebLogic installation, and can be found under ${WL_HOME}/samples/domains/examples and is named pointbase.ini.

• /noconsole— Starts the PointBase server as a background thread (on Unix and Solaris platforms) without the console window.

The class takes other command-line parameters, which can be found in the PointBase documentation.

Starting the PointBase Console

To start the PointBase console, include the JAR files pbclient.jar and pbtools.jar in your CLASSPATH. Execute the class com.pointbase.tools.toolsConsole to bring up the PointBase console. The console uses Sun Microsystems' Swing library for its GUI. After it starts, you must provide four parameters in order to connect to the database:

• Driver— Specifies the driver to use to connect to the database. The value for this field is its default value: com.pointbase.jdbc.jdbcUniversalDriver. You should have no reason to change this.

• URL— The URL of the database to connect to. The URL is of the form jdbc:pointbase:server://<host>[:<port>]/<db> where <host> is the host name on which the server is running (for example, localhost). <port> is the port on which the server is listening. This is an optional value and must be specified only if the server was started on a port other than 9092. <db> is the name of your database (for example, demo in the case of the default instance).

• User— The user ID to use while connecting to the database. This field is not case sensitive. The default PointBase user ID is PBPUBLIC. The default system administrator user ID for PointBase is PBSYSADMIN.

• Password— The password of the user. This field is not case sensitive. The default password for the default user is PBPUBLIC. The password for the default system administrator login is PBSYSADMIN.

After you provide these values and click the OK button, the console will connect with the database server.

Working with the PointBase Console

The PointBase Console has a self-descriptive GUI and you can access several of its features from its menu.

Clicking on the menu item Catalog, Catalog opens the catalog as a tree view and enables you to browse through the different schemas available in the current database. You can create a new schema by selecting the DBA, Create, Schema option. Right-clicking on a schema name in the tree view gives you the option of creating new tables. You may also create a new table by selecting the DBA, Create, Table menu option, which starts a wizard for creating new tables.

You may enter one or more SQL command in the Enter SQL Commands window on the main screen. The SQL commands can then be executed by selecting the SQL, Execute All menu item. The SQL menu also provides for mechanisms by which you can commit or rollback updates to the database. Right-clicking a table in the Catalog tree view also provides you with easy access to some simple predefined SQL statements.

You can perform several other tasks using the PointBase console, but it's beyond the scope of this chapter to discuss how to work with the console to perform those tasks.

The PointBase Database Driver

The PointBase driver is used to access PointBase databases using JDBC. The name of the PointBase driver that ships with the installation is com.pointbase.jdbc.jdbcUniversalDriver. (Sound familiar? The console uses JDBC behind the scenes to access the PointBase database.) The driver uses the parameters passed in while connecting to the server (such as URL, username, password, and so on) to connect to the PointBase database. This driver is a Type 4, or a native protocol all-Java driver, which directly accesses the PointBase database for servicing calls.

Case Study: XYZ Confectionaries

Now that we are familiar with the usage of the PointBase database server, let's create a schema for use in our examples. Let's take a simple case study and work with it as we discuss the features of JDBC.

We are building a schema for a billing system for a confectionary store, XYZ Confectionaries. The system must keep track of items and each item's unit price, as well as information about each sale.

When we model our case study, we'll have the following tables in our schema:

• The ITEM Table— This table stores a list of items that are present in the store, along with the stock quantity and unit price. Refer to Table 10.1 for the table definition.

• The BILL Table— This table maintains information about each generated bill—the bill ID and the total sale amount. Refer to Table 10.2 for the table definition.

• The BILL_ITEM Table— This table lists various items that are part of a bill, and the quantity of that item purchased. Refer to Table 10.3 for the table definition.

Table 10.1. The ITEM Table

Column Name	Type	Size
ITEM_ID	INTEGER
ITEM_DESCRIPTION	VARCHAR2	100
STOCK_QTY	DOUBLE
UNIT_PRICE	DOUBLE

Table 10.2. The BILL Table

Column Name	Type	Size
BILL_ID	INTEGER
SALE_AMOUNT	DOUBLE

Table 10.3. The BILL_ITEM Table

Column Name	Type	Size
BILL_ID	INTEGER
ITEM_ID	INTEGER
QUANTITY	DOUBLE

Create a schema called XYZCONF in the PointBase database. Then create the tables as described in this section under this schema. You can create the schema either by using the PointBase console Table Creation Wizard, or by executing the schema.sql that can be found along with the examples for this chapter. An easy way to execute the schema.sql file is to open the PointBase console and select File, Open. This enables you to pick the SQL file from your hard disk. Select the schema.sql file and open it. This will list the contents of this SQL file in the Enter SQL Commands window. You can then execute the commands by clicking the Execute All button.