Promotic

RsOpen - method of the PmaAdo object

Description:
Executes the SQL query SELECT and provides the resulting data in the form of the AdoRecordset object (set of records).
Syntax:
Object RsOpen(String sId, String sSource, String sParams)
Parameters:
sId(String) Unique identifier that is used by the PmaAdo object to store the result data in the form of the AdoRecordset object.
If an empty string ("") is used, then the SQL statement is executed and the data is returned in the form of the AdoRecordset object, but the PmaAdo 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) Usually the query in the SQL language syntax (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 are in the KeyVal format, for example "cursor:forwardonly;lock:readonly;command:text;return:map;".
"cursor:xxx;" (optional) - Specifies the cursor type to be used by the ADO Provider when opening the AdoRecordset object.
forwardonly (default) - Behaves the same way as 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 the -1 value because ADO can't determine the number of records with this cursor.
It corresponds to the ADO adOpenForwardOnly value.
keyset - Behaves the same way as 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 - Displays 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 the AdoRecordset.Resync method is called.
This is the only type of cursor allowed if you open the AdoRecordset object on client-size.
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) - Specifies the lock type (simultaneous access security), to be used by the ADO Provider when opening the AdoRecordset object.
readonly (default) - Read only. The data cannot be edited.
It corresponds to the ADO adLockReadOnly value.
pessimistic - Pessimistic locking, record by record, the ADO Provider does what is necessary to ensure successful editing of 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 by the AdoRecordset.Update method or cancel them by the AdoRecordset.CancelUpdate method.
It corresponds to the ADO adLockPessimistic value.
optimistic - Optimistic locking, record by record, the ADO Provider locks records in the data source only by calling the AdoRecordset.Update method. Other users can read, edit, and save changes to the same record while you have it open.
It corresponds to the ADO adLockOptimistic value.
batchoptimistic - Optimistic locking, batch updates, required for batch update mode as opposed to immediate update mode.
It corresponds to the ADO adLockBatchOptimistic value.
"command:xxx;" (optional) - Specifies how the corresponding ADO Provider evaluates the sSource parameter, especially if it contains something different than query/statement in the SQL language syntax (e.g. SELECT, INSERT, etc.).
text (default) - Specifies that the ADO Provider should evaluate the sSource parameter as a textual definition of a command.
It corresponds to the ADO adCmdText value.
table - Specifies that the ADO Provider should generate an SQL query to return all records of the database table named in the sSource parameter.
It corresponds to the ADO adCmdTable value.
storedproc - Specifies that the ADO Provider should evaluate the sSource parameter as a stored procedure.
It corresponds to the ADO adCmdStoredProc value.
file - Specifies that a persisted (saved) AdoRecordset object should be restored from the file named in the sSource parameter.
It corresponds to the ADO adCmdFile value.
tabledirect - Specifies that the ADO Provider should return all records of the database table named in the sSource parameter.
It corresponds to the ADO adCmdTableDirect value.
unknown - Specifies 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.
It corresponds to the ADO adCmdUnknown value.
"execute:xxx;" (optional) - Specifies the corresponding ADO Provider how to execute the SQL query. The default mode is the synchronous query, it means that the method waits for the operation result.
async - Asynchronous query, it means that the method does not wait for the operation result.
It corresponds to the ADO adAsyncExecute value.
asyncfetch - Specifies that the rows defined by the ADO Recordset.CacheSize property are to be read synchronously and any remaining rows should be fetched asynchronously.
It corresponds to the ADO adAsyncFetch value.
asyncfetchnonblocking - Specifies that the main thread will never be blocked while reading is being processed. If the requested row is not read, then the cursor will be moved to the end.
It corresponds to the ADO adAsyncFetchNonBlocking value.
norecords - Specifies that the called query/statement does not return any data. If so, then the data will be discarded and not returned (no AdoRecordset object = value null for JavaScript or Nothing for VBScript).
It corresponds to the ADO adExecuteNoRecords value.
record - Specifies that the called query/statement returns a single row, that is in the form of the AdoRecord object.
It corresponds to the ADO adExecuteRecord value.
"return:xxx;" (optional) - Specifies whether the method return value is a map containing multiple values (new procedure) or a single value (old procedure).
If not set, then the old procedure is used.
map - Returns a map (PmMap object) containing multiple returned values (new procedure) (e.g. Result, AffectedRows, ErrorCode, ErrorText).
Return value:
New procedure: Returns PmMap object with values in following items:
- "Result": resulting data in the form of the AdoRecordset object or error value (null for JavaScript or Nothing for VBScript)
- "ErrorCode": numeric error code where the 0 value means successful method execution (no error)
- "ErrorText": error text description


Old procedure: Returns resulting data in the form of the AdoRecordset object or error value (null for JavaScript or Nothing for VBScript)
Note:
This method serves for data access (physical database 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 the AdoRecordset object, because it cannot be confirmed whether the command has been executed successfuly or not. The DbExecute method is used for that.

The ADO Record object:
Depending on the specific ADO Provider it is possible, that a single row SQL query result will not be returned in the form of the AdoRecordset object containing a single record, but in the form of the AdoRecord object (represents the single row). However, this is not typical of the most commonly used ADO Providers. 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, the same way as the AdoRecordset object. The difference is that the methods for moving the current record position are not supported (e.g. MoveFirst).
In order to keep the documentation PROMOTIC simple, it will be referred only to the commonly returned AdoRecordset object, although in some special cases the system may also return the AdoRecord object.
See also:
- PmaAdo.RsGet (method)
- PmaAdo.RsClose (method)
- PmaAdo.RsIsOpen (method)
- PmaAdo.DbOpen (method)
- PmaAdo.DbExecute (method)
- AdoRecordset (object)
- AdoRecord (object)
- AdoField (object)
Example:
Data read in the PmaAdo object ("/TestAdoDb"), that is already connected to the database (see the DbOpen method), by the SQL query ("SELECT * FROM table1").
The resulting AdoRecordset object will be stored in the PmaAdo object with defined unique identifier ("table1"), it will be static and readonly.
It is tested whether no error occurs and the returned AdoRecordset object is valid. Then all the records of 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 object.
 
1) Forward record reading:
JavaScriptVBScriptSelect and copy to clipboard

var oDb = pMe.Pm("/TestAdoDb");
var oRs = oDb.RsOpen("table1", "SELECT * FROM table1", "cursor:static;");
if (oRs)
{
oRs.MoveFirst();
while (! oRs.EOF)
{
Pm.Debug("Field name=" + oRs.Fields.Item(0).Name + ", value=" + oRs.Fields.Item(0).Value);
oRs.MoveNext();
}
}
 
2) Backward record reading:
JavaScriptVBScriptSelect and copy to clipboard

var oDb = pMe.Pm("/TestAdoDb");
var oRs = oDb.RsOpen("table1", "SELECT * FROM table1", "cursor:static;");
if (oRs)
{
oRs.MoveLast();
while (! oRs.BOF)
{
Pm.Debug("Field name=" + oRs.Fields.Item(0).Name + ", value=" + oRs.Fields.Item(0).Value);
oRs.MovePrevious();
}
}

History:
Pm8.01.00: Created
Pm8.03.22: Returns the PmMap object with values in items "Result", "ErrorCode" and "ErrorText".
PROMOTIC 9.0.27 SCADA system documentation MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r.o.