When you’re using Visual J++ to create a data access solution, you must choose your strategy wisely. Remote Data Objects is a complete solution based on ODBC.

If you are programming with Java to create an application that accesses a client-server database management system (DBMS) such as SQL Server™ or Oracle, you must choose a data access strategy. Most of the currently publicized strategies rely on evolving technologies that aren't quite ready for use in production code. JDBC is the new, all-purpose data access strategy from Sun Microsystems that is part of the JDK 1.1. JDBC could evolve into a good strategy, but its limited API functionality and lack of available drivers currently make it a poor choice for serious database developers. The Microsoft® ActiveX™ Data Objects (ADO) also shows promise, though many people (falsely) see it as a work-in-progress that is not fully implemented. ADO shipped one year ago and now incorporates proven cursor functionality. One day in the not-too-distant future, ADO will have all the capabilities of all the present Microsoft database APIs combined; at that point it will be the preferred method for programmatic data access, but that day is not yet here. If you need to write production code today, you might want to use something that has already been around the block once or twice.       Many applications currently use Remote Data Objects (RDO), which has been layered on top of ODBC. There are a wealth of high-performance ODBC drivers for every major database, making the combination of ODBC and RDO a very open solution. This article is intended to get you started with RDO programming using Visual J++™. It will present some background information on both ODBC and RDO, and then present some RDO coding examples you can use to connect to your DBMS and to access your database. What Is ODBC?       A client-server DBMS is only useful once client applications can access it to retrieve and modify data. For a client application to establish a remote connection and interact with a DBMS, both computers involved must agree on a set of functions that will be used for communication. ODBC is an industry-standard protocol that defines how a client and a remote database interact. ODBC provides a common API for many different DBMS systems. Although drivers that are specific to one vendor's DBMS must be installed on the client machine, they are hidden from the programs that you write. You write your code to ODBC and let ODBC take care of communicating with the unique DBMS driver. Oracle, Informix, Sybase, and SQL Server all provide access through ODBC.       Instead of ODBC, you can use a proprietary API provided by the database vendor. The vendor must then also supply client-side drivers that include the code required to remote function calls across a network. Using a proprietary API from a specific DBMS vendor was much more common a decade ago than it is today. Any application that uses a proprietary API is tightly coupled to one vendor's DBMS. To create and maintain an application that would work with several systems would require a different set of source code for each vendor-specific API. The ODBC architecture serves to decouple the application code you write from anything that is specific to a vendor's system.       While a single API for all DBMS access offers many benefits, it does have one important limitation: it cannot deliver all the functionality that each DBMS offers. Vendors can add innovative features to their systems faster than ODBC can be adjusted to accommodate them. ODBC represents the most common set of functionality available in modern client-server DBMSs, and it's matured to the point where the common functionality it provides is sufficient for most database applications. However, there will always be special cases where a vendor-specific API is required.       Remember that ODBC was created for client-server database systems that employ a centralized database engine. Client applications typically access these systems using views and stored procedures. ODBC makes it possible for a client application to submit SQL statements and manage sets of data returned by the server. ODBC can create a cursor-driven cache of data on either the client or the server (this is an example where ODBC is far more sophisticated than JDBC). ODBC allows for the remote execution of stored procedures, while giving you complete access to output parameters, return values, and system messages. A database architecture that uses a centralized database engine accessed by clients across process or machine boundaries is referred to as a two-tier system.       ODBC can also accommodate single-tier drivers that do not require a centralized database engine. These drivers read and write their data directly to a local file. Multiuser systems with a single-tier architecture require multiple client applications that concurrently access a single database file. These systems are much less scalable than the two-tier systems that ODBC was created for.       ODBC provides an API compliance level that indicates whether a driver supports a given category of functions. Drivers that are core compliant are mainly used in single-tier systems. Drivers that are to be used with a client-server DBMS should be at least level 1 (and preferably level 2) compliant. ODBC also defines different levels of compliance with SQL grammar. Drivers that only support the minimum SQL compliance level are limited to a small number of datatypes. Drivers that have a compliance level of core SQL or extended SQL have more capabilities for dealing with stored procedures, advanced datatypes, and data definition language (DDL). ODBC Architecture       ODBC provides vendor transparency by introducing a layer between your code and that provided by the vendor. Figure 1 shows how the components are layered on both the client and server computers. ODBC apps are partitioned into the following four subsystems: the application, the ODBC driver manager, the ODBC driver, and the data source.

Figure 1: ODBC Components

      Your client application communicates with a core set of ODBC services provided by a component that ships with the operating system. These services are collectively known as the ODBC driver manager, and are responsible for loading and interacting with vendor-specific ODBC drivers. Each ODBC driver is responsible for connecting to and communicating with a unique data source. A data source can be a database within a client-server DBMS or a database file like those used by the Jet database engine. Each ODBC driver is responsible for reading and writing data to and from its own proprietary data source. Client applications track the profile of each data source with a data source name (DSN). A DSN is a set of registry entries that define an ODBC driver, a host computer, and a target database.
      Client-side ODBC components provide a conduit between your application and a remote DBMS. Client applications establish remote connections and execute SQL statements and stored procedures. The DBMS responds by returning messages as streams of data. The ODBC API also makes it possible to handle output parameters, return values, and error messages from stored procedures.
      If you're accessing the ODBC API directly, you'll need to understand the purpose of three different ODBC objects: the environment object, the connection object, and the statement object. The environment represents the ODBC runtime environment. The connection represents an established communications channel between a client application and a database within a DBMS. Within the context of an established connection you can create statement objects to submit SQL statements and manage resultsets returned by the server. ODBC allows you to connect a single client application with several DBMS hosts at the same time. Some, but not all, DBMSs allow a single connection to support multiple statements.
      The ODBC API is accessible through a set of DLLs that provide a standard call level interface. ODBC was written in C and is intended to be used by C programmers. Modern languages like Visual Basic® and Java have tried to hide the grungy details of dealing with handles, pointers, memory buffers, and character arrays from its programmers. When you write to the ODBC API you are expected to think and act like a C programmer, grunge included.
      As a Java programmer, you could use J/Direct™ to write Java code that talks directly to the ODBC API. If you go down this road, you will have to confront conversion issues between C and Java. For example, Java doesn't support unsigned integers, and ODBC uses ANSI strings while Java uses Unicode. While you might feel compelled to wrap ODBC calls in generic Java classes to hide the grungy stuff and improve your productivity, there is no reason to create a collection of ODBC wrapper classes when RDO already does this for you.

Object-oriented Access to ODBC
      RDO was created for one reason: to provide a set of COM objects that offer friendly object-oriented access to the ODBC API. The philosophy behind RDO is that programmers working with Visual Basic and Java are more productive and less prone to creating bugs when they use COM objects instead of a C-style call level interface. The first version of RDO shipped with Visual Basic 4.0 Enterprise Edition in 1995. Version 2.0 of RDO, which shipped in 1997, addresses enhancements made to ODBC 3.0.

Figure 2: RDO Object Model

Figure 2 shows the object model exposed by RDO. If you have used Data Access Objects (DAO), you will notice that it resembles the RDO object model. Both object models provide a top-level object that controls the database engine. DAO has a Workspace object, while RDO provides a similar rdoEnvironment object. DAO has a Database object, while RDO has rdoConnection. DAO uses the terms recordset and field, while RDO uses resultset and column. Porting code from DAO to RDO is straightforward, if tedious.       DAO is usually employed to access Jet database files, but you can also use it to communicate with DBMS hosts through ODBC. Using DAO to access a DBMS like SQL Server requires bulky, inefficient runtime components on the client. Moreover, DAO is limited in its control over stored procedures. In particular, DAO doesn't give you control over stored procedure parameters, nor does it handle error messages generated by the server. A recent release of DAO contains a new component called ODBCDirect, which simply provides an additional layer so DAO can communicate with RDO. ODBCDirect was intended for developers of Microsoft Office-based apps who do not have the licensing rights to distribute RDO to client desktops. If you can write your application to RDO, you are better off. If you have Visual J++ Enterprise Edition, you should use RDO because ODBCDirect only supplies a subset of RDO functionality and it introduces additional runtime layers. (For more information on ODBCDirect, see the November 1997 issue of MIND.) COM Programming with Java and Visual J++       Before you can do much with RDO and Visual J++, you have to come to terms with COM programming. One of the best things about COM is that it's language independent. Java/COM integration is beyond the scope of this piece, so take a look at "The Java/COM Integration Model" by Guy Eddon and Henry Eddon (MIND, April 1997) for the full story. I will summarize the most important points about writing COM client code with Java.       COM is all about interfaces. Conceptually, an interface is an agreement between a client and an object about how they will communicate. By defining a set of methods, the interface becomes a communications channel between these two parties. Syntactically, an interface is simply a collection of methods that defines the type of each argument and the return value. The interface becomes an immutable contract. In the world of COM, clients and objects communicate exclusively through interfaces. When you talk to a server component such as RDO, you will always use an interface reference to invoke methods on an object.       Although the concept behind COM is simple, the details of COM programming can be fairly complex. A client application must call native functions provided by the operating system to connect to a COM object. The application must also communicate with objects though the low-level COM binding protocol. C++ programmers can do this without any help. Visual Basic requires a mapping layer in the runtime environment and some additional support from the compiler to create COM-based components. Java requires a mapping layer in the Java virtual machine (VM) and a set of shim classes that execute the native calls into the OS. To use RDO, you need a special package of these shim classes that give your Java applications and applets access to the RDO library.       Java/COM shim classes are built with the JavaTLB.exe utility that ships with Visual J++ and the Microsoft SDK for Java 1.5. When you run this utility from the command line and pass a path to the type library of a COM component, it produces a Java package of the required shim classes for the COM objects and interfaces you need to access. These class files are stored in the Java\TrustLib path in your Windows® directory. You can run the utility from the command line, or you can run the Type Library Wizard from the Visual J++ IDE. They both do the exact same thing. The Microsoft SDK for Java 2.0 ships with a new version of this utility called JCOM.exe. JCOM improves on JavaTLB by producing the actual Java source files as well as the byte code class files. Examining the Java source files produced by JCOM and the RDO type library is an important technique because it will show you the actual argument types for RDO methods in Java code. You may find it difficult or impossible to find these signatures elsewhere. Hopefully, a future version of Visual J++ will provide Java documentation for these RDO methods.       When you install Visual J++, these RDO 2.0 shim classes are installed automatically. Your development machine will be ready to go, but it's your responsibility to make sure these shim classes and the proper ODBC and RDO components make it to the client desktop as well. If you intend to use RDO from an applet, you must also contend with security issues and the Java sandbox. The article, "Building and Distributing Java/COM Applets," by Mike Pietraszak (MIND, December 1996), covers the essential techniques for wrapping everything up in a CAB file for distribution to Web-based clients. One thing to note is that the CAB file that ships with Visual J++ for installing ODBC doesn't work correctly. Visual Studio™ Service Pack 2 has a new ODBC CAB file that fixes the problem. You must also make sure that the ODBC driver is installed on the client desktop.       Once all these components are in place, you can gain access to RDO by simply using two import statements at the top of your Java source files. One statement imports a package of all-purpose COM classes, and the other imports the package of RDO shim classes.

import com.ms.com.*; import msrdo20.*;

After importing the RDO shim class package, you can use the classes and interfaces as if they were written in Java.       If you have already done RDO programming using Visual Basic, you will find that writing the equivalent code in Java is a little more challenging. One of the bigger obstacles is dealing with the variant datatype. Variants are commonly used as argument types in COM components like RDO and DAO. Visual Basic will convert back and forth automatically whenever a variant is required. Java programmers must explicitly create variant objects and set the values before passing them as an argument. It's not that hard to do, but setting up variants makes for more lines of code.       You will find it difficult to locate adequate online help or documentation for Java/RDO programming. Even the help files that ship with Visual J++ were written for programmers using Visual Basic. These help files will tell you everything you need to know about each RDO database object and the semantics of each method, but it will not tell you the correct Java types to use when you call them.       OLEVIEW.exe is a handy COM utility that comes with Developer Studio. This tool will allow you to examine the entire RDO type library. It shows a list of methods for each interface that includes method signatures. It also lists the enumerations of RDO constants you can use. The only problem with OLEVIEW is that it uses COM's native language, IDL, to show you all this information. If you don't know how to read IDL, you might still be confused over what Java type to use in a particular method call. (Your efforts to learn IDL and OLEVIEW will make you a much stronger COM programmer.) Connecting to a DBMS       Let's look at the code behind these concepts. I'll begin by looking at the top-level object in RDO: the rdoEngine. The rdoEngine can serve as the entry point into an RDO session. You can get a reference to the _rdoEngine interface with the following code:

// create the object and cast it to get an interface reference rdoEngine objEngine = new rdoEngine(); _rdoEngine itfEngine = (_rdoEngine)objEngine; // or use the shorthand version like this _rdoEngine itfEngine = new rdoEngine();

Once your application or applet has an _rdoEngine reference, you can use it to create an rdoEnvironment object, which wraps an ODBC environment handle and provides the means for establishing a connection to a DBMS. The environment is also responsible for providing a scope for transactions. If you want to conduct two separate transactions at the same time, you must use two separate rdoEnvironment objects. You can create an rdoEnvironment by using the rdoCreateEnvironment method like this:

_rdoEngine eng = new rdoEngine(); _rdoEnvironment env = eng.rdoCreateEnvironment("", "", "");

rdoCreateEnvironment takes three parameters. The first argument is the name. Passing a name allows you to associate a unique string with the rdoEnvironment object. There usually isn't a need to associate a unique name with an rdoEnvironment object, so you can pass an empty string for this argument. (Only rdoEnvironments that have names will be appended to the rdoEnvironments collection.) The other arguments allow you to pass a default user name and password. Typically you pass these when you establish a connection so you can leave them blank as well.       After acquiring a reference to an rdoEnvironment, you can now open an ODBC connection by creating an rdoConnection object. To open an rdoConnection you must have a specific database in mind. This means that you must know which ODBC driver to use (SQL Server or Oracle, for example), where the host machine is located, and the name of the logical database within the server. There are a few options as far as how to profile this information. One way is to create a DSN on the machine that will be running the application.       The DSN can be stored in the form of registry settings or in a simple text file. User DSNs and system DSNs are both stored as collections of registry settings. User DSNs are associated with a single user and will not be available when one user logs off and another logs on. System DSNs are usually a better choice because a single DSN can accommodate any user that has logged on to the computer. The ODBC driver manager also provides a graphical utility to help create DSNs. You can run this utility by double-clicking on the ODBC icon in the control panel of any Windows system. ODBC recently introduced file DSNs, which store all settings in a simple text file on the user's computer. All three types of DSN are recognized by a logical name. When your code references a DSN, the ODBC driver manager looks through all sets of DSNs on the local machine to find a matching name.       Once you have registered a DSN, you can connect to a DBMS by invoking the OpenConnection method on a rdoEnvironment.

_rdoEngine eng = new rdoEngine(); _rdoEnvironment env = eng.rdoCreateEnvironment("", "", ""); // create Variant for passing default parameters Variant vDef = new Variant(); vDef.noParam(); // there must be a registered DSN named 'Customers' _rdoConnection conn = env.OpenConnection("Customers", vDef, vDef, vDef, vDef);

This code shows a very simple connection technique. The first argument to OpenConnection passes the logical name of the DSN and all the other arguments are set to use their default values. However, using these other arguments will give you more control over the login process. The second argument allows you to control the interactive dialogs that the user receives when connecting to a DBMS. The third argument allows you to set a read-only flag which can help to optimize a connection. The fourth argument passes a connect string, which gives you more control than merely supplying the name of a DSN. The last parameter allows you to set various connection options.       Many developers prefer to use the connect string argument instead of the DSN because it provides the ability to pass values for the user name and password to the DBMS. The following code demonstrates two calls to OpenConnection that illustrate the differences between these techniques:

_rdoConnection conn1, conn2; // use the DSN argument conn1 = env.OpenConnection("Customers", vDef, vDef, vDef, vDef); // use the connect string argument instead Variant vConnect = new Variant("DSN=Customers;UID=sa;PWD="); conn2 = env.OpenConnection("", vDef, vDef, vConnect, vDef);

Many developers are not comfortable letting users configure their own DSNs. If you don't want users messing with DSN settings you can either create a DSN programmatically with the RegisterDSN method of the rdoEngine or you can create a DSN-less connection string. The following example shows you how to connect with a registered DSN. Note that you must include the driver, the database server name, and the target database.

_rdoConnection conn; String s; s = "DRIVER=SQL Server;SERVER=Fred;DATABASE=Customers;UID=sa;PWD="; Variant vConnect = new Variant(s); conn = env.OpenConnection("", vDef, vDef, vConnect, vDef);

When you include the second argument to OpenConnection you should use the constants provided in the RDO enumeration PromptConstants. (OLEVIEW will let you examine all the RDO constant enumerations.) These constants allow you to control whether the user receives a dialog to select a DSN or a dialog to provide a user name and password for logging onto a DBMS. Some RDO applications require each user to log in with a unique user account maintained on the database server. Other applications rely on a single, generic account for every user on the system. If your application can use a single user account it eliminates the need to administer an account for each user. Whether you use a single user account or a separate one for each user, you have complete control over these interactive dialogs at connection time.       If you use the constant rdDriverPrompt, the driver manager displays the ODBC Data Sources dialog box. The connection string used to establish the connection is constructed from the DSN selected and completed by the user with the dialog boxes provided by ODBC. If you use rdDriverNoPrompt, the driver manager uses the connection information provided by the DSN and connect string arguments to establish a connection without user interaction. If sufficient information is not provided, the OpenConnection method throws a runtime exception. If you use rdDriverComplete, the ODBC driver manager only interacts with the user if the connect string doesn't supply all the needed information.       If the connect string has everything, then the connection is made without user interaction. This is the default setting for this argument. RdDriverCompleteRequired behaves like rdDriverComplete except the driver disables the controls on the ODBC dialogs for any information that is supplied by the connect string. This means any settings you pass in the connect string cannot be modified by the user.       Another option for connecting to a DBMS is to create a standalone rdoConnection object. This type of connection doesn't require you to create an rdoEnvironment object. Instead, it uses the default rdoEnvironment object in the rdoEnvironments collection. You can create a standalone connection object by using operator new. After you have created the new rdoConnection object, you must set the Connect property and execute the EstablishConnection method to connect to the DBMS. A simple example looks like this:

_rdoConnection conn = new rdoConnection(); String s; s = "DRIVER=SQL Server;SERVER=Fred;DATABASE=Customers;UID=sa;PWD="; conn.putConnect(s); Variant vPrompt = new Variant(); vPrompt.putInt(PromptConstants.rdDriverNoPrompt); conn.EstablishConnection(vPrompt, vDef, vDef); // connection now ready for use

Executing SQL Statements       Once you have successfully connected to a database within a DBMS, you can begin modifying and retrieving data. When you would like to retrieve or modify data, you must supply the SQL statements that instruct the database engine what to do. If you have used DAO and the Jet engine, you're probably used to asking for the name of a table or QueryDef, but when using RDO and ODBC everything you do will require a SQL statement. You must know enough about the SQL language to be able to insert, update, and delete rows from a table. The following SQL statement will add a new row to a table:

// assume 'conn' is an open rdoConnection // create SQL statement for inserting new row String sSQL = "INSERT INTO Employees" + " (FirstName, LastName, Title)" + " VALUES('Tim', 'Leary', 'Associate')"; // execute that statement conn.Execute(sSQL, vDef); // determine how many rows were modified int RowsAffected = conn.getRowsAffected();

The example above demonstrates a SQL statement that will modify a database. You, of course, will not hardcode the actual column values for a row into your Java code; you must parse these together at runtime. One tricky aspect of doing this is dealing with string values that have embedded single quotes. Most DBMS engines such as SQL Server use the single quote to delimit string values in a SQL statement. If a string within a SQL statement contains a single quote, the SQL engine will think this is another delimiter and will become very confused. Most DBMSs will reject a SQL statement that contains a literal string value with an embedded quote like the name O'Leary:

/* Invalid SQL statement */ INSERT INTO Employees (FirstName, LastName, Title) VALUES('Tim', 'O'Leary', 'Associate')

There are a few different techniques that developers have used to deal with this problem. The best way is to double-pack all the single quotes within string values. The DBMS engine will interpret two consecutive single quotes as a single quote. The following SQL statement will fix the problem and insert the row into the database:

/* Valid SQL statement with double-packed quote */ INSERT INTO Employees (FirstName, LastName, Title) VALUES('Tim', 'O''Leary', 'Associate')

Many ODBC and RDO developers who use this double-packing technique create a utility function that takes a string value and returns a string with single quotes at the beginning and end and double-packs any embedded single quotes. After executing a SQL statement that modifies data, you can examine the RowAffected property. This property can be used to determine if the correct number of records was inserted, updated, or deleted. Managing Resultsets       In the ODBC API, the statement object is used to submit a SQL statement. The statement is also used to manage a set of rows returned by the server. RDO hides the ODBC statement behind a set of RDO objects that yield a more intuitive programming model. We have already seen code that executed an SQL statement against an rdoConnection object. The rdoConnection object automatically creates and uses an ODBC statement behind the scenes. RDO also provides an rdoResultset object that wraps a statement object and makes it easy to manage rows returned to the client application by the DBMS.       The code in Figure 3 shows how you can create and move through an rdoResultset. When you use the OpenResultset method on an open connection, you must supply a row-returning SQL statement as the first argument. The second argument allows you to indicate what type of cursor you want. The third argument allows you to indicate the type of locking to be used. The last argument allows you to ask for various options such as asynchronous execution.       Before you select a cursor type it is important to know whether you are using server-side cursors or client-side cursors. ODBC 3.0 makes it possible to use either, but only certain level 2 drivers (like those for SQL Server) allow for server-side cursors. Your choice comes down to one question: where do you want to buffer the data? Server-side cursors use server-side resources, while client-side cursors use memory on the desktop. You indicate what you want by setting the CursorDriver property to either rdUseServer for server-side cursors or rdUseODBC for client-side cursors. You can set the CursorDriver property on either the rdoEnvironment object or the rdoConnection object. If you ask for server-side cursors and they are not available, RDO simply uses client-side cursors. rdUseODBC is obsolete in RDO 2.0. You should use rdUseClientBatch instead.       RDO 2.0 introduced a second client-side cursor library called Client Batch cursors. They can yield better performance than the old client-side cursor library. You can request these by setting the CursorDriver property to rdUseClientBatch, or you can use no cursor library at all by using the constant rdUseNone. This sounds a little strange, but it provides the fastest method for getting data to the client desktop.       Once you select a cursor library, you can select a cursor type by setting the second argument to OpenResultset. If you have instructed your connection to use no library through the rdUseNone option, you can only open read-only, forward-only cursorless resultsets. This "firehose" technique will give the best performance when you are filling a grid or a listbox. You can only execute the MoveFirst method on this type resultset. Any other navigation method, including MoveLast, will result in an RDO exception.       If you are using one of the cursor libraries, you may choose between various cursor types. Figure 4 shows a list of your choices and the constants from the ResultsetTypeConstants enumeration that you can use when you execute the OpenResultset method. There is always a trade-off between flexibility and efficiency. A forward-only cursor is very efficient, but it doesn't allow you to scroll backward. A static cursor will cache complete data rows and allow you to scroll backward and forward, but you usually cannot use it to modify data. A keyset will cache keys and allow you to scroll around and modify data, yet it will not include rows that were added after the keyset was populated. The dynamic cursor gives you everything you need, but it is costly in terms of resource usage and concurrency.       The third argument to OpenResultset allows you to indicate the type of locking you want to use. The locking scheme has a serious impact on the ability of other users to concurrently access data. By default, RDO always retrieves read-only resultsets. Figure 5 shows the different lock types you can acquire, along with the constants from the enumeration LockTypeConstants. If you use pessimistic locking, you are locking out all other users from any data in the resultset. This is harmful to concurrency and should be avoided. The two optimistic locking schemes are much more sympathetic to other users. RdConcurRowVer instructs RDO and ODBC to conduct optimistic concurrency based on a timestamp value of the record being modified. RdConcurValues will compare the original values in a row to the current values just before overwriting them. If the row values have changed, ODBC knows that the record has been changed by another user since the resultset has been opened. If this happens, the record will not be written and RDO raises an exception to let you know there has been a problem.       Most programmers do not use cursors to modify data. You should adopt the philosophy, "Get in, modify the data, and get out." Using cursors usually suggests that you want to keep the resultset open for a while. If you use INSERT, UPDATE, and DELETE statements, your impact on concurrency will be much lower, making your application far more scalable. Using Stored Procedures       Most sophisticated client-server database development is based on stored procedures. Stored procedures allow you to maintain business logic written in SQL in the DBMS instead of the client applications. The code for business logic is therefore easier to maintain and can be reused among all client applications. Stored procedures are also precompiled and cached in memory on the server, so they yield significantly better performance than the execution of raw SQL statements submitted by client applications. Furthermore, stored procedures serve as a great security mechanism. Users are given execute permissions on stored procedures, yet they have no permissions on the base tables in the DBMS. This prevents users from snooping around with an ad-hoc query tool like Microsoft Access.       Stored procedures are exposed to client applications with logical names such as AddEmployee. Stored procedures can be defined to have parameters and return values. Parameters can be defined as either input, output, or input/output. ODBC was created explicitly to manage the execution of complex stored procedures, and RDO also gives you full control over execution and parameter passing. DAO and Jet, on the other hand, have never done this well. This is why RDO is usually a much better choice for client-server development.       RDO 1.0 provided a rdoPreparedStatement object to set up the parameters for a stored procedure. RDO 2.0 has replaced the rdoPreparedStatement object with the rdoQuery object. However, the new rdoQuery object implements an interface named rdoPreparedStatement. When you communicate with an rdoQuery object, you must do it through an rdoPreparedStatement interface reference. If you ask me, the RDO team should have been consistent and named this interface _rdoQuery.       The code in Figure 6 demonstrates the Java code required to execute a stored procedure. In this code, parameters are accessed through the rdoParameters collection of the rdoQuery object. The ugly looking string in the variable vSQL is used to tell ODBC how many parameters there are. ODBC is fairly fussy about the format of the string:

"{ ? = call AddEmployee(?, ?, ?) }"

Each question mark in the string will become part of the rdoParameters collection. If you want to use a return value, it must be the first parameter in the collection. Each parameter has a Direction property that defaults to rdParamInput. If your parameters aren't designed for input parameters, you must change them before executing the procedure. It's pretty simple once you set up the parameter directions. You assign a value to input parameters, execute the procedure, and inspect the values of output parameters and return values. RDO Exception Handling       ODBC functions use their return value to indicate that a call was unsuccessful. When you code directly against the ODBC API you should look at the return value of each function. This is tedious and most programmers get in the habit of ignoring these return values, which creates bugs that are hard to find. RDO comes to the rescue by examining the ODBC return values for you. RDO methods will throw an exception when an underlying ODBC call is unsuccessful. This makes handling errors much more Java-like. However, to deal with these RDO exceptions correctly you must know how to deal with an exception thrown from a COM component. The code in Figure 7 demonstrates how you can create an all-purpose RDO exception handler.       When an ODBC error occurs, RDO throws a COM-based exception to your application or applet. RDO uses the COM all-purpose error code, also called an HRESULT. Microsoft created a Java class named ComException to deal with the exceptions and HRESULTs thrown by COM servers. There are also two classes, ComFailException and ComSuccessException, that derive from ComException. The HRESULT is a 32-bit value, but the RDO error code is stored in the lower 16 bits. The highest bit in an HRESULT signifies success or failure. The other bits in an HRESULT contain information important to COM in out-of-process communications. You can extract the RDO error code by conducting a bitwise AND between the HRESULT and the number 65535. The number 65535 represents the binary integer with all bits on from 1 to 16.       You can also use the rdoError object and the rdoErrors collection to get more information about the exception. These RDO objects hide the messy details of HRESULTs from your Java code. The rdoError object provides a Description property that allows you to retrieve the RDO error string associated with the exception. Figure 7 demonstrates how to set up your exception handler and determine what has gone wrong. RDO exceptions may originate in the ODBC Driver manager, the ODBC driver, or the back-end DBMS. The description will usually tell you where the error occurred and what went wrong. The rdoError object makes it very easy to catch and deal with custom errors that are generated by stored procedures. The rdoError object can also tell you the SQLRetCode and SQLState.       You will find that writing such handlers is essential in debugging as well as in production code. Whenever you are writing a line of RDO code that has a chance of failing, you should place that code in a try…catch block with an adequate exception handler. Where to Go from Here       This article was intended to get you started with RDO programming using Visual J++. In the space available, I was only able to scratch the surface of a complex and powerful API. Another powerful feature of RDO is the ability to execute asynchronous queries. When you execute a query asynchronously, your calling thread will not be blocked while you are waiting for the DBMS to process your request. You can use the rdAsynchEnable option flag when you connect to a database or execute a SQL statement or stored procedure.       RDO 2.0 is also capable of raising events to inform you when an asynchronous task is done. Unfortunately, Java doesn't know how to handle COM-style events thrown by automation servers yet. Hopefully, Microsoft will implement this behavior into a future version of their Java compiler and VM. You can still conduct asynchronous queries with RDO and Visual J++. RDO also exposes a few properties that you can periodically poll to see if an asynchronous task has completed. You can check the StillConnecting or StillExecuting properties of a connection, query, or resultset to see if the server is done.       Finding resources to help you learn how to write RDO programs with Visual J++ is still difficult. One excellent resource is Bill Vaughn's Hitchhiker's Guide to Visual Basic and SQL Server (Microsoft Press, 1997). Make sure to get the 5th edition, which covers RDO 2.0. This book is written for Visual Basic-based programming, so you'll have to bridge the gap between his Visual Basic code and the equivalent Java code.

From the February 1998 issue of Microsoft Interactive Developer.