The following topics provide instructions and details for programming Java in PSQL:

This topic describes the classes for the PSQL Java interface.

Applying the “factory” pattern, almost all classes (with the exception of Driver, Timestamp, FieldDef and PsqlException) can only be produced through methods of another class (or other classes). In the description of classes, under the label Constructors no constructors are listed when no public constructors are available.

The PSQL Java interface has a special class, PsqlException to handle error conditions occurring when methods of the API are invoked. PsqlException extends the java.lang.Exception. Each method of the PSQL Java classes throws PsqlException and for brevity, the following clause is left out from the method definitions:

Figure 2 shows all classes and their relationship.

The classes of the PSQL Java Interface can be divided into three groups:

The following classes belong to General Classes group:

These classes can be used both for tightly coupled and loosely coupled databases.

The following classes belong to the Rowset Family Classes group:

These classes make use of the metadata found in the dictionary of a PSQL database and, therefore, can be applied only for tightly coupled databases.

This is the low-level portion of the PSQL Java Interface. If you use this portion of the interface, you must handle position blocks, data buffers, and other elements of programming that are typical of the Btrieve API.

The following classes belong to Cursor Family Classes group:

These classes do not use any meta data, they directly support the “classic” Btrieve operations (for example, through a BTRV method). Therefore, they are applied typically for loosely coupled databases. However, they can also be used to access data files that store tables of a tightly coupled database. (Although, it is more convenient to use the “rowset family” in the latter case.)

The following are the principal classes and methods involved in a basic Java application.

There are other classes and methods that you can view in the PSQL Java Class Library Reference, but these are the classes you will use most often in your application.

The structure of the Java interface classes is flat; there is no inheritance in these classes.

Driver establishes Sessions. You can use Driver to instantiate as many Session objects as needed. You can also use the KillallSessions method to stop the application, as it will clean up socket connections, database connections, open files, and system resources.

Sessions use the connection to the driver and allow you to connect to databases using URIs and start and end transactions. Each session receives a unique Btrieve client ID. Unless your application specifically needs multiple Btrieve client IDs, you can generally use one Session for the entire application. You can reset the Session and the Btrieve client by calling Session.close(). For more information about client IDs, see Btrieve Clients in PSQL Programmer's Guide and License Models in PSQL User's Guide.

Objects instantiated using this class are the database itself. Methods in this class allow the developer to, in the case of tightly coupled databases, get the table names, tables and so forth. In the case of the loosely coupled database, the developer can access familiar Btrieve-like APIs including extended operations.

An instance of this class represents a table as an object. Using table objects, you can create row sets and get meta data information about tables.

RowSet is an extremely important class. It is used to access the rows of the associated table. In addition, rows can be inserted, updated, and deleted through methods of this class.

An instance of this class represents a set of rows that is derived from the base table. One other way to look at it: a RowSet is a virtual table (a view) generated from a physically stored table.

A row set could include all rows or a subset of rows of the base table. A number of methods in RowSetMetaData (e.g.addFirstTerm, addAndTerm, and so forth) is available to provide the terms of a restriction criterion that determines which rows from the base table are included into the row set.

A row set could include all columns or any number of selected columns of the base row. When a RowSet object is created by Table.createRowSet, it is initialized according to the value of noColumns specified explicitly or implicitly in the call. If noColumns is specified as true, then no column is included into the row set and the application can selectively include the columns into the row set by calling RowSetMetaData.addColumns. If noColumns is specified a false or it is not included as an argument then the created RowSet includes all columns of the table.

The applications normally do not have to open and close the row sets. The associated data files are opened at the construction time of the RowSet objects and the data files are closed when either the application explicitly calls RowSet.close or as a result of closing a higher-level associated object (Table, Database, etc).

The elements of a row set can be accessed through Row objects. There multiple options are available to retrieve the rows.

For important additional information about row sets, see PSQL Java Class Library Reference.

An instance of this class represents a row buffer for a row in a RowSet. The Class Row enables access to rows and to column values within a row. It can be used to retrieve rows, update, or delete rows and to build rows for insertion.

This class has no methods of its own, but the methods inherited from Buffer can be used to get/set values of columns in the row buffer.

The following sequence of steps are typical of an application or applet. The first two steps are the same for both tightly coupled and for loosely coupled databases:

Now choose either Case 1: The application is accessing tables of a tightly coupled database. (Btrieve database uses a DDF) or Case 2: The application is accessing data files of a loosely coupled database (or data files that store tables of a tightly coupled database in the "classic" way).

The sections that follow explain these overview steps in more detail.

To build your first Java Btrieve application:

You should make sure your environment is set up correctly as described in How to Set Up your Environment.

In addition, make sure that “psql.jar” is in your CLASSPATH environment variable. The installation procedure should have performed this step for you. To access the classes in the PSQL Java interface class library, you must import the package in your Java source files.

Currently, the PSQL Java interface class library does not support the creation of databases and database tables. Use the PSQL Control Center utility to perform these tasks. Once the database Data Dictionary Files (DDFs) and data files have been created, you can use the Java API classes to populate and access the tables.

For more information on the PSQL Control Center and creating tables, see PSQL User's Guide.

There are two options for connecting to a PSQL database. Either connect directly to the engine or connect using the I*net Data Server.

where “MyTable” is the name of the database table. The table name is case sensitive.

You can also get a list of the database’s table names.

Once you have a Table object, you can obtain information about the columns and indexes from its TableMetaData object.

Some examples:

To access a Table’s rows, you need a RowSet object which can only be created by a Table object.

You can create multiple RowSet objects from the same table. RowSet objects are used to retrieve a table’s rows, insert new rows, and delete and update existing rows. To iterate through all the table’s rows, create a RowSet object and call getNext() until an PsqlEOFException occurs.

Once you have a RowSet object, you can obtain information about the RowSet from its RowSetMetaData.

Consult the methods of the RowSetMetaData class in PSQL Java Class Library Reference for more details.

As previously shown, you can iterate through the row set’s rows by repeatedly calling the “getNext” method. You can iterate backwards by changing the “direction” property of the row set’s RowSetMetaData object.

After setting this property, the behavior of the “getNext” method becomes “getPrevious.”

Row objects inherit a multitude of accessor/mutator methods from the Buffer class. These methods allow you to set/get column data to/from a Row object’s buffer.

where “ColumnName” is the name defined in the data dictionary for column 0. Column names are case sensitive.

To restrict/filter the rows in a row set, you have to use the RowSetMetaData’s “addFirstTerm,” “addOrTerm,” and “addAndTerm.” For example, if the first column of a table contains integer data and you want all the rows where the first column’s value is greater that 25, do the following.

You can add additional terms with the RowSetMetaData’s “addOrTerm” and “addAndTerm.” These methods allow you to build up more complex filtering conditions similar to a SQL “WHERE” clause.

You can specify a subset of the columns to retrieve, similar to the SQL “select” statement, by using the RowSetMetaData methods, “addColumns” and “dropColumns.” By default, a row set will retrieve all the column data unless the row set was created with the “noColumns” parameter set to “true.”

In this case, no columns will be retrieved. Alternatively, after creating the row set, you can drop all or some of the columns.

Now you can add the set of columns you’re interested in to the row set.

You can add columns to the row set by column names or by column numbers. See the various “addColumns” and “dropColumns” methods of the “RowSetMetaData” class in the PSQL Java Interface Reference documentation for more details.

If you access column data in the rows by column number, be aware that the column numbers will be affected by the new ordering produced by “addColumns” and “dropColumns” methods. For example, if “FirstName” was originally column number 3 in the row set, after dropping all the columns and then adding “LastName” and “FirstName,” the “FirstName” column number will be 1.

You can retrieve a row by using a defined index along with a comparison operator. For instance, if an index, “Last_Name” has been created on the “LastName” column for a hypothetical table, you can do the following to find a row with a “LastName” equal to “Smith.”

If the index allows duplicate key values, the first row which satisfies the comparison operator will be returned. The returned row becomes the current row, allowing you to retrieve the next logical row, based on the index, with the row set’s “getNext” method.

You can get the list of index names from the table’s TableMetaData object.

Be aware that an index is not required to have a name. In that case, you should use the index number instead. Index numbers are zero based.

Like “insertRow,” the newly updated row becomes the current row of the row set unless “no currency changed” is indicated, as shown following.

Transactions allow you to combine a series of operations into a single operation that will either be committed or aborted.

You can make a set of operations a transaction by using the transaction methods of the Session class.

Here is an example of a transaction:

Transactions can be “exclusive” or “concurrent.” See the PSQL Programmer's Guide for more information about transactions.

The setObject() and getObject() methods can be used to store and retrieve Java objects that implement the java.io.Serializable interface. The setBinaryStream() and getBinaryStream() methods can be used to store and retrieve binary data using Java InputStreams. If we have a "simple" object Employees with the following interface:

...and we have a file that we wish to read at C:\Employees\Java Duke\report.txt, we can instantiate an Employee object, set its state with the mutator methods, and store it into the database in the Employee_Data column as well as storing the file in the Manager_Report column:

For more information, see Binary Large Object Support.

This section contains information that may be of use to the PSQL Java programmer:

Support has been added to the Java Class Library to handle Binary Large Objects or BLOBs. BLOBs represent large (up to 4 Gigabytes) binary data, and are represented as LONGVARBINARY data types in the PSQL engine. The Java Class Library supports these data types through these methods (which are found in the PSQL.database.Row class):

where columnNumber = the zero based sequence number of a column in the row buffer. columnName = the name of a column in the row buffer. object = a serializable Java object to store into the database. inStream = a Java InputStream object used to stream bytes into the database.

The methods above can be split into two categories: those that operate on Serialized Java objects and those that operate on Java InputStream objects. Each of these categories will be discussed further in the following sections. In the following sections, examples will be assuming that a table named Employees has been created with the following schema:

table Employees (SS_Num ubigint primary key,
   Employee_Data longvarbinary not null,
   Manager_Report longvarbinary not null)

The PSQL database engines interpret one byte integers as having possible values from 0 to 255. Java interprets its byte type as signed quantities with possible values from -128 to 127.

Because of this difference in interpretation, your code must perform the procedure described here so that data is not misinterpreted.

theOneByteInt = row.getByte("OneByte") & 0x00FF;

listCourses.add(theOneByteInt + " ");

For more information on Java data types and other Java language information, please see http://java.sun.com/docs/books/tutorial/index.html.

You can use the sample database files provided with the SDK to write Java applications. If you damage the database files because of faulty programming, you can restore them from a backup directory installed with the SDK.

There are additional samples included with the Java API. One sample demonstrates joining two tables provided in the SDK package. Another is a simple connection to the DEMODATA database. The final one demonstrates object serialization.

These samples are located in the following directories, (assuming you installed to the default location):

For default locations of PSQL files, see Where are the PSQL files installed? in Getting Started with PSQL. For additional PSQL Java information and to obtain future samples, see the PSQL website.