Promotic
WikipediaLinkedInYoutubeTwitterFacebook

RsOpen - method of object PmAdo

Description:
Executes the SQL query SELECT, and provides the resulting data in the form of AdoRecordset.
Syntax:
RsOpen(sId As String, sSource As String, sParams As String) As Object
Calling:
Set oRecordset = oDb.RsOpen(sId, sSource, sParams)
Parameters:
sId(String) Unique identifier that is used by the PmAdo object to store the result data in the form of AdoRecordset.

If an empty string ("") is used, then the SQL comman is executed and the data is returned as AdoRecordset, but the PmAdo object will not store it. This is handy if it is necessary to execute some SQL query in the script and process the resulting data immediately, and release the AdoRecordset object right after that.

sSource(String) Isually the SQL language syntax query (SELECT). It can also contain table name, call saved procedure, file name or URL address of the file containing stored AdoRecordset object.
sParams(String) Additional parameters of the RsOpen method. Entries with assigned value are separated by a semicolon, for example "cursor:forwardonly;lock:readonly;command:text;".
"cursor:xxx;" (optional) - Defines the cursor type to be used by the ADO Provider when opening the AdoRecordset.
forwardonly (default) - Behaves identically to a Static cursor except that it only allows you to scroll forward through records. This improves performance in situations where you need to make only a single pass through a AdoRecordset object. The AdoRecordset.RecordCount property always returns -1 because ADO can't determine the number of records with this cursor. It corresponds to the ADO adOpenForwardOnly value. To get a valid count of records when using a Recordset object to work with an Access database, you must use either a Keyset cursor or a Static cursor.
keyset - Behaves like a "dynamic" cursor, except that it doesn't contain any new or deleted records added by other users. Any data changes made by other users to the records available when the AdoRecordset object was opened will still be visible. This cursor always allows bookmarks and therefore allows all types of movement through the AdoRecordset object. It corresponds to the ADO adOpenKeyset value.
dynamic - Reflects any new additions, changes, and deletions made by other users, and allows all types of movement through the AdoRecordset object that don't rely on Bookmarks. Bookmarks are allowed if the ADO Provider supports them. This cursor type isn't supported by the Microsoft Jet 4.0 OLE DB Provider. It corresponds to the ADO adOpenDynamic value.
static - Provides a static, but updatable, copy of a set of records. Any additions, changes, or deletions by other users will not be visible until the ADO Recordset.Resync method is called. This is the only type of cursor allowed when you open a client-side ADOR Recordset. This cursor always allows bookmarks and therefore allows all types of movement through the AdoRecordset object. It corresponds to the ADO adOpenStatic value.
"lock:xxx;" (optional) - Defines the lock type (simultaneous access security), to be used by the ADO Provider when opening the AdoRecordset.
readonly (default) - Read-only — the data cannot be edited. Corresponds to the ADO adLockReadOnly value.
pessimistic - Pessimistic locking, record by record, the ADO Provider does what is necessary to ensure successful editing of the records. Usually by locking records at the data source as soon as you start editing records. No other users can read or edit the data, until you either save changes with the AdoRecordset.Update method or cancel them with the AdoRecordset.CancelUpdate method. Corresponds to the ADO Recordset.CancelUpdate value.
optimistic - Optimistic locking, record by record, the ADO Provider locks records in the data source only when you call the AdoRecordset.Update method. Other users can read, edit, and save changes to the same record while you have it open. Corresponds to the ADO adLockOptimistic value.
batchoptimistic - Optimistic locking, batch updates, required for batch update mode as opposed to immediate update mode. Corresponds to the ADO adLockBatchOptimistic value.
"command:xxx;" (optional) - Defines how the corresponding ADO Provider evaluates the sSource parameter, especially if it contains something different than SQL language syntax query/command (e.g. SELECT, INSERT, etc.).
text (default) - Indicates that the ADO Provider should evaluate the sSource parameter as a textual definition of a command. Corresponds to the ADO adCmdText value.
table - Indicates that the ADO Provider should generate an SQL query to return all records from the table named in the sSource parameter. Corresponds to the ADO adCmdTable value.
storedproc - Indicates that the ADO Provider should evaluate the sSource parameter as a stored procedure. Corresponds to the ADO adCmdStoredProc value.
file - Indicates that a persisted (saved) AdoRecordset object should be restored from the file named in the sSource parameter. Corresponds to the ADO adCmdFile value.
tabledirect - Indicates that the ADO Provider should return all records from the table named in the sSource parameter. Corresponds to the ADO adCmdTableDirect value.
unknown - Indicates that the type of command in the sSource parameter is not known. The ADO Provider will try to evaluate it, but it will just consume more time. Corresponds to the ADO adCmdUnknown value.
"execute:xxx;" (optional) - Instructs the corresponding ADO Provider how to execute the SQL query. The default mode is the synchronous query, i.e. the method waits for the operation result.
async - Asynchronous query, i.e. the method does not wait for the operation result. Corresponds to the ADO adAsyncExecute value.
asyncfetch - Indicates that the rows defined by the ADO Recordset.CacheSize property are to be read synchronously and any remaining records should be fetched asynchronously. Corresponds to the ADO adAsyncFetch value.
asyncfetchnonblocking - Defines that the main thread will never be blocked while reading is being processed. If the requested row is not read, the cursor will be moved to the end. Corresponds to the ADO adAsyncFetchNonBlocking value.
norecords - Defines that the called query/command does not return any data. If so, the data will be discarded and not returned (no AdoRecordset Nothing value). Corresponds to the ADO adExecuteNoRecords value.
record - specifies that the called query/command returns a single row, that has the form of AdoRecord object. Corresponds to the ADO adExecuteRecord value.
Return Values:
Returns the resulting data in the form of AdoRecordset. If error occures, the Nothing value may be returned.
Note:
This method is used for data access (physical databese tables, etc.) in the form of the AdoRecordset object by the SQL query SELECT. This method should not be used for executing general SQL command, that does not return data in the form of AdoRecordset, because it cannot be confirmed whether the command has been executed successfuly or not. The DbExecute method is used for that.
 
ADO Record object:

Depending on the specific ADO Provider it is possible, that a single row SQL query result will not be returned as AdoRecordset object containing a single record, but as a AdoRecord object, representing the single row. Není to ale typické pro nejběžněji užívané ADO Provider. The AdoRecord object is usually returned by explicit operations, when the specific AdoRecord is returned from the existing AdoRecordset object. The AdoRecord object consists of multiple AdoField objects, similar as the AdoRecordset object. The difference is that the methods for moving the position of current record are not supported (e.g. MoveFirst). In order to keep the documntation simple, it will be referred only to the commonly returned object AdoRecordset, although in some special cases the system may also return the AdoRecord object.

Example:
Data read over the PmAdo object ("/TestAdoDb"), that is already connected to the database (see DbOpen), by the SQL query ("SELECT * FROM table1"). The resulting AdoRecordset object will be stored in the PmAdo object with defined unique identifier ("table1"), it will be static and readonly. It will be checked if errors occured and the returned AdoRecordset object is valid. Then all the records in the AdoRecordset object will be checked and the value of the first column of each record will be written into the INFO system. Below, there are variants of the same example that differ only in the direction of reading the records in the AdoRecordset.
 
Common part:
Dim oDb, oRs
Set oDb = pMe.Pm("/TestAdoDb")
Set oRs = oDb.RsOpen("table1", "SELECT * FROM table1", "cursor:static;")
 
1) Forward record reading:
  oRs.MoveFirst
  Do Until oRs.EOF
    Pm.Debug oRs.Fields(0)
    oRs.MoveNext
  Loop
End If
 
2) Backward record reading:
  oRs.MoveLast
  Do Until oRs.BOF
    Pm.Debug oRs.Fields(0)
    oRs.MovePrevious
  Loop
End If
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice