Forums / Developer Forum / Smart Business Entity Adapter - Retrieve Only 1 Record

Smart Business Entity Adapter - Retrieve Only 1 Record

8 posts, 0 answered
  1. Roger Blanchard
    Roger Blanchard avatar
    34 posts
    Registered:
    29 Jun 2018
    03 Dec
    Link to this post
    I have a form with a smart business entity adapter, a smartbindingsource and a smartviewer. We are not using a smart data browser to allow the user to see all records. This is a form in our tablet application where the user scans a barcode and the item record is displayed on the form where the user can update certain fields.

    So, I am not calling ItemAdapter:RetrieveData in the CONSTRUCTOR. Instead, when the barcode is scanned or key entered I am doing the following;

    cWhereString = "WHERE eItem.ItemNum = " + QUOTER (cItemNum) + " AND eItem.PackNum = 1". // eventually use scanner input
      
    THIS-OBJECT:ItemAdapter:EmptyDatasetOnRetrieveData = TRUE.
    THIS-OBJECT:ItemAdapter:BatchSize = 1. // we only want one record
    THIS-OBJECT:ItemAdapter:PrefetchOnFindRowWhere = 0.
    THIS-OBJECT:ItemAdapter:FindRowWhere (cWhereString, FindRowWhereModeEnum:FindOnServerOnly).

    The above code works but there are always two records that are fetched. Is there a way to make sure there is only one record that is returned? 

    Is there a better way to handle this?
  2. Mike Fechner
    Mike Fechner avatar
    23 posts
    Registered:
    14 Sep 2016
    03 Dec in reply to Roger Blanchard
    Link to this post
    BatchSize = 1 does not work. After fetching a single record, the Progress.Data.BindingSource will fetch the next record, because it realizes it was already on the last record available on the client.

    Use BatchSize = 2 instead - or a unique query that only returns a single record.
  3. Roger Blanchard
    Roger Blanchard avatar
    34 posts
    Registered:
    29 Jun 2018
    03 Dec in reply to Mike Fechner
    Link to this post
    I am using a unique query. There is only one record that matches my query.

    For example. my query is "WHERE eItem.ItemNum = 13 AND eItem.PackNum = 1". 

    There is a unique index on the Item table for ItemNum and PackNum.

    After I call FindRowWhere if I dump the dataset to disk it contain not only ItemNum of 13 but the Item record following it (14)

    THIS-OBJECT:ItemAdapter:FindRowWhere (cWhereString, FindRowWhereModeEnum:FindOnServerOnly)



  4. Mike Fechner
    Mike Fechner avatar
    23 posts
    Registered:
    14 Sep 2016
    03 Dec
    Link to this post
    Ok. Did you try setting the Business Entity Adapter's QueryString property to 

    FOR EACH <eTempTableName> WHERE <unique record criteria>

    and then call RetrieveData?
  5. Roger Blanchard
    Roger Blanchard avatar
    34 posts
    Registered:
    29 Jun 2018
    03 Dec in reply to Mike Fechner
    Link to this post
    I did not but will right now.
  6. Roger Blanchard
    Roger Blanchard avatar
    34 posts
    Registered:
    29 Jun 2018
    03 Dec in reply to Mike Fechner
    Link to this post
    Ah, seems to work. The xml on disk only has the one record and the THIS-OBJECT:ItemAdapter:RecordPosition returns "OnlyRecord".

    Let me do a bit more testing.

    thanks
  7. Roger Blanchard
    Roger Blanchard avatar
    34 posts
    Registered:
    29 Jun 2018
    03 Dec in reply to Mike Fechner
    Link to this post
    Okay, this works for my ItemAdapter where the dataset is limited to the unique record.

    This ItemAdapter is also the LinkDataSource for another adapter (HistoryTotsAdapter) that uses ForeignFields = "ItemNum,ItemNum,PackNum,PackNum" as well as well as QueryString = HistoryTots.Period = "M" AND. This HistoryTotsAdapter is the LinkDataSource for a SmartDataBrowse. If I use the QueryString on the ItemAdapter I see no records in the SDB. However, in reviewing the appserver logs the client query, default query and prepare query seem identical. 

    This is without using the QueryString on the ItemAdapter

    [18/12/03@10:55:14.072-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eItem: no
    [18/12/03@10:55:16.699-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: HistoryTots.Period = "M" AND WHERE  eHistoryTots.ItemNum = "           6" AND eHistoryTots.PackNum = "1"
    [18/12/03@10:55:16.699-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH HistoryTots INDEXED-REPOSITION
    [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH HistoryTots WHERE HistoryTots.Period = "M" AND HistoryTots.ItemNum = "           6" AND HistoryTots.PackNum = "1" INDEXED-REPOSITION
    [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer HistoryTots: pItemDate
    [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eHistoryTots: ?
    [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eHistoryTots: ?
    [18/12/03@10:55:16.702-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eHistoryTots: yes

    This is with using the QueryString on the ItemAdapter

    [18/12/03@10:55:45.037-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eItem: yes
    [18/12/03@10:55:45.182-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: HistoryTots.Period = "M" AND WHERE  eHistoryTots.ItemNum = "           6" AND eHistoryTots.PackNum = "1"
    [18/12/03@10:55:45.182-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH HistoryTots INDEXED-REPOSITION
    [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH HistoryTots WHERE HistoryTots.Period = "M" AND HistoryTots.ItemNum = "           6" AND HistoryTots.PackNum = "1" INDEXED-REPOSITION
    [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer HistoryTots: pItemDate
    [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eHistoryTots: ?
    [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eHistoryTots: ?
    [18/12/03@10:55:45.185-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eHistoryTots: yes





  8. Roger Blanchard
    Roger Blanchard avatar
    34 posts
    Registered:
    29 Jun 2018
    03 Dec in reply to Roger Blanchard
    Link to this post
    As a follow up if I add a call to HistoryTotsAdapter:RetrieveData in the ultratab:ActiveTabChanged I then see the records. If you look at the appserver logs you can see RetrieveData was called twice.


    [18/12/03@11:27:27.438-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: FOR EACH eItem WHERE eItem.ItemNum = "           6" AND eItem.PackNum = 1
    [18/12/03@11:27:27.438-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH Item INDEXED-REPOSITION
    [18/12/03@11:27:27.439-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH Item WHERE Item.ItemNum = "           6" AND Item.PackNum = 1 INDEXED-REPOSITION
    [18/12/03@11:27:27.439-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer Item: upItem
    [18/12/03@11:27:27.439-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eItem: ?
    [18/12/03@11:27:27.440-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eItem: ?
    [18/12/03@11:27:27.440-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eItem: yes
    [18/12/03@11:27:27.592-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: HistoryTots.Period = "M" AND WHERE  eHistoryTots.ItemNum = "           6" AND eHistoryTots.PackNum = "1"
    [18/12/03@11:27:27.592-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH HistoryTots INDEXED-REPOSITION
    [18/12/03@11:27:27.595-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH HistoryTots WHERE HistoryTots.Period = "M" AND HistoryTots.ItemNum = "           6" AND HistoryTots.PackNum = "1" INDEXED-REPOSITION
    [18/12/03@11:27:27.595-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer HistoryTots: pItemDate
    [18/12/03@11:27:27.596-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eHistoryTots: ?
    [18/12/03@11:27:27.596-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eHistoryTots: ?
    [18/12/03@11:27:27.596-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eHistoryTots: yes
    [18/12/03@11:27:30.753-0500] P-017108 T-000740 1 AS-4 DataAccess     Client-Query: HistoryTots.Period = "M" AND WHERE  eHistoryTots.ItemNum = "           6" AND eHistoryTots.PackNum = "1"
    [18/12/03@11:27:30.754-0500] P-017108 T-000740 1 AS-4 DataAccess     Default-Query: FOR EACH HistoryTots INDEXED-REPOSITION
    [18/12/03@11:27:30.756-0500] P-017108 T-000740 1 AS-4 DataAccess     Prepare-Query: FOR EACH HistoryTots WHERE HistoryTots.Period = "M" AND HistoryTots.ItemNum = "           6" AND HistoryTots.PackNum = "1" INDEXED-REPOSITION
    [18/12/03@11:27:30.756-0500] P-017108 T-000740 1 AS-4 DataAccess     Index Information on Buffer HistoryTots: pItemDate
    [18/12/03@11:27:30.756-0500] P-017108 T-000740 1 AS-4 DataAccess     Restart-Rowid for Table eHistoryTots: ?
    [18/12/03@11:27:30.757-0500] P-017108 T-000740 1 AS-4 DataAccess     Next-Rowid for Table eHistoryTots: ?
    [18/12/03@11:27:30.757-0500] P-017108 T-000740 1 AS-4 DataAccess     Last-Batch for Table eHistoryTots: yes
8 posts, 0 answered