JDBC Connector

The JDBC connector provides an industry standard Structured Query Language(SQL) interface to Host Integrator servers. Although the server is not a relational database system, the Design Tool's Table and Procedure feature provides access to the host application in a way that simulates traditional relational database tables.

The JDBC connector works only if you're accessing host data using procedures; accessing host data at the model or terminal level is not an option.

This document assumes that you are familiar with using JDBC to access a standard relational database and also that you understand the basics of creating procedures and tables using the Design Tool.

Connecting to a Host Integrator Server using the ApptrieveDriver Class

Use of the Host Integrator JDBC connector is always within the context of the standard java.sql.* classes. Connecting to a Host Integrator session using JDBC is a four step process:

1. Import the java.sql* class. Include this line at the top of your Java file:

   import java.sql.*;

2. Insert the Bouncy Castle security providers. Add the following method and invoke it in the beginning of your program:

    static void registerProviders()
    {
        // initialize a few properties used by BCFIPS and BCJSSE
        Security.setProperty("ssl.KeyManagerFactory.algorithm", "PKIX");
        System.setProperty("org.bouncycastle.ec.disable_mqv", "true");
        System.setProperty("org.bouncycastle.jsse.ec.disableChar2", "true");

        // the next line shows how to place BCFIPS in approved-only mode.
        // CryptoServicesRegistrar.setApprovedOnlyMode(true);
        Security.insertProviderAt(new BouncyCastleFipsProvider(), 1);
        Security.insertProviderAt(new BouncyCastleJsseProvider(), 2);
    }

1. Register the Host Integrator driver with the JDBC Driver Manager. The Host Integrator driver is implemented in com.wrq.apptrieve.jdbc.ApptrieveDriver.class;

   Include the following Java statement before using the JDBC connector:

   Class.forName("com.wrq.apptrieve.jdbc.ApptrieveDriver");

2. Connect to the Host Integrator JDBC driver using the standard DriverManager methods.

The java.sql.DriverManager class contains a getConnection method which establishes a connection to a database using the desired driver. There are three forms of this method:

• one that takes only a URL string which specifies the connection to make,
• one that takes a URL and a java.util.Properties object, and
• one that takes a URL and user name and password String parameters.

The key parameter to each of these three variants is the URL string. The syntax for Host Integrator is:

jdbc:apptrieve:table[;managementserver=managementserver_name][;domain=domain_name][;serverid=vhi_server]
            [;database=model_name][;session=sessionpool_name]
            [;user=user_name][;password=xxx][;variables.varname=value]
            [;debug=out|err]

The first part of the URL, jdbc:apptrieve:table indicates that you want to make a connection using the Host Integrator JDBC connector (formerly called Apptrieve).

A connection can be made either directly with the Host Integrator Server or, to use the dynamic load balancing and failover features of the product, indirectly using a domain. To specify a serverID, use the server keyword and include either the hostname of the server or the IP address for the desired server. To specify a domain, include the management server keyword and the management server hostname or IP address and the domain keyword, along with the name of the Host Integrator domain that you wish to use. Never include both the server keyword and the management server/domain keywords because they are mutually exclusive.

A Host Integrator session can either load a model when the connection is established, or it can use an existing session from a session pool. To specify a model-based session, use the database keyword and include the name of the model that you wish to use.

Models and session pools are configured with the Host Integrator Administrative Console. Never include both a database keyword and a session keyword in a URL — they are mutually exclusive.

If security is enabled for the domain or Host Integrator Server, you must specify the user name and password for the connection using the user and password keywords. Alternatively, you can use the getConnection() method that includes user and password String parameters — these do exactly the same thing as specifying the user and password in the URL itself. These keywords refer to the user name and password for the Host Integrator Server — not the host that the Host Integrator Server connects to for executing the model or the session pool. If these need to be passed into the session, use the 'variables' part of the URL to set the built-in userid and password model variables. The variables keyword can set any model variable that is included in the model and has write permission as established in the model definition.

Connections to a Host Integrator Server are always encrypted, independent of the security setting.

The debug keyword enables the JDBC connector debug messages, which are sent to either standard 'out' or standard 'err' output stream. The messages may be useful in debugging your application.

Example URL strings

The following are example URLs to illustrate the various alternatives of establishing a connection to a Host Integrator session:

• Simple connection to a model, no security or model variables:

  String url = jdbc:apptrieve:table;serverid=vhi_server;database=CCSDemo

• Connection to a domain, security enabled:

  String url = jdbc:apptrieve:table;aads=vhi_aads;domain=myDomain;database=CCSDemo; user=vhi_user;password=login1

• Connection to a session pool, specifying host user name and password:

  String url = jdbc:apptrieve:table;serverid=vhi_server;session=CCSDemoSessionPool; variables.userid=bjones;variables.password=bjones

Examples of using DriverManager.getConnection()

Once you have a suitable URL string, include it in one of the three getConnection methods to establish a connection with the Host Integrator Server. For example, to specify everything using a URL:

Connection myConnection = DriverManager.getConnection(url);

To specify a Host Integrator user name and password in the method call rather than in the URL, use the three parameter form:

Connection myConnection = DriverManager.getConnection(url, "vhi_user", "login1");

Finally, any of the keywords that can be used in a URL string can alternatively be put into a java.util.Properties object and sent to the JDBC connector using the two parameter form of getConnection():

    java.util.Properties myProps = new java.util.Properties();
    myProps.put("serverid", "vhi_server");
    myProps.put("database", "CCSDemo");
    Connection myConnection = DriverManager.getConnection("jdbc:apptrieve:table", myProps);

Using Standard SQL Statements

The primary way of interacting with the Host Integrator Server using the JDBC connector is SQL statements. Host Integrator supports a subset of the SQL language that allows retrieving information from the server, adding new information, deleting information, and updating information. See Creating SQL-Based Queries for a full description of the allowed SQL subset and how it relates to tables constructed in the Design Tool.

To execute an SQL statement using the JDBC connector, use one of the execute methods in the java.sql.Statement class. There are three forms of execute: one for any SQL statement, one for queries, and one for updates, selects, inserts, and deletes. The most common form, executeQuery, is for retrieving information using a SELECT SQL statement, (queries). An example of using executeQuery() is:

Statement stmt = myConnection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Accounts where AcctNum=20000");

To update, add, or delete information in a table, use the executeUpdate() JDBC method. For example, the following shows how to insert a record:

Statement stmt = myConnection.createStatement();
String acctCols = "AcctNum, LastName, FirstName, MiddleInitial, Title, Phone, Address1, Address2,";
acctCols += "NumCardsIssued, DayIssued, MonthIssued, YearIssued, Reason, CardCode, ApprovedBy";
String acctVals = "20005, 'Smith', 'Steven', 'W', 'exec','2065551234','1342 15th Street E', 'Seattle, WA',";
acctVals += "2,'06','01','99','M','C','GWB'";
int recordsUpdated = stmt.executeUpdate("INSERT INTO Accounts ("+acctCols+") VALUES ("+acctVals+")");

Handling SQL Exceptions

Many of the methods in the java.sql classes throw SQLException upon failure. Host Integrator takes advantage of SQLException to chain multiple exceptions together. Use the method getNextException to get the next exception in the chain, stopping when this method returns null.

For example, to print the full exception chain starting with the top level SQLException, use the following Java method:

void printException(Exception e) {
    Class cl= e.getClass();
    if(cl.getName().compareTo("java.sql.SQLException") == 0) 
        {
        SQLException SQLEx = (SQLException) e;
        System.out.println("Exception chain:");
        while(SQLEx != null) {
            System.out.println("Code:"+SQLEx.getErrorCode()+" -- "+SQLEx.getMessage());
            SQLEx = SQLEx.getNextException();
        }
    }
    else {
        System.out.println("Exception: "+e.getMessage());
    }
}

You can retrieve the error code for SQLException using the getErrorCode() method.

Using Prepared Statements

The primary advantage of using prepared statements with the Host Integrator JDBC connector is to allow parameterized SQL statements. Host Integrator does not support compiling SQL statements, which is the traditional reason for using prepared statements; therefore there is no gain in efficiency when executing a prepared statement with Host Integrator JDBC.

However, it is often convenient to create a prepared statement that includes parameters to provide variable aspects of an SQL statement. For example, in a Web application, to insert a new record into the host application, parameters can be associated with input items in an HTML form. When the user enters new data into the form, each input item is transferred into the corresponding prepared statement parameter and then the prepared statement is executed. This results in the insertion of the desired record into the host application via the Host Integrator table layer.

The following Java example illustrates how a prepared statement might appear for an insert operation (written for the CICSAccts model).

String acctCols = "AcctNum, LastName, FirstName, MiddleInitial, Title, Phone, Address1, Address2,";
    acctCols += "NumCardsIssued, DayIssued, MonthIssued, YearIssued, Reason, CardCode, ApprovedBy";

        String sql = "INSERT INTO Accounts ("+acctCols+") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    PreparedStatement pstmt = myConnection.prepareStatement(sql);

    pstmt.setInt(1, 20005);
    pstmt.setString(2, "WILSON");
    pstmt.setString(3, "MARTIN");
    pstmt.setString(4, "B");
    pstmt.setString(5, "MR");
    pstmt.setString(6, "9876543210");
    pstmt.setString(7, "1234 56TH ST.");
    pstmt.setString(8, "HOUSTON, TX 98765");
    pstmt.setInt(9, 1);
    pstmt.setString(10, "01");
    pstmt.setString(11, "01");
    pstmt.setString(12, "2001");
    pstmt.setString(13, "N");
    pstmt.setString(14, "1");
    pstmt.setString(15, "1");        
    pstmt.executeUpdate();

Executing Table Procedures

The Host Integrator JDBC connector directly accesses the table layer of a given model. When processing an SQL statement, the Host Integrator Server analyzes the statement and decides the most appropriate table procedure to perform. It is possible to perform a procedure directly using the CallableStatement JDBC class. In the Host Integrator driver, the only purpose for the CallableStatement class is to provide access to the predefined procedure that exists in the connected model.

The format for preparing a callable statement is always the same for Host Integrator:

CallableStatement cstmt = myConnection.prepareCall("{call <procedureName>(?,?,?,?,?,?)}");

Where <procedureName> is the name of the procedure in the Host Integrator model. The parameters always have a fixed meaning, as follows:

For String parameters, use setString(); for Map, List and Boolean parameters, use setObject; and for the integer parameter (maximum rows), use setInt().

Example

To call the CompoundNameSearch procedure of the Accounts table in the CICSAccts model, the Java implementation would look like this:

CallableStatement cstmt = myConnection.prepareCall("{call CompoundNameSearch(?,?,?,?,?,?)}");

cstmt.setString(1, "Accounts");     // table name

HashMap filter = new HashMap();
filter.put("LastName", "W");
cstmt.setObject(3, filter);     // filter

cstmt.setObject(4, new Boolean(true));  // filter is case sensitive flag

List outputCols = new Vector();
outputCols.add("AcctNum");
outputCols.add("LastName");
outputCols.add("FirstName");
outputCols.add("MiddleInitial");
cstmt.setObject(5, outputCols);          // output columns

cstmt.setInt(6, 5);                      // maximum rows

ResultSet rs = cstmt.executeQuery();

Accessing Table Metadata

Information about the Host Integrator ‘database’ (that is, model that you are using) is available in two forms: ResultSet metadata and Database metadata.

ResultSet Metadata

After performing a SQL SELECT statement, you can retrieve metadata from the returned ResultSet object. By calling the getMetaData method of ResultSet to obtain a ResultSetMetaData object for that ResultSet, you can then query several items of metadata. Some of the most important items for Host Integrator are:

Database Metadata

For information about the database being used in the current connection, use the DatabaseMetaData object. For Host Integrator, the term 'database' refers to the model being used for the session. You can obtain the DatabaseMetaData object for a current connection by executing the getMetaData method on the current JDBC connection object. for example:

DatabaseMetaData DBMetaData = myConnection.getMetaData();

With the DatabaseMetaData object, you can find out many things about the Host Integrator model being used. The most important of these are:

Using ResultSets

Data returned by many of the java.sql classes are contained within a ResultSet object. In Host Integrator, all ResultSet objects contain static data. That is, the data that is fetched from the Host Integrator Server is stored in a ResultSet object, but once fetched there is no connection maintained to the server from within the ResultSet. This means that the only methods that are implemented in the ResultSet are concerned with accessing the data within the ResultSet.Conversely, methods that update or modify the ResultSet object are not implemented. From a JDBC terminology perspective, these facts mean that Host Integrator ResultSets are CONCUR_READ_ONLY rather than CONCUR_UPDATEABLE.

A ResultSet object in Host Integrator is always of type TYPE_SCROLL_INSENSITIVE. This means that the result set is scrollable: for instance, its cursor can move forward or backward and can be moved to a particular row or to a row whose position is relative to the current cursor position. As described above, however, the result set does not reflect changes made to the underlying host application. That is, the data in a ResultSet object are static - fixed at the time the ResultSet object was created.

The following example is a Java method that sequences through a Host Integrator result set returned from a SQL SELECT statement and prints each row in the ResultSet to the standard output stream.

void processRS(ResultSet rs) throws Exception 
{
    // First, get the list of columns from ResultSetMetaData
    ResultSetMetaData rsMd = rs.getMetaData();
    ArrayList outputColumns = new ArrayList();
    int cols = rsMd.getColumnCount();


    for(int i = 1; i <= cols; i++) {
        String colName = rsMd.getColumnName(i);
        outputColumns.add(colName);
    }

    // Now sequence through all of the rows in the ResultSet
    int rowIndex=1;
    while(rs.next()) {
        String rowData = new String("Row "+rowIndex+": ");
        Iterator it = outputColumns.iterator();
        while(it.hasNext()) {
            String col = (String)it.next();
            rowData += col+"="+rs.getString(col)+" ";
        }

        // Print the row string
        System.out.println(rowData);
        rowIndex++;
    } 
}