The Query Object is used to iterate through the record set to do some computation or to check some criteria.
It has a predefined set of steps it follows:
1. Set Query
2. Check if there are records
3. While there are records, do something with the data and go to the next record.
Most importantly, the Query object has an expression scope.
edtSum = 0
Query.SetQuery("QSelectOrders")
WHILE Query.HasRecord()
{
edtSum = edtSum + Query.GetColumnValue("QTY")
Query.GoToNext()
}
Note:
edtSum – an edit control that will display the total order quantity.
QSelectOrders – the query that returns all the ORDERS records. Query definition:
Parent table: ORDERS
Output Fields: all columns
Use the HasRecord method as a WHILE condition to check if there are any more records to retrieve.
edtSum = 0
Query.SetQuery("QSelectOrdersByCustID")
intCUST_ID = lvCustList.GetCurrentRowColumnValue("ID")
Query.SetQueryParam("pCustID", intCUST_ID)
WHILE Query.HasRecord()
{
edtSum = edtSum + Query.GetColumnValue("QTY")
Query.GoToNext()
}
Note:
edtSum – an edit control that will display the total order quantity.
QSelectOrdersByCustID – the query that returns the order records for each customer. Query definition:
Parent table: ORDERS
Child table: CUSTOMER
Relationship: ORDERS.CUST_ID=CUSTOMER.ID
Condition: ORDERS.CUST_ID = pCustID
Output Fields: all columns
Use the HasRecord method as a WHILE condition to check if there are any more records to retrieve.
edtSum = 0
Query.SetSql("Select QTY from ORDERS where CUST_ID IN (Select ID from CUSTOMER where NAME ='BrightSoft')")
WHILE Query.HasRecord()
{
edtSum = edtSum + Query.GetColumnValue("QTY")
Query.GoToNext()
}
Note:
Use the SetSql method instead of creating a query in BrightBuilder to create more complex SELECT statements and iterate through the result set with the HasRecord, GetColumnValue and GoToNext methods.
The SetSql method does not change the database records in any way.