ProDataSets

1. ProDataSets John Campbell CTO, ProStar Software

2. Short Introduction Delivering Excellence in the Progress Community since 1985 Our mottos is how we work Creators of TailorPro Authors, consultants, trainers, developers

3. Overview • What They Are • Why They Were Invented • How They Work • How They can be Used

4. Technical Introduction • Comparison with Temp-tables • Similarities • Provides standard means of normalizing and handling data • Differences • Container for Multiple temp-tables • Richer Functionality • Conceptually more intricate

5. What Else You Need to Know • Designed to work efficiently • Locally & Across app server • Using multiple data sources • With .NET

6. Concepts • Collection of temp-tables • Static or Dynamic • We'll use static for clarity • Multiple "kinds" of data

7. Examples of data in ProDataSets • Related "family" sets • Order-orderline • Patient-appointment • Can contain Lookup data • Customer-order and state table

8. Code Sample –Temp Table Defs define temp-table ttOrder like Order field OrderTotal as decimal field CustName like Customer.name field RepName like SalesRep.RepName. define temp-table ttOline like OrderLine. define temp-table ttItem field ItemNum like item.ItemNum field ItemName like item.ItemName field Price like item.Price field Weight like item.Weight field OnHand like item.OnHand field OnOrder like item.OnOrder.

9. Code Sample DataSet Definition define dataset dsOrder for ttOrder, ttOline, ttItem data-relation OrderLine for ttOrder, ttOline relation-fields (OrderNum, OrderNum) data-relation LineItem for ttOline, ttItem relation-fields (ItemNum, ItemNum)

10. Data-relations • Internal definition of relation • Roadmap for navigation • Defines an implicit query

11. Field pairs • Explicit denotation of fields • First from parent • Second from child

12. Associating a DataSet define variable hDSOrder as handle no-undo. assign hDSOrder = dataset dsOrder:handle

13. Data-source • Allows various sources of data • Progress • XML, Excel etc. • Foreign DBs

14. Code Sample DataSource define data-source srcOline for OrderLine keys (OrderNum).

15. Progress Data-sources • Query • Buffer • Can use both in a dataset

16. Specifying “Keys” • Creates an explicit key for eliminating duplicates

17. Datasource and Dataset • Why data-source is distinct from dataset • Dataset may need to draw data from multiple sources • Different DBs, non-db sources • A data source is only meaningful locally • Attached to one dataset at a time

18. DataSources and Queries • Queries are not mandatory • When you Don't need them • Implicit relation is adequate • You want all records from a table • When You Want them • Custom filters of data • Mid-level or top-level tables

19. Attaching a Data Source • Only possible with handles • Still possible with static temp tables • Field Mapping for asymmetric names • Not all DBs use the same name for the same field value

20. Attaching A DataSource buffer ttOline:attach-data-source(data-source srcOline:handle).

21. Populating a Dataset • If using a query as datasource, "prepare" the query first • Static queries not permitted for filling • You've done all the work, so it's easy: • Dataset-handle:fill().

22. Attaching A DataSource hDSOrder:fill().

23. What does fill do? 1. Does a get-first on top-level query 2. Creates and populates top-level record 3. Prepares child query, opens, does get-first, creates child record 4. Repeats step 3 for all child records 5. Repeats steps 1-4 with get-next.

24. Fill-Mode • What it is • Why it exists

25. Types of Fill-mode • Empty • No-fill • Append • Merge (default)

26. Multiple Datasets • Using the same temp table in multiple datasets • Different datasets with different data

27. Attributes, Methods and Events • There are 15 attributes • For managing fills • Articulating the structure • Handling errors • Misc. • Sixteen methods • Filling and Saving Data • Creating and Instantiating the Dataset • Misc. • Eight Events • For manipulating data during fill or change

28. Passing datasets • Statically • Dynamically • Dataset-handle • Handle • Static to Dynamic

29. Passing Datasets – cont’d • Local • Remote • Implications of Static vs. dynamic

30. By-reference vs. by-value • Progress' default is by-value for temp-tables • By-value copies the whole structure and data • By-reference reduces overhead • Automatic, dynamic pointer to the static structure • Irrelevant in remote calls

31. Importance of deleting Datasets • If it's dynamic remember Bruce's Law • "You create it, you delete it" • (Even if it's an input dataset-handle parameter) • It takes up memory

32. Schema-marshal • New attributes for all temp-tables • Not just dataset temp-tables • Use if static definition exists on the client • Reduces network traffic for big tables • No-schema-marshal leaves the schema behind • Min-schema-marshal leaves formats, labels, help etc behind

33. Finer Points* –Why ProDataSetsare SO Powerful * i.e., more technical stuff

34. Fill Events • Two on dataset • Two on temp-table • Two on individual row

35. Event Types • Before-fill (Dataset and Temp-Tables) • After-fill (Dataset and Temp-Tables) • Before- and After-row-fill

36. Uses • Database Connections • Preparing unique queries • Detach data-sources • Verify, accept, reject fills • Modify data

37. Set-callback-procedure method • Three Parameters • Callback-type • Before-fill, after-row-fill, etc. • Internal Procedure Name • Name of procedure to run on this event • Procedure Context Handle • Default is this-procedure • Could be another persistent procedure

38. Dataset States • Fill mode • Navigation Mode • Change Mode • Fill and navigation are logically the same • Change mode has special circumstances

39. Change Tracking • A little like "old buffer" concept of schema triggers • Ability to compare old and new values of data • Handled by the tracking-changes attribute • Done at the temp-table level

40. Importance of no-undo • Temp-tables with change tracking must NOT be no-undo • Gives a runtime error • Standard before-imaging not used with no-undo • LBI tracking essential for tracking changes

41. Row-state Attribute • Row-unmodified • Row-deleted • Row-modified • Row-created • Integer values (like lock levels and other things)

42. Before and After Tables • Before table keeps original record image • After table is modified or created record

43. What’s in the after-table • The after-table is the modified copy of the record • No record buffer is in the after-table if it is deleted • Also contains newly created records

44. What's In the Before Table? • Unless changes are made, it is empty • The before-table is the original copy of the record • There IS a record buffer in the before-table for deleted after-table records • No syntax available to modify before-table records • (Once placed there, they’re unchangeable)

45. Creating Tracking Table • Before-table • For static Temp-Tables, they are only definable at Compile Time • For dynamic Temp-Tables, this can be done at runtime

46. Other Before & After Info • Both are part of the dataset • Both are passed with the dataset • Both have row-state attributes • The same is true for row-modified

47. More “B&A” Information • No record in before-table for row-unmodified • No record in after-table for row-deleted • Blank (template) record in before-table for row-created • Original record in before-table for row-modified

48. Dataset Change Tracking • Use a "change" dataset • Use create-like the origin dataset • This is the original dataset • You will copy changes in the original to it

49. Merge-changes • A dataset is returned to the server from the client • If the server modifies any rows, it can track changes and errors • Server side changes can then be "copied" into the origin DB • Errors can be passed at the temp-table or row level • Merge-changes permits server changes to be visible on the client

50. More on merging • You can accept or reject changes at three levels • Dataset • Temp-table • Individual Row