Searching data using ROWID's within the SmartBusinessEntityAdapter
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:
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 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
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 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.
DEFINE VARIABLE oAdapter AS SmartBusinessEntityAdapter NO-UNDO . DEFINE VARIABLE roRowid AS ROWID NO-UNDO . FIND Customer WHERE Customer.CustNum = 51 NO-LOCK NO-ERROR . roRowid = ROWID (Customer) . oAdapter = NEW SmartBusinessEntityAdapter ( ) . oAdapter:EntityName = "Sports.Customer.CustomerBusinessEntity":U . oAdapter:EntityTable = "eCustomer":U . /* Give client enough records, so that the ROWID in question is available on the client */ oAdapter:BatchSize = 200 . oAdapter:RetrieveData ( ) .
The second section shows how to query our current Adapter using the "FindRowWhere" method:
oAdapter:FindRowWhere (SUBSTITUTE ("where eCustomer.CustomerRowid = TO-ROWID(&1)", QUOTER (roRowid))). /* Let's show the FindRowWhere Result */ BufferHelper:ShowBuffer(oAdapter:DATASET::eCustomer:HANDLE). /* OR */ MESSAGE oAdapter:GetFieldValues("CustNum":U) VIEW-AS ALERT-BOX.
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:
Within the "OVERRIDE PUBLIC VOID FetchData" method, we need to construct code that manipulates the Query before calling "SUPER:FetchData(INPUT poFetchDataRequest)".
The FetchData use case is given below:
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.
Note: we are basically replacing the relevant "CustomerRowid" segment of the Query.
Once we have manipulated our Query, we then simply assign the Query back into our FetchDateRequest object.
An example use case for Server Side Filtering is given below:
DEFINE VARIABLE oAdapter AS SmartBusinessEntityAdapter NO-UNDO . DEFINE VARIABLE roRowid AS ROWID NO-UNDO . FIND Customer WHERE Customer.CustNum = 10 NO-LOCK NO-ERROR . roRowid = ROWID (Customer) . oAdapter = NEW SmartBusinessEntityAdapter() . oAdapter:EntityName = "Sports.Customer.CustomerBusinessEntity":U . oAdapter:EntityTable = "eCustomer":U . oAdapter:PrefetchOnFindRowWhere = 2 . /* Give client enough records, so that the ROWID in question is available on the client */ oAdapter:BatchSize = 5 . oAdapter:RetrieveData ( ) . oAdapter:FindRowWhere (SUBSTITUTE ("where eCustomer.CustomerRowid = TO-ROWID (&1)", QUOTER (roRowid)), FindRowWhereModeEnum:FindOnServerOnly). /* Let's show the FindRowWhere Result */ BufferHelper:ShowBuffer(oAdapter:DATASET::eCustomer:HANDLE). /* OR */ MESSAGE oAdapter:GetFieldValues("CustNum":U) VIEW-AS ALERT-BOX.