You should still use the Db.getRecord method when instantiating a single Record<Class> object versus a set of Record<Class> objects. Also, if you only need to select the data, consider using the Db.select method.
Class: Db
Description
This method selects all columns from a table based on the conditions specified in the WHERE clause and stores the information in a type DbSet object. Use Db.selectDbSet any time the program logic loops through a ResultSet and subsequently instantiates Record<class> objects based on the key columns.
Use the Db.getRecord method to move the data into an object where the individual columns can be referenced. The instantiated record can be passed and used in other elements.
In contrast, the result set from a Db.select can be passed; however, the value of passing it is limited, as other elements cannot trust the source of a result set that has been passed into it.
Using selectDbSet Method versus Result Sets
Use of this method is preferred over Db.getRecord if the following conditions exist:
The program subsequently instantiates records using selected columns.
The program passes the object as a parameter.
The program references more than a few selected columns (e.g., five or more). Db.selectDbSet improves readability in this scenario; however, this is a subjective decision.
|
|
You should still use the Db.getRecord method when instantiating a single Record<Class> object versus a set of Record<Class> objects. Also, if you only need to select the data, consider using the Db.select method. |
Db.selectDbSet does not require validation that a record exists (i.e., Db.isDefined) because it only returns valid records from the database. Using the Db.selectDbSet method (instead of the combination of Db.select and Db.getRecord) results in half of the database IO. Benchmark results on the M database comparing the combination of Db.selectDbSet and Db.getRecord to Db.select and Db.getRecord produced a significant benefit by using the first pair (about 15%). The anticipated benefit on an ORACLE database is more significant (probably over 100% or less than half run-time) due to the reduction in SELECT statements by half.
Dynamic/Run-Time Parameters
The Db.selectDbSet method supports dynamic/run-time parameters (i.e., any combination of SELECT, FROM, WHERE, ORDERBY, GROUPBY). The dynamic select is supported under the following conditions:
The variables exe and vsql are not in scope in the subroutine.
Only one dynamic select is allowed within the subroutine.
The ResultSet or DbSet object cannot be passed into or out of the subroutine.
The dynamic select generates a run-time error if the input parameters cause an error within the SQL parser or compiler.
Lowercase Variables, Properties, and System Variables
As of Profile version 6.3, this method supports lowercase variables, object.property, and system variables within the host variable syntax. PSL maps these variables and object references into uppercase host variables.
Declaration of DbSet Class Object
type DbSet ds = Db.selectDbSet(...)
Syntax
Db.selectDbSet(literal String table,String whereClause,String orderByClause)
Parameters
|
table |
The name of the table from which to select rows. The table name must be literal. |
|
whereClause |
A valid SQL WHERE clause (optional). Include all query conditions that may exclude a record from retrieval. Minimize the number of records in the DbSet by executing the selection criteria in the DBMS, not in the PSL code. |
|
orderByClause |
A valid SQL ORDER BY clause (optional). |
Returns
A DbSet instance.
When Became Available
Profile v6.4
Example
type String BRCITY,BRSTATE,BRZIP,DESC
type DbSet ds = Db.selectDbSet(“UTBLBRCD”)
while ds.next() do {
type RecordUTBLBRCD utblbrcd = ds.getRecord("UTBLBRCD")
set DESC = utblbrcd.desc
set BRCITY = utblbrcd.brcity
set BRSTATE = utblbrcd.brstate
set BRZIP = utblbrcd.brzip
}