by Kevin D. Runnels
Over the past few years, the term client/server computing has changed substantially since it was first introduced. Chapter 10, "Clients, Servers, and Components: Web-Based Applications," gives a complete overview of the newer uses of this term.
This chapter focuses on the classic, two-tier client/server paradigm. Also, the server component described in this chapter is Microsoft SQL Server, which ships with the Enterprise Edition of Visual Studio 97. Although the concepts and examples given in this chapter are built around Microsoft SQL Server, they also apply generally to other database servers. Keep in mind, however, that even though the standardization developed around Structured Query Language (SQL) and Open Database Connectivity (ODBC) has made development for diverse database platforms much easier, you'll still find subtle differences in feature implementation and non-conformance to published standards (typically referred to as enhancements) that occasionally will cause problems. You can gain access to information on the database server from a VB program in many ways:
Today, the most complete and robust model for use in the traditional client/server architecture when accessing a database is the Remote Data Objects (RDO) object model. This chapter introduces you to some of the key concepts necessary to understand RDO, as well as shows you the details of the RDO object model and how to use it successfully in your Visual Basic applications. n
The classic two-tier definition of client/server computing essentially describes a process model where a single process is distributed along functional lines between two separate computers. Each computer communicates over a network with the other to coordinate the execution of the process. The roles carried out by each computer are described as a client or server process.
The client computer sends requests to the server, and the server carries out the requests. The request could be for the server to retrieve some information and send the information back to the client computer. The request could also be for the server to carry out some action on behalf of the client and to notify the client when the action is complete.
This model is seen within networks where print servers receive data from client computers and print the data on behalf of the client. Some other instances of this type of model are as follows:
Figure 22.1 shows an example of client/server architecture.
FIG. 22.1
A typical client/server application architecture consists of three distinct processing
entities.
As you can see, a common process division in application development is to segment the process into functional components referred to as the Data, Rules, and Presentation components:
Because the division of the process typically places more of the processing intensive burden on the server component, a more powerful computer is used on the server side. The server could be a large multiprocessing machine running Microsoft Windows NT Server, or perhaps a machine running a UNIX derivative. At any rate, the server platform will be running some type of database server software, the most popular type of which is the relational database management system (RDBMS). An RDBMS such as SQL Server stores data in tables and columns and relates the rows of one table to those of another by using joins.
NOTE: A full discussion of the relational data model is beyond the scope of this chapter. It's ass-umed that you understand the basic design issues involved when using an RDBMS such as data integrity, data normalization, joins, indexing, and so forth. For more information regarding RDBMSs and design issues, see Chapter 3, "Creating Database-Aware Applications with Visual Studio."
The following sections discuss these important server responsibilities: data management, security, error handling, and query processing.
Data Management The server takes responsibility for the integrity of the data it stores. If your client application attempts to enter data into a table column that has been defined as a numeric field with a value such as Laurie Ann, an error will be raised and the information won't be written into the table. The server is also responsible for maintaining relationships between defined tables. For instance, if a table has been defined with an EmployeeID field that's a foreign key to the Employee table, you won't be permitted to insert data that doesn't have a matching EmployeeID from the Employee table. Also, you can design triggers that "fire" when you add or change data in a table; these triggers will run special stored procedures to perform complex user-defined error checking and validation.
Another aspect of data management handled by the server is concurrency. Because many users are accessing the database at the same time, the server must be able to handle potential conflicts. The database typically handles concurrency by using transactions.
Security The server restricts access to the database based on user IDs and passwords. This access mechanism permits various degrees of access depending on how your account was set up on the server. You might have access to only some of the tables in the database. Your access might include only certain columns from tables in the database. You might even have no permissions at all on tables, but are restricted to accessing the data based on predefined views. The server also keeps track of all the activities against the data in the database and stores them in a transaction log for possible later review by a utility program.
Query Processing One primary responsibility of the server is to process requests that your client program sends to it. The server will accept the request that you give and analyze it to see how it can be most effectively executed. This will involve syntax checking, analyzing for redundancies, determining what indexes can be used, and so on. The server might select information and return it to the client, or it might execute some action on the data in the database without sending information back to the client. Stored procedures, which are like small programs with several processing steps to be executed, can also be executed on the server. Also, the server might have to keep track of cursors if you've opened up a resultset by using server-side cursors.
The client program has to handle certain responsibilities, no matter which data access method is used. These responsibilities include initializing the data access interface, making the connection to the server, sending requests to the server, responding to server errors, manipulating the results of a query, presenting query results, and closing the database connection.
These key responsibilities, discussed in the following sections, are just the specific responsibilities that deal with being a client process to the server. Of course, your program will have myriad other responsibilities that can include execution of business logic ("rules"), error handling, additional security, and so on.
Initializing the Data Access Interface The client program initiates communication with the database server. To initiate communication, the client program has to set up the interface to the server, a task commonly referred to as establishing a connection to the database. All communication to the server takes place through this connection. Different aspects to this connection must be initialized to provide the ground rules for the communication that will take place. This includes establishing timeout values for connections and queries, and identifying the cursor library to be used.
Sending Requests to the Server When communication is established with the server and the data access interface is initialized, data access and manipulation requests can be sent to the server. These requests can be for the server to perform some action on behalf of the client, such as deleting rows that meet a specific criteria. The request might instruct the server to identify rows that meet a specific criteria and return those rows to the client process in the form of a resultset.
Responding to Server Errors Occasionally, the server might be unable to carry out instructions sent by the client process. The problem could be a lost connection, or perhaps the instruction to modify data can't be completed because another process had locked the data for its own purposes. It's important to identify where errors are likely to occur in the client process due to common server errors and then gracefully trap those errors. Unhandled errors are ugly and frequently fatal to the client program.
Manipulating Resultsets When a query has instructed the server to return rows of data in a resultset, the client program can then edit the resultset. Depending on the cursor type being used, the amount of time spent manipulating a resultset can adversely affect data concurrency in a multiuser environment. As a result, minimizing the amount of time the client holds open a resultset and cursor is usually a good idea.
Presenting Query Results Because the server simply responds to requests made by the client program, the client program takes all responsibility for presenting the results of the data access to users. In some instances, using data-bound controls might provide an effective simple solution. Most likely, however, the client program will need to deal with storing information returned by the server in resultsets by transferring the data to a grid, list box, text box, or a specially created object. Again, concurrency issues involved with maintaining an open resultset most likely will dictate how the information is presented to users.
Closing the Database Connection After the client program completes its data-manipulation functions, the connection to the database is closed.
No matter which data access method is used, certain concepts apply equally across those methods. The following sections cover these concepts, which include synchronous versus asynchronous operations, row-returning queries, action queries, stored procedures, standard Windows Data Source Names (DSNs), and the various cursor types.
You can establish connections and execute queries synchronously or asynchronously. Synchronous queries execute on the database server, and your program blocks or waits for the server to return a response or timeout. Asynchronous queries continue processing while the server works on returning data, and the application must check to see whether the processing is complete, or respond to the QueryComplete event. Asynchronous queries provide for a more responsive user interface but require slightly more complex programming. Programmers must make sure that the query has executed and returned a resultset, for instance, before attempting cursor operations on the resultset.
TIP: If you want to code for events within the RDO library, you must use the withevents keyword when declaring your RDO object. See the Visual Basic online help for more information regarding event usage within your Visual Basic program.
Queries can return information from the database or instruct the database to perform some action on the data on behalf of the client program. Information is returned from the database in the form of resultsets. These resultsets can be the results of dynamic SQL constructed by the client program, or it could be the result of a predefined query or stored procedure. Because action queries don't return resultsets, they are commonly used to update or delete rows in the database.
Stored procedures are compiled collections of SQL statements stored and executed by the database server that are called by a client program. Stored procedures allow user-declared variables, conditional logic, cursors, and other powerful programmatic features. Because stored procedures are compiled and executed on the database server, they're very fast. They can also accept parameters, call other stored procedures, and return information in the form of return codes, resultsets, or output parameters.
Use stored procedures whenever possible because of the speed advantages. You sometimes can also use them to implement business logic that's more accessible and easier to modify than your Visual Basic program. Stored procedures can be changed to provide different information to clients without forcing code changes and recompilation of the client program.
Data Source Names (DSNs) are used as sources of connection information--they describe the connection and its options. The DSN information is stored in ODBC.INI or in the Registry, depending on which version of Windows is used. When ODBC needs to make the connection, connection information can simply be retrieved from the DSN rather than all the detailed connection information be specified again. DSNs aren't required to be used, however, and all the detailed connection information can be specified at runtime by using a non-DNS connection strategy.
Cursors provide a mechanism for maintaining a "current row" in a resultset. This concept harkens back to file-based ISAM systems, where you maneuvered through a file based on the file pointer. Several common types of cursors include forward-only, static, keyset, and dynamic, which are all contained in the standard ODBC cursor library. VB5 and RDO2 have introduced a new client batch cursor library that has some real advantages over the older ODBC library.
The most important thing to understand about cursors--at least the cursors as defined by the standard ODBC library--is that you should avoid them whenever possible. They're extremely expensive in terms of system resources and are very slow. Cursors allow you to access data in a row-by-row format, which is counter to the set-based processing nature of SQL. If you find yourself in a position where you're about to use a cursor, stop and rethink your design and consider how to accomplish what you need to do without using a cursor. Nevertheless, you might find a situation where the only viable answer is to use a cursor. Occasionally, you might need to execute a series of complex function calls and processing steps for each row of a resultset. Cursors allow you to keep track of which row of the resultset on which it's positioned.
TIP: In situations where you feel you absolutely have to use a cursor, consider a couple of alternatives:
- Copy the data in the resultset to a temporary table and cursor through the data in the new temporary table. Because the temporary table is visible only to your application, you won't have to worry about contention issues with other users.
- Read the data with a forward-only cursor (the fastest cursor type available) and load the data returned into an object. When the data is loaded, write your own logic to traverse a "current pointer" through the object and issue dynamic SQL against the database if changes to the data are required. This is faster and causes less database contention with other users.
The following section discuss various cursor types and some pros and cons of using them.
Forward-Only Cursors Forward-only cursors are less painful to implement than the other standard cursor types. These cursors aren't scrollable, meaning that you can't freely move forward or backward within the resultset; you can only move forward (hence the name). These cursors are sometimes updatable, but not always.
Keyset Cursors Keyset cursors store pointers to the rows in the tables you're accessing and not the actual data. You're free to move forward or backward through these pointers, meaning that you're essentially reselecting each row of the resultset as you move through your cursored resultset. This also means that the data your cursor points to is visible to other database users. As you move through your resultset, you'll see any modifications to the data that other users have made. If another user deletes a row that you've contained in your cursor, a trappable error results. If a user adds a row that would have been included in your resultset when you created the cursor, you won't see the added data because the rows pointed to are "fixed" at the time the cursor was opened.
Snapshot/Static Cursors Static cursors, unlike keyset cursors, move all the data that you've selected to your client. As you move through the resultset, the data you see is coming from the cached copy of the data created on your computer. As a result, you won't see any changes made by any other database users. You've created a snapshot of the data at the specific time you created the cursor. Static cursors are sometimes updatable, depending on the implementation of the device driver.
Dynamic Cursors Like keyset cursors, dynamic cursors store pointers to the rows contained in your resultset. Unlike keysets, their membership isn't fixed, and the resultsets shrink or grow depending on the actions of other users. They requery the server as you move through the resultset so that you have the most recent updates and membership information. As you can guess, this is extremely resource-intensive, and you should limit its use accordingly.
RDO2 offers a new and different approach to editing data that promises to be very useful in helping reduce data-contention issues. The new batch cursor library and optimistic batch updates allow your application to be connected to the data source only while it's actually retrieving or sending information to the server. If this sounds similar to a web page access metaphor, where the browser client is connected to the web server only while actually retrieving a web page, you have the right picture.
VB5 and RDO2 have introduced a new cursor library, the client batch cursor library, that provides more flexibility in environments where data concurrency is an issue and offline updates to data can be made and "posted" to the database all at once. This diminishes problems associated with database locks in a multiuser environment.
In the past, VB programmers frequently implemented this type of data access approach by querying the database to acquire a resultset, and then immediately copying each row of the resultset into an object collection that mimicked the structure of the original resultset. Once the collection was built, the original resultset was closed and any database locks were released. Changes then could be made to the data contained in the collection, and updates to the data would be made by using action queries against the database as the data changed, or when the changes that were being made to the data were complete. Although effectively minimizing database contention, the construction of the collection and creation of the SQL contained in the action queries created substantial programming overhead.
The introduction of the client batch library in VB5 means that this work is done for you automatically. Essentially, the client batch library allows for the creation of resultsets that can be dissociated from their connection to the database. After the resultset is dissociated, it can be edited by using the standard RDO Edit method. When the edits are complete, the resultset is reassociated to the connection to the database, and a BatchUpdate method causes all the edits to be made against the database.
Dissociated resultsets and optimistic batch updates are totally new to VB5 and RDO2. This new concurrency option is extremely useful when the data being manipulated is unlikely to be modified by another user (such as batch-oriented data).
Assume that the data being accessed is batch-oriented data, and more than one person can't edit a batch at the same time. A resultset can be opened against a specific set of data based on BatchID, but data from another batch could be adversely affected because of the page-locking mechanisms used by some database server. SQL Server locks pages in 2KB increments. If your batch data makes up 11KB of data, you'll still set a page lock on 12KB to edit your data. This means that 1KB of data that doesn't belong to your batch is also locked. In the past, programmers would frequently create a forward-only read-only cursor, quickly read in the data, and load a special data structure that mimics the layout of the resultset. As soon as the structure was loaded, the resultset was closed. Any edits to the structure resulted in SQL Action queries to update the remote database.
The rdUseClientBatch cursor library, written as a replacement for the ODBC cursor library, can relieve much of the drudgery from the process of creating and loading special data structures to release any locks on the server. Using dissociated rdoResultsets and the rdUseClientBatch library allows users to retrieve the rdoResultset and then disconnect from the database without losing the ability to edit the data in the rdoResultset. Changes made to the resultset use the standard RDO Edit, AddNew, and Delete methods and a special BatchUpdate method to commit all the changes en masse after the rdoResultset is reconnected to the data source.
The approach in using the optimistic batch update features is very similar to standard data manipulation using RDO, but a few things need to be done a little differently to be successful. The following steps outline what's necessary to take advantage of this new feature:
Although RDO is fairly new, it relies on an old standby to accomplish much of its work. The RDO object hierarchy is sometimes referred to as a "thin wrapper" over the ODBC API. Thus, some people claim that RDO is slower than using direct ODBC calls. This is true in the absolute sense, but isn't really noticeable. Also, the ease of use in accessing the different features provided by ODBC when using RDO saves development time and results in code that's easier to maintain and debug.
Using servers that utilize products from different manufacturers has been made easier because of the industry acceptance of several standards set forth by Microsoft. Examples of these interfaces include Mail Application Programming Interface (MAPI), Telephony Application Programming Interface (TAPI), and ODBC. All these standards commonly provide a uniform interface that allows a client process to work transparently with disparate server products. The MAPI interface standard supplies a generic interface to use many different mail servers, such as Microsoft Exchange and Lotus cc:Mail. The TAPI interface standard provides uniform access to many different types of telephony equipment. The ODBC standard provides a generic approach to data access.
ODBC is implemented as a two-layer interface between the client and the database server. The first layer, to which your client application interfaces, is the Driver Manager. The Driver Manager loads the appropriate database driver, initializes the interface, provides an interface to the ODBC features that the database driver will support, and does some error checking and parameter validation. The second layer of the ODBC interface is the actual database driver, in the form of a vendor-specific DLL, that communicates with the database server. The vendor- specific ODBC driver is responsible for implementing as many ODBC functions as the server natively supports, but it can also include extra code to provide for ODBC-specific functionality itself if the database server doesn't support some particular features. The driver is also responsible for establishing connections, submitting requests to the server, returning information from the server, managing transactions, handling server-side cursors, and providing for error checking.
Although ODBC provides a common API, not all ODBC drivers provide all ODBC functionality. There are conformance levels for drivers that categorize them as providing core, level 1, or level 2 services. The drivers also must support SQL grammar and are categorized by the degree to which the full SQL grammar is supported. Figure 22.2 shows the different conformance levels and their corresponding feature requirements.
FIG. 22.2
To be fully compliant with their conformance level, ODBC drivers frequently implement
some additional functionality that the data source doesn't implement natively.
ODBC makes it possible for a single client application to use many different kinds of database servers. The problem for VB5 developers is that the ODBC API was designed primarily for interfacing to programs written in C/C++. Although you can directly call ODBC API functions from within VB5, it's a skill that requires a high level of expertise and a high tolerance for general protection faults (GPFs). Microsoft has developed a Component Object Model (COM) interface for use by VB developers that acts as a wrapper around ODBC. This COM interface, referred to as Remote Data Objects, is now in its second incarnation with VB5 and is commonly called RDO or RDO2. The RDO2 method of database access is the most commonly used client/server access method used by VB5 developers and offers high performance as well as an easily accessible interface to the database server. Figure 22.3 shows the relationship of the RDO2 library to the underlying ODBC layer.
FIG. 22.3
RDO2 encapsulates ODBC in a COM object so that ODBC's functionality can be made available
to the broadest range of Microsoft language products.
In Figure 22.4, the RDO2 object library is implemented as a hierarchy, with the rdoEngine as the topmost object. With the exception of the single rdoEngine object, all the other objects actually exist in collections. The rdoResultset object is a member of the rdoResultsets collection, etc. This collection structure was first introduced in VB4 with RDO1. In addition to this behavior, RDO2 also now allows for standalone objects that aren't part of a collection.
FIG. 22.4
The RDO2 object hierarchy is an abstract model of a data source and provides a COM
interface to manipulating the data source.
NOTE: Before the RDO2 library can be addressed from within your VB code, remember to add the library to your current project. Choose Project, References, and select Microsoft Remote Data Objects 2.0 from the list.
The RDO object hierarchy is rather large, with several high-level objects with many methods, events, and properties. The Visual Basic online help system provides excellent topical access to individual features and elements of RDO. It doesn't, however, provide convenient access to learn when you aren't sitting at your computer with a specific question in mind. The rest of this chapter gives you a complete breakdown of the RDO2 object library and offers a look at each object and its associated methods, events, and properties, with some examples of how they're used. If you need more information regarding a specific topic, check the Visual Basic online help system, using the topic that you have a question about as the search key.
Methods: rdoCreateEnvironment, rdoRegisterDataSource
Events: InfoMessage
Properties: rdoDefaultCursorDriver, rdoDefaultErrorThreshold, rdoDefaultLoginTimeOut, rdoDefaultPassword, rdoDefaultUser, rdoLocalID, rdoVersion
The top object in the RDO2 hierarchy, the rdoEngine is an abstract representation of the remote data source. The rdoEngine isn't declared and isn't explicitly instantiated from within VB code. Those steps are handled for you the first time an rdoEnvironment object is created. The properties of the rdoEngine object that begin with the word rdoDefault provide the default settings for newly instantiated rdoEnvironment objects.
As a COM object, the rdoEngine object supports various methods, events, and properties. All other RDO objects are a component of the rdoEngine.
The rdoCreateEnvironment Method
Set MyrdoEnv = rdoCreateEnvironment ( Name, User, Password )
The rdoCreateEnvironment method is used to create a new rdoEnvironment object and add it to the rdoEnvironments collection. There will always be a default rdoEnvironment with a collection index of 0 created when the rdoEngine is first initialized. If you need to add more rdoEnvironment objects--perhaps to manage transaction scopes--you can use this method to create the object. The Name, User, and Password arguments are all required arguments.
The rdoRegisterDataSource Method
rdoRegisterDataSource DSN, Driver, Silent, Attributes
The rdoRegisterDataSource method is used to store information about a connection in the Windows Registry. DSN is a descriptive string that describes the data source. The Driver argument specifies the name of the ODBC driver. Silent is a Boolean value that enables or suppresses the display of the ODBC dialog boxes. If Silent is True, the Attributes property must contain all the vendor-specific ODBC keyword attributes as specified by the ODBC vendor.
The InfoMessage Event The InfoMessage event is fired when informational messages are received from the ODBC Driver Manager. The messages are stored in the rdoErrors collection.
The rdoDefaultCursorDriver Property The rdoDefaultCursorDriver specifies the cursor library to implement by seeding the CursorDriver property of rdoEnvironment objects. The valid settings are rdUseIfNeeded, rdUseODBC, rdUseServer, rdUseClientBatch, and rdUseNone. rdUseIfNeeded is the default setting, and RDO will choose what it feels is the most appropriate driver for the cursor. rdUseODBC uses the ODBC cursor library.
CAUTION: The ODBC cursor library is notorious for leaking memory. Use it at your own risk.
ON THE WEB:Check http://www.microsoft.com for new updates to the ODBC cursor library that can solve some of the problems.
rdUseServer directs the rdoEnvironment to use server-side cursors. A server-side cursor is available for SQL Server but might not be available for your database.
New to RDO2, rdUseClientBatch is a cursor library that allows batch-mode operations and dissociated rdoResultsets. This cursor library is extremely useful in many situations and was covered more fully in the earlier section "VB5 and the Client Batch Cursor Library." rdUseNone doesn't create a cursor at all, but provides the functionality of a forward-only, read-only resultset.
The rdoDefaultErrorThreshold Property The rdoDefaultErrorThreshold property exists only to provide backward compatibility with RDO1. The property specifies a default value for the ErrorThreshold property for rdoQuery objects. It allows you to specify a value for non-fatal messaging from stored procedures. RDO2 uses the InfoMessage event for this functionality.
The rdoDefaultLoginTimeout Property The rdoDefaultLoginTimeout property specifies how long to wait while a connection is being established before an error is generated. This property seeds the LoginTimeout property of rdoEnvironment objects. Don't confuse this with the QueryTimeout property of an rdoConnection object, which specifies how long to wait for a resultset to be returned from a query. The rdoDefaultLoginTimeout is specified in the number of seconds to wait; 15 seconds is the default if no value is specified. When the time has expired, the rdoEnvironment's ConnectionTimeout event will fire. To cancel the timeout behavior, specify a value of zero to have the rdoEnvironment wait forever.
The rdoDefaultUser and rdoDefaultPassword Properties The rdoDefaultUser and rdoDefaultPassword properties seed the rdoEnvironment object's UserName and Password properties. Setting these properties in code is a good idea if security isn't a major concern, or if the account you specify has limited access rights on the database. You'll almost always explicitly set these properties when creating your rdoConnection object. The default property values are a zero-length string ("").
The rdoLocaleID and rdoVersion Properties The rdoLocaleID specifies the language to use when generating RDO error messages and is defaulted to the Windows system locale. The rdoVersion property returns a value that represents the version of the RDO library in use. The following code instantiates the rdoEngine and sets various properties:
Dim MyRdoEnv as rdoEnvironment `Automatically instantiates rdoEngine object
With rdoEngine
.rdoDefaultLoginTimeout = 30 `Sets timeout on login to 30 seconds
.rdoDefaultCursorLibrary = rdUseNone `Specifies forward-only, read-only
`functionality
.rdoDefaultUser = "Default" `Specifies default user, should be
`low security account
.rdoDefaultPassword = "LowSecurity" `Specifies the default password
End With
Methods: None
Events: None
Properties: Description, HelpContext, HelpFile, Number, Source, SQLRetcode, SQLState
The rdoErrors collection is a property of the rdoEngine object. This collection of rdoError objects acts as a repository of errors and informational messages returned from ODBC. When an operation generates an error or a series of errors, they're added to this collection. If another operation generates an error, the prior errors are automatically cleared.
Not all errors are fatal; many times messages or warnings are generated from stored procedures and are captured in this collection. The rdoEngine InfoMessage event is fired when one of these messages arrives, and you can examine the message in the rdoErrors collection. This collection automatically prioritizes its members from the most detailed to the most general. This means that the most detailed error will be placed at rdoErrors(0) and the most general error will be last in the index. The rdoError object has no methods or events, but consists of a set of properties as shown in the following sections.
The Description Property The Description property is a string that describes the error or informational message.
The HelpContext and HelpFile Properties The HelpContext property specifies a Long value that corresponds to a Help topic in a standard Windows Help file. The HelpFile property is a string that specifies the name of the help file to be used.
TIP: Help files and error handling are commonly the last things added to a program but are some of the most important aspects of programming. If your practice is to add error-handling code after a routine is written, at least comment the code to remind yourself of errors that need to be trapped. Maintenance programmers will also appreciate this practice.
The Number Property The Number property is error number returned from the database server or ODBC. This number can represent a built-in error, such as a type mismatch in a convert function in a stored procedure. The error number can also be generated by design from within a stored procedure by using a mechanism such as the RAISERROR command used by Microsoft SQL Server and Sybase.
The Source Property The Source property is a string value that identifies the source of the error. Errors beginning with MSRDO20 occurred within RDO. Other strings are unique to the application/class that generated the error.
The SQLRetCode Property SQLRetCode returns a Long value that indicates the return code from the last RDO operation. Possible values are rdSQLSuccess, rdSQLSuccessWithInfo, rdSQLNoDataFound, rdSQLError, and rdSQLInvalidHandle.
The SQLState Property The SQLState property is a string value that represents a type of error as defined by the X/Open and SQL Access Group. It's rather cryptic, consisting of a two-character class value and a three-character subclass value. Because this property is rather arcane, it's doubtful you'll use it under most circumstances. For now, rely on the SQLRetCode, Number, and Description properties for information regarding your rdoErrors.
Methods: BeginTrans, Close, CommitTrans, OpenConnection, RollbackTrans
Events: BeginTrans, CommitTrans, RollbackTrans
Properties: CursorDriver, hEnv, LoginTimeout, Name, Password, UserName
The rdoEnvironments property is a collection of rdoEnvironment objects. The first entry in the collection, rdoEnvironments(0), is created automatically. The BeginTrans, CommitTrans, and RollbackTrans methods allow fine control over synchronizing different data-manipulation operations that span different connections in the same rdoEnvironment. For example, you could invoke the BeginTrans method of an rdoEnvironment object, and then issue an update command against a table on one connection and a delete command against a table in another connection. If those two sequences were logically tied together and you encountered an error during one of the sequences, you could invoke the RollBack environment method to cancel both operations.
The transaction methods also fire BeginTrans, CommitTrans, and RollbackTrans events with RDO2. Those events fire immediately after the respective method completes its operation and are used primarily to synchronize other processes.
Before RDO2, this collection was the sole source of connection objects via the OpenConnection method. As the OpenConnection method is invoked, a new rdoConnection object is created and added to the rdoConnections collection. By specifying a zero-length string for the Connect string, the rdoConnection object will use the rdoEnvironment's default user name and password. You must, however, specify a Data Source Name (DSN) if you use this technique.
The BeginTrans Method The BeginTrans method has no arguments and acts as a marker that identifies where a transaction begins. SQL statements executed after BeginTrans is invoked are part of a single transaction that can be committed or rolled back.
The Close Method The Close method has no parameters and can be used to close an rdoEnvironment object. This method will also close any rdoConnection objects belonging to the rdoEnvironment. The default rdoEnvironments(0) can't be closed.
The CommitTrans Method The CommitTrans method has no parameters. It's used to commit a transaction and cause the actions taken by the SQL contained in that transaction to be applied against the data source. By definition, the transaction is concluded when this method is invoked, and can't be rolled back.
The OpenConnection Method
Set MyConnection = MyrdoEnv.OpenConnection(DSN[,Prompt[, ÂReadOnly[,Connect[,Options]]]])
The OpenConnection method opens a connection and creates an rdoConnection object.
The DSN argument is a string that specifies the name of a registered ODBC data source name. This argument can be a zero-length string if the optional parameters are provided.
The optional Prompt parameter has a default value of rdDriverComplete, which means that if the DSN argument is present, use the Connect argument to connect to the data source without displaying the ODBC Data Sources dialog box. The value of rdDriverPrompt would specify that the ODBC Data Sources dialog box be displayed. RdDriverNoPrompt specifies that the ODBC Data Sources dialog box not be displayed and that the values from DSN and Connect are used. RdDriverCompleteRequired specifies that the same behavior as rdDriverComplete is used, except that the controls in the ODBC Data Sources dialog box are disabled for any information not required to complete the connection.
The RollbackTrans Method The RollbackTrans method specifies that the SQL statements since the last BeginTrans method was invoked should be rolled back, or canceled.
The BeginTrans, CommitTrans, and RollbackTrans Events The BeginTrans event is fired after the BeginTrans method is completed. The CommitTrans event is fired after the CommitTrans method is completed. The RollbackTrans event is fired after the RollbackTrans method is completed.
The CursorDriver Property The CursorDriver property specifies what type of cursor to use against the data source:
The hEnv Property The hEnv property returns a Long that's the ODBC environment handle. This isn't needed for RDO2 programming, but can be used if you're making direct ODBC API calls.
The LoginTimeout Property The LoginTimeout property specifies the amount of time, in milliseconds, that the rdoEnvironment object uses to seed the rdoConnection.LoginTimeout property. This will specify how long the rdoConnection object waits for success when attempting to establish a connection.
The Name Property The Name property specifies the name of the rdoEnvironment.
The Password Property The Password property is the default password to be used by subsequent rdoConnection objects. Remember the security concerns in supplying a default user and password.
The Username Property The Username property is the default user name to be used by subsequent rdoConnection objects. Remember the security concerns in supplying a default user and password.
Methods: BeginTrans, Cancel, Close, CommitTrans, CreateQuery, EstablishConnection, Execute, OpenResultset, RollbackTrans
Events: BeforeConnect, Connect, Disconnect, QueryComplete, QueryTimeout, WillExecute
Properties: AsyhcCheckInterval, Connect, CursorDriver, hDbc, LastQueryResults, LoginTimeout, LogMessages, Name, QueryTimeout, rdoQueries, rdoResultsets, rdoTables, RowsAffected, StillConnecting, StillExecuting, Transactions, Updateable, Version
By using the rdoEnvironment object's OpenConnection method, you establish an open connection and add an rdoConnection object to the rdoEnvironment's rdoConnections collection. The newly created rdoConnection object inherits the parent rdoEnvironment object's CursorDriver and LoginTimeOut values. If a zero-length string was used in the ConnectString argument of the OpenConnection method, the parent rdoEnvironment's UserName and Password values are used.
A new feature of RDO2 now allows you to create an unopened connection by instantiating the rdoConnection object with the New keyword. After the object is instantiated, you can use the EstablishConnection method to open the connection. With this technique, the created rdoConnection object isn't part of any collection. This allows you to break the hierarchical nature of the RDO library somewhat to create "free-standing" connection objects. As you've probably noticed, many methods and properties are duplicated between parent and child objects in the hierarchy. By allowing you to create a standalone "child" object, you still have the functionality you need without the overhead of the parent object. The standalone rdoConnection object also provides a convenient connection source when used with optimistic batch updates and the batch cursor library.
The following code is an example of how to use a standalone rdoConnection object:
Dim myRdoC as New rdoConnection
Dim ConnectString as String
ConnectString = "UID=Laurie;PWD=Sister;Database=Sales;Server=EntServ; Driver={SQL Server};DSN="";"
With myRdoC
.Connect = ConnectString
.LoginTimeout = 30
.CursorDriver = rdUseNone
.EstablishConnection rdDriverNoPromopt, False, False
end with
The BeginTrans, CommitTrans, and RollbackTrans Methods The BeginTrans, CommitTrans, and RollbackTrans methods provide client-side transaction management in a similar way as the same methods of the rdoEnvironment object, except that at the rdoConnection object level the methods affect only the rdoQuery and rdoResultset objects instantiated under the rdoConnection. The BeginTrans, CommitTrans, and RollbackTrans methods have no parameters.
The Cancel Method The Cancel method, when used at the rdoConnection object level with rdAsynchOption enabled, allows you to cancel the attempted asynchronous connection to the server before it's completed.
The Close Method The Close method of the rdoConnection object closes the connection to the server and destroys any child objects, such as rdoResultsets objects, that were instantiated under the connection. If the rdoConnection object is a member of an rdoEnvironment object's rdoConnections object, the rdoConnection object whose method is invoked is closed but not removed from the collection. You must use the rdoConnections collection's Remove method to remove the object from the collection.
The CreateQuery Method
MyRdoC.CreateQuery QueryName[, SQLString]
This method creates a new rdoQuery object and adds it to the rdoQueries collection. Like dissociated rdoResultsets, rdoQuery objects can also be instantiated as standalone objects with the New keyword. The rdoQuery object created with the CreateQuery method provides a mechanism for using stored procedures and accessing input/output parameters.
The required QueryName parameter specifies the name of the query. The name acts as a key to reference the specific rdoQuery object in the rdoQueries collection. Input/output parameters associated with the query are stored in the rdoParameters collection of the rdoQuery object.
The optional SQLString parameter specifies the SQL query for the new prepared statement.
The EstablishConnection Method
MyRdoC.EstablishConnection [Prompt][, Readonly][,Options]
The EstablishConnection method is used to connect to the data source. It can also be used when reconnecting an existing rdoConnection object that was disconnected through the Close method. The EstablishConnection method can also be used to create standalone rdoConnection objects. The EstablishConnection method doesn't automatically add a rdoConnectionObject to the rdoConnections collection, as does the rdoEnvironment object's OpenConnection method.
The optional Prompt argument acts the same way as the rdoEnvironment's OpenConnection method by specifying how the ODBC Driver Manager manages prompting for missing connection information. The optional Readonly argument is a Boolean that can be set to True to enable read-only access. The Options argument is the same integer as the rdoEnvironment OpenConnection Options argument.
The Execute Method
MyRdoC.Execute SQLActionQuery, Options
The Execute method executes SQL statements that don't return rows. An example would be to execute a Delete action query against a table accessible through a specific connection. The SQLActionQuery string is the actual SQL action query or the name of an rdoQuery object. The Options value is either rdAsyncEnable or rdExecDirect.
The following query deletes all the rows from the Orders table where the order amount was less than $1:
Dim sDeleteActionQuery as String sDeleteActionQuery = "Delete Orders Where OrderAmount < 1" MyRdoC.Execute sDeleteActionQuery
You can append optional parameters to this example for added functionality. The rdAsyncEnable option allows the query to run asynchronously and immediately returns control back to the calling program. The rdExecDirect option provides slightly faster performance by not setting up a temporary stored procedure, but executing the query directly. To use both options, simply AND the values together.
The OpenResultSet Method The OpenResultSet method is used to execute SQL queries that return rows and then stores those rows in a new rdoResultset object, which becomes part of the rdoConnection's rdoResultsets collection:
Set MyRdoR = MyRdoC.OpenResultset(Source[,CursorType[LockType[,Options]]])
The Source argument is generally a string that contains one or more row-returning SQL statements, or the name of an rdoQuery. You can also include SQL action queries in the string, but there must also be at least one row returning query--otherwise, a trappable error results.
The CursorType argument specifies the cursor type to use. The default is rdOpenForwardOnly, but rdOpenKeyset, rdOpenDynamic, and rdOpenStatic are also valid values.
The LockType argument specifies what type of locking to use. RdConcurReadOnly is the default, but rdConcurLock (pessimistic concurrency), rdConcurRowVer (optimistic based on row ID), rdConcurValues (optimistic based on row values), and RdConcurBatch (optimistic using batch mode updates) are also valid values.
The Options are the now familiar rdAsyncEnable and rdExecDirect.
The BeforeConnect, Connect, and Disconnect Events The BeforeConnect event is fired just before a connection is made to a data source. The Connect event is fired just after a connection is made to a data source. The Disconnect event is fired after a connection is closed.
The QueryComplete Event This event is fired after the query of an rdoResultset returns a resultset. This event fires for all queries executed by OpenResultSet or Execute methods of the rdoConnection or rdoQuery objects. It's much cleaner to use this event rather than poll the StillExecuting property.
The QueryTimeout Event This event is fired when the execution time of a query exceeds the time limit set in the QueryTimeout property. The event handler passes the query and a Boolean Cancel argument. The Cancel argument is defaulted to True, so if this value isn't overridden, the query will be canceled when the event handler executes. By setting this value to False, the query isn't canceled, and the application will wait for another time period equal to the QueryTimeout value before the QueryTimeout event is fired again.
The WillExecute Event This event is fired just before a query is executed. It doesn't matter whether the query is an action query or a row-returning query. If you trap this event and examine the rdoQuery object, you can set the default Cancel argument from False (allow the query to proceed) to True (cancel the query). Certain queries can be disallowed or modified.
The AsynchCheckInterval Property The AsynchCheckInterval property is used when the connection was made with the rdAsynchOption to set a time interval, in milliseconds, used to check whether a query has completed processing. The default value is 1,000 milliseconds (1 second). More frequent polling can be detrimental to the performance of your application, whereas a longer polling period can delay how quickly results are available to users. This setting normally isn't changed, unless foreground processing is very intensive and you need to devote more resources to it than to checking the status of your query. In that case, set AsynchCheckInterval to a longer time period. The QueryComplete event will eventually fire at the end of one of the AsynchCheckInterval time periods. If you set this value to 10,000, you'll be informed that the query has been completed only after at least 10 seconds elapse.
The Connect Property The Connect property is read-only after a connection is made and is a string representing the ODBC connect string. Depending on the Prompt argument of the rdoEnvironments OpenConnection method or the rdoConnection object's EstablishConnection method, the connect string can be fully constructed in code by the application or partially provided by the ODBC Login dialog box. A typical connect string might look something like this:
"DSN=AcctSrvr;UID=Laurie;PWD=Sis;DATBASE=AcctRec"
The CursorDriver Property The CursorDriver property of the rdoConnection object is necessary because of dissociated rdoConnection objects. Before RDO2, this connection information was stored at the rdoEnvironment level only, because all rdoConnections were part of an rdoEnvironment object. Because rdoConnections can now be a standalone object, this property has been added to the object. It's seeded from the rdoEngine's rdoDefaultCursorDriver property.
The hDbc Property The hDbc property is a Long that represents the ODBC connection handle. Although this property isn't needed for RDO2 programming, it can be useful if you need to make direct ODBC API calls.
The LastQueryResults Property If the rdoConnections's rdoResultsets collection contains any members, the LastQueryResults property returns a reference to the last added rdoResultset. If no rdoResultsets are available, nothing is returned. This property was added because you can now invoke rdoQueries as methods of the rdoConnection object. Because many queries provide return codes captured when the query is executed as a method, a different way of referencing the resultset returned by the query is necessary. The following code illustrates LastQueryResults in use:
Dim lReturnCode as Long Dim MyRdoRS as rdoResultset lReturnCode = MyRdoC.MyRdoQuery(arg1, arg2) Set MyRdoRS = MyRdoC.LastQueryResults
The LoginTimeout Property Another property added as a result of the new standalone capabilities of the rdoConnection object is LoginTimeout. This property specifies the amount of time, in seconds, that the connection object waits for success when attempting to establish a connection.
The LogMessages Property The LogMessages property specifies the location of a log file and enables the logging of ODBC operations. If the property is set to a file location (for example, C:\Logs\MyApp.Log), ODBC operations are written to the file. If the property is set to an empty string, logging is disabled. If you set this property, be aware that the file can become very large very quickly. You'll probably want to use this only for debugging purposes.
The Name Property The Name property is the name given to the rdoConnection object.
The QueryTimeout Property This property specifies the number of seconds to wait before a timeout error occurs when a query is executed. The time is specified in seconds, like the LoginTimeout property, not in milliseconds like the AsynchCheckInterval property. The default timeout for queries is 30 seconds.
The rdoQueries Property The rdoQueries property is a collection of rdoQuery objects.
The rdoResultsets Property This property is a collection of rdoResultset objects that represent rows returned from one or more invocations of the rdoConnection's OpenResultSets method.
The rdoTables Property The rdoTables collection is an obsolete property typically used to examine the structure of the tables making up a database. There are easier and faster ways of retrieving this information, such as applying a standard query against the system tables. Most likely, this collection will disappear in future versions of VB. Microsoft discourages its use.
The RowsAffected Property The RowsAffected property provides access to the count of the number of rows affected by the last Execute statement. Assume that you want to delete all orders of less than $1 from a table you're accessing. When you execute the SQL to accomplish this, the set-based nature of SQL will delete a set of rows based on the criteria of the order amount less than $1. You don't know how many rows were deleted until you check the RowsAffected property. After the Execute statement is executed, the RowsAffected property provides access to that information. The following code illustrates this example:
Dim MySQL as String Dim MyrdoCon as rdoConnection Set cn = rdoEnvironments(0).OpenConnection(dsname := "Accting", Prompt:=rdDriverCompleteRequired) MySQL = "DELETE FROM Orders WHERE OrderAmount < 1" myrdoCon.Execute Debug.Print "Rows Deleted = " & MyrdoCon.RowsAffected
The StillConnecting Property This property returns a Boolean representing whether a connection attempt is in progress against a remote data source. True indicates that the connection attempt is in progress; False indicates that the connection is established. If the progress of the connection attempt needs to be determined so that some specific action can be taken, it's easier to use the Connect event handler to get the same result. You also don't have to create a busy wait loop to check the StillConnecting property if you rely on the Connect event. The following code snippet shows a busy wait loop that would execute until the connection was established. This is used when using asynchronous connections:
While MyCon.StillConnecting
DoEvents
Wend
The StillExecuting Property This property returns a Boolean value representing whether a query has returned any results. Like with the StillConnecting method, a True value represents that the query is still attempting to retrieve a resultset, whereas False means that the query is ready to return the resultset. If the query returns no results, an empty resultset is returned. The QueryComplete event will also fire.
The Transactions Property The Transactions property specifies whether the rdoConnection supports the actions of the transaction-management functions BeginTrans, CommitTrans, and RollBackTrans. If transactions aren't supported, the transaction-management functions won't cause errors but simply won't work.
The Updatable Property The Updatable property specifies whether changes can be made to the object.
The Version Property The Version property specifies the RDO library version in use.
Methods: AddNew, BatchUpdate, Cancel, CancelBatch, CancelUpdate, Close, Delete, Edit, GetClipString, GetRows, MoreResults, Move, MoveFirst, MoveLast, MoveNext, MovePrevious, Requery, Resync, Update
Events: Associate, Dissociate, ResultsChanged, RowCurrencyChange, rowStatusChanged, WillAssociate, WillDissociate, WillUpdateRows
Properties: AbsolutePosition, ActiveConnection, BatchCollisionCount, BatchCollisionRows, BatchSize, BOF, Bookmark, Bookmarkable, EditMode, EOF, hStmt, Lastmodified, LockEdits, LockType, Name, PercentPosition, rdoColumns, Restartable, RowCount, Status, StillExecuting, Transactions, Type, Updatable, UpdateCriteria, UpdateOperation
Information returned in an rdoResultset is generated by writing a SQL query. The SQL statement can return data from one table, such as
Select * from Sales where Amount > 1000
or you can return information from multiple tables by specifying the join condition, such as
Select Sales.ProductName, Sales.ProductAmount, SalesForce.SalespersonName from Sales, SalesForce Where Sales.Amount > 1000 and SalesForce.SalespersonID = Sales.SalespersonID
The AbsolutePosition Property The AbsolutePosition property is used to determine or set the ordinal position of the current row within a keyset or static rdoResultset. It's tempting to think of this as a row number, but this is unwise if the rdoResultset is repopulated.
The AddNew Method The AddNew method creates a new row in the rdoResultset that you can use to edit and add to the remote data source by using the Update method. If you attempt to add a record by using the AddNew method against a non-updatable rdoResultset, an error isn't generated until the Update method is invoked. If you need to undo an added record and haven't invoked the Update method, use the CancelUpdate method to retract the addition. The AddNew method has no parameters.
The BatchUpdate Method
MyrdoResultset.BatchUpdate([SingleRow][, Force])
The BatchUpdate method performs an optimistic batch update and sends the appropriate SQL to the server to synchronize the data on the remote data source with the changes made to the rdoResultset. The SingleRow parameter overrides the batch update and sends only the current row back to the database. The Force option is essentially a dirty write, where the local version of the data overwrites the data on the server, even if collisions occur. Setting SingleRow to False and Force to True causes the modifications made in the local rdoResultset to overwrite the data in the server, even if collisions occur. The default values of SingleRow and Force are both False.
The Cancel Method The Cancel method has no parameters and cancels an asynchronous query, or flushes any remaining resultset rows.
The CancelBatch Method The CancelBatch method is analogous to the CancelUpdate method in that any modifications made to the rdoResultset since the last BatchUpdate will be discarded.
The CancelUpdate Method The CancelUpdate method destroys any changes made to the current row that exist in the edit buffer and haven't yet been committed to the remote data source. You can use the EditMode property to determine whether any changes exist in the edit buffer. If the edit buffer is empty (the EditMode property is set to rdEditNone), no error is generated.
The Close Method The Close method has no parameters and closes the resultset to release resources. Always remember to call it after you complete any data-manipulation methods.
NOTE: In RDO1, reusing the same rdoResultset object without invoking the Close method would result in open cursors and resources. RDO2 now automatically closes the Close method if an object is reused.
The Delete Method The Delete method has no parameters and deletes the current row from the rdoResultset; if there is no current row, a trappable error results. The deleted row remains the current row after Delete is invoked, so you must move the current record pointer before trying to retrieve any data from the next row. Depending on your cursor type, you might be able to maneuver to a row deleted by another user. If this happens, a trappable error results.
The Edit Method The Edit method has no parameters and allows changes to be made in the current row of an rdoResultset object. The Edit method creates a memory buffer and copies the contents of the current record into the buffer. Changes made to the buffer can then be written back to the rdoResultset by using the Update method. If you execute Edit and subsequently move the current record pointer to another row before Update is executed, any pending changes are lost. The following code shows this method in use, with and without the Update method:
MyrdoRS.Edit MyrdoRS.rdoColumns(0) = "Changed Value Row 1" MyrdoRS.MoveNext `Changes are lost! MyrdoRS.Edit MyrdoRS.rdoColumns(0) = "Changed Value Row 2" myrdoRS.Update `Changes are saved!
The GetClipString Method
MyString = MyRDOR.GetClipString(NumRows [,ColumnDelim] [,RowDelim] [,NullExpr])
The GetClipString method returns a delimited string that represents the rows of data in a resultset. This is very handy for adding information to a grid or to some other object. The required NumRows argument specifies the number of rows to return. The ColumnDelim argument specifies a column delimiter to separate columns. The RowDelim argument specifies a row delimiter to separate rows. The NullExpr argument allows a specified character to take the place of the default empty string value that represents a NULL value.
The GetRows Method The GetRows method retrieves multiple rows from a recordset object and stores them in a two-dimensional array. The NoOfRows argument specifies the number of rows to retrieve from the rdoResultset. The following code lines show the method and argument in use:
Dim MyArray as Variant MyArray = MyRdoResultset.GetRows(NoOfRows)
The values in the array can be referenced by using the first dimension as the column number and the second dimension as the row number. Remember that arrays are base 0. The following piece of code returns the value of the first column (0) from the second row (1):
Fieldvalue = MyArray(0,1)
If a larger NoOfRows is specified than actually exists in the rdoResultset, the entire rdoResultset is returned.
The MoreResults Method Because a query can return multiple resultsets, this method is used to clear the current resultset and returns a Boolean value to indicate whether more resultsets are available.
NOTE: Not all ODBC drivers support multiple resultsets, so this method might be unavailable to you. Check with your ODBC driver documentation to see if this functionality is supported.
The Move Method
MyRdoRS.Move NoOfRows, [StartPosition]
The Move method moves the current pointer in the rdoResultset object a specified number of rows. If NoOfRows is positive, the current pointer moves forward for NoOfRows from its position. If the number is negative, the current pointer moves backward from its position. If the pointer is directed to a row beyond the beginning of the file or the end of the file, the BOF or EOF properties are set to True.
If the StartPosition is specified, it is to be a variant that identifies a bookmark. Also, moving the current pointer after using Edit or AddNew, but before invoking the Update method, causes any changes that were made to be lost. This method also fires the RowCurrencyChange event.
The MoveFirst, MoveLast, MoveNext, and MovePrevious Methods The MoveFirst method positions the current pointer to the first record in the rdoResultset. The MoveLast method positions the current pointer to the last record. MoveNext moves the current pointer forward one record, and MovePrevious moves it backward one record. Monitor the BOF and EOF properties to determine when the end of a recordset has been reached.
The Requery Method
MyrdoResultset.Requery [options]
The Requery method ensures that you have the most current data available in the resultset by re-executing the query and refreshing the data in the resultset. If the rdAsyncEnable option is used, check the StillExecuting property or wait for the QueryCompleted event to fire before attempting to examine the rdoResultset.
The Resync Method The Resync method has no parameters and is valid only when using client batch cursors. Resync resynchronizes the columns in the current row with the current data on the server.
The Update Method The Update method has no parameters and saves changes in the copy buffer by updating the rdoResultset object.
The Associate Event The Associate event is fired immediately after a new connection is associated with an rdoResultset.
The Dissociate Event The Dissociate event is fired after the ActiveConnection property is set to nothing and the rdoResultset is dissociated from a connection.
The ResultsChanged Event After the MoreResults method closes the current resultset and returns the next available resultset, the ResultsChanged event is fired. If there are no other resultsets to return, the BOF and EOF properties of the rdoResultset are set to True.
The RowCurrencyChange Event This event is fired whenever the current pointer is moved, including moving the current pointer to BOF or EOF.
The RowStatusChange Event This event is fired when the state of the current row changes due to an edit, a delete, or an insert. The current status for a specific row can be determined by moving the current pointer to that row and examining the rdoRecordSets Status property.
The WillAssociate Event The WillAssociate event is raised after a valid connection is set to the ActiveConnection property but before the actual connection is made. The Cancel parameter can be set to True to abort the attempted connection.
The WillDissociate Event The WillDissociate event is raised when the ActiveConnection property is set to nothing, but before actually disconnecting form the data source. The Cancel parameter can be set to True to abort the attempt to disconnect from the data source.
The WillUpdateRows Event The WillUpdateRows event fires before the updated data from an rdoResultset is sent to the remote data source. The update process can be overridden by some special code implemented for a specific purpose. The ReturnCode specifies to RDO2 if the update was handled by another process, what the result was of the update by the other process, or whether it should handle the update itself. The default value, rdUpdateNotHandled, directs RDO to handle updates itself. The rdUpdateSuccessful value tells RDO2 that another process handled the update and was successful. The rdUpdateWithCollisions value, used only in batch mode, specifies that another process handled the update, but that some data collisions were encountered. rdUpDateFailed specifies that another process failed while attempting to complete the update.
The ActiveConnection Property The ActiveConnection property is a reference to an rdoConnection object to which the rdoResultset is associated.
The BatchCollisionCount Property The BatchCollisionCount property returns a count of the number of rows that weren't updated successfully during the execution of the last UpdateBatch method. A zero means that all rows were updated successfully.
The BatchCollionsRows Property The BatchCollisionRows property is a variant array of bookmarks that specify which rows in the rdoResultset weren't successfully updated. The count of this collection is the BatchCollisionCount property.
The BatchSize Property The BatchSize property refers to the number of SQL statements sent together as a batch to the remote data source. The default value is 15. Not all data sources support multiple SQL statements in a batch, so this value can be set to 1 to send a single SQL statement at a time.
The BOF Property The Beginning Of File (BOF) property indicates whether the current row of the rdoResultset is before the first row. A True value is typically what's seen when an rdoResultset is first retrieved, but is empty. This property is also used if the MovePrevious method is being used to move the cursor backward through an rdoResultset. When the BOF property becomes True during this activity, there are no more rows beyond the current position.
The BookMark Property This method is used to uniquely identify the current row in an rdoResultset object. You can save the value of a row's Bookmark property by assigning it to a variant data type. To move to that specific row at a later time when the current pointer is anywhere within the rdoResultset, simply set the rdoResultset object's Bookmark property to the value of the saved variant variable. You can retrieve the Bookmark for a row only when the current pointer is positioned on that row. If the rdoResultset doesn't support Bookmarks, a trappable error results.
The Bookmarkable Property This property determines whether an rdoResultset supports the use of Bookmarks.
The EditMode Property The EditMode property identifies whether the current row has values copied to the edit buffer. The values returned are rdEditNone, which means the row isn't being edited; rdEditInProgress, which means a copy of the row exists in the edit buffer; and rdEditAdd, which means that a new row exists in the edit buffer but hasn't yet been written to the remote data source.
The EOF Property The analog to the BOF property, the End Of File (EOF) property indicates whether the current row of the rdoResultset is after the last row. The EOF value is also set to True if an rdoResultset is empty. This property is frequently used when using the MoveNext method to move forward through an rdoResultset. When the EOF property becomes true, there are no more rows in the rdoResultset beyond the current position.
The hStmt Property This property is a pointer to an ODBC statement handle. It's not used in RDO2 programming but can be used when making direct ODBC API calls.
The LastModified Property The LastModified property is a variant that represents a bookmark to the last modified row in the resultset.
The LockEdits Property The LockEdits property is a Boolean value set to True when pessimistic locking is being used and False (the default) when optimistic locking is in effect.
The LockType Property The LockType property specifies the type of concurrency handling the rdoQuery object implements. Possible LockType values are
The Name Property The Name property is the name given to the rdoResultset and used to reference the object in code.
The PercentPosition Property The PercentPosition property represents an approximate position of the current pointer as a percentage of the number of rows in the resultset.
NOTE: The PercentPosition property can be used only with keyset and dynamic cursor types. If the property is examined when some other cursor type is used, a value of 50 is returned.
The rdoColumns Property The rdoColumns property is a collection of rdoColumn objects that make up the fields of the resultset.
The Restartable Property The Restartable property returns True or False, which determines whether the rdoResultset supports the Requery method. If the property is False, the rdoResultset should be closed and the OpenResultSet method must be used to refresh the data.
The RowCount Property The RowCount property returns the number of rows accessed by an rdoResultset object. This is different than returning how many total rows are in the rdoResultset. To get a total row count, the last record has to have been accessed.
NOTE: Some drivers can't return a RowCount value and return -1 if the value is unavailable.
The Status Property The Status property is a value of the rdoResultset object, which represents the status of the current row. The possible values are
The StillExecuting Property Used with asynchronous operations, the StillExecuting property is True if the query is still being processed by the server and False if the resultset is available.
The Transactions Property The Transactions property of the rdoResultset object functions like the property of the same name that belongs to the rdoConnection object. It merely tells you whether the rdoResultset will support transactions.
The Type Property The Type property returns a value that specifies the data type of the rdoResultset object.
The Updatable Property This property is True when the rdoResultset can be updated and False when it can't be updated.
The UpDateCriteria Property The UpDateCriteria property is used to specify or determine how the WHERE clause is constructed for each row in an optimistic batch update. The default value is rdCriteriaKey, which specifies that just the key columns are used. The rdCriteriaUpdCols value uses the key columns and all updated columns. The rdCriteriaAllCols value uses the key columns and all the other columns in the rdoResultset. The rdCriteriaTimeStamp value specifies that a timestamp column be used, if available; if no timestamp column is available, a trappable runtime error results.
The UpDateOperation Property The UpdateOperation specifies how UPDATE statements sent to the remote database are represented. The default value, rdOperationUpdate, species that an UPDATE statement is used. The alternative value, rdOperationDelIns, specifies that DELETE and ADD statements be substituted for the UPDATE statement. This property is used to provide some flexibility in performance tuning when different triggers are attached to the ADD, UPDATE, and DELETE actions on the database.
Methods: AppendChunk, ColumnSize, GetChunk
Events: DataChanged, WillChangeData
Properties: AllowZeroLength, Attributes, BatchConflictValue, ChunkRequired, KeyColumn, Name, OrdinalPosition, Required, Size, SourceColumn, SourceTable, Status, Type, Updatable, Value
The rdoColumn object represents a column from an rdoTable or a column from an rdoResultset. The name of the column comes from the name defined by the Data Definition Language (DDL) that created the underlying table of an rdoTable object, or by the column name as aliased by a SQL query in an rdoResultset.
The AppendChunk Method The AppendChunk method is used to handle Binary Large Objects (BLOBs) and appends data from a Variant expression to an rdoColumn object with a data type rdTypeLONGVARBINARY or rdTypeLONGVARCHAR to the database column. Because the data is appended to the column, AppendChunk is used to write out successive blocks of data, one portion at a time.
The ColumnSize Method The ColumnSize method returns the bytes in an rdoColumn object with a data type of rdTypeLONGVARBINARY or rdTypeLONGVARCHAR. A value of -1 is returned if the size isn't available.
The GetChunk Method The GetChunk method returns all or a portion of an rdoColumn object with a data type of rdTypeLONGVARBINARY or rdTypeLONGVARCHAR.
The DataChanged Event The DataChanged event occurs after the value of a column changes.
The WillChangeData Event The WillChangeData event is fired before data is changed in the column. The event handler for this event provides a cancel parameter that defaults to False, which will allow the change to occur, but can be set to True, which will disallow the pending change.
The AllowZeroLength Property The AllowZeroLength property is used to determine if zero-length strings ("") are valid for data types of rdTypeCHAR, rdTypeVARCHAR, and rdTypeLONGVARCHAR. A value of True indicates null strings are accepted, whereas False indicates that they're unacceptable.
The Attributes Property The Attributes property returns a value representing the characteristics of an rdoColumn object:
A column can have more than one of these values, so you must AND them to determine whether their bits are represented in the value of Attributes.
The BatchConflictValue Property In an optimistic batch update, it's possible that another process changed a value on the remote database between the time it was originally fetched and the time the BatchUpdate method was executed. The BatchConflictValue property represents the value of the column as it exists on the remote data source at the time BatchUpdate was executed.
The ChunkRequired Property Some column values can be too large to be fetched all at once and must be retrieved in chunks. The ChunkRequired property returns a Boolean value set to True when data must be accessed by using the GetChunk method and False if the value can be retrieved directly.
The KeyColumn property The KeyColumn property returns True if the column is part of the primary key and False if it isn't. This read/write property uses the rdClientBatch cursor library and helps build the WHERE clauses for the data-modification statements in an optimistic batch update.
The Name Property The Name property is the name of the column as defined in the DDL that created the underlying table in rdoTable objects, or possibly aliased name used in the SELECT statement used to create an rdoResultset object.
The OrdinalPosition Property The OrdinalPosition property represents the position of column in an rdoTable or rdoResultset. The position is base 0, so the OrdinalPosition of the first column equals 0.
The OriginalValue Property The OriginalValue property returns the value of the column as it was originally fetched from the database. You can resolve conflicts during optimistic batch updates by comparing this value with the value the batch update supplies.
The Required Property The Required property returns a True value if null values aren't allowed as a valid entry to the column. False specifies that nulls are allowed.
The Size Property The Size property returns the maximum number of characters in an rdoColumn object that contains text or the number of bytes of an rdoColumn object that contains numeric values.
The SourceColumn Property The SourceColumn property contains the name of the data source column.
The SourceTable Property The SourceTable property contains the name of the table that's the underlying source of the data.
The Status Property See "The Status Property" section under the earlier section "The rdoResultsets Object."
The Type Property The Type property returns a value that specifies the underlying data type of an rdoColumn object. There are many possible values, but a quick way to differentiate between numeric or string is that numeric values are between 2 (rdTypeNumeric) and 8 (rdTypeDouble), strings are 1 (rdTypeChar) and 12 (rdTypeVarChar), and Date/Time values are between 9 (rdTypeDate) and 11 (rdTypeTimeStamp). Negative numbers -1 through -7 are more esoteric binary or BLOB types.
The Updatable Property Updatable returns a True value indicating that the column can be updated, and a False value if the column can't be updated.
NOTE: Be careful about identity columns, as they will be Updatable, but the Attribute property will be rdAutoIncrColumn and not rdUpdatableColumn.
The Value Property The Value property returns a string variant that represents the value in the current row of the underlying rdoColumn.
Methods: Cancel, Close, Execute, OpenResultset
Events: None
Properties: ActiveConnection, BindThreshold, CursorType, hStmt, KeysetSize, LockType, MaxRows, Name, Prepared, QueryTimeout, rdoColumns, rdoParameters, RowsAffected, RowsetSize, SQL, SillExecuting, Type
The rdoQuery object is used to define SQL queries that use input and output parameters and acts as a compiled SQL statement. The object is useful when a single query is executed repeatedly with changes to its parameters. This object replaces the rdoPreparedStatement object used in RDO1.
The Cancel Method The Cancel method requests the remote data source to cancel the processing of a query. Depending on the state of the query processing by the server, the query might be able to be canceled.
The Close Method The Close method closes the rdoQuery object, releases it from the rdoQueries collection, and frees resources in use by the object.
The Execute Method The Execute method is used to execute a query that doesn't return any rows, such as an action query.
The OpenResultSet Method The OpenResultSet method executes the row returning SQL that defines the rdoQuery object.
The ActiveConnection Property The ActiveConnection property represents the current connection to a remote data source. It's the conduit for information exchanged between the remote data source and the rdoQuery. rdoQuery objects can be set to nothing and "disconnected" from a connection, and then reused by setting the ActiveConnection property to another rdoConnection object.
The BindThreshold Property The BindThreshold property sets the largest column size that ODBC will automatically bind. Some BLOB type data is too large to handle by using standard methods. By setting this property, you identify the threshold where if the data is less than the value, standard RDO techniques can be used to manipulate the data. If the column size is larger than the value of this property, GetChunk and AppendChunk must be used. The default BindThreshold value is 1,024 bytes.
The CursorType Property The CursorType property specifies the cursor type that will be used. The default is rdOpenForwardOnly, but rdOpenKeyset, rdOpenDynamic, and rdOpenStatic are also valid values.
The hStmt Property This property represents a pointer to an ODBC statement. It's not used in RDO2 programming but can be used when making direct ODBC API calls.
The KeysetSize Property The KeysetSize property specifies the number of rows used in the keyset buffer for keyset or dynamic cursors. KeysetSize must be larger than the RowsetSize property. The default keyset size, 0, specifies a true keyset-driven cursor; a value greater than 0 actually results in a mixed-cursor type.
NOTE: Not all ODBC drivers support keyset cursors.
The LockType Property The LockType property specifies the type of concurrency handling that the rdoQuery object implements:
The MaxRows Property The MaxRows property defaults to 0 (return all rows), but any positive value can be used to specify the maximum number of rows a query can return.
The Name Property This property is used to give a name to the rdoQuery object.
The Prepared Property The Prepared property accepts a Boolean True or False value that specifies whether the query is to create a stored procedure (True, the default) or to execute the query directly as dynamic SQL. Creating the query as a stored procedure results in compilation of the SQL and an execution plan created by the remote data source that specifies the most efficient way to access the data. This results in faster access time during the actual operation of the query, but some time is used to create the stored procedure itself. In general, if a query will be used many times, create a stored procedure for greater throughput.
The QueryTimeOut Property The QueryTimeOut property specifies the number of seconds to wait if rows haven't been returned before the operation is canceled by the ODBC driver manager.
The rdoColumns Property The rdoColumns property is a collection of rdoColumn objects.
The rdoParameters Property The rdoParameters property is a collection of rdoParameter objects.
The RowsAffected Property The RowsAffected property returns the count of the number of rows modified by the SQL executed by the most recent Execute method.
The RowsetSize Property The RowsetSize property specifies how many rows will be "active" at any one time during the creation of a resultset. If a query returns 300 rows and RowsetSize is set to 100, only 100 rows at a time will populate the resultset. This way, the application can bring in subsets of the data and ease contention at the remote data source. Rows not included in the resultset aren't buffered by the application and remain unaffected on the server.
The SQL Property The SQL property is a string that makes up the actual Structured Query Language (SQL) to be executed by the rdoQuery.
The StillExecuting Property The StillExecuting property specifies whether an asynchronous query is still executing or has completed.
The Type Property The Type property returns a value that specifies the underlying data type of an rdoColumn object. There are many possible values, but a quick way to differentiate between numeric or string is that numeric values are between 2 (rdTypeNumeric) and 8 (rdTypeDouble), strings are 1 (rdTypeChar) and 12 (rdTypeVarChar), and Date/Time values are between 9 (rdTypeDate) and 11 (rdTypeTimeStamp). Negative numbers -1 through -7 are more esoteric binary or BLOB types.
Methods: AppendChunk
Events: None
Properties: Direction, Name, Type, Value
The rdoParameters property is a collection of rdoParameter objects that make up those variables to be passed to the stored procedure.
The AppendChunk Method The AppendChunk method is used to add large data elements in manageable sections referred to as chunks.
The Direction Property The Direction property specifies whether a parameter is considered an input or output parameter. The possible values include the following:
The Name Property This is the name that will represent the rdoParameter object.
The Type Property The Type property returns a value that specifies the underlying data type of an rdoColumn object. There are many possible values, but a quick way to differentiate between numeric or string is that numeric values are between 2 (rdTypeNumeric) and 8 (rdTypeDouble), strings are 1 (rdTypeChar) and 12 (rdTypeVarChar), and Date/Time values are between 9 (rdTypeDate) and 11 (rdTypeTimeStamp). Negative numbers -1 through -7 are more esoteric binary or BLOB types.
The Value Property The Value property returns or sets the actual value of the rdoParameter.
To be complete, a short discussion is included on some objects that exist in RDO2 but have been deemed obsolete. They exist primarily to provide backward compatibility with RDO1 code.
The rdoTable Object The rdoTable object is used to examine the structure of tables within a database or to examine the data contained in a table. The rdoTable object can be used to determine whether a table is updatable or not, or the columns can be examined to determine their data type, and so on. If the OpenResultset method is used against the rdoTable object, a Select * from MyTable SQL command is executed, using the specified cursor library. Needless to say, this can wreak havoc on multiuser databases, so its use isn't advised. In fact, Microsoft warns that the rdoTable object might not be supported in future versions of RDO. If detailed information is needed about a table, simply querying the system tables for the information is simpler and requires less overhead.
The rdoPreparedStatement Object The rdoPreparedStatement object has been replaced by the rdoQuery object. Use the rdoQuery object for situations where stored procedures with input/output parameters are used.
In this chapter, you learned about the "classic" two-tier client/server architecture and the roles of each component of that architecture. You were introduced to the common concepts in client/server programming, such as stored procedures, cursors and cursor types, DSNs, row-returning and action queries, and asynchronous versus synchronous operations. You also were exposed to the RDO2 object hierarchy, and examined each object, method, property, and event of that object model.
Although RDO2 is now the best choice available for two-tier client/server database access, it will eventually be displaced by the Active Data Objects (ADO) library. RDO is tied very closely to ODBC. ODBC is tied very closely to the relational data model and SQL. ADO promises to provide access to heterogeneous data sources by using a consistent data access method to the client program. This way, you can query databases, phone lists, mail systems, and so forth--all using the same code! This will be a wonderful thing, but ADO is a very young product and requires some maturing. For now, RDO is your best access method to corporate RDBMSs.
© Copyright, Macmillan Computer Publishing. All rights reserved.