Windows Foundation Classes for Java and ADO/WFC

November 1998

By Greg Hinkel

Microsoft® ActiveX® Data Objects for Windows Foundation Classes (ADO/WFC) were developed to provide the Microsoft® Visual J++® programmer with a set of classes that allow easy access to both relational and nonrelational data. You will find the classes in the package com.ms.wfc.data.

The purpose of this article is to explain and clarify the usage of ADO/WFC. It provides a number of Java and WFC examples that illustrate not only how to use ADO from Visual J++, but why you would want to use ADO.

Examples illustrating how to use ADO in a Java component or application are simplified by severely limiting error checking. For the purpose of demonstration, the example is clearer if error checking and try catch blocks are limited. However, you should not limit your error checking.

In regards to error handling, you are in a much better position to determine which errors you need to be concerned about. This paper presents only a few canonical cases you will easily understand by looking at the code.

Using ADO in Java

Before ADO/WFC, there was only one way to use ADO if you programmed in Java. Visual J++ 1.1 provided a Type Library Wizard that read the ADO type library and generated a set of Java classes. You could use these classes when you wanted to use ADO in your Visual J++ code.

With Visual J++ 6.0, the natural choice for the Java ADO programmer is ADO/WFC. It provides you with command line completion for ADO/WFC and an object browser.

ADO/WFC Features

ADO/WFC is a set of classes that extend ADO interfaces in a Java-friendly manner. You can deal with data types in an object-based manner rather than as variants. ADO methods accept native ADO data types rather than variants.

In this model, performance is improved by directly accessing the OLE DB Rowset object and presenting the native field data types to you as Java types, as opposed to obtaining a variant and converting to Java type and vice-versa.

The model also exposes the ADO event notification to you through the WFC event notification model.

ADO errors are mapped to exceptions.

To use ADO/WFC, import com.ms.wfc.data and use the classes in this package.

Simple Example

Take a look at the following example. Keep in mind this example is very simple, with no exception handling at all. This example, like most of the examples in this paper, is a console application with a single class, Class1, which has a main function. The examples are as simple as possible in order to show clearly and distinctly what works.

import com.ms.wfc.data.*;

public class Class1
{
   public static void main (String[] args)
   {
      Connection conn = new Connection();
      Recordset rs = new Recordset(); 
      conn.open("Provider=SQLOLEDB;Data Source=(local);" +
              "User ID =sa;Password=;Initial Catalog=Pubs;");
      rs = conn.execute("select * from authors");
      for(int i = 0; i < rs.getFields().getCount(); i++ )
      {
         System.out.print(rs.getFields().getItem(i).getName() + "\t");   
      }
      System.out.print("\n");
      while( !rs.getEOF() )
      {
         for( int i = 0; i < rs.getFields().getCount(); i ++ )
         {
            System.out.print(rs.getFields().getItem(i).getString() +
                                                             "\t");
         }
         System.out.print("\n");
         rs.moveNext();
      }
      rs.close(); 
      conn.close(); 
      rs.release();
   }
}

First Recordset and Connection objects are declared and initialized. The following line opens the Connection object:

conn.open("Provider=SQLOLEDB;Data Source=(local);" +
              "User ID =sa;Password=;Initial Catalog=Pubs;");

This specifies the use of the OLE DB provider for Microsoft® SQL Server™, the local SQL Server, the user ID and password, and the Pubs database.

The Connection.execute() method is called, passing in a string that contains a simple select statement. The execute() method returns a Recordset which iterates through the Fields collection and prints out the column names.

rs = conn.execute("select * from authors");
for(int i = 0; i < rs.getFields().getCount(); i++ )
{
   System.out.print(rs.getFields().getItem(i).getName() + "\t");   
}

Then System.out.print("\n"); is issued to move to the next line so that the contents of the Recordset contents can be printed. Another System.out.print("\n"); separates the rows, and then both the Recordset and the Connection are closed. In the last line, rs.release() ensures that all the Recordset object's resources are released in a timely manner.

while( !rs.getEOF() )
{
   for( int i = 0; i < rs.getFields().getCount(); i ++ )
   {
      System.out.print(rs.getFields().getItem(i).getString() + "\t");
   }
   System.out.print("\n");
   rs.moveNext();
}
rs.close(); 
conn.close(); 
rs.release();

ADO/WFC Programming Examples

The rest of the examples assume that you are already familiar with Visual J++ development. As mentioned previously, each example is a standalone console application and uses a connection to the SQL Server pubs database and tables contained in the database. You can modify the connection string and table names to adapt to your system. The examples do not deal with exception handling in detail but show some examples of how you can use it in your coding.

Connection Example

A connection is all you need to send an Update or Insert statement to the database using the Connection.executeUpdate() method. The following connection example sets several connection properties, executes an Update statement, and then prints out the number of rows affected by the statement.

It also exposes the ADO/WFC executeUpdate method. In Microsoft® Visual Basic® for Applications (VBA), the Execute method has an optional parameter called RecordsAffected. This parameter is exposed in ADO/WFC as the return value for the executeUpdate method. When using the Insert, Update, Delete, or Create methods, you should invoke the executeUpdate method. It returns the numbers of records affected by the particular Insert, Update, or Delete statement.

The following example sets the CursorLocation, ConnectionTimeout, and CommandTimeout properties. If you do not set these properties, the defaults for the particular property are used. In this example, the CursorLocation is set to use a client-side cursor that supports scrolling and caching of data; this reduces network round trips to the server. The default is a server-side, forward-only cursor.

The properties ConnectionTimeout and CommandTimeout ensure that the application has to only wait for a certain period of time for the connection and command requests to be processed.

The executeUpdate method updates author Anne Ringer's last name to "Richards" and returns the count of the records that have been updated.

import com.ms.wfc.data.*;

public class Class1
{
   public static void main (String[] args)
   {
      Connection conn = new Connection();
      String strSQL = new String();
      int iRecordsAffected;
      
      try
      {
         conn.setConnectionString("Data Source=(local);" + 
                        "User ID =sa;Password=;");
         conn.setProvider("SQLOLEDB");
         conn.setCommandTimeout(20);
         conn.setConnectionTimeout(20); 
         conn.open();
         conn.setDefaultDatabase("Pubs");
         strSQL = "update authors set au_lname = 'Richards' " +
                "where au_fname = 'Anne' and au_lname= 'Ringer'";
         iRecordsAffected = conn.executeUpdate(strSQL);
         System.out.println(iRecordsAffected + " records were updated.");
      }
      catch(AdoException e)
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally
      {
         try { conn.close(); } 
         catch (Exception e) { /* Ignore close errors */ }    
      }
   }
}

Connection Properties Example

The following example accesses the Properties collection on the Command object. In ADO, Properties collections on the Recordset, Field, Parameter, and Command objects allow each provider to expose an extensible set of properties for each object.

import com.ms.wfc.data.*;

public class Class1
{
   public static void main (String[] args)
   {
      Connection conn = new Connection();
      AdoProperties props;
      AdoProperty prop;
      int iNumProps;
      
      try
      {
         conn.open("Provider=SQLOLEDB;Data Source=(local);" +
                 "User ID =sa;Password=;Initial Catalog=Pubs;");
         iNumProps = conn.getProperties().getCount();
         props = conn.getProperties();
         for( int i = 0; i < iNumProps; i++ )
         {
            prop = props.getItem(i);
            System.out.println(prop.getName() + " = " + 
                                 prop.getString());
         }
      }
      catch(AdoException e)
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally
      {
         try { conn.close(); } 
         catch (Exception e) { /* Ignore close errors */ }    
      }
   }
}

Connection.openSchema() Example

The Connection.openSchema() method retrieves information about a data source, such as which tables are on the server, what data types the server supports, or what columns are defined on a particular table. The openSchema method returns a Recordset that is different for each schema. The AdoEnums.Schema.TABLES schema returns a Recordset with six columns: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID, and DESCRIPTION. As another example the AdoEnums.Schema.COLUMNS schema returns a Recordset with 28 fields that completely describe each column in a table.

The following two examples show how to use the openSchema method.

Table Demo

The first example retrieves information about all the tables in the Pubs database.

import com.ms.wfc.data.*;

public class Class1
{
   public static void main (String[] args)
   {
      Connection conn = new Connection();
      Recordset rs = new Recordset();
      Field fldTableName;
      Field fldTabelType;
      
      try
      {
         conn.open("Provider=SQLOLEDB;Data Source=(local);" +
            "User ID =sa;Password=;Initial Catalog=Pubs;");
         rs = conn.openSchema(AdoEnums.Schema.TABLES,
            new Object[] {"Pubs",null,null,"TABLE"});   
         fldTableName = rs.getFields().getItem("TABLE_NAME");
         fldTabelType = rs.getFields().getItem("TABLE_TYPE");
         while ( !rs.getEOF())
         {
            System.out.print(fldTableName.getString());
            System.out.println(" is of type " + 
                        fldTabelType.getString());
            rs.moveNext();
         }
         rs.close(); rs.release();
      }
      catch(AdoException e)
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally
      {
         try { conn.close(); } 
         catch (Exception e) { /* Ignore close errors */ }    
      }
   }
}

Column Demo

The second example retrieves information about all the columns in a table. The table to be used is hardcoded.

import com.ms.wfc.data.*;

public class Class1
{
   public static void main (String[] args)
   {
      Connection conn = new Connection();
      Recordset rs = new Recordset();
      
      try
      {
         conn.open("Provider=SQLOLEDB;Data Source=(local);" +
            "User ID =sa;Password=;Initial Catalog=Pubs;");
         rs = conn.openSchema(AdoEnums.Schema.COLUMNS,
            new Object[] {null,null,"authors",null});
         int iCount = rs.getFields().getCount();
         if( !rs.getEOF() )
         {
            for(int i = 0; i < iCount; i++ )
            {
               Field field = rs.getFields().getItem(i);
               System.out.print(field.getName() + " = ");
               System.out.print(field.getString() + "\n");
            }
         }
         rs.close(); rs.release();
      }
      catch(AdoException e)
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally
      {
         try { conn.close(); } 
         catch (Exception e) { /* Ignore close errors */ }    
      }
   }
}

Look at the Table Demo example. It uses the name of a database (catalog) from which to print out the names of the tables contained therein.

rs = conn.openSchema(AdoEnums.Schema.TABLES, new Object[]      "Pubs",null,null,"TABLE"});   

This version of the openSchema() method takes two parameters, an integer and an object array. The integer indicates the particular schema of interest, and the second parameter indicates what information to retrieve about that schema. In the AdoEnums.Schema.TABLES case, an object array with four objects is created.

In the table demo example, new Object[] "Pubs",null,null,"TABLE"} specifies which database to use, and "TABLE" indicates to return tables only. In the Pubs database that ships with SQL Server, this results in the following output:

authors is of type TABLE
discounts is of type TABLE
employee is of type TABLE
jobs is of type TABLE
Orders is of type TABLE
pub_info is of type TABLE
publishers is of type TABLE
roysched is of type TABLE
sales is of type TABLE
stores is of type TABLE
titleauthor is of type TABLE
titles is of type TABLE

Because the openSchema() method returns a Recordset, you can test for the EOF property and walk through each row printing out the "TABLE_NAME" column of the Recordset.

The following code prints this information:

while ( !rs.getEOF())
{
   Field field = rs.getFields().getItem("TABLE_NAME");
   System.out.print(field.getString());
   field = rs.getFields().getItem("TABLE_TYPE");
   System.out.println(" is of type " + field.getString());
   rs.moveNext();
}

If you change the array and omit "TABLE", for example, new Object[] {"Pubs",null,null,null} you get the following output:

sysallocations is of type SYSTEM TABLE
syscolumns is of type SYSTEM TABLE
syscomments is of type SYSTEM TABLE
sysdepends is of type SYSTEM TABLE
sysfilegroups is of type SYSTEM TABLE
sysfiles is of type SYSTEM TABLE
sysfiles1 is of type SYSTEM TABLE
sysforeignkeys is of type SYSTEM TABLE
sysindexes is of type SYSTEM TABLE
sysindexkeys is of type SYSTEM TABLE
sysmembers is of type SYSTEM TABLE
sysobjects is of type SYSTEM TABLE
syspermissions is of type SYSTEM TABLE
sysprotects is of type SYSTEM TABLE
sysreferences is of type SYSTEM TABLE
systypes is of type SYSTEM TABLE
sysusers is of type SYSTEM TABLE
authors is of type TABLE
discounts is of type TABLE
employee is of type TABLE
jobs is of type TABLE
Orders is of type TABLE
pub_info is of type TABLE
publishers is of type TABLE
roysched is of type TABLE
sales is of type TABLE
stores is of type TABLE
titleauthor is of type TABLE
titles is of type TABLE
sysalternates is of type VIEW
sysconstraints is of type VIEW
syssegments is of type VIEW
titleview is of type VIEW
CHECK_CONSTRAINTS is of type VIEW
COLUMN_DOMAIN_USAGE is of type VIEW
COLUMN_PRIVILEGES is of type VIEW
COLUMNS is of type VIEW
CONSTRAINT_COLUMN_USAGE is of type VIEW
CONSTRAINT_TABLE_USAGE is of type VIEW
DOMAIN_CONSTRAINTS is of type VIEW
DOMAINS is of type VIEW
KEY_COLUMN_USAGE is of type VIEW
REFERENTIAL_CONSTRAINTS is of type VIEW
SCHEMATA is of type VIEW
TABLE_CONSTRAINTS is of type VIEW
TABLE_PRIVILEGES is of type VIEW
TABLES is of type VIEW
VIEW_COLUMN_USAGE is of type VIEW
VIEW_TABLE_USAGE is of type VIEW
VIEWS is of type VIEW

Allowable values for the fourth parameter are "ALIAS", "TABLE", "SYNONYM", "SYSTEM TABLE", "VIEW", "GLOBAL TEMPORARY", "LOCAL TEMPORARY" or null. Null indicates not to restrict the Recordset, whereas "VIEW" indicates that you want to see the views.

The column demo example specifies a table name and prints out all 28 fields of the resulting Recordset. Only one row from the Recordset is printed, and it contains all the information you could possibly want about that column of the table.

rs = conn.openSchema(AdoEnums.Schema.COLUMNS,
         new Object[] {null,null,"authors",null});   
   int iCount = rs.getFields().getCount();
   if( !rs.getEOF() )
   {
      for(int i = 0; i < iCount; i++ )
      {
         Field field = rs.getFields().getItem(i);
         System.out.print(field.getName() + " = ");
         System.out.print(field.getString() + "\n");
      }
   }

The output for one column of the authors table of the pubs database is the following:

TABLE_CATALOG = pubs
TABLE_SCHEMA = dbo
TABLE_NAME = authors
COLUMN_NAME = au_id
COLUMN_GUID = null
COLUMN_PROPID = null
ORDINAL_POSITION = 1
COLUMN_HASDEFAULT = False
COLUMN_DEFAULT = null
COLUMN_FLAGS = 4
IS_NULLABLE = False
DATA_TYPE = 129
TYPE_GUID = null
CHARACTER_MAXIMUM_LENGTH = 11
CHARACTER_OCTET_LENGTH = 11
NUMERIC_PRECISION = null
NUMERIC_SCALE = null
DATETIME_PRECISION = null
CHARACTER_SET_CATALOG = master
CHARACTER_SET_SCHEMA = dbo
CHARACTER_SET_NAME = iso_1
COLLATION_CATALOG = master
COLLATION_SCHEMA = dbo
COLLATION_NAME = nocase_iso
DOMAIN_CATALOG = pubs
DOMAIN_SCHEMA = dbo
DOMAIN_NAME = id
DESCRIPTION = null

To learn more about the different Schema rowsets you can refer to the "OLE DB Programmer's Reference" and the ADO online Help. Also, keep in mind that the various schema require differing amounts of information in the openSchema() call. For example, the catalog schema can be retrieved with the following call:

rs = conn.openSchema(AdoEnums.Schema.CATALOGS); 

This version of openSchema takes a single integer parameter and the resulting Recordset will give you a list of all the catalogs on the server to which you are connected. If you know the name of the catalog you are interested in you can make the following call:

rs = conn.openSchema(AdoEnums.Schema.CATALOGS,   new Object[] {"Pubs"});

This call returns information about only the "Pubs" catalog and the Recordset returned has two columns, CATALOG_NAME and DESCRIPTION.

Long Recordset Example

The following long example shows some of the things you can do with a Recordset. Appendix A contains the complete example, but the discussion that follows concentrates only on the code that demonstrates particular functionality. You will best understand the example by examining it in sections.

The application simply calls a series of functions that do interesting things. Here is the body of the main function:

public static void main (String[] args)
{
   Class1 App = new Class1();
   App.openConnection(App.conn);
   App.printAuthors(App.conn);
   App.sortAuthors(App.conn);
   App.filterAuthors(App.conn);
   App.setMaxRecords(App.conn);
   App.printRSProperties(App.conn);
   App.useBookmarks(App.conn);
   App.multipleRecordsets(App.conn);
   App.saveRSToFile(App.conn, "c:\\RecordsetDemo\\AuthorsRS.adtg");
   App.openAndPrintRSFromFile("c:\\RecordsetDemo\\AuthorsRS.adtg");
   App.editRSFromFile("c:\\RecordsetDemo\\AuthorsRS.adtg");
   App.openAndPrintRSFromFile("c:\\RecordsetDemo\\AuthorsRS.adtg");
}

Class1 declares a Connection variable, conn, that is used throughout the example. The variable conn is intialized in the first method, openConnection. Nothing in the openConnection method is unusual.

The second method, printAuthors, prints out the rows resulting from "select * from authors".

rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
rs.setSource("select * from authors"); 
rs.setActiveConnection(conn);
rs.open();
printRecordset(rs);

The method printRecordset is used throughout the example to print out the Recordset. It takes a Recordset as a parameter and then prints out the field names followed by all the rows in the Recordset. Following is the relevant code for printRecordset(Recordset rs).

void printRecordset(Recordset rs)
{
   Fields fields;
   Field fld;
   int iFieldCount;
   
   try
   {
      fields = rs.getFields();
      iFieldCount = fields.getCount();
      //For this to be accurate we must use 
      //AdoEnums.CursorLocation.CLIENT
      System.out.println("There are " + rs.getRecordCount() + 
                        " rows in the recordset.");
      for( int i = 0; i < iFieldCount; i++ )
      {
         fld = fields.getItem(i);
         System.out.print(fld.getName() + "\t");
      }
      System.out.println();
      while( !rs.getEOF() )
      {
         for( int j = 0; j < iFieldCount; j++ )
         {
            fld = fields.getItem(j);
            System.out.print(fld.getString() + "\t");   
         }
         rs.moveNext();
         System.out.println();
      }
      System.out.println();
   }
   //exception handling goes here

First, the Fields collection is retrieved from the Recordset and the number of fields are counted.

fields = rs.getFields();
iFieldCount = fields.getCount();

Then the number of records in the Recordset are printed.

System.out.println("There are " + rs.getRecordCount() + " rows in the recordset.");

In order for the recordcount to be accurate, the CursorLocation of all Recordset objects is set to AdoEnums.CursorLocation.CLIENT.

A reference exists to the Fields collection; it is used to walk through each field in the Recordset and print its name.

for( int i = 0; i < iFieldCount; i++ )
{
      fld = fields.getItem(i);
      System.out.print(fld.getName() + "\t");
}

Next, the value of each field in each row is printed.

while( !rs.getEOF() )
{
      for( int j = 0; j < iFieldCount; j++ )
      {
         fld = fields.getItem(j);
         System.out.print(fld.getString() + "\t");   
      }
      rs.moveNext();
      System.out.println();
}

The third method, sortAuthors(), uses the Sort property of the Recordset to sort the Recordset according to one of the fields. In this case, the "state" column is used to sort the Recordset. The following code shows how to set it.

rs.setSort("state");

Next, printRecordset is called and the sorted recordset is passed in. The rows are printed in sorted order based on the "state" column.

The fourth method, filterAuthors(), uses the Filter property to restrict the rows that are visible. First, the Filter is set as follows:

rs.setFilter("state = 'CA'");

Now the Recordset can be printed out to verify that only rows with the "state" column set to 'CA' are printed.

printRecordset(rs);

The Filter property is set to an empty string so that all rows are visible again. The Recordset is printed as verification.

rs.setFilter("");
printRecordset(rs);

The fifth method, setMaxRecords(), uses the MaxRecords property to limit the number of rows in the Recordset. This is useful if you want to limit the resources used by a Recordset or the number of rows sent over the network.

MaxRecords is set to 5, the select string is specified, the Recordset is opened, and the rows are printed.

rs.setMaxRecords(5);
   rs.setSource("select * from authors"); 
   rs.setActiveConnection(conn);
   rs.open();
   printRecordset(rs);

The sixth method, printRSProperties(), prints the properties of an open Recordset. This is very similar to the example that printed out the properties of the Connection object.

iNumProps = rs.getProperties().getCount();
   props = rs.getProperties();
   for( int i = 0; i < iNumProps; i++ )
   {
      prop = props.getItem(i);
      System.out.println(prop.getName() + " = " + prop.getString());
   }

Following are all 105 of the properties. Keep in mind that different providers may well have a different number of properties.

IAccessor = -1
IChapteredRowset = -1
IColumnsInfo = -1
IColumnsRowset = -1
IConnectionPointContainer = -1
IConvertType = -1
ILockBytes = 0
IRowset = -1
IDBAsynchStatus = 0
IParentRowset = -1
IRowsetChange = 0
IRowsetExactScroll = -1
IRowsetFind = -1
IRowsetIdentity = -1
IRowsetInfo = -1
IRowsetLocate = -1
IRowsetRefresh = -1
IRowsetResynch = 0
IRowsetScroll = -1
IRowsetUpdate = 0
IRowsetView = -1
IRowsetIndex = 0
ISequentialStream = -1
IStorage = 0
IStream = 0
ISupportErrorInfo = -1
Preserve on Abort = -1
Access Order = 2
Append-Only Rowset = 0
Blocking Storage Objects = 0
Use Bookmarks = -1
Skip Deleted Bookmarks = -1
Bookmark Type = 1
Cache Deferred Columns = 0
Fetch Backwards = -1
Hold Rows = -1
Scroll Backwards = -1
Change Inserted Rows = -1
Column Privileges = 0
Command Time Out = 0
Preserve on Commit = -1
Defer Column = 0
Delay Storage Object Updates = -1
Private1 =
Filter Operations = 27
Find Operations = 27
Hidden Columns = 0
Immobile Rows = -1
Literal Bookmarks = -1
Literal Row Identity = -1
Maximum Open Rows = 0
Maximum Pending Rows = 0
Maximum Rows = 0
Column Writable = 0
Memory Usage = 0
Notification Granularity = 1
Notification Phases = 31
Column Set Notification = 3
Row Delete Notification = 3
Row First Change Notification = 3
Row Insert Notification = 3
Row Resynchronization Notification = 3
Rowset Release Notification = 3
Rowset Fetch Position Change Notification = 3
Row Undo Change Notification = 3
Row Undo Delete Notification = 3
Row Undo Insert Notification = 3
Row Update Notification = 3
Bookmarks Ordered = -1
Others' Inserts Visible = 0
Others' Changes Visible = 0
Own Inserts Visible = -1
Own Changes Visible = -1
Quick Restart = -1
Reentrant Events = -1
Remove Deleted Rows = -1
Report Multiple Changes = 0
Return Pending Inserts = -1
Row Privileges = 0
Asynchronous Rowset Processing = 16
Row Threading Model = 1
Server Cursor = 0
Strong Row Identity = -1
Objects Transacted = 0
Unique Rows = 0
Updatability = 7
Batch Size = 15
Update Criteria = 2
Background Fetch Size = 15
Initial Fetch Size = 50
Background thread Priority = 3
Cache Child Rows = -1
Maintain Change Status = 0
Auto Recalc = 1
Unique Table =
Unique Schema =
Unique Catalog =
Resync Command =
Cursor Engine Version = 21
Reshape Name =
Update Resync = 1
Bookmarkable = -1

The seventh method, useBookmarks(), uses an array of bookmarks to print the Recordset in reverse order. After opening the Recordset, the number of records returned is used to size an array of bookmarks.

rs.open();
iNumRecords = rs.getRecordCount();
Object bm[] = new Object[iNumRecords]; 

The code moves through the Recordset from first row to last printing out the "au_lname" field. The bookmark for each row is retrieved and stored in an array.

for( int i = 0; i < iNumRecords; i++ )
{
   System.out.println(rs.getField("au_lname").getString());
   bm[i] = rs.getBookmark();
   rs.moveNext();
}

Now the code moves through the bookmark array in reverse order, setting the Bookmark property and printing the "au_lname" field again.

for( int j = iNumRecords - 1; j >= 0; j-- )
{
   rs.setBookmark(bm[j]);
   System.out.println(rs.getField("au_lname").getString());
}

The eighth method demonstrates how ADO can manage a Recordset that contains multiple Recordset objects. The Source property is set to a string that contains three select statements.

rs.setSource("select * from authors;select * from titles;" +
               "select * from stores;");
rs.setActiveConnection(conn);
rs.open();

Once the Recordset is open, the "au_lname" column from each row in the first recordset is printed.

while( !rs.getEOF() )
{
   System.out.println("\t" + rs.getField("au_lname").getString());
   rs.moveNext();
}

The following line of code moves to the next Recordset.

rs = rs.nextRecordset();

The following code prints out the "title" column from each row in the second Recordset.

while( !rs.getEOF() )
{
   System.out.println("\t" + rs.getField("title").getString());
   rs.moveNext();
}

Another call to NextRecordset is executed, and the "stor_name" field from each row of the third Recordset is printed.

rs = rs.nextRecordset();
while( !rs.getEOF() )
{
   System.out.println("\t" + rs.getField("stor_name").getString());
   rs.moveNext();
}

The next three methods demonstrate how ADO can save a Recordset as a file. This functionality will allow you to take your laptop with you to another city and still make and save changes to a Recordset.

The first method opens a Recordset, executes a select statement, and saves the Recordset to a file on the local computer. You will find the code in the saveRSToFile() method. The name of the file we want to use as a parameter is passed in, the Recordset is opened and saved to the local computer.

rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
rs.setSource("select * from authors"); 
rs.setActiveConnection(conn);
rs.open();
rs.save(strFileName);

Notice that the Recordset is not closed here, but in the 'finally' block, which also closes the file. Once you have a file open you have exclusive access to it until you close the Recordset. So you can save the file, continue to make more changes and then save the file again. It is not until you close the Recordset that the file is actually closed.

This example uses a file with the following path: "c:\RecordsetDemo\AuthorsRS.adtg". Keep in mind that the directory "c:\RecordsetDemo\" must already exist on the computer. The Recordset.save() method will not create it. The .adtg extension refers to the format in which the Recordset is saved (Advanced Data TableGram).

Be aware that the file name you pass must not already exist on the computer. If the file already exists, the error number 80 will be returned, indicating the file already exists. This is for security reasons. You can open a new file or open an existing file but you can not overwrite an existing file that you have not opened.

The second method, openAndPrintRSFromFile(), is a helper method that prints out the "au_lname" column. It is called after the Recordset is first saved and then again after the Recordset is modified and saved. The output is as follows.

White
Green
Carson
O'Leary
Straight
Smith
Bennet
Dull
Gringlesby
Locksley
Greene
Blotchet-Halls
Yokomoto
del Castillo
DeFrance
Stringer
MacFeather
Karsen
Panteley
Hunter
McBadden
Ringer
Ringer

WhiteX
GreenX
CarsonX
O'LearyX
StraightX
SmithX
BennetX
DullX
GringlesbyX
LocksleyX
GreeneX
Blotchet-HallsX
YokomotoX
del CastilloX
DeFranceX
StringerX
MacFeatherX
KarsenX
PanteleyX
HunterX
McBaddenX
RingerX
RingerX

Now that you have a file on the local computer that is a saved Recordset, you can do something interesting with it. Open it again at a later time, maybe several days later, make changes to the Recordset and then resave the modified Recordset.

The third method, editRSFromFile() method does this. You do not need a connection for this to work, only a Recordset and a saved Recordset on your local computer. A string is passed to this method that indicates what file to use. The string opens the file. Then the code moves through the Recordset and change the "au_lname" field in each row. In fact, it adds "X" to end of each author's last name. Then the Recordset is saved back to disk and closed. The helper method is called again, printing the results to verify that the changes were saved.

rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
rs.setLockType(AdoEnums.LockType.OPTIMISTIC);
rs.open(strFileName);
while( !rs.getEOF() )
{
   rs.getField("au_lname").setString(rs.getField("au_lname").getString() 
                                                               + "X");
   rs.update();
   rs.moveNext();
}
rs.save(strFileName);

The Recordset continues to be saved in a file. There are a number of interesting things you could do with it. You could send it as an attachment in email, make a backup copy, or continue to edit and update it for the next three weeks.

You could also use a business object running in Transaction Server to update the SQL Server database. To do this, create the business object and invoke a method that takes a Recordset as a parameter. The business object is then responsible for applying business rules and applying the update to the underlying SQL Server. You can implement this on a row by row basis, or you could use the Recordset.updateBatch() method to make all the changes at once. Keep in mind that no locks are placed on the database until the time the updates are applied and, yes, you will need to figure out what to do in the case that one of the rows has been changed by someone else. Business objects are discussed more thoroughly in a later section of the paper.

Command Object with Input Parameters Example

This is a longer example but it is quite simple. It involves an Insert statement with nine input parameters. You fill in the parameter information, update the command, fill in the parameters with new values and update the command again. Each time, a new row is inserted in the database.

It is important that you fill out all the parameter information completely and correctly. The example below relies on the correct setting of each parameter's data type, size, direction and value. If you are calling a stored procedure and do not want to fill out this information, then you can use the Parameters.refresh() method to automatically fill in this information. However, performance will suffer because ADO must go to the server and interrogate it for information about the stored procedure you want to use, what parameters it has and so on. In most cases if you are calling a stored procedure you know what parameters it has and your application will be faster if you take the time to explicitly tell ADO about the parameters.

This command is also prepared by calling cmd.setPrepared(true); with SQL Server this means that an execution plan will be prepared and each time the command is executed the same execution plan will be used. This speeds performance because the execution plan may well be cached by SQL Server, especially if it is used in succession so that cache hits keep the plan in memory.

import com.ms.wfc.data.*;

public class Class1
{
   public static void main (String[] args)
   {
      Connection conn = new Connection();
      Command cmd = new Command();
      String strSQL = new String();

      try
      {
         conn.open("Provider=SQLOLEDB;Data Source=(local);" +
                 "User ID =sa;Password=;Initial Catalog=Pubs;");
         cmd.setActiveConnection (conn);
         strSQL = "insert authors values (?,?,?,?,?,?,?,?,?)";
         cmd.setCommandText(strSQL);         
         cmd.setCommandType (AdoEnums.CommandType.TEXT);
         cmd.setPrepared(true);
      
         Parameters params = cmd.getParameters(); 
         Parameter param[] = new Parameter[9];
         //set the properties for the first parameter, id
         param[0] = new Parameter();
         param[0].setType(AdoEnums.DataType.VARCHAR);
         param[0].setDirection(AdoEnums.ParameterDirection.INPUT);
         param[0].setSize(11);
         param[0].setString("977-72-1234");
         params.append(param[0]);
         //Second parameter, au_lname
         param[1] = new Parameter();
         param[1].setType(AdoEnums.DataType.VARCHAR);
         param[1].setDirection(AdoEnums.ParameterDirection.INPUT);
         param[1].setSize(40);
         param[1].setString("Jones");
         params.append(param[1]);
         //Third parameter, au_fname
         param[2] = new Parameter();
         param[2].setType(AdoEnums.DataType.VARCHAR);
         param[2].setDirection(AdoEnums.ParameterDirection.INPUT);
         param[2].setSize(20);
         param[2].setString("Tom");
         params.append(param[2]);
         //Fourth parameter, phone
         param[3] = new Parameter();
         param[3].setType(AdoEnums.DataType.CHAR);
         param[3].setDirection(AdoEnums.ParameterDirection.INPUT);
         param[3].setSize(12);
         param[3].setString("777-555-1233");
         params.append(param[3]);
         //Fifth parameter, address
         param[4] = new Parameter();
         param[4].setType(AdoEnums.DataType.VARCHAR);
         param[4].setDirection(AdoEnums.ParameterDirection.INPUT);
         param[4].setSize(40);
         param[4].setString("123 Main St.");
         params.append(param[4]);
         //Sixth parameter, city
         param[5] = new Parameter();
         param[5].setType(AdoEnums.DataType.VARCHAR);
         param[5].setDirection(AdoEnums.ParameterDirection.INPUT);
         param[5].setSize(20);
         param[5].setString("Seattle");
         params.append(param[5]);
         //Seventh parameter, state
         param[6] = new Parameter();
         param[6].setType(AdoEnums.DataType.CHAR);
         param[6].setDirection(AdoEnums.ParameterDirection.INPUT);
         param[6].setSize(2);
         param[6].setString("WA");
         params.append(param[6]);
         //Eighth parameter, zip
         param[7] = new Parameter();
         param[7].setType(AdoEnums.DataType.CHAR);
         param[7].setDirection(AdoEnums.ParameterDirection.INPUT);
         param[7].setSize(5);
         param[7].setString("98026");
         params.append(param[7]);
         //Last parameter, contract
         param[8] = new Parameter();
         param[8].setType(AdoEnums.DataType.BOOLEAN);
         param[8].setDirection(AdoEnums.ParameterDirection.INPUT);
         //param.setSize(1);
         param[8].setBoolean(true);
         params.append(param[8]);
      
         int iRecordsAffected;
         iRecordsAffected = cmd.executeUpdate();
         System.out.println(iRecordsAffected);
         
         //Let's insert another row
         param[0].setString("977-72-1236");
         param[1].setString("Jones");
         param[2].setString("Tom");
         param[3].setString("777-555-1234");
         param[4].setString("123 Main St.");
         param[5].setString("Seattle");
         param[6].setString("WA");
         param[7].setString("98026");
         param[8].setBoolean(true);
         iRecordsAffected = cmd.executeUpdate();
         System.out.println(iRecordsAffected);
         
         //You get the idea, we could do thousands more
         param[0].setString("977-72-1237");
         param[1].setString("Jones");
         param[2].setString("Tom");
         param[3].setString("777-555-1234");
         param[4].setString("123 Main St.");
         param[5].setString("Seattle");
         param[6].setString("WA");
         param[7].setString("98026");
         param[8].setBoolean(true);
         iRecordsAffected = cmd.executeUpdate();
         System.out.println(iRecordsAffected);
      }
      catch(AdoException e)
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally
      {
         try { conn.close(); } 
         catch (Exception e) { /* Ignore close errors */ }    
      }
   }
}

Parameterized Command Example

This example shows how to execute a parameterized query using a Command object. When the Command is executed it returns a Recordset. A parameterized query allows you to retrieve a particular set of records and reduces execution time in cases where the query execution plan gets cached. Thus if you have a query that you execute repeatedly, your performance would benefit if the query execution plan were cached in memory on the server. Examples of this are a Select, Insert, or Update statement, or a stored procedure that takes input parameters. Stored procedures are interesting in that they contain parameters whose Direction property can also be INPUT, INPUTOUTPUT, or RETURN type.

import com.ms.wfc.data.*;

public class Class1
{
   public static void main (String[] args)
   {
      Connection conn = new Connection();
      Command cmd = new Command();
      Recordset rs = new Recordset();
      Field fld;
      try
      {
         conn.open("Provider=SQLOLEDB;Data Source=(local);" +
                 "User ID =sa;Password=;Initial Catalog=Pubs;");
         cmd.setActiveConnection (conn);
         cmd.setCommandText( "select * from authors where au_lname = ?");
         cmd.setCommandType (AdoEnums.CommandType.TEXT);
         cmd.setCommandTimeout(10);
         cmd.setPrepared(true);
      
         Parameters params = cmd.getParameters(); 
         Parameter param = new Parameter();
         //set the properties for the parameter
         param.setType(AdoEnums.DataType.VARCHAR);
         param.setDirection(AdoEnums.ParameterDirection.INPUT);
         param.setSize(40);
         param.setString("Ringer");
         params.append(param);
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setCursorType(AdoEnums.CursorType.FORWARDONLY);
         rs.open(cmd);         
         fld = rs.getField("au_lname");
         while  (!rs.getEOF()) 
         {
            System.out.println(fld.getString());
            rs.moveNext();
         }
         rs.close();
         rs.release();
      }
      catch(AdoException e)
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally
      {
         try { conn.close(); } 
         catch (Exception e) { /* Ignore close errors */ }    
      }
   }
}

The usual connection opening code is followed by code that sets the command properties.

cmd.setActiveConnection (conn);
cmd.setCommandText( "select * from authors where au_lname = ?");
cmd.setCommandType (AdoEnums.CommandType.TEXT);
cmd.setCommandTimeout(10);
cmd.setPrepared(true);

Setting the CommandTimeout property to 10 seconds prevents you from waiting too long if anything is wrong with the server. The CommandType property is set to AdoEnums.CommandType.TEXT. If you were calling a stored procedure you would use AdoEnums.CommandType.PROCEDURE. The Prepared property is set equal to true. This is not necessary if you are only going to execute the statement once but if you plan to use it multiple times, preparing the command will cause SQL Server to generate an execution plan for the query. It reuses this execution query when subsequent invocations of this command are run.

After setting the properties of the Command object, any parameters, denoted by the question mark symbol (?) in the query, must be defined. Command objects have a Parameters collection consisting of Parameter objects. The Parameters collection is obtained and the single parameter to the query is defined. The Direction property indicates that it is an input parameter. The Type property indicates the parameter's data type. If the data type is not compatible with the column type in the database, an error on execution of the Command will be returned. The au_lname column is a varchar(40) field in the authors table. The single parameter, param.setString("Ringer"), indicates that all rows where the au_lname column is equal to "Ringer" will be returned.

Next, two properties on the Recordset to be generated are set, the command is executed, and any resulting rows are printed.

rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
rs.setCursorType(AdoEnums.CursorType.FORWARDONLY);
rs.open(cmd);
rld = rs.getField("au_lname");
while  (!rs.getEOF()) 
{
   System.out.println(fld.getString());
   rs.moveNext();
}

Finally, the Recordset is closed and released, then the connection is closed.

Creating and Using Business Objects

The following examples demonstrate two of ADO's unique capabilities: enabling disconnected Recordset objects and allowing creatable Recordset objects.

In addition to saving a Recordset to a file, you can also retrieve a disconnected recordset from a COM object on another computer and save it locally. Users of ADO applications don't need to be in their regular offices or have a connection to their data server all the time. In fact, a user may not have a regular office. The fact that ADO allows you to work with both disconnected recordsets and create recordsets is very powerful.

The first example is a business object that generates a Recordset, disconnects it and then sends it to the client. The client could be on another computer, in another process on the same computer or in the same process. The client is free to modify existing rows, add rows, or delete rows. The client is also free to save the Recordset to a file. This allows the client to retrieve the Recordset hours, days, or months later, and still work with it. The business object also has a method that accepts an ADO Recordset as a parameter, reconnects to the database, and applies the updates using the Connection.execute() method. This requires generating an update string that is sent to the SQL Server. In the interest of keeping the example simple, it uses an update string rather than a stored procedure.

The second example uses ADO's ability to create a Recordset with the fields and field types that you want. You can then add rows to this Recordset and call a business object that takes a Recordset as an in parameter. This business object then applies some business rules to each row in turn and then inserts the information into the database. The business object could also send an MSMQ message for each row to the shipping and accounting departments, all as part of a transaction.

The first example business object has two methods. One returns a disconnected recordset while the other takes a disconnected Recordset as a parameter and applies the updates to the database.

import com.ms.wfc.data.*;
import com.ms.com.ComFailException;

public class CreateAndUpdateRS
{
   Connection conn;
   Recordset rs;
   public com.ms.wfc.data.IDataSource GetRS()
   {
      try
      {
         conn = new Connection();
         rs = new Recordset();
         conn.setConnectionString("Provider=SQLOLEDB;" + 
                        "Data Source=(local);" +
               "User ID =sa;Password=;Initial Catalog=Pubs;");
         conn.open();
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setLockType(AdoEnums.LockType.BATCHOPTIMISTIC);
         rs.setCursorType(AdoEnums.CursorType.STATIC);
         rs.setActiveConnection(conn);
         rs.setSource("select * from Orders");
         rs.open();
         Connection nullConnection = null;
         rs.setActiveConnection(nullConnection);
      }
      catch (AdoException e)
      {
         int err = (0xFFFF) & e.getHResult();
         String strMSG = e.getMessage();
         ComFailException ComExcept = new ComFailException(err, strMSG);
         throw ComExcept;
      }
      finally
      {
         try{ conn.close(); } 
         catch (Exception e) {/* ignore errors */ }
      }
      return (com.ms.wfc.data.IDataSource) rs.getDataSource();
   }
   public  String UpdateRS(IDataSource rsIn)
   {
      try
      {
         conn = new Connection();
         rs = new Recordset();
         
         rs.setDataSource(rsIn);
         conn.setConnectionString("Provider=SQLOLEDB;" + 
               "Data Source=(local);" +
               "User ID =sa;Password=;Initial Catalog=Pubs;");
         conn.open();
         rs.moveFirst();
         while ( ! rs.getEOF() )
         {
         if(rs.getFields().getItem("au_lname").getString().indexOf("'") == -1)
            {
               conn.execute("update authors set au_lname = '" + 
                  rs.getFields().getItem("au_lname").getString() + 
            "' where au_id = '" +  
            rs.getFields().getItem("au_id").getString() + "' ");
            }
            rs.moveNext();
         }
      }
      catch (AdoException e)
      {
         int err = (0xFFFF) & e.getHResult();
         String strMSG = e.getMessage();
         ComFailException ComExcept = new ComFailException(err, strMSG);
         throw ComExcept;
      }
      return "Updates applied.";       
   }
}

The client code is presented in small snippets because it is a simple client that does several things with the business object and the Recordset it sends down to the client. The main function looks like this

public static void main (String[] args)
   {
      Class1 App = new Class1();
      App.getTheRSAndSaveToFile();
      App.getRSFromFileandEdit();
      App.openAndPrintRSFromFile("C:\\JavaSample\\authorsFromBO.rs");
      App.ApplyUpdates();
      
   }

The first method, getTheRSAndSaveToFile(), does just that, it retrieves a recordset and saves it to a file.

void getTheRSAndSaveToFile()
   {
      Recordset rs = new Recordset();
      IDataSource dataSourceIn;
      
      try
      {
      CreateAndUpdateRS bizObject = new CreateAndUpdateRS();
      dataSourceIn = (IDataSource)bizObject.GetRS();
      rs.setDataSource(dataSourceIn);
      rs.save("C:\\JavaSample\\authorsFromBO.rs");
      //exception handling removed.

This is really very simple code for what it does. A COM wrapper has been added to the project, which wraps the CreateAndUpdateRS. An IDataSource variable is declared because the return from the business object's GetRS method is an IDataSource. Once the business object is created and the GetRS method is called, the IDataSource return variable is used to set the DataSource property of the local Recordset. This initializes the Recordset and it is immediately saved to a file. The Recordset is closed.

The second client method, getRSFromFileandEdit(), opens the Recordset from the saved file, edits each row, applies the updates, and saves it back to the same file. The word "File" is appended to each author's last name.

rs.open("C:\\JavaSample\\authorsFromBO.rs");
   while( !rs.getEOF() )
   {
      rs.getField("au_lname").setString(rs.getField("au_lname").getString() + "File");
      rs.update();
      rs.moveNext();
   }
   rs.save("C:\\JavaSample\\authorsFromBO.rs");

Next, openAndPrintRSFromFile() is called, which just prints the "au_lname" field to the console, verifying the changes have been made. The last method, ApplyUpdates(), creates the business object again, opens the Recordset from the file, and calls the UpdateRS method which takes the Recordset as a parameter.

Recordset rs = new Recordset();
   IUnknown dataSourceOut;
      
   try
   {
      rs.open("C:\\JavaSample\\authorsFromBO.rs");
      CreateAndUpdateRS bizObject = new CreateAndUpdateRS();
      dataSourceOut = rs.getDataSource();
      bizObject.UpdateRS(dataSourceOut);

The following demonstrates what the business object does with the Recordset.

public  String UpdateRS(IDataSource rsIn)
   {
try
      {
         conn = new Connection();
         rs = new Recordset();
      
         rs.setDataSource(rsIn);
         conn.setConnectionString("Provider=SQLOLEDB;Data Source=(local);" +
            "User ID =sa;Password=;Initial Catalog=Pubs;");
         conn.open();
         rs.moveFirst();
         while ( ! rs.getEOF() )
         {
            if( rs.getFields().getItem("au_lname").getString().indexOf("'") == -1 )
            {
               conn.execute("update authors set au_lname = '" + 
                   rs.getFields().getItem("au_lname").getString() + "' where au_id = '" +
                   rs.getFields().getItem("au_id").getString() + "' ");
            }
            rs.moveNext();
         }
      }

First the DataSource or the local Recordset is set with the IDataSource that was passed in, and a connection to a database is opened. The moveFirst method is called just to ensure that every row is retrieve. Finally, !rs.getEOF() is called to walk through each row.

The following is the business logic. Using rs.getFields().getItem("au_lname").getString().indexOf("'") == -1 each row is checked to see if the author's last name has a single quote in it. If it does, the row is skipped. In the authors table, O'Leary is skipped without being updated.

The second example created an arbitrary Recordset on the client. A table was created on the server with the following structure.

create table Orders 
( counter int identity, 
CompanyName varchar(30), 
OrderNumber int, 
ItemOrdered varchar(20), 
Quantity int, 
Priority int, 
Notes varchar(255)) 

The client creates a Recordset that has fields to match this table definition.

The following is the COM wrapper for the business object, BOForCreatedRecordset. The business object has one function that accepts a Recordset as a parameter. It is very similar to the UpdateRS method previously discussed.

import com.ms.wfc.data.*;
import boforcreatedrecordset.*;

public class Class1
{
   public static void main (String[] args)
   {
      try
      {
         Recordset rs = new Recordset();
         BOForCreatedRecordset bo = new BOForCreatedRecordset();
         rs.getFields().append("CompanyName", AdoEnums.DataType.VARCHAR, 30);
         rs.getFields().append("OrderNumber", AdoEnums.DataType.INTEGER);
         rs.getFields().append("Item", AdoEnums.DataType.VARCHAR, 20);
         rs.getFields().append("Quantity", AdoEnums.DataType.INTEGER);
         rs.getFields().append("Priority", AdoEnums.DataType.INTEGER);
         rs.getFields().append("Notes", AdoEnums.DataType.VARCHAR, 1000);
         rs.open();
         rs.addNew();
         rs.getFields().getItem("CompanyName").setString("A Big Company" );
         rs.getFields().getItem("OrderNumber").setInt(1);
         rs.getFields().getItem("Item").setString( "Red Widgets" );
         rs.getFields().getItem("Priority").setInt(2);
         rs.getFields().getItem("Quantity").setInt(10);
         rs.getFields().getItem("Notes").setString( "Some text goes here" );
         rs.update();
         rs.addNew();
         rs.getFields().getItem("CompanyName").setString( "Another Big Company" );
         rs.getFields().getItem("OrderNumber").setInt(2);
         rs.getFields().getItem("Item").setString( "Orange Widgets" );
         rs.getFields().getItem("Priority").setInt(1);
         rs.getFields().getItem("Quantity").setInt(20);
         rs.getFields().getItem("Notes").setString( "Some text goes here" );
         rs.update();
         Connection nullConnection = null;
         rs.setActiveConnection(nullConnection);
         System.out.println(bo.UpdateRS(rs));
      }
      catch (Exception e)
      {
         System.out.println(e.getMessage());
      }
   }
}

First, the COM wrapper class for the business object is imported.

import boforcreatedrecordset.*;

Then, the code walks through and creates each of the six fields the Recordset will have, specifying the field's name, data type, and size. The AdoEnums.DataType.INTEGER fields do not need to have a size specified.

Recordset rs = new Recordset();
   rs.getFields().append("CompanyName", AdoEnums.DataType.VARCHAR, 30);
   rs.getFields().append("OrderNumber", AdoEnums.DataType.INTEGER);
   rs.getFields().append("Item", AdoEnums.DataType.VARCHAR, 20);
   rs.getFields().append("Quantity", AdoEnums.DataType.INTEGER);
   rs.getFields().append("Priority", AdoEnums.DataType.INTEGER);
   rs.getFields().append("Notes", AdoEnums.DataType.VARCHAR, 1000);

When the Recordset object's fields are defined, rs.open() is called. This finalizes the definition of the shape, and the Recordset is created. Two rows of data are added to the Recordset by calling rs.addNew(), inserting a value into each column and then calling rs.update() for each row added.

   rs.addNew();
   rs.getFields().getItem("CompanyName").setString( "A Big Company" );
   rs.getFields().getItem("OrderNumber").setInt(1);
   rs.getFields().getItem("Item").setString( "Red Widgets" );
   rs.getFields().getItem("Priority").setInt(2);
   rs.getFields().getItem("Quantity").setInt(10);
   rs.getFields().getItem("Notes").setString( "Some text goes here" );
   rs.update();
   rs.addNew();
   rs.getFields().getItem("CompanyName").setString( "Another Big Company" );
   rs.getFields().getItem("OrderNumber").setInt(2);
   rs.getFields().getItem("Item").setString( "Orange Widgets" );
   rs.getFields().getItem("Priority").setInt(1);
   rs.getFields().getItem("Quantity").setInt(20);
   rs.getFields().getItem("Notes").setString( "Some text goes here" );
   rs.update();

A connection variable is declared so that the Recordset object's ActiveConnection property can be properly set before it is passed to the business object:

Connection nullConnection = null;
   rs.setActiveConnection(nullConnection);

Because the UpdateRS method returns a string, the string returned is printed. In this simple example it just prints out, "All went well."

System.out.println(bo.UpdateRS(rs));

Here is the code for the business object, BOForCreatedRecordset. It has a single method, UpdateRS() that takes an IDataSource parameter. The IDataSource is associated with a Recordset so that all the rows can be reviewed, an "Insert" statement can be constructed, and the Recordset sent to the database with the Connection::executeUpdate() method. Note that the ActiveConnection on the Recordset does not need to be set because Recordset.updateBatch(); is not called.

import com.ms.wfc.data.*;
import com.ms.com.*;

public class BOForCreatedRecordset
{
   Connection conn;
   Recordset rs;

   public  String UpdateRS(IDataSource rsIn)
   {
      String strSQL = new String();
      try
      {
         conn = new Connection();
         rs = new Recordset();
         rs.setDataSource(rsIn);
         
         conn.setConnectionString("Provider=SQLOLEDB;" 
                  + "Data Source=(local);User ID=sa;"
                  + "Password=;Initial Catalog=pubs;");
         conn.open();
         rs.moveFirst();
         while( !rs.getEOF() )
         {
            strSQL = "insert Orders values( '" 
               + rs.getFields().getItem(0).getString()         //CompanyName
               + "'," + rs.getFields().getItem(1).getString()   //OrderNumber
               + ",'" + rs.getFields().getItem(2).getString()   //ItemOrdered
               + "'," + rs.getFields().getItem(3).getString()   //Quantity
               + "," + rs.getFields().getItem(4).getString()   //Priority
               + ",'" + rs.getFields().getItem(5).getString()   //Notes
               + "' )";
            conn.executeUpdate(strSQL);
            rs.moveNext();
         }
      }
      catch (AdoException e)
      {
         int err = (0xFFFF) & e.getHResult();
         String strErrorMSG = e.getMessage();
         ComFailException ComExcept = new ComFailException(err, strErrorMSG);
         throw ComExcept;
      }
      finally
      {
         try      { conn.close(); } 
         catch   (Exception e) {/* ignore errors */ }
         return "All went well.";          
      }
   }
}

IDataSource is associated with a local Recordset:

   rs.setDataSource(rsIn);

The connection to the database is opened and the moveFirst method is called on the Recordset. The code then walks through each row using a while loop. As the Insert statement is constructed, single quotes are placed around the fields that are character based, such as CompanyName, ItemOrdered, and Notes. The insert statement looks like this: "insert Orders values('Another Big Company',2,'Orange Widgets',20,1,'Some text goes here' )"

rs.moveFirst();
   while( !rs.getEOF() )
   {
      strSQL = "insert Orders values( '" 
         + rs.getFields().getItem(0).getString()         
         + "'," + rs.getFields().getItem(1).getString()
         + ",'" + rs.getFields().getItem(2).getString()   
         + "'," + rs.getFields().getItem(3).getString()   
         + "," + rs.getFields().getItem(4).getString()   
         + ",'" + rs.getFields().getItem(5).getString()   
         + "')";
      conn.executeUpdate(strSQL);
      rs.moveNext();
   }

The two rows are inserted into the table and a string is returned to the console application so that you can print something for the user to see.

Appendix A

import com.ms.wfc.data.*;
public class Class1
{
   Connection conn = new Connection();
   String m_strFileName = "c:\\JavaDemo\\AuthorsRS";
   public static void main (String[] args)
   {
      Class1 App = new Class1();
      App.openConnection(App.conn);
      App.printAuthors(App.conn);
      App.sortAuthors(App.conn);
      App.filterAuthors(App.conn);
      App.setMaxRecords(App.conn);
      App.printRSProperties(App.conn);
      App.useBookmarks(App.conn);
      App.multipleRecordsets(App.conn);
      App.saveRSToFile(App.conn, "c:\\RecordsetDemo\\AuthorsRS.adtg");
      App.openAndPrintRSFromFile("c:\\RecordsetDemo\\AuthorsRS.adtg");
      App.editRSFromFile("c:\\RecordsetDemo\\AuthorsRS.adtg");
      App.openAndPrintRSFromFile("c:\\RecordsetDemo\\AuthorsRS.adtg");
   }
   void openConnection(Connection conn)
   {
      try
      {
         conn.open("Provider=SQLOLEDB;Data Source=(local);" +
                 "User ID =sa;Password=;Initial Catalog=Pubs;");
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
   }
   void editRSFromFile(String strFileName)
   {
      Recordset rs = new Recordset();
      
      try
      {
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setLockType(AdoEnums.LockType.OPTIMISTIC);
         rs.open(strFileName);
         while( !rs.getEOF() )
         {
                  rs.getField("au_lname").setString(rs.getField("au_lname").getString()
                                                    + "X");
            rs.update();
            rs.moveNext();
         }
         rs.save(strFileName);
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
   }
   void saveRSToFile(Connection conn, String strFileName)
   {
      Recordset rs = new Recordset();
      
      try
      {
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setSource("select * from authors"); 
         rs.setActiveConnection(conn);
         rs.open();
         rs.save(strFileName);
      }
      catch(AdoException e)   
      {   //if err == 80 then the file already exists
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
   }
   void openAndPrintRSFromFile(String strFileName)
   {
      Recordset rs = new Recordset();
      
      try
      {
         rs.open(strFileName);
         while( !rs.getEOF() )
         {
            System.out.println(rs.getField("au_lname").getString());
            rs.moveNext();
         }
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
   }
   void multipleRecordsets(Connection conn)
   {
      Recordset rs = new Recordset();
      
      try
      {
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setSource("select * from authors;select * from titles;" +
                        "select * from stores;");
         rs.setActiveConnection(conn);
         rs.open();
         System.out.println("First Recordset\nColumn: " 
                  + rs.getFields().getItem(1).getName());
         while( !rs.getEOF() )
         {
            System.out.println("\t" 
                  + rs.getField("au_lname").getString());
            rs.moveNext();
         }
         rs = rs.nextRecordset();
         System.out.println("\n\nSecond Recordset\nColumn: " 
                  + rs.getFields().getItem("title").getName());
      
         while( !rs.getEOF() )
         {
            System.out.println("\t" + rs.getField("title").getString());
            rs.moveNext();
         }
         rs = rs.nextRecordset();
         System.out.println("\n\nThird Recordset\nColumn: " 
                  + rs.getFields().getItem("stor_name").getName());
      
         while( !rs.getEOF() )
         {
            System.out.println("\t" + 
                     rs.getField("stor_name").getString());
            rs.moveNext();
         }
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
   }
   void useBookmarks(Connection conn)
   {
      Recordset rs = new Recordset();
      int iNumRecords;
      
      try
      {
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setSource("select * from authors"); 
         rs.setActiveConnection(conn);
         rs.open();
         iNumRecords = rs.getRecordCount();
         Object bm[] = new Object[iNumRecords];
         System.out.println("Original order:");
         for( int i = 0; i < iNumRecords; i++ )
         {
            System.out.println(rs.getField("au_lname").getString());
            bm[i] = rs.getBookmark();
            rs.moveNext();
         }
         System.out.println("Bookmark array order:");
         for( int j = iNumRecords - 1; j >= 0; j-- )
         {
            rs.setBookmark(bm[j]);
            System.out.println(rs.getField("au_lname").getString());
         }
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
   }
   void printRSProperties(Connection conn)
   {
      Recordset rs = new Recordset();
      AdoProperties props;
      AdoProperty prop;
      int iNumProps;
      
      try
      {
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setSource("select * from authors"); 
         rs.setActiveConnection(conn);
         rs.open();
         iNumProps = rs.getProperties().getCount();
         props = rs.getProperties();
         for( int i = 0; i < iNumProps; i++ )
         {
            prop = props.getItem(i);
            System.out.println(prop.getName() + " = " 
                              + prop.getString());
         }
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
      
   }
   void filterAuthors(Connection conn)
   {
      Recordset rs = new Recordset();
      
      try
      {
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setSource("select * from authors"); 
         rs.setActiveConnection(conn);
         rs.open();
         rs.setFilter("state = 'CA'");
         PrintRecordset(rs);
         //This wipes out the filter so that we have all rows again
         rs.setFilter("");
         PrintRecordset(rs);
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
   }
   void setMaxRecords(Connection conn)
   {
      Recordset rs = new Recordset();
      
      try
      {
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setMaxRecords(5);
         rs.setSource("select * from authors"); 
         rs.setActiveConnection(conn);
         rs.open();
         PrintRecordset(rs);
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
   }
   void sortAuthors(Connection conn)
   {
      Recordset rs = new Recordset();
      
      try
      {
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setSource("select * from authors"); 
         rs.setActiveConnection(conn);
         rs.open();
         rs.setSort("state");
         PrintRecordset(rs);
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
   }
   void printAuthors(Connection conn)
   {
      Recordset rs = new Recordset();
      
      try
      {
         rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
         rs.setSource("select * from authors"); 
         rs.setActiveConnection(conn);
         rs.open();
         PrintRecordset(rs);
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
      finally      
      {
         try {rs.close(); rs.release(); } 
         catch (Exception e) { /* Ignore close errors */ } 
      }
   }
   void PrintRecordset(Recordset rs)
   {
      Fields fields;
      Field fld;
      int iFieldCount;
      
      try
      {
         fields = rs.getFields();
         iFieldCount = fields.getCount();
         //For this to be accurate we must 
         //use AdoEnums.CursorLocation.CLIENT
         System.out.println("There are " + rs.getRecordCount() 
                     + " rows in the recordset.");
         for( int i = 0; i < iFieldCount; i++ )
         {
            fld = fields.getItem(i);
            System.out.print(fld.getName() + "\t");
         }
         System.out.println();
         while( !rs.getEOF() )
         {
            for( int j = 0; j < iFieldCount; j++ )
            {
               fld = fields.getItem(j);
               System.out.print(fld.getString() + "\t");   
            }
            rs.moveNext();
            System.out.println();
         }
         System.out.println();
      }
      catch(AdoException e)   
      {
         int err  = (0xFFFF) &  e.getHResult();
         System.out.println(e.getMessage() + "  hr = " + err);
      }
      catch(Exception exception)
      {
         System.out.println(exception.getMessage());
      }
   }
}

© 1999 Microsoft Corporation. All rights reserved. Terms of use.