Visual Basic: RDO Data Control
Creates a new rdoResultset object.
Set variable = connection.OpenResultset(name [,type [,locktype [,option]]])
Set variable = object.OpenResultset([type [,locktype [, option]]])
The OpenResultset method syntax has these parts:
|variable||An object expression that evaluates to an rdoResultset object.|
|connection||An object expression that evaluates to an existing rdoConnection object you want to use to create the new rdoResultset.|
|object||An object expression that evaluates to an existing rdoQuery or rdoTable object you want to use to create the new rdoResultset.|
|name||A String that specifies the source of the rows for the new rdoResultset. This argument can specify the name of an rdoTable object, the name of an rdoQuery, or an SQL statement that might return rows.|
|type||A Variant or constant that specifies the type of cursor to create as indicated in Settings.|
|locktype||A Variant or constant that specifies the type of concurrency control. If you dont specify a locktype, rdConcurReadOnly is assumed.|
|option||A Variant or constant that specifies characteristics of the new rdoResultset.|
The name argument is used when the OpenResultset method is used against the rdoConnection object, and no query has been pre-defined. In this case, name typically contains a row-returning SQL query. The query can contain more than one SELECT statement, or a combination of action queries and SELECT statements, but not just action queries, or a trappable error will result. See the SQL property for additional details.
- Cursor type
Note Not all types of cursors and concurrency are supported by every ODBC data source driver. See rdoResultset for more information. In addition, not all types of cursor drivers support SQL statements that return more than one set of results. For example, server-side cursors do not support queries that contain more than one SELECT statement.
The type argument specifies the type of cursor used to manage the result set. If you dont specify a type, OpenResultset creates a forward-only rdoResultset. Not all ODBC data sources or drivers can implement all of the cursor types. If your driver cannot implement the type chosen, a warning message is generated and placed in the rdoErrors collection. Use one of the following result set type constants that defines the cursor type of the new rdoResultset object. For additional details on types of cursors, see the CursorType property.
|rdOpenForwardOnly||0||(Default) Opens a forward-only-type rdoResultset object.|
|rdOpenKeyset||1||Opens a keyset-type rdoResultset object.|
|rdOpenDynamic||2||Opens a dynamic-type rdoResultset object.|
|rdOpenStatic||3||Opens a static-type rdoResultset object.|
- Concurrency LockType
In order to maintain adequate control over the data being updated, RDO provides a number of concurrency options that control how other users are granted, or refused access to the data being updated. In many cases, when you lock a particular row using one of the LockType settings, the remote engine might also lock the entire page containing the row. If too many pages are locked, the remote engine might also escalate the page lock to a table lock to improve overall system performance.
Not all lock types are supported on all data sources. For example, for SQL Server and Oracle servers, static-type rdoResultset objects can only support rdConcurValues or rdConcurReadOnly. For additional details on the types of concurrency, see the LockType property.
|rdConcurReadOnly||1||(Default) Read-only .|
|rdConcurRowVer||3||Optimistic concurrency based on row ID.|
|rdConcurValues||4||Optimistic concurrency based on row values.|
|rdConcurBatch||5||Optimistic concurrency using batch mode updates. Status values returned for each row successfully updated.|
- Other options
If you use the rdAsyncEnable option, control returns to your application as soon as the query is begun, but before a result set is available. To test for completion of the query, use the StillExecuting property. The rdoResultset object is not valid until StillExecuting returns False. You can also use the QueryComplete event to determine when the query is ready to process. Until the StillExecuting property returns True, you cannot reference any other property of the uninitialized rdoResultset object and only the Cancel and Close methods are valid.
If you use the rdExecDirect option, RDO uses the SQLExecDirect ODBC API function to execute the query. In this case, no temporary stored procedure is created to execute the query. This option can save time if you dont expect to execute the query more than a few times in the course of your application. In addition, when working with queries that should not be run as stored procedures but executed directly, this option is mandatory. For example, in queries that create temporary tables for use by subsequent queries, you must use the rdExecDirect option.
You can use the following constants for the options argument:
|rdAsyncEnable||32||Execute operation asynchronously.|
|rdExecDirect||64||(Default.) Bypass creation of a stored procedure to execute the query. Uses SQLExecDirect instead of SQLPrepare and SQLExecute.|
If the OpenResultset method succeeds, RDO instantiates a new rdoResultset object and appends it to the rdoResultsets collection even if no rows are returned by the query. If the query fails to compile or execute due to a syntax error, permissions problem or other error, the rdoResultset is not created and a trappable error is fired. The rdoResultset topic contains additional details on rdoResultset behavior and managing the rdoResultsets collection.
Note RDO 2.0 behaves differently than RDO 1.0 in how it handles orphaned references to rdoResultset objects. When you Set a variable already assigned to an rdoResultset object with another rdoResultset object using the OpenResultset method, the existing rdoResultset object is closed and dropped from the rdoResultsets collection. In RDO 1.0, the existing object remained open and was left in the rdoResultsets collection.
Note Before you can use the name of a base table in the name argument, you must first use the Refresh method against the rdoTables collection to populate it. You can also populate the rdoTables collection by referencing one of its members by its ordinal number. For example, referencing rdoTables(0) will populate the entire collection.
Executing Multiple Operations on a Connection
If there is an unpopulated rdoResultset pending on a data source that can only support a single operation on an rdoConnection object, you cannot create additional rdoQuery or rdoResultset objects using the OpenResultset method, or use the Refresh method on the rdoTable object until the rdoResultset is flushed, closed, or fully populated. For example, when using SQL Server 4.2 as a data source, you cannot create an additional rdoResultset object until you move to the last row of the last result set of the current rdoResultset object. To populate the result set, use the MoreResults method to move through all pending result sets, or use the Cancel or Close method on the rdoResultset to flush all pending result sets.