Users of business applications could potentially at some point require the ability to search for data using ROWID values. This article highlights and demonstrates how data can be retrieved using ROWID values.
ROWID within the SmartBusinessEntityAdapter QueryString
The simplest method would be to build a "QueryString" using a ROWID value. An example usage of a ROWID within the SmartBusinessEntityAdapter QueryString statement is given below:
Code Block | ||
---|---|---|
| ||
DEFINE VARIABLE oAdapter AS SmartBusinessEntityAdapter NO-UNDO .
DEFINE VARIABLE roRowid AS ROWID NO-UNDO .
FIND Customer WHERE Customer.CustNum = 10 NO-LOCK NO-ERROR.
IF AVAILABLE Customer THEN
roRowid = ROWID (Customer) .
oAdapter = NEW SmartBusinessEntityAdapter ( ) .
oAdapter:EntityName = "Consultingwerk.SimpleCustomerBusinessEntity.CustomerBusinessEntity":U .
oAdapter:EntityTable = "eCustomer":U .
oAdapter:QueryString = SUBSTITUTE ("for each eCustomer where ROWID (Customer) = TO-ROWID (&1)",
QUOTER (roRowid)) .
oAdapter:RetrieveData ( ) . |
It should be noted that the BusinessEntity contains DataSet ProDataSet Temp-Tables which are a representation of Database Tables (and NOT physical Database Tables). Therefore, whilst building the QueryString, it should be noted that the "rowid(……)" construct should contain the actual Database Table Name. In addition, as the QueryString is character based, it should always be remembered to use the "to-rowid" function to return a true ROWID value.
ROWID as a calculated field in the Business Entity
Anchor | ||||
---|---|---|---|---|
|
The following describes how we can use a ROWID Calculated Field on our Business Entity.
First, we create a Calculated field named "CustomerRowid" on our Customer Business Entity:
...
We specify the Data Type as "ROWID" and in the Source Field we use the statement "ROWID(Customer)".
Note: As we have tagged the Source Field as ROWID(Customer), the field value shall be populated automatically on the Fill StatementFILL Method, so there is no need to specify any Calculated Field Expression.
When filtering data on a Business Entity Calculated Field, we need to first determine whether we are going to be filtering Client or Server Side.
Client Side Filtering
If we perform a FetchData on our Customer Business Entity, we know that on each record returned, the calculated field shall be populated accordingly. Therefore, it is also possible to perform a Query on the data already contained within our SmartBusinessEntityAdapter.
An example usage of a ROWID within a prepopulated SmartBusinessEntityAdapter is given below:
The first section defines our SmartBusinessEntityAdapter and prepopulates the Adapter with 200 records.
...
The above code snippet is ideal if we know that the data we are filtering on exists within our Adapter's Dataset content.
Server Side Filtering
However, it may well be the case that we would be required to perform a Server Side Filter as we could not guarantee that the data required has of yet been retrieved. For example, our initial query could be based on our "CustomerRowid".
Performing a Server Side Query on the Calculated field "CustomerRowid" initially would fail, due to the fact, that we would be attempting to Filter on data that has not yet been initialised. Therefore, we would be required to Override our "FetchData" method to facilitate Queries on calculated fields ("CustomerRowid").
Within our CustomerDataAccess Class, we select the option to insert an override method for FecthData:
...
Code Block | ||
---|---|---|
| ||
METHOD OVERRIDE PUBLIC VOID FetchData ( poFetchDataRequest AS Consultingwerk.OERA.IFetchDataRequest ): DEFINE VARIABLE cFindString AS CHARACTER NO-UNDO. DEFINE VARIABLE cRequestContext AS CHARACTER NO-UNDO. DEFINE VARIABLE oParser AS QueryParser NO-UNDO . DEFINE VARIABLE oExpression AS IQueryExpression NO-UNDO . DEFINE VARIABLE oFieldExpression AS QueryExpression NO-UNDO . ASSIGN cRequestContext = ENTRY (1, poFetchDataRequest:Context, CHR (1)) . IF NUM-ENTRIES (cRequestContext, CHR (2)) > 1 THEN ASSIGN cFindString = ENTRY (2, cRequestContext, CHR (2)) . /* When we're searching for CustomerRowid, we need to turn that into ROWID (Customer) = ... */ IF cFindString > "":U AND INDEX (cFindString, "TO-ROWID (":U) > 0 THEN DO: cFindString = REPLACE (REPLACE ("for each eCustomer " + cFindString, "TO-ROWID (", ""), ")", "") . oParser = NEW QueryParser () . oExpression = oParser:ParseQueryString(cFindString) . IF QueryExpression:HasExpressionForField("eCustomer.CustomerRowid", oExpression) THEN DO: oFieldExpression = QueryExpression:GetExpressionForField ("eCustomer.CustomerRowid", oExpression) . ASSIGN cFindString = SUBSTITUTE ("WHERE ROWID (Customer) = TO-ROWID (&1)", QUOTER (CAST (oFieldExpression:SecondOperand, CharacterHolder):Value)) . ENTRY (2, cRequestContext, CHR (2)) = cFindString . ENTRY (1, poFetchDataRequest:Context, CHR (1)) = cRequestContext . poFetchDataRequest:Context = cRequestContext . END. END. SUPER:FetchData (INPUT poFetchDataRequest). END METHOD. |
...
The above code use-case is extracting the query expression and storing that query into a temporary string variable.
We are then checking to see if the query contains the Progress Function "TO-ROWID", if so, we know that the query is based on our Calculated Field. We subsequently proceed in replacing Progress Function "TO-ROWID" with an empty string. This is done to avoid the Query Parser parsing and segmenting "TO-ROWID" as another filter block option (e.g EQ, BEGINS). Once we have performed our pre-initial operation, we proceed by checking if the Query expression contains our "CustomerRowid" calculated field. If it does, then we manipulate the query to read as our SmartBusinessEntityAdapter QueryString example.
...