Class: Db
Description
This method selects one or more records from a table based on the conditions specified in the optional WHERE clause. The method stores the information in a type ResultSet object. This method is equivalent to the SQL SELECT statement.
Example SQL SELECT statement:
SELECT CID,BAL,LNM FROM DEP WHERE CID<:ABC
Use of this method is preferred if you are only selecting data, or if the size of a column list for a Db.select reduces readbility. However, if you also need to update the data, use the Db.selectDbSet method to select and update the data.
As of Profile version 6.3, this 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.
If either condition is not met, the compiler generates the appropriate error message. Because the use of dynamic SQL may negatively impact run-time performance, the compiler will generate the warning "Dynamic SQL statement" to signal the need for formal review and acceptance (refer to the #ACCEPT complier command).
As of 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 ResultSet class object
type ResultSet rs = Db.select(...)
Syntax
Db.select(String columnList,String tableName,String whereClause,
String orderByClause,String groupByClause,String PSQLparameters)
Parameters
|
columnList |
A comma-separated list of table columns from which to select data. |
|
tableName |
A comma-separated list of database table names from which to select data. |
|
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 Result Set or DbSet by executing the selection criteria in the DBMS, not in the PSL code. Ensure that all query conditions on columns described in the SQL WHERE clause are passed into the WHERE clause. For example, if you only need to select accounts with a balance greater than 100, specify this in the whereClause parameter. For example, type ResultSet rs.Db.select("CID,BAL,LNM","DEP","BAL>100") |
|
orderByClause |
A valid SQL ORDER BY clause used to sort the returned data (optional). |
|
groupByClause |
A valid SQL GROUP BY clause used to aggregate the returned data (optional). |
|
PSQLparamaters |
A slash-separated list of PSQL parameters (optional) to pass to the method (e.g., DQMODE, EFD, ROWS, USING) or SQL processing qualifier (e.g., PROTECTION) |
Returns
An object containing the result set form the database selection.
When Became Available
v6.0
Examples
type ResultSet rs = Db.select("ACN,NAM,TAXID","CIF")
while rs.next() do {
set ACN = rs.getCol(1) // by number is allowed
set NAM = rs.getCol("NAM") // by name is preferred
set TAXID = rs.getCol("TAXID")
}
___________________________________________________________
set BAL = 1000
type ResultSet rs= Db.select("CID,BAL,IRN","DEP","BAL>:BAL")
while rs.next() do {
set CID = rs.getCol(1)
set INT = (rs.getCol(2))*(rs.getCol(3))
}
___________________________________________________________
type String columns
type String BRCD,BRCITY,BRSTATE
set columns = "BRCD,DESC,BRCITY,BRSTATE"
type ResultSet rs = Db.select(columns,"UTBLBRCD","BRCD>10")
while rs.next() do {
set BRCD = rs.getCol(“BRCD”)
set BRCITY = rs.getCol(“BRCITY”)
set BRSTATE = rs.getCol(“BRSTATE”)
(...)
Examples - Dynamic Runtime Parameters
TEST(select,from)
type String ACN,NAM,TAXID
type ResultSet rs = Db.select(select,from)
while rs.next() do {
set ACN = rs.getCol(“ACN”)
set NAM = rs.getCol(“NAM”)
set TAXID = rs.getCol(“TAXID”)
}
____________________________________________________________
TEST(select,where)
type Number BAL,CID,INT
set BAL = 1000
type ResultSet rs = Db.select(select,”DEP”,where)
while rs.next() do {
set CID = rs.getCol(“CID”)
set INT = (rs.getCol(“INT”))*(rs.getCol(“BAL”))
}
quit
*The formal parameters (select, from and where) must be the appropriate SELECT, FROM, and WHERE clauses.