Manual ADO

This first example ADO app is built manually without the aid of the Visual J Plus Plus tools. The purpose of this app is to demonstrate the principles of this manual approach, rather than to perform a serious function.

Problem

This example program, ManualADO, accesses the Customers table of the Access database MyCompany.mdb described previously. Although this database was created with Microsoft Access, the database format doesn't matter as long as there is an ODBC32 driver for the type of database you want to use.

NOTE
ODBC32 drivers for Access, SQL Server, Microsoft FoxPro, Microsoft Visual FoxPro, Oracle, formatted text files, and several other storage formats are built into Windows. Other vendors provide their own ODBC32 drivers for their products.

ManualADO reads the first name, last name, and phone number from a row in the Customers table and combines them into one string that is displayed in an edit box along the top of a form. In addition, ManualADO provides a Next and a Previous button so the user can navigate through the database records.

Setup Work

Start by creating a new directory named Windows Database apps, to hold all the data apps we'll create in this chapter. In addition, you'll need to create an Access database named MyCompany. Add to the database a table called Customers that contains columns named CustomerID, CustomerFirstName, CustomerLastName, and PhoneNumber, and add some data to the table. Place the database in a Databases subdirectory of the Windows Database apps directory.

NOTE
It would be simpler and less error prone for you to use the database MyCompany.mdb from the companion CD instead of creating it yourself from scratch. This is true even if you want to add new records using Microsoft Access. The database and all the examples in this chapter are in the Windows Database apps subfolder on the companion CD.

Create a conventional Visual J Plus Plus Windows app project in the directory Windows Database apps. Name the project ManualADO.

Forms Designer Work

From Project Explorer, open Form1.java in the Forms Designer. Using the Properties window, change the form's text property from Form1 to Manual ADO. Resize the default form to roughly half its original height and two-thirds its original width (the exact size isn't critical).

Place an Edit control along the top of the form. Size the Edit control to be almost as wide as the form. Rename the Edit control nameEdit and anchor it to the left, right, and top sides of the form. Since this simple app has no writing capability, set the Edit control's readOnly property to true. Finally, erase the initial value in the text property.

Now add two Button controls side by side and immediately below the Edit control. Name the left button previousButton and set its text property to Previous. Anchor previousButton to the left and bottom sides of the form. Name the right button nextButton and set its text property to Next. Anchor nextButton to the right and bottom sides of the form. Finally, double-click both buttons in order to create an event handler for each.

Code

For simplicity's sake, I have broken the code into two public classes, ManualADO.java and Form1.java. The ManualADO class contains all of the ADO-related functions, and the Form1 class concentrates solely on the mundane output functions.

ManualADO class

The following code is the source code for the ManualADO class:

import com.ms.wfc.app.*;
import com.ms.wfc.core.*;
import com.ms.wfc.ui.*;
import com.ms.wfc.data.*;
/**
 * This class gives the user convenient access to a database
 * via the ActiveX Data Objects API.
 */
public class ManualADO
{
 // an ADO connection to the database
 Connection con = new Connection();
 // a recordset returned from a query
 Recordset rs = new Recordset();
 // the name of the fields within the recordset to access
 String[] fields;
 /**
 * Create the ManualADO object that will
 * be used to perform the actual ADO calls.
 */ public ManualADO(String dbType, // database information
 String dbPath,
 String dbName,
 String userID,
 String password,
 String table, // table information
 String[] fields,
 String sortField)
 {
 // save the table information
 this.fields = fields;
 // combine the database fields into one string;
 // this string will be used to make the connection
 String sDB = createDatabaseString(dbType,
 userID,
 password,
 dbPath + dbName);
 // now build a SQL query from the fields
 String SQL = buildSQL(table, sortField, fields);
 // open the database
 dbQuery(sDB, SQL);
 }
 /**
 * Create a complete database string from the parts.
 * @param type of database (DSN)
 * @param user id
 * @param user password
 * @param path to database file
 */
 public static String createDatabaseString(String dsn,
 String userID,
 String psswd,
 String db)
 {
 String s = "PROVIDER=MSDASQL;";
 // add the DSN
 s += "dsn=" + dsn + ";";
 // now the user id and password
 s += "uid=" + userID + ";";
 s += "pwd=" + psswd + ";";
 // now the path to the database itself
 s += "DBQ=" + db;
 // return the result
 return s;
 }
 /**
 * Build a SQL query.
 * @param the table name to query
 * @param the order field (null->don't sort)
 * @param the fields to extract
 */
 public static String buildSQL(String sTable,
 String sOrder, String[] fields)
 {
 // always start with SELECT
 String queryString = "SELECT ";
 // now add in the field names separated by commas
 int index = 0;
 while(true)
 {
 queryString += fields[index];
 if (++index >= fields.length)
 {
 break;
 }
 queryString += ", ";
 }
 // add the table name
 queryString += " FROM " + sTable;
 // if there is a sort order, add that too
 if (sOrder != null)
 {
 queryString += " ORDER BY " + sOrder;
 }
 return queryString;
 }
 /**
 * Perform a query on specified database.
 * @param fully qualified database name (use createDatabaseString)
 * @param the SQL query to execute
 */
 public void dbQuery(String s, String queryString)
 throws AdoException {
 // first create a Connection on specified database
 con.setConnectionString(s);
 con.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
 con.open();
 // now open a Recordset with the SQL query
 rs.setActiveConnection(con);
 rs.setSource(queryString);
 rs.setCursorType(AdoEnums.CursorType.STATIC);
 rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
 rs.setLockType(AdoEnums.LockType.OPTIMISTIC);
 rs.open();
 }
 /**
 * Return the contents of an individual field from a recordset.
 */
 public String getField(String fieldName)
 throws AdoException
 {
 Field f = rs.getField(fieldName);
 return f.getString();
 }
 /**
 * Update the fields within the current recordset.
 * @param commit true->update the database table
 */
 public void setField(String field, String value, boolean commit)
 {
 Field fld = rs.getField(field);
 fld.setString(value);
 if (commit)
 {
 rs.update();
 }
 }
 /**
 * Move to the previous record; return false if at
 * beginning of table.
 */
 public boolean movePrevious()
 {
 // if already at beginning of file (the table), // don't go any further
 if (rs.getBOF())
 {
 return false;
 }
 // move to the previous entry
 rs.movePrevious();
 // BOF now?
 return !rs.getBOF();
 }
 /**
 * Move to the next record; return false if at end of table.
 */
 public boolean moveNext()
 {
 // if already at the end of the table, don't go any further
 if (rs.getEOF())
 {
 return false;
 }
 // OK, move to the next entry
 rs.moveNext();
 // end of table now?
 return !rs.getEOF();
 }
 /**
 * Close the current database connection.
 */
 public void close()
 {
 rs.close();
 con.close();
 }
}


The ManualADO class begins with the constructor. This constructor appears more complicated than it really is, because it accepts each part of the connection string (explained below) as a separate argument. In fact, the first four arguments merely describe the database.

In our case, the dbType is MS Access 97 Database, the dbPath is the path to the database directory, and the dbName is MyCompany.mdb. Normally, the userID and password arguments are null—unless the database is secured.

The remaining arguments to the constructor are table, which in our case has the value of Customers; fields, which is an array of strings that contain the names of the fields in which we are interested; and sortField, which is the name of the field (column) in Customers by which the data is to be sorted. The sort field might or might not be one of the members of fields.

The ManualADO() constructor begins by calling createDatabaseString() to combine the database name information into an ADO connection string. A typical ADO connection string might look like the following:

PROVIDER=MSDASQL; dsn= MS Access 97 Database; uid=; pwd=; DBQ=\databases\MyCompany.mdb

From there, ManualADO() calls buildSQL() to build the SQL SELECT statement. Again, a typical SELECT statement might be something like this:

SELECT CONTACTLASTNAME, CONTACTFIRSTNAME FROM CUSTOMERS ORDER BY PHONENUMBER


Finally, ManualADO() invokes dbQuery() to open a connection to the database and read its contents.

The dbQuery() method starts by opening a Connection object using the connection string built by createDatabaseString(). As the name implies, a Connection object is a connection to the database. Once the connection has been established, the program initializes the Recordset object rs.

First, rs is attached to the open Connection object. The SQL query string built earlier is then passed to rs.setSource(). The final call to rs.open() performs the query of the database and populates the recordset with information from the database. If anything goes wrong with the query, dbQuery() throws an ADOException.

NOTE
The cursor to which the Connection and the Recordset objects refer is the index of the current row in the SQL database.

The remaining methods are relatively simple. The getField() method queries the recordset for the value of the specified field in the current record by getting a Field object and then fetching the String contents.

The setField() method is almost the reverse of getField(). The only difference is that it isn't until the argument commit is true that the updated data in the recordset is written back to the current row of the database. In this way, a program can update all of the fields that have changed within a single row before committing the result to the database. This is much faster than writing to the database every time each field is updated.

The movePrevious() and moveNext() methods move the cursor to the previous and next row of the table, respectively. Both return true if the operation was successful and false if the beginning of the table or end of the table was encountered. Both methods guard against moving past the beginning-of-file and the end-of-file, because these would throw an exception.

Finally, close() closes the recordset and the connection to the database. This operation is similar to writing the database to disk and exiting Access.

Form1 class

With the bulk of the work done by the ManualADO class, the Form1 class is relatively simple:

import com.ms.wfc.app.*;
import com.ms.wfc.core.*;
import com.ms.wfc.ui.*;
public class Form1 extends Form
{
 // the ManualADO class makes the actual
 // ADO connections
 ManualADO ado;
 // define the fields to extract from the database table
 String[] fields = new String[]
 {"ContactFirstName",
 "ContactLastName",
 "PhoneNumber"};
 public Form1()
 {
 // Required for Visual J Plus Plus Form Designer support
 initForm(); // create an ADO connection
 String db = "C:\\ProgramVJ\\" +
 "Windows Database apps\\Databases\\";
 ado = new ManualADO(
 "MS Access 97 Database", // type of database db, // path to database file
 "MyCompany.mdb", // name of database file
 "", // user id (normally "")
 "", // password (normally "")
 "Customers", // name of table
 fields, // array of fields to extract
 null); // sort field (null -> don't sort)
 // now update the edit field with the first entry
 updateField(nameEdit);
 }
 /**
 * Update the outputEdit field with the current row.
 */
 void updateField(Edit outputEdit)
 {
 // fetch the field contents for the current row
 String[] outFields = getStrings(fields);
 // convert the phone number into xxx-xxx-xxxx format
 outFields[2] = phoneToString(outFields[2]);
 // generate output string
 String s = outFields[1] + ", " +
 outFields[0] + " (" +
 outFields[2] + ")";
 // now output it
 outputEdit.setText(s);
 }
 /**
 * Return the database fields from the current recordset * as strings.
 * @return array of strings
 */
 String[] getStrings(String[] fields)
 {
 // allocate enough strings to hold all the fields
 String[] s = new String[fields.length];
 // now loop through the fields, fetching their values
 for (int i = 0; i < fields.length; i++)
 {
 // get the string version of each column
 // of the current row
 s[i] = ado.getField(fields[i]);
 }
 return s;
 }
 /**
 * Update the current database entry.
 */
 void setStrings(String[] fields, String[] values)
 {
 // the last field in the list is at this offset
 int last = fields.length - 1;
 // update all of the fields except the last w/o commit
 for (int i = 0; i < last; i++)
 {
 ado.setField(fields[i], values[i], false);
 }
 // now update the last field and commit it
 ado.setField(fields[last], values[last], true);
 }
 /** * Convert a phone number into a string in the format xxx-xxx-xxxx.
 */
 static String phoneToString(String phoneNumber)
 {
 String sAC = phoneNumber.substring(0, 3);
 String sEx = phoneNumber.substring(3, 6);
 String sPh = phoneNumber.substring(6);
 return sAC + "-" + sEx + "-" + sPh;
 }
 /**
 * Form1 overrides dispose so it can clean up the
 * component list.
 */
 public void dispose()
 {
 super.dispose();
 components.dispose();
 ado.close();
 ado = null;
 }
 private void previousButton_click(Object source, Event e)
 {
 if (!ado.movePrevious())
 {
 nameEdit.setText("Beginning of database");
 }
 else
 {
 updateField(nameEdit); }
 }
 private void nextButton_click(Object source, Event e)
 {
 if (!ado.moveNext())
 {
 nameEdit.setText("End of database");
 }
 else
 {
 updateField(nameEdit); }
 }
 /**
 * NOTE: The following code is required by the Visual J Plus Plus form
 * designer. It can be modified using the form editor. Do not
 * modify it using the code editor.
 */
 Container components = new Container();
 Edit nameEdit = new Edit();
 Button previousButton = new Button();
 Button nextButton = new Button();
 private void initForm()
 {
 // …built by Forms Designer…
 }
 /**
 * The main entry point for the app. */
 public static void main(String args[])
 {
 app.run(new Form1());
 }
}


The data member fields contains an array of strings that represent the fields within the Customers table that Form1 is to read and display. After the Form1() constructor has initialized the form, Form1() creates a ManualADO object to access the data within the Customers table of the Access database MyCompany.mdb. (Note that the only reason that the path db is broken in two is to allow each part to fit on a single line on the page. Also, if the path to MyCompany.mdb on your machine isn't the path shown here, you'll need to change this path before this program will work.)

The final call, to updateField(), causes the current record of the table (the first record) to be displayed in the Edit object nameEdit.

The updateField() method works by first fetching the desired fields from the current record, using the local method getStrings(). The phone number field is converted from its stored format into that of a U.S. telephone number (xxx-xxx-xxxx) using the local method phoneToString(). Finally, updateField() concatenates the fields into a single string and displays the string in the Edit object specified in the argument to updateField().

The getStrings() method accepts an array of field names and returns the field values in an array of String objects. The getStrings() method begins by allocating the output String array to be the same size as the input fields array. The method then enters a for loop, in which it calls ado.getField() on the current record for each field passed in the fields array, and saves the result in the output array. The setStrings() method is the analogous output method, which isn't used in this example but is included as a demonstration.

The dispose() method has been updated from the system-generated code to also close the ado object. Closing the ado object closes the database.

The previousButton_click() and nextButton_click() methods move the current record pointer to the previous record or next record, respectively. If the current record pointer is already before the first record in the recordset or after the last record in the recordset, then the associated method displays an appropriate message. If the current record pointer is at a record, then the local updateField() method displays the new record values in the nameEdit field.

Result

The result of your hard work should look something like Figure 12-1. The output form is simple, but it does allow you to navigate back and forth within the Customers table of the MyCompany.mdb database.

Screenshot

Screenshot-1. The appearance of the ManualADO demonstration app when used on the simple Customers table of the MyCompany.mdb Access database.

Adding more capability—for example, adding new Insert, Delete, and Update buttons—is just a matter of your adding methods to the ManualADO class. Each new method would invoke the proper Recordset method for the corresponding button and tie it back to the event handler of the button. The code for an Update button is already present in ManualADO.

If you decide to add an Insert button, you'll need to add data to all of the fields within a row or else the new entry won't be complete. In addition, it's much easier to parse user input if you provide a separate edit box for each entry, but it's not as attractive.

It's surprisingly simple to access different data from ManualADO. For example, you can add records to the Customers table by opening the MyCompany database from Access and selecting the Customers table.

To access a new table within MyCompany.mdb, create a table with Access, define the fields you want, and add data to the table. Within the ManualADO code, update the fields array and change the table name, which is the sixth argument, in the call to the ManualADO() constructor within Form1.java.

To access a different database, create a new database with Access and then follow the same steps as for accessing a new table. In addition, update the third argument to the ManualADO() constructor call to the new database name.

To access a different type of database, such as a Microsoft Visual FoxPro database, you'll need to change the data source name (DSN) specification in addition to changing the database, table, and field names. It's easy to figure out what this new DSN specification should be by looking at the DSN for an Access database: MS Access Database. If you have any questions about changing the DSN, create a simple app using the Visual J Plus Plus app Wizard as described in the next section, and look to see what DSN the wizard came up with. Comments