The AdoEnums class
by Ken Rimple
Microsoft's ActiveX Data Objects (ADO) library was created with the goal of unifying database access from all COM and ActiveX-capable clients and servers. Now a 2.0 release, it's being shipped with Visual Studio 6.0, and can be accessed from Visual C++, Visual Basic, Visual Interdev, and Visual J++, to name a few environments.
The WFC version of ADO is contained in classes in the package com.ms.wfc.data. There are Java wrapper classes that support all major ADO functionality, including Recordsets, batch updating, client and server cursoring, stored procedure execution, and Remote Data Services.
In this article, we discuss the ADO Java classes, accessing them from WFC, data binding, and the DataGrid control. We'll also show you how you can create the data form shown in Figure A using the ADO classes.
Figure A: Our sample application produces the forms you see here.
The AdoEnums class
Before delving into the ADO library, we should look at one of the utility classes, AdoEnums. This class was designed to hold all of the enumerated values for various attributes of Recordsets, Commands, and other ADO objects. It's simply a collection of Static references to other class constants. Some critical AdoEnums enumerations are listed in Table A.
| Enumeration | Description |
| CommandType | Sets the type of command (TEXT, STOREPROCEDURE, etc). |
| CursorType | Sets the type of cursor (see below). |
| CursorLocation | Sets the location of the cursor (CLIENT or SERVER). |
| LockType | Sets the locking mode (OPTIMISTIC, BATCHOPTIMISTIC, PESSIMISTIC). |
To successfully use one of the AdoEnums collections, simply type AdoEnums and a period. The different collections will be displayed, and you can select the one you want using the code completion feature of Visual Studio. Here are some examples of using the AdoEnums class:
AdoEnums.LockType.PESSIMISTIC
AdoEnums.CursorLocation.CLIENT
AdoEnums.CommandType.TEXT
The ADO class library consists of several major components. The main classes are Connection, Command, Recordset, Field, Property, and Parameter.
Use the Connection class to establish a database connection. In order to connect to a database, you must use an OLEDB or ODBC connection string. For example:
Connection c = new Connection("DSN=pubs");
Once a connection has been established, you create statement objects that can call SQL statements or execute stored procedures. There are several ways to execute a SQL command. One of the most common ways follows this sequence:
Recordsets are scrollable; meaning that they have a current row or position which can be changed by calling the move functions moveFirst, moveNext, movePrevious, moveLast, and move.
Each Recordset contains a collection of Field objects that are retrieved using the getFields method, which returns a Fields object (that acts as an aggregator class, containing methods to retrieve all fields) or by using the getField function, which will return a Field object, as in the following example:
// Output the value of "last_name"
// for the current row.
Field fLastName =
rs.getField("last_name");
System.out.println(fLastName.getString());
Field object values can be changed (if the Recordset supports it) by using a set function (such as setInt, setString, or setDouble), as in this example:
// Change the value of "last_name"
// to "Jones"
rs.getField("last_name").setString
("Jones");
Recordsets are executed via cursors. In ADO, you can set the type, location, and locking mode of the Recordset's cursor. There are four different types of cursors: DYNAMIC, STATIC, FORWARD-ONLY, and KEYSET. Table B lists their descriptions.
Table B: Cursor type and description
| Cursor type | Add heading | Description |
| DYNAMIC | Read/write | Allows viewing of changes by others and all movement types. |
| KEYSET | Read/write | Allows all movement types but doesn't reflect new or deleted rows from others. |
| STATIC | Read only | A "snapshot" of the data for reporting purposes. |
| FORWARD-ONLY | Read/write | Allows only forward movement (can improve performance). |
In addition to cursor types, there are also cursor location options. A Recordset cursor can live on the client or the server. Server-side Recordsets usually take up more resources, especially when they aren't read-only. Making a Recordset updatable
Recordsets are cursors--objects that can navigate row-by-row through a result set. Depending on the type of cursor used, they may be updateable or read-only, and may run on the client or the server.
Cursor types can be set within the Recordset and Connection objects, but the cursor location can only be specified from the Recordset object. Here are some sample cursor settings:
// Read-only client cursor
rs.setCursorLocation(AdoEnums.
CursorLocation.CLIENT);
rs.setCursorType(AdoEnums.
CursorType.STATIC);
// Read-write server-side cursor
rs.setCursorLocation(AdoEnums.
CursorLocation.SERVER);
rs.setCursorType(AdoEnums.
CursorType.DYNAMIC);
Let's look at one last option for Recordset cursors: Lock Type. This is the level of concurrency you allow your command to hold. In other words, this parameter specifies the scope of locking required for your command. There are several locking modes (AdoEnums.LockType), which can be seen in Table C.
Table C: Please provide caption
| Locking mode | Description |
| OPTIMISTIC | No locks will be held on the object. If a record is updated using the update() function, locks will be released after the update completes. |
| BATCHOPTIMISTIC | Same as OPTIMISTIC, but allows batching of all updates to a Recordset. This provides the most concurrency, but risks collisions when two batch updates from different users change the same row. |
| PESSIMISTIC | Holds locks on a row when editing begins, and when update is called, locks are released. This is the mode with the least concurrency, but the most consistency. |
Keep in mind that you don't want to hold locks for an arbitrarily long period of time in a distributed application. Never use the PESSIMISTIC mode unless you reasonably expect to complete the entire operation within the same method call. Never allow user input during a PESSIMISTIC lock cursor. Here's an example of using a LockType in code:
m_rs.setLockType(AdoEnums.
LockType.BATCHOPTIMISTIC);
We've included some sample Recordset setups here. Since there are so many options in ADO, we figured that the best way to explore the options is to dive in and look at the code. Listing A shows batch-updateable Recordset, and Listing Bshows a read-only Recordset. Assume that m_cmd is an ADO Command object that has been previously initialized.
Listing A: A batch-updateable Recordset
m_cmd = new com.ms.wfc.data.Command();
m_cmd.setActiveConnection(AppData.m_conn);
m_cmd.setCommandText("select * from titles");
m_cmd.setCommandType(AdoEnums.CommandType.TEXT);
m_rs = new Recordset();
m_rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
m_rs.setCursorType(AdoEnums.CursorType.STATIC);
m_rs.setLockType(AdoEnums.LockType.BATCHOPTIMISTIC);
Listing B: A batch-updateable Recordset
m_cmd = new com.ms.wfc.data.Command();
m_cmd.setActiveConnection(AppData.m_conn);
m_cmd.setCommandText("select * from titles");
m_cmd.setCommandType(AdoEnums.CommandType.TEXT);
m_rs = new Recordset();
m_rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
m_rs.setCursorType(AdoEnums.CursorType.STATIC);
m_rs.setLockType(AdoEnums.LockType.OPTIMISTIC);
We can change values of existing rows and columns by getting a Field object from the Recordset. Fields can be accessed using the field name or number (starting with zero). To change the last_name field to Adams, either one of the following will work:
m_rs.getField("last_name").setString
("Adams");
or
m_rs.getField(0).setValue(new Variant
("Adams"));
Note the use of the Variant class. Visual Basic developers can relate to the name of this class--it represents a wrapper class for COM datatypes. There are twelve constructors for the Variant class, each passing in a different primitive Java type. The Variant class is contained in the com.ms.com package, which must be imported to use it. Any of the Field or Parameter values can use Variants or Java primitive types. The Java primitives will be faster, but the Variant class is more flexible. Creating a row
Creating a row To create a new row, use the addNew function in Recordset. The current row pointer will be set to the new row. Simply set the column values using the methods above. Or, create an array of fields, or two arrays (field names and field values), as shown here:
m_rs.addNew();
m_rs.getField("au_id").
setString("195-32-3242");
m_rs.getField("au_lname").
setString("Williams");
m_rs.getField("au_fname").
setString("Teddy");
m_rs.update();
String a[] = {"9921", "Rimple
=>Publishing", "Newtown Square", "PA",
=>"USA" };
r2.addNew(a);
r2.update();
We can delete the current record in a Recordset by using the delete function. The most common synatax deletes the current row. For example:
m_rs.moveFirst();
m_rs.delete();
ADO allows two modes of data updates--immediate and batch. In immediate mode, locks are accumulated as each row is updated, either by moving to another row or by calling Recordset.update(). If the process doing the updates is entirely self-contained and happens within a well-defined period of time, this is acceptable. But if user interaction is required, such as in a grid, the batch update mode works better.
Another way of submitting SQL statements is to use a Command object's executeUpdate command. This command will return an integer specifying the number of rows updated (or zero). Some examples:
m_conn.executeUpdate("update titles set
price = $30.00");
int rows_affected = m_conn.executeUpdate
("delete from authors where " +
"price > $30", AdoEnums.
CommandType.TEXT);
Calling a Stored Procedure is much the same as using a read-only Recordset. In fact, if you leave out the commandtype setting, ADO usually finds out the command for you and handles it automatically.
For MS SQL Server, use the exec keyword in front of the procedure. You can pass parameters using the Parameter object. For example:
c.setCommandText("byroyalty");
// Set up the @percentage parameter:
// integer, value 100
Parameter pct = new Parameter();
pct.setName("@percentage");
pct.setValue(new Variant(100));
pct.setType(AdoEnums.DataType.INTEGER);
pct.setDirection(AdoEnums.
ParameterDirection.INPUT);
c.getParameters().append(pct);
Database Recordsets can be "bound" to WFC components, such as Edit controls and Grids. One approach to doing this is to use the setDataSource method (and optionally the setFieldName method to further restrict the binding to a specific field), as in:
dataGrid_employees.setDataSource(m_rs);
editFirstName.setDataSource(m_rs);
editFirstName.setFieldName("first_name");
Listing C gives you part of the code for creating the sample form we showed you in Figure A. The application uses MDI frames, and can be downloaded from our FTP site at ftp.zdjournals.com. In Listing C, we've included the source code for the initial connection, as well as for the complex Databound grid form.
Listing C: Code for creating our sample form.
// The AppData class: a placeholder class to keep
// our constantly connected ADO connection object
// (we log in on the main frame).
import com.ms.wfc.core.*;
import com.ms.wfc.ui.*;
import com.ms.lang.Delegate;
public class AppData {
/**
*The connection all frames use for this
*application
*/
public static com.ms.wfc.data.Connection m_conn;
}
// The logon code in the mainframe class constructor
// method
AppData.m_conn = new
=>com.ms.wfc.data.Connection("DSN=pubs;
=>UID=sa;PWD=");
AppData.m_conn.open();
// This is the ADO_Grid_Form class. It demonstrates
// basic data binding and batch updating.
import com.ms.wfc.app.*;
import com.ms.wfc.core.*;
import com.ms.wfc.ui.*;
import com.ms.wfc.html.*;
import com.ms.wfc.data.*;
/**
* This class can take a variable number of
* parameters on the commandline. Program
* execution begins with the main() method.
* The class constructor is not invoked unless an
* object of type 'ADO_Grid_Form' is created in the
* main() method.
*/
public class ADO_Grid_Form extends Form {
/**
* Command object
*/
private com.ms.wfc.data.Command m_cmd;
/**
* Recordset for bound grid
*/
private com.ms.wfc.data.Recordset m_rs;
public ADO_Grid_Form() {
// Required for Visual J++ Form Designer support.
initForm();
m_cmd = new com.ms.wfc.data.Command();
m_cmd.setActiveConnection(AppData.m_conn);
m_cmd.setCommandText("select * from titles");
m_cmd.setCommandType(AdoEnums.CommandType.TEXT);
m_rs = new Recordset();
m_rs.setCursorLocation(AdoEnums.
=>CursorLocation.CLIENT);
m_rs.setCursorType(AdoEnums.CursorType.DYNAMIC);
m_rs.setLockType(AdoEnums.
=>LockType.BATCHOPTIMISTIC);
}
/**
* ADO_Grid_Form overrides dispose so it can
* clean up the component list.
*/
public void dispose() {
super.dispose();
components.dispose();
if (m_rs.isOpen()) {
m_rs.close();
}
}
private void ADO_Grid_Form_closing(Object source,
if (m_rs.isOpen()) {
int res = MessageBox.show("Records have been
changed. Save first?", "Warning",
MessageBox.YESNO | MessageBox.ICONQUESTION);
if (res == MessageBox.IDYES) {
AppData.m_conn.beginTrans();
m_rs.updateBatch();
AppData.m_conn.commitTrans();
}
}
}
private void btnRetrieve_click(Object source,
Event e) {
if (m_rs.isOpen()) {
m_rs.close();
}
m_rs.open(m_cmd);
m_Grid.setDataSource(m_rs);
}
private void btnSave_click(Object source, Event e){
AppData.m_conn.beginTrans();
m_rs.updateBatch();
AppData.m_conn.commitTrans();
}
private void btnNew_click(Object source, Event e){
m_rs.addNew();
m_rs.moveLast();
m_Grid.focus();
}
private void btnDelete_click(Object source,
Event e) {
m_rs.delete();
int i = AppData.m_conn.executeUpdate("update
titles " + "set price = $30",
AdoEnums.CommandType.TABLE);
}
/**
* NOTE: The following code is required by the
* Visual J++ form designer. It can be modified
* using the form editor. Do not modify it using
* the code editor.
*/
Container components = new Container();
com.ms.wfc.data.ui.DataGrid m_Grid =
=>new com.ms.wfc.data.ui.DataGrid();
Button btnRetrieve = new Button();
Button btnSave = new Button();
Button btnNew = new Button();
Button btnDelete = new Button();
Panel panel1 = new Panel();
private void initForm() {
this.setText("Data Bound Grid Example");
this.setAutoScaleBaseSize(new Point(5, 13));
this.setClientSize(new Point(434, 313));
this.addOnClick(new
EventHandler(this.ADO_Grid_Form_click));
this.addOnClosing(new CancelEventHandler(
this.ADO_Grid_Form_closing));
m_Grid.setBackColor(Color.WINDOW);
m_Grid.setDock(ControlDock.FILL);
m_Grid.setSize(new Point(434, 265));
m_Grid.setTabIndex(0);
m_Grid.setText("dataGrid1");
m_Grid.setCaption("");
m_Grid.setColumns(new
com.ms.wfc.data.ui.Column[] {});
m_Grid.setEnterAction(com.ms.wfc.data.ui.
EnterAction.ROWNAVIGATION);
m_Grid.setHeaderFont(Font.DEFAULT_GUI);
m_Grid.setTabAction(com.ms.wfc.data.ui.
TabAction.COLUMNNAVIGATION);
m_Grid.setWrapCellPointer(true);
m_Grid.setDynamicColumns(true);
m_Grid.setDataMember("");
button1.setAnchor(ControlAnchor.NONE);
button1.setLocation(new Point(24, 272));
button1.setSize(new Point(80, 24));
button1.setTabIndex(4);
button1.setText("Retrieve");
button1.addOnClick(new
EventHandler(this.button1_click));
button2.setAnchor(ControlAnchor.NONE);
button2.setLocation(new Point(112, 272));
button2.setSize(new Point(80, 24));
button2.setTabIndex(3);
button2.setText("Save");
button2.addOnClick(new
EventHandler(this.button2_click));
button3.setAnchor(ControlAnchor.NONE);
button3.setLocation(new Point(208, 272));
button3.setSize(new Point(80, 24));
button3.setTabIndex(2);
button3.setText("New");
button3.addOnClick(new
EventHandler(this.button3_click));
button4.setAnchor(ControlAnchor.NONE);
button4.setLocation(new Point(296, 272));
button4.setSize(new Point(80, 24));
button4.setTabIndex(1);
button4.setText("Delete");
button4.addOnClick(new
EventHandler(this.button4_click));
panel1.setDock(ControlDock.BOTTOM);
panel1.setLocation(new Point(0, 265));
panel1.setSize(new Point(434, 48));
panel1.setTabIndex(5);
panel1.setText("panel1");
panel1.addOnPaint(new
PaintEventHandler(this.panel1_paint));
this.setNewControls(new Control[] {
button4, button3, button2, button1,
m_Grid, apanel1});
}
/**
* The main entry point for the application.
* @param args Array of parameters passed to the
* application via the command line.
*/
public static void main(String args[]) {
Application.run(new ADO_Grid_Form());
}
}
Keep in mind that ADO is a Microsoft-specific native library. Since it only works in an ActiveX-enabled virtual machine, the ADO library (and WFC, inherently) is a Microsoft JVM-only environment. However, the trade-off is well worth it if your plan is to deploy applications on a Win32 operating system, which is currently running on the large majority of desktops today.
Copyright © 1999, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.